DBA Scripts:转换RDBA的文件和数据块地址
昨天作测试,涉及很多rdba的转换,一般我是自己手工转换的,不过考虑到自己的书中有地方涉及这些转换,手工显然不容易解释的。
写了一个简单的函数,用来从RDBA中转换file#和block#出来: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;
/
有了这个函数,方便了很多:SQL> col BFNO for a60
SQL> select getbfno('0x037d86de') BFNO from dual;
BFNO
------------------------------------------------------------
datafile# is:13
datablock is:4032222
dump command:alter system dump datafile 13 block 4032222;
-The End-
By eygle on 2007-07-05 21:00 |
Comments (5) |
FAQ | 1488 |
5 Comments
写得太复杂了吧.
ASQL> select to_number('037d86de','xxxxxxxx') from dual;
TO_NUMBER('037D86DE','XXXXXXXX')
--------------------------------
58558174
1 rows returned.
ASQL> SELECT BITAND(58558174,4194303) from dual;
BITAND(58558174,4194303)
------------------------
4032222
1 rows returned.
ASQL> SELECT trunc(58558174/4194303) from dual;
TRUNC(58558174/4194303)
-----------------------
13
1 rows returned.
你还要注意,file#大于1023以后的情况呢,rfile#是重新开始循环的.没有这么简单转换的.
那种情况下, 肯定是要查数据字典的进行从rfile#到file#的转换的, 没有规律可言啊.
这个大Bug很难避免的,光靠Rdba的信息是不足够的。
不过通常的测试,这个还是够用了。
dbca起的够早的啊