oracle跟踪:使用Oracle的外部表访问跟踪文件来源: 发布时间:星期一, 2009年2月23日 浏览:0次 评论:0
="t18">
前面说过Oracle外部表可以用来访问警告日志文件其实Oracle外部表可以非常灵活被使用
通过Create Directory命令创建相应Directory的后我们可以将目录访问权限授予其他用户这样其他用户就能通过外部表访问很多主机上文件
有关Directory使用思路方法可以参考:http://www.eygle.com/archives/2005/04/using_create_di.Html
我们看下使用外部表访问跟踪文件例子(我例子是用SYS用户来完成)
首先创建个指向跟踪文件Directory:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - ProdUCtion _disibledevent=> Session altered.
SQL> select count(*) from dba_users;
COUNT(*)
---------- 7 SQL> alter session sql_trace=false; Session altered. 通过简单脚本我们可以获得跟踪文件名称: SQL> echo _disibledevent=> 10 (SELECT t.INSTANCE 11 FROM SYS.v$thread t, SYS.v$parameter v 12 WHERE v.NAME = 'thread' 13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, 14 (SELECT VALUE 15 FROM SYS.v$parameter 16 WHERE NAME = 'user_dump_dest') d 17 / TRACE_FILE_NAME ------------------------------------------------------------------------------------------------------------------------ /opt/oracle/admin/eygle/udump/eygle_ora_29731.trc SQL> 然后我们可以创建外部表用以访问这个跟踪文件: SQL> create table eygle_ora_29731 ( text varchar2(400) ) 2 organization external ( 3 type oracle_loader 4 default directory UDUMP 5 Access parameters ( 6 records delimited by line 7 nobadfile 8 nodiscardfile 9 nologfile 10 ) 11 location('eygle_ora_29731.trc') 12 ) reject limit unlimited 13 / Table created. 最后我们可以很轻易使用外部表访问和查询这个跟踪文件内容: SQL> pagesize 100 SQL> select * from eygle_ora_29731; TEXT ----------------------------------------------------------------- /opt/oracle/admin/eygle/udump/eygle_ora_29731.trc Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production ORACLE_HOME = /opt/oracle/product/9.2.0 name: Linux Node name: jumper.hurray.com.cn Release: 2.4.21-15.EL Version: #1 Thu Apr 22 00:27:41 EDT 2004 Machine: i686 Instance name: eygle Redo thread mounted by this instance: 1 Oracle process number: 8 Unix process pid: 29731 *** 2006-10-17 22:15:50.310 *** SESSION ID:(7.141) 2006-10-17 22:15:50.310 APPNAME mod='[email protected] (TNS V1-V3)' mh=0 act='' ah=0 = PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1133881396787772 hv=3943786303 ad='5ac609d4' alter session sql_trace=true END OF STMT EXEC #1:c=0 = PARSING IN CURSOR #3 len=37 dep=1 uid=0 oct=3 lid=0 tim=1133881402205250 hv=3468666020 ad='5adae2d8' select text from view$ where rowid=:1 END OF STMT PARSE #3:c=0 EXEC #3:c=0 FETCH #3:c=0 = PARSING IN CURSOR #4 len=116 dep=2 uid=0 oct=3 lid=0 tim=1133881402238727 hv=431456802 ad='5af20410' select o.owner# END OF STMT PARSE #4:c=10000 EXEC #4:c=0 FETCH #4:c=0 STAT #3 id=1 cnt=1 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 r=0 w=0 time=79 us)' = PARSING IN CURSOR #1 len=30 dep=0 uid=0 oct=3 lid=0 tim=1133881402246452 hv=3198913505 ad='5adaa4ac' select count(*) from dba_users END OF STMT PARSE #1:c=10000 EXEC #1:c=0 FETCH #1:c=10000 FETCH #1:c=0 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=48 r=0 w=0 time=5276 us)' STAT #1 id=2 cnt=7 pid=1 pos=1 obj=0 op='MERGE JOIN (cr=48 r=0 w=0 time=5250 us)' STAT #1 id=3 cnt=7 pid=2 pos=1 obj=0 op='SORT JOIN (cr=45 r=0 w=0 time=5051 us)' = PARSING IN CURSOR #3 len=116 dep=1 uid=0 oct=3 lid=0 tim=1133881410335382 hv=431456802 ad='5af20410' select o.owner# END OF STMT PARSE #3:c=0 EXEC #3:c=0 FETCH #3:c=0 STAT #1 id=4 cnt=7 pid=3 pos=1 obj=94 op='TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=45 r=0 w=0 time=4881 us)' STAT #1 id=5 cnt=127 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=44 r=0 w=0 time=3951 us)' STAT #1 id=6 cnt=7 pid=5 pos=1 obj=0 op='NESTED LOOPS (cr=42 r=0 w=0 time=3235 us)' STAT #1 id=7 cnt=7 pid=6 pos=1 obj=0 op='NESTED LOOPS (cr=26 r=0 w=0 time=3021 us)' STAT #1 id=8 cnt=7 pid=7 pos=1 obj=0 op='MERGE JOIN (cr=10 r=0 w=0 time=2760 us)' STAT #1 id=9 cnt=9 pid=8 pos=1 obj=0 op='SORT JOIN (cr=6 r=0 w=0 time=2479 us)' STAT #1 id=10 cnt=9 pid=9 pos=1 obj=94 op='TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=6 r=0 w=0 time=2284 us)' STAT #1 id=11 cnt=163 pid=10 pos=1 obj=0 op='NESTED LOOPS (cr=5 r=0 w=0 time=1303 us)' EXEC #3:c=0 FETCH #3:c=0 STAT #1 id=12 cnt=9 pid=11 pos=1 obj=280 op='TABLE ACCESS FULL USER_ASTATUS_MAP (cr=3 r=0 w=0 time=244 us)' STAT #1 id=13 cnt=153 pid=11 pos=2 obj=139 op='INDEX RANGE SCAN I_PROFILE (cr=2 r=0 w=0 time=518 us)' STAT #1 id=14 cnt=7 pid=8 pos=2 obj=0 op='SORT JOIN (cr=4 r=0 w=0 time=200 us)' STAT #1 id=15 cnt=7 pid=14 pos=1 obj=22 op='TABLE ACCESS FULL USER$ (cr=4 r=0 w=0 time=117 us)' STAT #1 id=16 cnt=7 pid=7 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=16 r=0 w=0 time=199 us)' STAT #1 id=17 cnt=7 pid=16 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=2 r=0 w=0 time=69 us)' STAT #1 id=18 cnt=7 pid=6 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=16 r=0 w=0 time=166 us)' STAT #1 id=19 cnt=7 pid=18 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=2 r=0 w=0 time=37 us)' STAT #1 id=20 cnt=119 pid=5 pos=2 obj=139 op='INDEX RANGE SCAN I_PROFILE (cr=2 r=0 w=0 time=342 us)' STAT #1 id=21 cnt=7 pid=2 pos=2 obj=0 op='SORT JOIN (cr=3 r=0 w=0 time=121 us)' EXEC #3:c=0 FETCH #3:c=0 STAT #1 id=22 cnt=1 pid=21 pos=1 obj=95 op='TABLE ACCESS FULL PROFNAME$ (cr=3 r=0 w=0 time=66 us)' = PARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=42 lid=0 tim=1133881410339660 hv=4238949625 ad='5ac49f60' alter session sql_trace=false END OF STMT PARSE #1:c=0 EXEC #1:c=0 84 rows selected. SQL> 外部表极大简化了我们很多治理工作前面说过Oracle外部表可以用来访问警告日志文件其实Oracle外部表可以非常灵活被使用 通过Create Directory命令创建相应Directory的后我们可以将目录访问权限授予其他用户这样其他用户就能通过外部表访问很多主机上文件 有关Directory使用思路方法可以参考: http://www.eygle.com/archives/2005/04/using_create_di.html 我们看下使用外部表访问跟踪文件例子(我例子是用SYS用户来完成) 首先创建个指向跟踪文件Directory: [oracle@jumper oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production _disibledevent=> Session altered. SQL> select count(*) from dba_users; COUNT(*) ---------- 7 SQL> alter session sql_trace=false; Session altered. 通过简单脚本我们可以获得跟踪文件名称: SQL> echo _disibledevent=> 10 (SELECT t.INSTANCE 11 FROM SYS.v$thread t, SYS.v$parameter v 12 WHERE v.NAME = 'thread' 13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, 14 (SELECT VALUE 15 FROM SYS.v$parameter 16 WHERE NAME = 'user_dump_dest') d 17 / TRACE_FILE_NAME ------------------------------------------------------------------------------------------------------------------------ /opt/oracle/admin/eygle/udump/eygle_ora_29731.trc SQL> 然后我们可以创建外部表用以访问这个跟踪文件: SQL> create table eygle_ora_29731 ( text varchar2(400) ) 2 organization external ( 3 type oracle_loader 4 default directory UDUMP 5 access parameters ( 6 records delimited by line 7 nobadfile 8 nodiscardfile 9 nologfile 10 ) 11 location('eygle_ora_29731.trc') 12 ) reject limit unlimited 13 / Table created. 最后我们可以很轻易使用外部表访问和查询这个跟踪文件内容: SQL> pagesize 100 SQL> select * from eygle_ora_29731; TEXT ----------------------------------------------------------------- /opt/oracle/admin/eygle/udump/eygle_ora_29731.trc Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production ORACLE_HOME = /opt/oracle/product/9.2.0 name: Linux Node name: jumper.hurray.com.cn Release: 2.4.21-15.EL Version: #1 Thu Apr 22 00:27:41 EDT 2004 Machine: i686 Instance name: eygle Redo thread mounted by this instance: 1 Oracle process number: 8 Unix process pid: 29731 *** 2006-10-17 22:15:50.310 *** SESSION ID:(7.141) 2006-10-17 22:15:50.310 APPNAME mod='[email protected] (TNS V1-V3)' mh=0 act='' ah=0 = PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1133881396787772 hv=3943786303 ad='5ac609d4' alter session sql_trace=true END OF STMT EXEC #1:c=0 = PARSING IN CURSOR #3 len=37 dep=1 uid=0 oct=3 lid=0 tim=1133881402205250 hv=3468666020 ad='5adae2d8' select text from view$ where rowid=:1 END OF STMT PARSE #3:c=0 EXEC #3:c=0 FETCH #3:c=0 = PARSING IN CURSOR #4 len=116 dep=2 uid=0 oct=3 lid=0 tim=1133881402238727 hv=431456802 ad='5af20410' select o.owner# END OF STMT PARSE #4:c=10000 EXEC #4:c=0 FETCH #4:c=0 STAT #3 id=1 cnt=1 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 r=0 w=0 time=79 us)' = PARSING IN CURSOR #1 len=30 dep=0 uid=0 oct=3 lid=0 tim=1133881402246452 hv=3198913505 ad='5adaa4ac' select count(*) from dba_users END OF STMT PARSE #1:c=10000 EXEC #1:c=0 FETCH #1:c=10000 FETCH #1:c=0 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=48 r=0 w=0 time=5276 us)' STAT #1 id=2 cnt=7 pid=1 pos=1 obj=0 op='MERGE JOIN (cr=48 r=0 w=0 time=5250 us)' STAT #1 id=3 cnt=7 pid=2 pos=1 obj=0 op='SORT JOIN (cr=45 r=0 w=0 time=5051 us)' = PARSING IN CURSOR #3 len=116 dep=1 uid=0 oct=3 lid=0 tim=1133881410335382 hv=431456802 ad='5af20410' select o.owner# END OF STMT PARSE #3:c=0 EXEC #3:c=0 FETCH #3:c=0 STAT #1 id=4 cnt=7 pid=3 pos=1 obj=94 op='TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=45 r=0 w=0 time=4881 us)' STAT #1 id=5 cnt=127 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=44 r=0 w=0 time=3951 us)' STAT #1 id=6 cnt=7 pid=5 pos=1 obj=0 op='NESTED LOOPS (cr=42 r=0 w=0 time=3235 us)' STAT #1 id=7 cnt=7 pid=6 pos=1 obj=0 op='NESTED LOOPS (cr=26 r=0 w=0 time=3021 us)' STAT #1 id=8 cnt=7 pid=7 pos=1 obj=0 op='MERGE JOIN (cr=10 r=0 w=0 time=2760 us)' STAT #1 id=9 cnt=9 pid=8 pos=1 obj=0 op='SORT JOIN (cr=6 r=0 w=0 time=2479 us)' STAT #1 id=10 cnt=9 pid=9 pos=1 obj=94 op='TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=6 r=0 w=0 time=2284 us)' STAT #1 id=11 cnt=163 pid=10 pos=1 obj=0 op='NESTED LOOPS (cr=5 r=0 w=0 time=1303 us)' EXEC #3:c=0 FETCH #3:c=0 STAT #1 id=12 cnt=9 pid=11 pos=1 obj=280 op='TABLE ACCESS FULL USER_ASTATUS_MAP (cr=3 r=0 w=0 time=244 us)' STAT #1 id=13 cnt=153 pid=11 pos=2 obj=139 op='INDEX RANGE SCAN I_PROFILE (cr=2 r=0 w=0 time=518 us)' STAT #1 id=14 cnt=7 pid=8 pos=2 obj=0 op='SORT JOIN (cr=4 r=0 w=0 time=200 us)' STAT #1 id=15 cnt=7 pid=14 pos=1 obj=22 op='TABLE ACCESS FULL USER$ (cr=4 r=0 w=0 time=117 us)' STAT #1 id=16 cnt=7 pid=7 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=16 r=0 w=0 time=199 us)' STAT #1 id=17 cnt=7 pid=16 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=2 r=0 w=0 time=69 us)' STAT #1 id=18 cnt=7 pid=6 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=16 r=0 w=0 time=166 us)' STAT #1 id=19 cnt=7 pid=18 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=2 r=0 w=0 time=37 us)' STAT #1 id=20 cnt=119 pid=5 pos=2 obj=139 op='INDEX RANGE SCAN I_PROFILE (cr=2 r=0 w=0 time=342 us)' STAT #1 id=21 cnt=7 pid=2 pos=2 obj=0 op='SORT JOIN (cr=3 r=0 w=0 time=121 us)' EXEC #3:c=0 FETCH #3:c=0 STAT #1 id=22 cnt=1 pid=21 pos=1 obj=95 op='TABLE ACCESS FULL PROFNAME$ (cr=3 r=0 w=0 time=66 us)' = PARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=42 lid=0 tim=1133881410339660 hv=4238949625 ad='5ac49f60' alter session sql_trace=false END OF STMT PARSE #1:c=0 EXEC #1:c=0 84 rows selected. SQL> 外部表极大简化了我们很多治理工作 0
相关文章读者评论发表评论 |