[i=s] 本帖最后由 wei-xh 于 2012-5-12 13:44 编辑

#通过BBED的COPY来覆盖表里现有的数据块
#修改完成后，需要修改RDBA。
#数据库版本10.2.0.4

1.创建测试表空间、测试表，表空间的段管理方式为ASSM。

2.查看表都占用了哪些数据块

CODE:

test@CRMD> select distinct dbms_rowid.ROWID_OBJECT(rowid) data_object_id#,
2 dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfile#,
3 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#
4 from test;

DATA_OBJECT_ID# RFILE#    BLOCK#
--------------- ---------- ----------
10815             4       12
10815             4       333.选择把4,23 的内容覆盖到4,12里。

CODE:

BBED> set dba 4,23
DBA 0x01000017 (16777239 4,23)
BBED> map /v
File: /data1/oracle/crmd/users01.dbf (4)
Block: 23 Dba:0x01000017
------------------------------------------------------------
KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
BBED> p rdba_kcbh
ub4 rdba_kcbh @4 0x01000017

4,23的rdba_kcbh值为0x01000017

BBED> set dba 4,12
DBA 0x0100000c (16777228 4,12)
BBED> p rdba_kcbh
ub4 rdba_kcbh @4 0x0100000c

4,12的rdba_kcbh值为0x0100000c

BBED> copy dba 4,23 to dba 4,12修改4,12的rdba的值为0c000001(需要对0x0100000c进行反转)

CODE:

BBED> m /x 0c000001
File: /data1/oracle/crmd/users01.dbf (4)
Block: 12 Offsets: 4 to 515 Dba:0x0100000c
------------------------------------
0c000001 36cb0500 00000104 424d0000

BBED> sum dba 4,12 apply
Check value for File 4, Block 12:
current = 0x4d59, required = 0x4d59

BBED> verify dba 4,12
DBVERIFY - Verification starting
FILE = /data1/oracle/crmd/users01.dbf
BLOCK = 12

DBVERIFY - Verification complete

Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0验证数据块OK。没有块损坏。

CODE:
test@CRMD>select count(*) from test;

COUNT(*)
----------
877
test@CRMD>delete from test where dbms_rowid.ROWID_BLOCK_NUMBER(rowid) =12;
ERROR:
ORA-03114: not connected to ORACLE

delete from test where dbms_rowid.ROWID_BLOCK_NUMBER(rowid) =12
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

test@CRMD>rollback;
ERROR:
ORA-03114: not connected to ORACLE

test@CRMD>conn test/test
Connected.
test@CRMD>delete from test;
delete from test
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktspstchg-1], [], [], [], [], [], [], [] DELETE 出现了问题。
我个人的猜测是：
由于采用的是ASSM管理，这样搞了之后，3级位图块内记录的块使用信息（比如使用的百分比）跟实际的不一致，造成了这个问题。
如果非ASSM表空间，这种方式可能就不会有问题。

解决的办法就是ceate table xx as select xxx;

使用手工段空间管理，没出现这个问题。过程就不贴出来。
CODE:
create tablespace users datafile '/data1/oracle/crmd/users01.dbf' size 2g SEGMENT SPACE MANAGEMENT MANUAL ;
test@CRMD>delete from test where dbms_rowid.ROWID_BLOCK_NUMBER(rowid) =17;
77 rows deleted.
test@CRMD>delete from test where dbms_rowid.ROWID_BLOCK_NUMBER(rowid) =35;
77 rows deleted.

ITPub博客

通过BBED的COPY来覆盖表里现有的数据块

最新文章