My Experiments with Oracle & Others- By $riram $anka

All About Issues, Errors,News & Latest Findings etc

  • Moderator at

  • Member at

  • Categories

  • Archives

  • December 2010
    M T W T F S S
    « Oct   May »
     12345
    6789101112
    13141516171819
    20212223242526
    2728293031  
  • Blogs I Follow

Read the file

Posted by Sriram Sanka 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

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.

 
Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBA's become bulletproof!

%d bloggers like this: