Installation errors for 9.02 SPN_BAS_CREATECONSTRAINT is invalid

Document ID : KB000126873
Last Modified Date : 13/02/2019
Show Technical Document Details
Issue:
Oracle error during create script; install exited.
2019-02-12 15:40:27,077 ERROR impl.UIMServerDBSchemaController:run:163 [main]  - NimException caught
(503) , java.sql.SQLException: ORA-06550: line 71, column 5:
PLS-00905: object UIM.SPN_BAS_CREATECONSTRAINT is invalid
ORA-06550: line 71, column 5:
PL/SQL: Statement ignored
: ORA-06550: line 71, column 5:
PLS-00905: object UIM.SPN_BAS_CREATECONSTRAINT is invalid
ORA-06550: line 71, column 5:
PL/SQL: Statement ignored

 
Environment:
UIM 9.0.2
Oracle 12.1.02
Cause:
The CA_UIM schema did not have permissions to v$session.
As a result, the stored procedure: SPN_LOGEVENT was marked invalid.
All other stored procedures that have SPN_LOGEVENT as a dependency were also marked invalid.

An easy way to troubleshoot this would be to use Oracle SQL Developer, connect as the "CA_UIM" user and expand the "Procedures" folder.
Look for any stored procedures marked with a red X.
Open those stored procedure.
The editor will mark any missing or invalid objects with a red underline.
Resolution:
In Oracle:
DROP USER CA_UIM CASCADE;
DROP TABLESPACE CA_UIM INCLUDING CONTENTS AND DATAFILES;

Next, follow the instructions documented here to create the CA_UIM user, tablespace and grant permissions.
https://docops.ca.com/ca-unified-infrastructure-management/9-0-2/en/installing/pre-installation-planning/install-and-configure-your-database-software/oracle#Oracle-GrantPermission
 
Re-run the installer for CA  UIM 9.0.2