通過BBED的COPY來覆蓋表裡現有的資料塊

wei-xh發表於2012-05-12
[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部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-723406/，如需轉載，請註明出處，否則將追究法律責任。

相關文章