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 ();