oracle追踪特定sql语句

--新建测试表

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;

--循环导入数据

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(' 成功录入 数据！ ');

   commit;

   end;

/

create table t2

as

select * from t1 where rownum<=5000;

oracle 11.1中可以使 用增强的SQL跟踪来追逐一条或多条SQL语句.

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

案例1：跟踪一组 SQL

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

--步骤0)初始化参数

---开启timed_statistics 参数

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  --需要设置为true

---查看所有trace文件路径

select name,value from v$diag_info where name='Diag Trace';

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

--步骤1).设置追踪标示

alter session set tracefile_identifier='ocpyang01';