Oracle Drop Table Purge后小表快速恢复

1. 内容介绍

   Oracle数据库Drop Table Purge操作后，如果drop table 只有一个extent，且drop时间小于undo_retention,使用本方法可实现快速恢复。

2.环境准备

create user hsql identified by hsql;
grant connect,resource,dba to hsql;
drop tablespace hsql including contents and datafiles;
create tablespace hsql datafile '/data2/enmo/hsql01.dbf' size 10M autoextend off;
drop table hsql.drop_1 purge;
create table hsql.drop_1(c_char1 char(10),c_char2 char(10)) tablespace hsql;


begin
for i in 1 .. 1000 loop
insert into hsql.drop_1 values(i,'orastar');
end loop;
commit;
end;
/

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;

3. 检查数据字典信息

set linesize 200 pagesize 200
col owner for a10
col segment_name for a10
select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where segment_name='DROP_1';

OWNER	   SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE
---------- ---------- ----------- ------------ ------------------
HSQL	   DROP_1		5	        130        TABLE


set linesize 200 pagesize 9999
col owner for a10
col object_name for a20
select owner,object_name,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name='DROP_1';

OWNER	   OBJECT_NAME		    OBJECT_ID  DATA_OBJECT_ID
---------- -------------------- ---------- --------------
HSQL	   DROP_1		        13746	    13746


set linesize 200 pagesize 999
col OWNER for a10
col segment_name for a60
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
SQL>

4. 记录变更时间 <-- 生产环境根据业务或logmgr确定该时间

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;
SQL> 

SYSDATE
-------------------
2020-07-13 23:00:42

SQL>

5. 执行Drop操作

drop table hsql.drop_1 purge;
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;

SQL> 
select count(1) from hsql.drop_1
                          *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

SQL> select count(1) from seg$ where HWMINCR=13746;
select count(1) from obj$ where DATAOBJ#=13746;
select count(1) from tab$ where DATAOBJ#=13746;
select count(1) from col$ where OBJ#=13746;
  COUNT(1)
----------
         0

1 row selected.

SQL> 
  COUNT(1)
----------
         0

1 row selected.

SQL> 
  COUNT(1)
----------
         0

1 row selected.

SQL> 

  COUNT(1)
----------
         0

1 row selected.

SQL> 

6. 恢复基表

select count(1) from seg$ AS OF timestamp  to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where HWMINCR=13746;
select count(1) from obj$ AS OF timestamp  to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13746;
select count(1) from tab$ AS OF timestamp  to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13746;
select count(1) from col$ AS OF timestamp  to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where OBJ#=13746;

--select * from seg$ where HWMINCR=13805;

insert into seg$ select * from seg$ AS OF timestamp  to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where HWMINCR=13746;
insert into obj$ select * from obj$ AS OF timestamp  to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13746;
insert into tab$ select * from tab$ AS OF timestamp  to_timestamp ('2020-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where DATAOBJ#=13746;
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-07-13 23:00:42','yyyy-mm-dd hh24:mi:ss') where OBJ#=13746;




select count(1) from seg$ where HWMINCR=13746;
select count(1) from obj$ where DATAOBJ#=13746;
select count(1) from tab$ where DATAOBJ#=13746;
select count(1) from col$ where OBJ#=13746;

SQL> select count(1) from seg$ where HWMINCR=13746;
select count(1) from obj$ where DATAOBJ#=13746;
select count(1) from tab$ where DATAOBJ#=13746;
select count(1) from col$ where OBJ#=13746;
  COUNT(1)
----------
         1

1 row selected.

SQL> 
  COUNT(1)
----------
         1

1 row selected.

SQL> 
  COUNT(1)
----------
         1

1 row selected.

SQL> 

  COUNT(1)
----------
         2

1 row selected.

SQL> 

--delete from seg$ where HWMINCR=13746;
--delete from obj$ where DATAOBJ#=13746;
--delete from tab$ where DATAOBJ#=13746;
--delete from col$ where OBJ#=13746;

7. 检查恢复结果

SQL> 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;
System altered.

SQL> 
System altered.

SQL> 
System altered.

SQL> 
System altered.

SQL> 

  COUNT(1)
----------
      1000

1 row selected.

SQL>