创建一个测试表:
SQL> create table goolen as select * from dba_objects;
Table created.SQL> alter table goolen add constraint pk_objd primary key (object_id);
Table altered.
SQL> select count(*) from goolen;
COUNT(*)
----------
72139
--查看,goolen表的段头块是block 50
SQL> select header_file,header_block from dba_segments where segment_name='GOOLEN';HEADER_FILE HEADER_BLOCK
----------- ------------
4 50
SQL> select file_id,block_id,blocks from dba_extents where segment_name='GOOLEN';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
4 48 8
4 56 8
4 64 8
4 72 8
4 80 8
4 88 8
4 96 8
4 104 8
4 112 8
4 120 8
4 128 8
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
4 136 8
4 144 8
4 152 8
4 160 8
4 168 8
4 256 128
4 384 128
4 512 128
4 640 128
4 768 128
4 896 128
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
4 1024 128
4 1152 128
24 rows selected.
SQL> col file_name for a55
SQL> set lines 120
SQL> select file_id,file_name,bytes from dba_data_files;
FILE_ID FILE_NAME BYTES
---------- ------------------------------------------------------- ----------
4 /opt/app/oracle/oradata/goolen/users01.dbf 12713984
3 /opt/app/oracle/oradata/goolen/undotbs01.dbf 78643200
2 /opt/app/oracle/oradata/goolen/sysaux01.dbf 629145600
1 /opt/app/oracle/oradata/goolen/system01.dbf 713031680
----使用bbed破坏段头块,模拟段头损坏
BBED> set file 4 block 50
FILE# 4
BLOCK# 50
BBED> map /v
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 50 Dba:0x01000032
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> m /x 1234 offset 24
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 50 Offsets: 24 to 535 Dba:0x01000032
------------------------------------------------------------------------
12340000
USER is "SCOTT"
SQL> select count(*) from goolen;
COUNT(*)
----------
72139
SQL> alter system flush buffer_cache;
System altered.
SQL> select /*+ full(goolen)*/ count(*) from goolen;
select /*+ full(goolen)*/ count(*) from goolen
*
ERROR at line 1:
ORA-08103: object no longer exists
SQL> execute dbms_space_admin.tablespace_verify('&tablespace_name')
Enter value for tablespace_name: USERS
BEGIN dbms_space_admin.tablespace_verify('USERS'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> conn / as sysdba
Connected.
SQL> execute dbms_space_admin.tablespace_verify('&tablespace_name')
Enter value for tablespace_name: USERS
BEGIN dbms_space_admin.tablespace_verify('USERS'); END;
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 83
ORA-06512: at line 1
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_1678.trc
kcbzib: dump suspect buffer
buffer tsn: 4 rdba: 0x01000032 (4/50)
scn: 0x0000.00000000 seq: 0x01 flg: 0x01 tail: 0x00000001
frmt: 0x02 chkval: 0x0000 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id =&file_id
4 and &block_id between block_id and block_id + blocks -1;
Enter value for file_id: 4
old 3: WHERE file_id =&file_id
new 3: WHERE file_id =4
Enter value for block_id: 50
old 4: and &block_id between block_id and block_id + blocks -1
new 4: and 50 between block_id and block_id + blocks -1
no rows selected
SQL> SELECT owner, segment_name, segment_type, partition_name
2 FROM dba_segments
3 WHERE header_file =&file_id
4 and header_block=&block_id;
Enter value for file_id: 4
old 3: WHERE header_file =&file_id
new 3: WHERE header_file =4
Enter value for block_id: 50
old 4: and header_block=&block_id
new 4: and header_block=50
OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME
------------------------------ ----------------------------------- ------------------ ------------------------------
SCOTT GOOLEN TABLE
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
PK_OBJD NORMAL
--使用rowid来抽取数据
SQL> insert into goolen_bak
2 select * from goolen where rowid in
3 (select /*+ index(goolen PK_OBJD)*/ rowid from goolen )
4 ;
72139 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from goolen_bak;
COUNT(*)
----------
72139