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

本文提供两个针对Oracle数据库管理员(DBA)的实用SQL脚本。第一个脚本通过输入块地址(RDBA)，返回对应的文件号及块号，便于进行数据库维护。第二个脚本用于获取数据库跟踪文件(.trc)的路径，帮助定位运行中的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
   RETURN VARCHAR2
IS
   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