获得Export_Import的trace文件

文章作者 100test 发表时间 2007:05:15 12:19:37
来源 100Test.Com百考试题网

可以应用在下面的环境下:
任意操作系统平台下
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0

目标:
获得imp/exp会话sql的trace文件

方法:
有4种方法可以获得imp/exp会话sql的trace文件.

1.在系统级别设置10046事件.

alter system set events 10046 trace name context forever, level 12.

2.使用dbms_system.set_sql_trace_in_session

用下面的查询从v$session获得sid,serail#,program:

0select sid,serial#,program from v$session where program like %exp% or %imp%.

execute dbms_system.set_sql_trace_in_session(sid,serial#,True).
这里的sid和serial#是从前面的查询中得到的

3.使用oradebug:

0select s.sid, p.pid, p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid = [X].

spid指示了exp/imp进程在操作系统中的pid.

使用SYS(internal或AS SYSDBA)身份来连接数据库

oradebug setospid [X]
设置spid

oradebug event 10046 trace name context forever, level 12
打开跟踪

4.选项trace=y

在exp/imp中设置trace=y,可以激活sql_trace和timed_statistics.

这个特性在8.1.6版本之后才可用.

缺省情况下,trace选项为no.

这是最简单的获得imp/exp会话sql的trace文件的方法.

exp a1/a1 file=expa1.dmp log=expa1.log trace=y

这个exp会话会在user_dump_dest的目录中产生10046的trace文件.

导入也一样:

imp system/ fromuser=a1 touser=b1 file=expa1.dmp log=impb1.log trace=y

这个imp会话会在user_dump_dest的目录中产生10046的trace文件.


实验:
1、测试开始，窗口1
oracle@yang:~> sqlplus hr/hr
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Apr 21 18:11:42 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production


窗口2
oracle@yang:~> sqlplus / as sysdba
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Apr 21 18:19:13 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> show user
USER is "SYS"

2、查找spid并没置event

SQL> 0select pid,spid from v$process where addr in (0select paddr from v$session where username=HR).
PID SPID
---------- ------------
15 8882

SQL> oradebug setospid 8882
Oracle pid: 15, Unix process pid: 8882, image: oracle@yang (TNS V1-V3)

SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.

SQL> oradebug tracefile_name
Statement processed.
--因hr用户窗口没执行sql，故trace文件还未生成

[1] [2] 下一页