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