### 8. 闪回查询基表
```c
select * from seg$ where HWMINCR=13863;
select * from obj$ where DATAOBJ#=13863;
select * from tab$ where DATAOBJ#=13863;
select * from col$ where OBJ#=13863;

select * from seg$ AS OF timestamp  to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where HWMINCR=13863;
select * from obj$ AS OF timestamp  to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13863;
select * from tab$ AS OF timestamp  to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13863;
select * from col$ AS OF timestamp  to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where OBJ#=13863;
```

### 9. 闪回恢复基表
```c
insert into seg$ select * from seg$ AS OF timestamp  to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where HWMINCR=13863;
insert into obj$ select * from obj$ AS OF timestamp  to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13863;
insert into tab$ select * from tab$ AS OF timestamp  to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13863;
insert into col$ select OBJ#,						
COL#,						
SEGCOL#,					
SEGCOLLENGTH,		
OFFSET, 					
NAME,						
TYPE#,						
LENGTH, 					
FIXEDSTORAGE,		
PRECISION#,			
SCALE,						
NULL$,						
DEFLENGTH,				
'',							
INTCOL#,					
PROPERTY,				
CHARSETID,				
CHARSETFORM,			
SPARE1, 					
SPARE2, 					
SPARE3, 					
SPARE4, 					
SPARE5, 					
SPARE6 					
from col$ AS OF timestamp  to_timestamp ('2020-06-21 07:33:07','yyyy-mm-dd hh24:mi:ss') where OBJ#=13863;

FILE#					   NOT NULL NUMBER
 BLOCK# 				   NOT NULL NUMBER
 TYPE#					   NOT NULL NUMBER
 TS#					   NOT NULL NUMBER
 BLOCKS 				   NOT NULL NUMBER

select count(1) from seg$ where HWMINCR=13863;
select count(1) from obj$ where DATAOBJ#=13863;
select count(1) from tab$ where DATAOBJ#=13863;
select count(1) from col$ where OBJ#=13863;
```
### 10. 检查恢复情况
```c
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.drop_1 a;
                          *
ERROR at line 1:
ORA-08103: object no longer exists

SQL>
```
### 11. 更新mhflag_ech  --ub4 mhflag_ech		  @276   --0x10000000
```c
set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a20
select OWNER,segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name ='DROP_1' order by extent_id;
查询结果为空，

BBED> set dba 5,130
	DBA            	0x01400082 (20971650 5,130)

BBED> d offset 276 count 12
 File: /data2/enmo/hsql01.dbf (5)
 Block: 130              Offsets:  276 to  287           Dba:0x01400082
------------------------------------------------------------------------
 00000012 80004001 08000000

<32 bytes per line>

BBED> m /x 00000010 offset 276
 File: /data2/enmo/hsql01.dbf (5)
 Block: 130              Offsets:  276 to  287           Dba:0x01400082
------------------------------------------------------------------------
 00000010 80004001 08000000

<32 bytes per line>

BBED> sum apply
Check value for File 5, Block 130:
current = 0x951b, required = 0x951b

BBED>

alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
select count(1) from hsql.drop_1 a;
COUNT(1)
----------
      1345

set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a20
select OWNER,segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name ='DROP_1' order by extent_id;
OWNER	   SEGMENT_NAME 	 EXTENT_ID    FILE_ID	BLOCK_ID     BLOCKS
---------- -------------------- ---------- ---------- ---------- ----------
HSQL	   DROP_1			 0	    5	     128	  8
```

ITPub博客

oracle drop table purge无备份bbed恢复(2/3)

最新文章