分享
 
 
 

使用Oracle的外部表访问跟踪文件

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

前面说过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 on Tue Oct 17 22:10:05 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 option

JServer Release 9.2.0.4.0 - Production

SQL> show parameter user_dump

NAME TYPE VALUE

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

user_dump_dest string /opt/oracle/admin/eygle/udump

SQL> create or replace directory udump

2 as '/opt/oracle/admin/eygle/udump';

Directory created.

SQL> col DIRECTORY_PATH for a30

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS BDUMP /opt/oracle/admin/eygle/bdump

SYS UDUMP /opt/oracle/admin/eygle/udump

假如我们的某些操作需要生成跟踪文件:

SQL> alter session set sql_trace=true;

Session altered.

SQL> select count(*) from dba_users;

COUNT(*)

----------

7

SQL> alter session set sql_trace=false;

Session altered.

通过简单的脚本我们可以获得跟踪文件的名称:

SQL> set echo on

SQL> @gettrcname

SQL> SELECT d.VALUE

2 '/'

3 LOWER (RTRIM (i.INSTANCE, CHR (0)))

4 '_ora_'

5 p.spid

6 '.trc' trace_file_name

7 FROM (SELECT p.spid

8 FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p

9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

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 newline

7 nobadfile

8 nodiscardfile

9 nologfile

10 )

11 location('eygle_ora_29731.trc')

12 ) reject limit unlimited

13 /

Table created. 最后,我们可以很轻易的使用外部表访问和查询这个跟踪文件的内容:

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

System 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='sqlplus@jumper.hurray.com.cn (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 set 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 set 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 on Tue Oct 17 22:10:05 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 option

JServer Release 9.2.0.4.0 - Production

SQL> show parameter user_dump

NAME TYPE VALUE

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

user_dump_dest string /opt/oracle/admin/eygle/udump

SQL> create or replace directory udump

2 as '/opt/oracle/admin/eygle/udump';

Directory created.

SQL> col DIRECTORY_PATH for a30

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS BDUMP /opt/oracle/admin/eygle/bdump

SYS UDUMP /opt/oracle/admin/eygle/udump

假如我们的某些操作需要生成跟踪文件:

SQL> alter session set sql_trace=true;

Session altered.

SQL> select count(*) from dba_users;

COUNT(*)

----------

7

SQL> alter session set sql_trace=false;

Session altered.

通过简单的脚本我们可以获得跟踪文件的名称:

SQL> set echo on

SQL> @gettrcname

SQL> SELECT d.VALUE

2 '/'

3 LOWER (RTRIM (i.INSTANCE, CHR (0)))

4 '_ora_'

5 p.spid

6 '.trc' trace_file_name

7 FROM (SELECT p.spid

8 FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p

9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

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 newline

7 nobadfile

8 nodiscardfile

9 nologfile

10 )

11 location('eygle_ora_29731.trc')

12 ) reject limit unlimited

13 /

Table created.

最后,我们可以很轻易的使用外部表访问和查询这个跟踪文件的内容:

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

System 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='sqlplus@jumper.hurray.com.cn (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 set 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 set sql_trace=false

END OF STMT

PARSE #1:c=0

EXEC #1:c=0

84 rows selected.

SQL>

外部表极大的简化了我们的很多治理工作。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有