Installing DBMS_SUPPORT package

Below Error occurs when you are trying to trace a user session using the DBMS_SUPPORT package.

ORA-06550: line 18, column 4:

PLS-00201: identifier 'DBMS_SUPPORT.START_TRACE_IN_SESSION' must be declared

ORA-06550: line 18, column 4:

PL/SQL: Statement ignored

You can install it using the script dbmssupp.sql

cd $ORACLE_HOME/rdbms/admin

[oracle@oracle-dg admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 15 15:53:47 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @dbmssupp.sql

Package created.

Package body created.

SQL> exit

Start/Stop the trace for all the oracle sessions specific to the database user .

Start the oracle trace

declare

my_sid       V$SESSION.SID%type;

my_serial_no V$SESSION.SERIAL#%type;

begin

for a in (

            select SID

            from   V$SESSION

            where  USERNAME = 'username'

         )

loop

   select SID, SERIAL#

   into   my_sid, my_serial_no

   from   V$SESSION

   where  SID = a.SID;

   dbms_output.put_line('sid: '||my_sid);

   dbms_support.start_trace_in_session(my_sid,my_serial_no,true,true);

  

end loop;

end;

/

Stop the oracle trace

declare

my_sid       V$SESSION.SID%type;

my_serial_no V$SESSION.SERIAL#%type;

begin

for a in (

            select SID

            from   V$SESSION

            where  USERNAME = 'username'

         )

loop

   select SID, SERIAL#

   into   my_sid, my_serial_no

   from   V$SESSION

   where  SID = a.SID;

   dbms_output.put_line('sid: '||my_sid);

    dbms_support.stop_trace_in_session(my_sid,my_serial_no);

  

end loop;

end;

/