PLS-00201: identifier ‘DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION’ must be declared

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"

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s