My Experiments with Oracle

By Sriram Sanka -The AUAA

Read the file

Posted by sriram on December 10, 2010


A Master piece from http://laurentschneider.com

An excellent piece of code handy for DBA`s


SQL> SELECT s.sid,
2 s.serial#,
3 pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
4 '_ora_' || p.spid || '.trc' AS trace_file
5 FROM v$session s,
6 v$process p,
7 v$parameter pa
8 WHERE pa.name = 'user_dump_dest'
9 AND s.paddr = p.addr
10 AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
11 /

SID SERIAL#
---------- ----------
TRACE_FILE
--------------------------------------------------------------------------------
147 2101
D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP\orcl_ora_1000.trc

SQL>
SQL> sho parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCLE\UDUMP
SQL> CREATE DIRECTORY udump_dest AS
2 'D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP';

Directory created.

SQL> CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 dest_loc CLOB;
5 src_loc BFILE;
6 ret VARCHAR2 (4000);
7 BEGIN
8 src_loc := BFILENAME ('UDUMP_DEST', file_name);
9 DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
10 DBMS_LOB.createtemporary (dest_loc, TRUE);
11 DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
12 ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
13 DBMS_LOB.CLOSE (src_loc);
14 RETURN ret;
15 END;
16 /

Function created.

 

SQL>

SQL> SELECT get_tracefile (‘orcl_ora_1000.trc’) FROM DUAL;
GET_TRACEFILE(‘ORCL_ORA_1000.TRC’)——————————————————————————–Dump file d:\oracle\product\10.2.0\admin\orcle\udump\orcl_ora_1000.trcFri Dec 10 22:54:36 2010ORACLE V10.2.0.1.0 – Production vsnsta=0vsnsql=14 vsnxtr=3Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – ProductionWith the Partitioning, OLAP and Data Mining optionsWindows XP Version V5.1 Service Pack 3CPU                 : 1 – type 586, 1 Physical CoresProcess Affinity    : 0x00000000Memory (Avail/Total): Ph:269M/959M, Ph+PgF:1265M/2314M, VA:1606M/2047MInstance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 1000, image: ORACLE.EXE (SHAD)………………….

…………………………………..ETC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: