
|
 |

|

 |
 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')) ; |
|
|
 |
|
|
|
|