How to turn trace and set events using dbms_system?

To turn on trace for a specific event.
exec dbms_system.set_ev(<SID>, <serial#>, <event>, 8, '');
Example: Enable trace at level 8 for session id 10046
exec dbms_system.set_ev(12345, 543211, 10046, 8, '');

– To turn off the tracing:
exec dbms_system.set_ev(<SID>, <serial#>, <event>, 0, '');
Example: exec dbms_system.set_ev( 1234, 56789, 10046, 0, '');

How to set max_dump_file_size on a running session?

Using dbms_system.set_int_param_in_session, one can set max_dump_file_size of a running session. In the example it sets trace file to 100MB
exec sys.dbms_system.set_int_param_in_session(sid => <sid>, serial# => <serial#>, parnam => 'MAX_DUMP_FILE_SIZE', intval => 100*1024*1024);

How to force an error to generate a trace file when an ORA error occurs?

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:
– ORA-1652: unable to extend temp .. (for temp tablespace)
SQL> ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK LEVEL 3′;

It will write to the alert.log
Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off one would run the following SQL:
SQL> ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
SQL> alter system set event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on event for.

Job to delete log and trace files from OEM

I use this script to clean up all the log and trace file from each host..  You can schedule a job in OEM to go and execute this in all hosts you want.

$cat /etc/oratab

ESRTSP:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
ESRTSS:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
RESTST:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
LISTENER_10g:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
AGENT_10g:/oracle/app/EPW/agent/v10203_ee_suse10:N

#!/bin/ksh

export PATH=/usr/bin:$PATH
export USERNAME=`whoami`
export HOSTNAME=`hostname`

#
#      Clean up Oracle Export logs
#
find /work/dba/logs -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /tmp -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /work/dba/prod/backups -name ‘tmp_ora_${HOSTNAME}_*’ -mtime +3 -exec rm -r {} \;

###############################################
###############################################
##  ADD ALL INSTANCES TO THE FOR LOOK LIST   ##
###############################################
###############################################

for SID in `cat /etc/oratab|egrep ‘:N|:Y’|grep -v \*|cut -f1 -d’:'|egrep -v  ‘LISTENER|AGENT’`
do

find /oracle/EPW/${SID}/admin/udump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/cdump -name ‘core*’ -mtime +13 -exec rm -r {} \;
find /oracle/EPW/${SID}/admin/bdump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/adump -name ‘*.aud’ -mtime +13 -exec rm {} \;

done

Clean up script with oratab

A simple script to delete trace files and arc logs from remote location, based on oratab instances.

#!/bin/ksh

export PATH=/usr/bin:$PATH
export USERNAME=`whoami`
export HOSTNAME=`hostname`

########################################
#  Copy TNS Names file to local disk   #
########################################
/bin/ksh /work/dba/monora/scripts/update_local_tns.ksh > /tmp/update_local_tns.log 2>&1
#
#      Clean up Oracle Export logs
#
find /work/dba/logs -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /tmp -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /work/dba/prod/backups -name ‘tmp_ora_${HOSTNAME}_*’ -mtime +3 -exec rm -r {} \;

for SID in `cat /etc/oratab|egrep ‘:N|:Y’|grep -v \*|cut -f1 -d’:'|egrep -v  ‘LISTENER|AGENT’`
do
find /oracle/EPW/${SID}/admin/udump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/cdump -name ‘core*’ -mtime +13 -exec rm -r {} \;
find /oracle/EPW/${SID}/admin/bdump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/adump -name ‘*.aud’ -mtime +13 -exec rm {} \;

## To delete ach logs from remote online location for dev/test/training env that are in arch log mode
find /oracle/EPW/arch/${SID} -name ‘${SID}*.arc’ -mtime +3 -exec rm {} \;
done

How to grant SELECT access to v$session to other users?

One can’t grant direct access V$session as v$session is a synonym.

SQL> GRANT SELECT ON v$session TO scott;
grant select on v$session to test
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

– shows the V$SESSION is a public synonym
SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;

OWNER OBJECT_TYPE
———————— ——————-
PUBLIC SYNONYM

– shows the object (table/view) the synonym points to
SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;

TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION

But one can grant access the underlying table/view.

SQL> GRANT SELECT ON V_$SESSION TO scott;

Grant succeeded.

Note: The same steps can be used to find other V$ views and access can be granted depending on internal representation.

How to find the trace file name?

– One can use the following query to find the name of the trace filename

– To find the name of the current session
select instance_name || ‘_ora_’ || spid || ‘.trc’ filename
from v$process p, v$session s, v$instance
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum = 1);

FILENAME
——————————
orcl_ora_1360.trc

– To find the filename for another known SID
select instance_name || ‘_ora_’ || spid || ‘.trc’ filename
from v$process p, v$session s, v$instance
where p.addr = s.paddr
and s.sid = 170;

FILENAME
——————————
orcl_ora_2552.trc

How to find your session id (SID)?

Using one of the following you can find your session id (SID).

SQL> SELECT sid FROM v$mystat WHERE rownum = 1;

SID
———-
145

SQL> SELECT sid FROM V$SESSION WHERE audsid = userenv(’sessionid’);

SID
———-
145

SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT(‘userenv’,’sessionid’);

SID
———-
145

Different options to display execution plan and statistics from SQL*Plus?

– Turn off display of execution plan and statistics
SET AUTOTRACE OFF

– show user query output, execution plan
SET AUTOTRACE ON EXPLAIN

– display output from query and statistics
SET AUTOTRACE ON STATISTICS

– show user query output, execution plan and statistics
SET AUTOTRACE ON

– set trace on but suppress output from query, display execution plan and statistics
SET AUTOTRACE TRACEONLY

To be able view the trace using the above option the user needs PLUSTRACE role assigned, the role is created by running the script which is ORACLE_HOME @?sqlplus/admin/plustrce.sql to be run as SYS. If the PLUSTRACE role is not granted you will get the following error “SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled”

Setup PLAN_TABLE for all schema users

Each schema may require a PLAN_TABLE to debug performance so instead of creating the PLAN_TABLE within each schema, one can do the following:

– ? is the ORACLE_HOME
SQL> connect system
Password:
SQL> @?/rdbms/admin/utlxplan.sql
– create public synonym
SQL> CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
– Grant everyone access to PLAN_TABLE
SQL> GRANT ALL ON PLAN_TABLE TO PUBLIC;