www.1q.co.kr(park1q.com)

ID :  Password : Auto  

   ȸ¿ø:0¸í / ¼Õ´Ô:0¸í
 

 

Technote

ÀÚ·á ¹× °ü¸®ÆÁ

  • DBA Notes
  • Q & A

    ºÏ¸¶Å©
  • Asktom
       (Oracle ÀÇ ´ëÇ¥ Forum)
  • Technical Bulltin(KR)
       (±â¼úÁö¿ø°Ô½ÃÆÇ)
  • Dbazine

  •  

     


     TRACE FILE GET ÀÇ 2°¡Áö ¹æ¹ý
    park1q  2009-08-12 14:53:12, Á¶È¸ : 3,448, Ãßõ : 909


     
    Oracle ÀÇ user trace È­ÀÏÀ» °¡Á®¿À´Â µÎ°¡Áö ¹æ¹ýÀÌ´Ù.
    Å×½ºÆ® °á°ú ¼ÓµµÂ÷ÀÌ´Â °ÅÀÇ ¾øÁö¸¸ ÈÄÀÚ°¡ Á¶±Ý´õ ½Ã½ºÅÛ ÀÚ¿øÀ» ´ú »ç¿ëÇÏ´Âµí º¸ÀδÙ. ±×·¯³ª utl_file ¿¡ ´ëÇÑ ÆÐŰÁö¿¡ execute ±ÇÇÑÀÌ Oracle XE ó·³ ºüÁ® ÀÖ´Â »çÀÌÆ®°¡ ÀÖÀ¸¹Ç·Î Âü°í ÇϽñæ.. 
     
     
    1. EXTERNAL TABLE À» ÀÌ¿ëÇÏ´Â ¹æ¹ý
    declare
       l_user_dump_dest         v$parameter.value%type;
       l_instance_name          varchar2(255);
       l_directory_name         varchar2(255);
     
    begin
       select a.value, b.instance_name
         into l_user_dump_dest, l_instance_name
         from v$parameter a, v$instance b
        where a.name = 'user_dump_dest';
     
       dbms_output.put_line( l_user_dump_dest );
     
       l_directory_name := 'om$udump_'||l_instance_name ;
       execute immediate
       'create or replace directory '||l_directory_name||' as
       ''' || l_user_dump_dest || '''';
       execute immediate
       'create table om$ext_udump_'||l_instance_name||' (text varchar2(4000))
       organization external (
       type oracle_loader
       default directory '||l_directory_name||'
       access parameters
       (records delimited by newline NOLOGFILE badfile ''OM_EXT_UDUMP.bad''
       fields terminated by 0X''0D''
       )
       location ('''||'_'||'''))
       reject limit unlimited ' ;
    end ;
    /

    alter table om$ext_udump_adm1q location ('adm1q_ora_19707.trc') ;
    select * from om$ext_udump_adm1q  ;

     
    2. PIPELINED FUNCTION À» ÀÌ¿ëÇÏ´Â ¹æ¹ý
    declare
       l_user_dump_dest         v$parameter.value%type;
       l_instance_name          varchar2(255);
       l_directory_name         varchar2(255);
     
    begin
       select a.value, b.instance_name
         into l_user_dump_dest, l_instance_name
         from v$parameter a, v$instance b
        where a.name = 'user_dump_dest';
     
       dbms_output.put_line( l_user_dump_dest );
     
       l_directory_name := 'om$udump_'||l_instance_name ;
       execute immediate
       'create or replace directory '||l_directory_name||' as
       ''' || l_user_dump_dest || '''';
       execute immediate
       'create or replace type varchar2_array as table of varchar2(4000)' ;
     
       execute immediate
       'create or replace function om$fn_get_trace_'||l_instance_name||' (file_name in varchar2)
       return varchar2_array
       pipelined
       as
         v_handle  utl_file.file_type;
         v_line    varchar2(20000);
       begin
         v_handle := utl_file.fopen('''||upper(l_directory_name)||''', file_name, ''R'', 32767);
         loop
           begin
             utl_file.get_line(v_handle, v_line);
           exception when no_data_found then
             exit;
           end;
           pipe row(v_line);
         end loop;
         return;
       end; ' ;
    end ;
    /

    select * from table (om$fn_get_trace_adm1q('adm1q_ora_19707.trc')) ;


      ÃßõÇϱâ ÇÁ¸°Æ®   ¸ñ·Ïº¸±â

    Copyright 1999-2019 Zeroboard

     

     
     
    [Today:3 / Total:157794]    Design by p@rk1q