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"
