Shulou(Shulou.com)06/01 Report--

Http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm

The package dbms_system is defined as follows:

SQL > desc dbms_system

PROCEDURE ADD_PARAMETER_VALUE

Argument Name Type In/Out Default?

PARNAME VARCHAR2 IN

VALUE VARCHAR2 IN

SCOPE VARCHAR2 IN DEFAULT

SID VARCHAR2 IN DEFAULT

POSITION BINARY_INTEGER IN DEFAULT

PROCEDURE DIST_TXN_SYNC

Argument Name Type In/Out Default?

INST_NUM NUMBER IN

PROCEDURE GET_ENV

Argument Name Type In/Out Default?

VAR VARCHAR2 IN

VAL VARCHAR2 OUT

PROCEDURE KCFRMS

PROCEDURE KSDDDT

PROCEDURE KSDFLS

PROCEDURE KSDIND

Argument Name Type In/Out Default?

LVL BINARY_INTEGER IN

PROCEDURE KSDWRT

Argument Name Type In/Out Default?

DEST BINARY_INTEGER IN

TST VARCHAR2 IN

PROCEDURE READ_EV

Argument Name Type In/Out Default?

IEV BINARY_INTEGER IN

OEV BINARY_INTEGER OUT

PROCEDURE REMOVE_PARAMETER_VALUE

Argument Name Type In/Out Default?

PARNAME VARCHAR2 IN

VALUE VARCHAR2 IN

SCOPE VARCHAR2 IN DEFAULT

SID VARCHAR2 IN DEFAULT

PROCEDURE REMOVE_PARAMETER_VALUE

Argument Name Type In/Out Default?

PARNAME VARCHAR2 IN

POSITION BINARY_INTEGER IN

SCOPE VARCHAR2 IN DEFAULT

SID VARCHAR2 IN DEFAULT

PROCEDURE SET_BOOL_PARAM_IN_SESSION

Argument Name Type In/Out Default?

SID NUMBER IN

SERIAL# NUMBER IN

PARNAM VARCHAR2 IN

BVAL BOOLEAN IN

PROCEDURE SET_EV

Argument Name Type In/Out Default?

SI BINARY_INTEGER IN

SE BINARY_INTEGER IN

EV BINARY_INTEGER IN

LE BINARY_INTEGER IN

NM VARCHAR2 IN

PROCEDURE SET_INT_PARAM_IN_SESSION

Argument Name Type In/Out Default?

SID NUMBER IN

SERIAL# NUMBER IN

PARNAM VARCHAR2 IN

INTVAL BINARY_INTEGER IN

PROCEDURE SET_SQL_TRACE_IN_SESSION

Argument Name Type In/Out Default?

SID NUMBER IN

SERIAL# NUMBER IN

SQL_TRACE BOOLEAN IN

PROCEDURE WAIT_FOR_EVENT

Argument Name Type In/Out Default?

EVENT VARCHAR2 IN

EXTENDED_ID BINARY_INTEGER IN

TIMEOUT BINARY_INTEGER IN

First, open the sql_trace function for other windows. This function can be turned on before or after the execution of sql.

-- enable sql_trace function

Exec dbms_system.set_sql_trace_in_session (42, 1853, true)

-- disable the sql_trace function

Exec dbms_system.set_sql_trace_in_session (42, 1853, false)

-- query the location of trace files:

Select c.value | |'/'| | d.instance_name | |'_ ora_' | | a.spid | | '.trc' trace

FROM v$process a, v$session b, v$parameter c, v$instance d

WHERE a.addr = b.paddr

AND b.audsid = userenv ('sessionid')

AND c.name = 'user_dump_dest'

Second, open the 10046 event for other windows. This function can be turned on before or after the execution of sql.

-- start the 10046 event

Exec dbms_system.set_ev (42, 1853, 10046, 12, 12)

-- close the 10046 event

Exec dbms_system.set_ev (42, 1853, 100, 46, 0 and 0)

-- query the location of trace files:

Select c.value | |'/'| | d.instance_name | |'_ ora_' | | a.spid | | '.trc' trace

FROM v$process a, v$session b, v$parameter c, v$instance d

WHERE a.addr = b.paddr

AND b.audsid = userenv ('sessionid')

AND c.name = 'user_dump_dest'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.