How Oracle tracks SQL sessions

There are many ways to track an SQL session in Oracle, which is officially recommended for use in Oracle 11g dbms_monitor.

In the actual production environment, try to be careful with this operation, as far as possible to find a specific session ID or PID to track.

--New test table

Create Tablespace Test

DataFile ' E:\APP\ADMINISTRATOR\ORADATA\ORCL\test01. DBF '

Size 500m

Autoextend on

Next 100m MaxSize Unlimited

Extent Management Local Autoallocate

Segment Space management auto;

CREATE TABLE T1

(

SID int NOT NULL primary key,

Sname VARCHAR2 (10)

)

Tablespace test;

--Loop Import data

Declare

MAXRECORDS constant int:=100000;

I int: = 1;

Begin

For I in 1..maxrecords loop

INSERT into T1 values (i, ' Ocpyang ');

End Loop;

Dbms_output.put_line (' Successful data entry! ');

Commit

End

/

Select Se.sid,

se.serial#,

Pr.spid,

Se.status State,

SUBSTR (Se.program, 1, 20) "program",

SUBSTR (Se.machine, 1, 30) "Machine name",

Sq.sql_text "SQL Text"

From V$session SE,

V$sqlarea Sq,

V$process PR

where se.paddr = pr.addr (+)

and se.sql_address = sq.address (+)

and SchemaName <> ' SYS '

and Se.username=upper (' &username ')

ORDER BY Se.sid

;

SID    serial# SPID           status       procedure                   machine name       SQL text                                                   &NB Sp                                   &NB Sp                                   &NB Sp                                   &NB Sp                                   &NB Sp                                   &NB Sp                                  &NB Sp                          ---------------------------- ------ -------- -------------------- ---------- ---------------- -

171 25079 1539784 INACTIVE sqlplus.exe workgroup\log select * from t1

Execute dbms_monitor.session_trace_enable (session_id=>171,serial_num=> 25079,waits=>true,binds=> FALSE);

SELECT * from T1;

Execute dbms_monitor.session_trace_disable ();