#列出对象DDL,DBMS_METADATA.GET_DDL
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','LUDA') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','LUDA')
--------------------------------------------------------------------------------
CREATE TABLESPACE "LUDA" DATAFILE
'/oracle/oradata/LUDA/luda1.dbf' SIZE 1073741824
AUTOEXTEND ON NEXT 20971520 MAXSIZE 5120M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
#找出表段头
select header_file,header_block from dba_segments where owner='LUDA' and segment_name = 'T1';
select header_file,header_block from dba_segments where owner='TEST' and segment_name = 'EMP';
SQL> select rowid,name from emp;
ROWID NAME
------------------ --------------------
AAAM9BAAEAAAAA8AAE zhangsan
AAAM9BAAEAAAAA8AAF lisi
AAAM9BAAEAAAAA8AAG wanger
AAAM9BAAEAAAAA8AAH mazi
select dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num
from test.emp
where rownum < 2;
select dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location,
id,NAME,DEP_NP FROM TEST.EMP ;
select dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location,
id,NAME,DEP_NO FROM TEST.DMP ;
#截断分开rowid
select rowid ,
substr(rowid,1,6) "OBJECT",
substr(rowid,7,3) "FILE",
substr(rowid,10,6) "BLOCK",
substr(rowid,16,3) "ROW"
from luda.t1;
#计算rowid
select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,dbms_rowid.rowid_row_number(rowid) num from scott.emp where rownum<2;
依据这些编号得到具体的十进制的编码值呢,需要明白rowid的是基于64位编码的18个字符显示(数据对象编号(6) +文件编号(3) +块编号(6)+ 行编号(3)=18位),其中
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)
共64位,明白这个后,就可以计算出10进制的编码值,计算公式如下:
d * (b ^ p)
其中:b就是基数,这里就是64,p就是从右到左,已0开始的位置数
比如:上面的例子
文件号AAF,具体的计算应该是:
5*(64^0)=5;
0*(64^1)=0;
0*(64^2)=0;
文件号就是0+0+5=5
rowid的显示方式是基于64位编码的18个字符显示,其实rowid的存储方式是:10 个字节即80位存储,其中数据对象编号需要32 位,相关文件编号需要10 位,块编号需要22,位行编号需要16 位,由此,我们可以得出:
32bit的object number,每个数据库最多有4G个对象
10bit的file number,每个对象最多有1022个文件(2个文件预留)
22bit的block number,每个文件最多有4M个BLOCK
16bit的row number,每个BLOCK最多有64K个ROWS
#简化rowid获取
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;
/
## rowid改变的条件
1.数据迁移
2.mv
alter table luda.t1 move tablespace luda;
exp luda/luda tables=(t1) file=/oracle/t1.dmp
imp luda/luda tables=(t1) file=/oracle/t1.dmp
alter table rename luda.t1 to t2;
drop table luda.t1;
#RDBA
RDBA由,rfile# + block#组成的
相对文件号10位 block号22位
先算22位的BLOCK,其他的就是FILE
如果是ROWID的话,先算16的slot
举例:
#########################set 命令################################################
set 命令 定位数据块。
例子
定位 4号文件的18587号块
set file 4 block 18587
用rdba定位的话先换算 file 4 和 18587 号块
从右边开始算起 18587 对应2进制 00 0000 0100 1000 1001 1011
4 对应2进制 100
所以这里rdba为 0000 0010 000 0000 0100 1000 1001 1011 转换成16进制为 0x100489b
所以也可以定位成:
set dba 0x100489b
BBED> set dba 0x100489b
DBA 0x0100489b (16795803 4,18587)
###工具包换算10进制 dbms_utility.data_block_address_file
用法
SQL> select dbms_utility.data_block_address_file(20971712) "file",dbms_utility.data_block_address_block(20971712) "block" from dual;
file block
---------- ----------
5 192
###
set dba 4,18587
BBED> set dba 4,18587
DBA 0x0100489b (16795803 4,18587)
set filename
BBED> set filename "/oracle/oradata/znjtepp/users01.dbf"
FILENAME /oracle/oradata/znjtepp/users01.dbf
set file
BBED> set file 4
FILE# 4
set block and set block + ora -
BBED> set block 100
BLOCK# 100
BBED> set block +100
BLOCK# 200
BBED> set block +20
BLOCK# 220
BBED> set block -200
BLOCK# 20
set blocksize
set listfile这两个set都是用在切换配置文件时候用的
#设置屏幕显示区域
BBED> set width 100
WIDTH 100
########################offset 偏移量 以及count #################################################
offset 偏移量
BBED> dump
File: /oracle/oradata/znjtepp/users01.dbf (4)
Block: 18587 Offsets: 0 to 511 Dba:0x0100489b
------------------------------------------------------------------------
23a20000 0100489b 0031fc19 00000104 f8880000 00000000 00000000 00000000
00000000 00000011 00000100 0a9c0000 00000010 00000080 00000080 01004989
00000000 00000010 00000000 000000f4 00000000 00000000 00000000 00000010
.....
BBED> set offset 200
OFFSET 200
BBED> dump
File: /oracle/oradata/znjtepp/users01.dbf (4)
Block: 18587 Offsets: 200 to 711 Dba:0x0100489b
------------------------------------------------------------------------
00000000 00000000 00010000 00002000 00000000 00001434 00000000 0100489a
00000001 0100490a 0100489a 00000000 00000000 00000000 00000000 00000000
00000011 00000000 0000d7c4 10000000 01004899 00000008 010048a1 00000008
....
count可以设置dump的显示内容的多少
例:
BBED> set count 5000
COUNT 5000
BBED> dump
File: /oracle/oradata/znjtepp/users01.dbf (4)
Block: 18587 Offsets: 200 to 5199 Dba:0x0100489b
------------------------------------------------------------------------
00000000 00000000 00010000 00002000 00000000 00001434 00000000 0100489a
00000001 0100490a 0100489a 00000000 00000000 00000000 00000000 00000000
00000011 00000000 0000d7c4 10000000 01004899 00000008 010048a1 00000008
....
BBED> set file 4 block 18587
FILE# 4
BLOCK# 18587
BBED> set count 20
COUNT 20
##/v翻译内容
BBED> dump /v
File: /oracle/oradata/znjtepp/users01.dbf (4)
Block: 18587 Offsets: 0 to 19 Dba:0x0100489b
-------------------------------------------------------
23a20000 0100489b 0031fc19 00000104 l #.....H..1......
f8880000 l ....
<16 bytes per line>
##可以通过show ,info显示当前的设置情况
BBED> show
FILE# 4
BLOCK# 20
OFFSET 0
DBA 0x01000014 (16777236 4,20)
FILENAME /oracle/oradata/znjtepp/users01.dbf
BIFILE bifile.bbd
LISTFILE datafile.txt
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 100
COUNT 5000
LOGFILE log.bbd
SPOOL No
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /oracle/oradata/znjtepp/system01.dbf 101120
2 /oracle/oradata/znjtepp/XSPGIMS_DEVICE.dbf 473344
3 /oracle/oradata/znjtepp/sysaux01.dbf 62720
4 /oracle/oradata/znjtepp/users01.dbf 57760
5 /oracle/oradata/znjtepp/undotbs1.dbf 0
6 /oracle/oradata/znjtepp/XSPGIMS_FILE.dbf 406272
7 /oracle/oradata/znjtepp/XSPGIMS_FUEL.dbf 64000
8 /oracle/oradata/znjtepp/XSPGIMS_MATERIAL.dbf 64000
9 /oracle/oradata/znjtepp/XSPGIMS_PLAN.dbf 64000
10 /oracle/oradata/znjtepp/XSPGIMS_PRODUCE.dbf 288000
11 /oracle/oradata/znjtepp/XSPGIMS_SEQURITY.dbf 12800
12 /oracle/oradata/znjtepp/XSPGIMS_SYSTEM.dbf 115200
13 /oracle/oradata/znjtepp/XSPGIMS_OTHER.dbf 38400
14 /oracle/oradata/znjtepp/RIMS_DATA.dbf 25600
15 /oracle/oradata/znjtepp/XSPGIMS_MONITOR.dbf 38400
16 /oracle/oradata/znjtepp/XSPGIMS_HUMANRESOURCE.dbf 38400
......
############################find 命令###################################
map 命令
用于显示数据块的逻辑结构和这些逻辑结构的偏移量,一般配合/v参数使用
例
BBED> map /v dba 4,1
File: /oracle/oradata/LUDA/datafile/o1_mf_users_6zlksyqo_.dbf (4)
Block: 1 Dba:0x01000001
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes @0
struct kcvfhbfh, 20 bytes @0
struct kcvfhhdr, 76 bytes @20
ub4 kcvfhrdb @96
struct kcvfhcrs, 8 bytes @100
ub4 kcvfhcrt @108
......
####################
使用bbed修改行数据
使用bbed恢复被删除的行
fb, lb, cc就是每一行数据头的信息,分别表示Row Flag, Lock Byte (ITL entry), Column Count. (tl, 应该表示每一行数据实际占用的空间大小,包括row header, 每一个column实际占用的字节数,和用来存储每个column长度的overhead。 tl的信息并没有实际存储。)
这8个bit从高到低依次是:
(128) : Cluster Key
(64) : Cluster Table Member
(32) : Header of row piece
(16): Deleted
(8) : First data piece
(4) : Last data piece
(2) : First column continues from previous piece
(1) : Last column continues in next piece
----------------------------------------------------------------------------------------------------------------------
因此,如果表中的一行记录完全处于一个block中(没有出现row chain, row migration), 不是属于聚簇表,也没有被删除,那么这一行数据的flag应该就是
32 (Header of row piece) + 8 (First data piece) + 4 (Last data piece) = 44 (0x2c), 这个也就是在dump文件中看到的标识--H-FL--
恢复被删除的行