How to access trace files using an Oracle external table

As I said earlier, Oracle's external tables can be used to access warning log files, but Oracle's external tables can be very flexible to use.

After creating the appropriate directory with the Create Directory command, we can grant access to the directory to other users so that other users can access files on many hosts through external tables.

Let's take a look at an example of using an external table to access a trace file (my example is done with the SYS user).

First create a directory that points to the trace file:

[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

If we need to generate a trace file for some of our operations:

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.

With a simple script we can get the name of the trace file:

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,

(SELECT t.instance

One from Sys.v$thread T, Sys.v$parameter v

WHERE v.name = ' thread '

V.value = 0 OR t.thread# = to_number (v.value)) I,

(SELECT VALUE

From Sys.v$parameter

WHERE NAME = ' user_dump_dest ') d

17/

Trace_file_name

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

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

/opt/oracle/admin/eygle/udump/eygle_ora_29731.trc

Sql>

We can then create an external table to access this trace file:

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)

One location (' EYGLE_ORA_29731.TRC ')

) Reject Limit Unlimited

13/

Table created.

Finally, we can easily use the external table to access and query the contents of this trace file: