Cause:
Appropriate permissions are not in place for the procedure,function or package for user!
SQL> exec dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace= >TRUE); BEGIN dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=>TRU E); END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored SQL> exec dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=TRUE); BEGIN dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=TRUE); END; * ERROR at line 1: ORA-06550: line 1, column 67: PLS-00201: identifier 'SQL_TRACE' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
Solution:
Even though the user has dba privilege,still the user couldnot execute the packages of SYS and you can also create a public synonym for this package
Grant the privilege using sysdba account
SQL> conn / as sysdba Connected. SQL> create public synonym dbms_system for dbms_system; Synonym created. SQL> grant execute on dbms_system to test; Grant succeeded. SQL> grant all on dbms_system to test; Grant succeeded.
Check the privilege given
SQL> select distinct 'GRANT '||privilege||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE from DBA_TAB_PRIVS where grantee='TEST'; 'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE -------------------------------------------------------------------------------- GRANT EXECUTE ON SYS.DBMS_SYSTEM TO TEST GRANT DEBUG ON SYS.DBMS_SYSTEM TO TEST
It works fine now
SQL> exec dbms_system.set_sql_trace_in_session(45,14530,TRUE); PL/SQL procedure successfully completed. SQL> show user USER is "TEST"