可以應(yīng)用在下面的環(huán)境下:
任意操作系統(tǒng)平臺(tái)下
Oracle Server - EnterPRise Edition - Version: 8.1.7.4 to 10.2.0.0
目標(biāo):
獲得imp/eXP會(huì)話sql的trace文件
方法:
有4種方法可以獲得imp/exp會(huì)話sql的trace文件.
1.在系統(tǒng)級(jí)別設(shè)置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:
select 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:
select 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進(jìn)程在操作系統(tǒng)中的pid.
使用SYS(internal或AS SYSDBA)身份來連接數(shù)據(jù)庫
oradebug setospid [X]
設(shè)置spid
oradebug event 10046 trace name context forever, level 12
打開跟蹤
4.選項(xiàng)trace=y
在exp/imp中設(shè)置trace=y,可以激活sql_trace和timed_statistics.
這個(gè)特性在8.1.6版本之后才可用.
缺省情況下,trace選項(xiàng)為no.
這是最簡(jiǎn)單的獲得imp/exp會(huì)話sql的trace文件的方法.
exp 'a1/a1' file=expa1.dmp log=expa1.log trace=y
這個(gè)exp會(huì)話會(huì)在user_dump_dest的目錄中產(chǎn)生10046的trace文件.
導(dǎo)入也一樣:
imp system/ fromuser=a1 touser=b1 file=expa1.dmp log=impb1.log trace=y
這個(gè)imp會(huì)話會(huì)在user_dump_dest的目錄中產(chǎn)生10046的trace文件.
實(shí)驗(yàn):
1、測(cè)試開始，窗口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> select pid,spid from v$process where addr in (select 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用戶窗口沒執(zhí)行sql，故trace文件還未生成
3、hr窗口執(zhí)行sql
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
8 rows selected.
SQL> select * from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------------- ----------
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-87 AD_PRES
24000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-89 AD_VP
17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-93 AD_VP
17000 100 90
4、重新檢查trace是否生成,并關(guān)掉10046
SQL> oradebug tracefile_name;
/opt/oracle/admin/yang/udump/yang_ora_8882.trc
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> exit
5、查看trace
oracle@yang:~> vi /opt/oracle/admin/yang/udump/yang_ora_8882.trc
opt/oracle/admin/yang/udump/yang_ora_8882.trc
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
ORACLE_HOME = /opt/oracle/product/9ir2
System name: linux
Node name: yang
Release: 2.4.21-138-default
Version: #1 Fri Oct 31 00:44:18 UTC 2003
Machine: i686
Instance name: yang
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 8882, image: oracle@yang (TNS V1-V3)
*** 2006-04-21 18:15:49.954
*** SESSION ID<img src="http://www.QQread.com/oracle/2006/09/images/smilies/2.gif" border="0" alt="皺眉">23.405) 2006-04-21 18:15:49.943
WAIT #0: nam='SQL*Net message from client' ela= 239971432 p1=1650815232 p2=1 p3=0
=====================
PARSING IN CURSOR #2 len=198 dep=1 uid=0 oct=3 lid=0 tim=1118764208951706 hv=2703824309 ad='52b9d678'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #2:c=0,e=1318,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1118764208951664
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=0ada7a90 bln=22 avl=02 flg=05
value=29
bind 1: dty=1 mxl=32(03) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0
bfp=0adace7c bln=32 avl=03 flg=
