The installation of UIM fails with an Oracle error in the installation log:
PLS-00905: object UIM.SPN_BAS_CREATECONSTRAINT is invalid
ORA-06550: line 71, column 5:
The installation of UIM created the stored procedure. However, Oracle has it marked as INVALID.
Oracle 11gR2, 12c
1- Delete the tablespace, data files, and contents in Oracle:
DELETE TABLESPACE <uim_tablespace> INCLUDING CONTENTS AND DATA FILES;
2- Drop the user. The reason for this is to remove any dangling objects linked to the user.
DROP USER <uim_database_user> CASCADE;
3- Recreate the tablespace and user as appropriate for Oracle 11 or 12, reference: UIM installation guide
4- Please validate with the DBA that the UIM database user has these permissions. These are a requirement.
grant unlimited tablespace to db_owner;
grant administer database trigger to db_owner;
grant create table to db_owner;
grant create view to db_owner;
grant alter any table to db_owner;
grant select any table to db_owner;
grant create sequence to db_owner;
grant create procedure to db_owner;
grant create session to db_owner;
grant create trigger to db_owner;
grant create type to db_owner;
grant select on sys.v_$session to db_owner;
grant execute on sys.dbms_lob to db_owner;
grant execute on dbms_redefinition to db_owner;
grant create any table to db_owner;
grant drop any table to db_owner;
grant lock any table to db_owner;
In Oracle, if an object (stored procedure, View, etc...) is marked as INVALID, it would normally get marked as VALID, the next time it executes.
The exceptions are:
1- Something is syntactically wrong with the object's DDL
2- One of the object's dependencies is also INVALID.
In this case, SPN_BAS_CREATECONSTRAINT referenced the stored procedure SPN__LOGEVENT.
The procedure SPN__LOGEVENT referenced V$SESSION in it's DDL. The UIM user was not able to query v$session, which highlighted that the user did not have the correct permissions.
Please work with your DBA to identify the correct steps were followed to setup the Oracle environment for UIM: UIM Installation Guide
Sample query to find an object's dependencies:
TYPE || ' ' ||
OWNER || '.' || NAME || ' references ' ||
REFERENCED_TYPE || ' ' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME
Where name = UPPER(LTRIM(RTRIM( 'SPN__LOGEVENT' )))
AND (REFERENCED_OWNER <> 'SYS'
AND REFERENCED_OWNER <> 'SYSTEM'
AND REFERENCED_OWNER <> 'PUBLIC'
AND (OWNER <> 'SYS'
AND OWNER <> 'SYSTEM'
AND OWNER <> 'PUBLIC'
order by OWNER, name,