Oracle tracks specific SQL statements

--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

/

CREATE TABLE T2

As

SELECT * from t1 where rownum<=5000;

An enhanced SQL Trace can be used in Oracle 11.1 to chase one or more SQL statements.

---------------------------------- ---------------------------------------------------------

Case 1: Tracking a set of SQL

-------------------------------------------------------------------------- -----------------

--Step 0 initialization parameters

---open timed_statistics parameters

Show parameters statistics;

NAME TYPE VALUE

------------------------------------ -------- --- ------------------------------

Optimizer_use_pending_statistics Boolean FALSE

Statistics_level string Typical

Timed_os_statistics integer 0

Timed_statistics Boolean true--needs to be set to True

---View all trace file paths

Select Name,value from V$diag_info where name= ' diag Trace ';

E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace

--Step 1). Set the tracking mark

Alter session set tracefile_identifier= ' OCPYANG01 ';