How to enable trace in Oracle

1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

sql_trace = TRUE

to disable trace:

sql_trace = FALSE

- or -

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

to stop trace run:

SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;


2. Enable trace at session level

to start trace:

ALTER SESSION SET sql_trace = TRUE;

to stop trace:

ALTER SESSION SET sql_trace = FALSE;

- or -

EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);

- or -

EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;


3. Enable trace in another session

Find out SID and SERIAL# from v$session. For example:

SELECT * FROM v$session WHERE osuser = OSUSER;

to start trace:

EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop trace:

EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

- or -

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);



SQL> select sid, serial# from v$session where username = 'USER';

SQL> alter system kill session 'SID,SERIAL#';

4 comments:

Anonymous said...

Nice info, Mani.

--Moid M

Microsoft Office 2007 said...

NEVERTHELESS, THE CIVIL LAW is and must be neutral about who has a more noble or rewarding faith. The breakaway parishes ought to win every Office 2010facet of the lawsuit not becauseMicrosoft Office 2010 their beliefs or their politics are better, Microsoft wordbut because both lawOffice 2007and equity, along with common sense, are on Microsoft Officetheir side.Microsoft Office 2007 Not only does Virginia state law (the Division Statute)Office 2007 keyexplicitly apply to just such a Office 2007 downloadsituation as now exists, but the history Office 2007 Professionalespecially of The Falls Church argues against the claims of Outlook 2010the Virginia Diocese with which theyMicrosoft outlookhave disassociated.Microsoft outlook 2010First, The Falls Church wasWindows 7 founded, formed, and developed long before the diocese, or the national Episcopal Church, even existed.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

Unknown said...

Hindustan Copper Limited Recruitment Notification 2017

 Download TSPSC Beat Officer,Section Officer posts Hall Tickets 2017