How to get trace file from getbfno.sql gettrcname.sql

来源：互联网发布：java 字符串查找编辑：程序博客网时间：2024/05/01 00:52

根据块rdba地址,找到文件号,块号.
getbfno.sql

CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)   RETURN VARCHAR2IS   l_str   VARCHAR2 (255) DEFAULT NULL;   l_fno   VARCHAR2 (15);   l_bno   VARCHAR2 (15);BEGIN   l_fno :=      DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),                                                       'xxxxxxxx'                                                      )                                           );   l_bno :=      DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),                                                        'xxxxxxxx'                                                       )                                            );   l_str :=         'datafile# is:'      || l_fno      || CHR (10)      || 'datablock is:'      || l_bno      || CHR (10)      || 'dump command:alter system dump datafile '      || l_fno      || ' block '      || l_bno      || ';';   RETURN l_str;END;/

引自http://www.eygle.com/archives/2007/07/function_rdba_convert.html

gettrcname.sql

SELECT    a.VALUE       || b.symbol       || c.instance_name       || '_ora_'       || d.spid       || '.trc' trace_file  FROM (SELECT VALUE          FROM v$parameter         WHERE NAME = 'user_dump_dest') a,       (SELECT SUBSTR (VALUE, -6, 1) symbol          FROM v$parameter         WHERE NAME = 'user_dump_dest') b,       (SELECT instance_name          FROM v$instance) c,       (SELECT spid          FROM v$session s, v$process p, v$mystat m         WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d;

引自http://www.eygle.com/archives/2007/05/script_gettrcname.html