I have a database just upgraded from 12c to 19c. Today I noticed there were some objects (packages & triggers) in invalid status.

When I tried to compile one of simple trigger and I got the following errors:

SQL> alter trigger RELEASE_LOCK compile;

Warning: Trigger altered with compilation errors.

SQL> show error
Errors for TRIGGER RELEASE_LOCK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/11     PL/SQL: SQL Statement ignored
5/29     PL/SQL: ORA-00942: table or view does not exist

The trigger itself is quite simple — it queries V$SESSION & DBA_DML_LOCKS and try to kill any sessions having row exclusive lock “Row-X (SX)” waiting longer than 30 seconds on a table.

CREATE OR REPLACE
TRIGGER USER1.RELEASE_LOCK BEFORE INSERT ON CUSTOMER_LOCK FOR EACH ROW
DECLARE
    SESSIONID number;
    SERIALNUMBER number;
BEGIN
    BEGIN
        SELECT SID, SERIAL# INTO SESSIONID, SERIALNUMBER FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = 'CUSTOMER' AND MODE_HELD = 'Row-X (SX)') AND BLOCKING_SESSION IS NULL AND LOCKWAIT IS NULL AND SECONDS_IN_WAIT > 30;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN SESSIONID := NULL;
    END;
    IF (SESSIONID IS NOT NULL AND SERIALNUMBER IS NOT NULL) THEN
        EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || SESSIONID || ',' || SERIALNUMBER || '''');
    END IF;
END;

So only 4 tables & views are invovled and I’ve confirmed this user “USER1” can query them without any issues — I used “select count(*) from” on 4 tables & views when login as this user.

Also SESSION_PRIVS showed “SELECT ANY DICTIONARY” & “ANALYZE ANY DICTIONARY” for this user.

SQL> select * from session_privs where PRIVILEGE like '%DIC%';

PRIVILEGE
----------------------------------------
ANALYZE ANY DICTIONARY
SELECT ANY DICTIONARY

SQL> select * from USER_SYS_PRIVS where PRIVILEGE like '%DIC%';

USERNAME             PRIVILEGE                                ADM COM INH
-------------------- ---------------------------------------- --- --- ---
AS_SYS               SELECT ANY DICTIONARY                    NO  NO  NO
AS_SYS               ANALYZE ANY DICTIONARY                   NO  NO  NO

I could use the exact statemtns (syntax) on another test system to create the trigger correctly with the same user.

Not sure what was wrong, I just blindly re-grant “SELECT ANY DICTIONARY” to this user and it worked after. That’s very strange. Anyway, to check privileges for a user, the following views are quite handy:

• DBA_SYS_PRIVS
• DBA_TAB_PRIVS
• DBA_ROLE_PRIVS
• USER_SYS_PRIVS
• USER_TAB_PRIVS
• USER_ROLE_PRIVS
• SESSION_PRIVS

• Oracle