适用范围

Oracle Database - Enterprise Edition - Version 8.0.6.0 and later Oracle Database Cloud Schema Service - Version N/A and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform.

环境模拟

1、环境模拟


create tablespace tbs_drop datafile '/oradata/enmo/tbs_drop01.dbf' size 100M;
create table scott.t tablespace tbs_drop as select * from dba_objects;
insert into scott.t select * from dba_objects;
commit;
insert into scott.t select * from dba_objects;
commit;
insert into scott.t select * from dba_objects;
commit;
insert into scott.t select * from dba_objects;
commit;


select count(*) from scott.t;

  COUNT(*)
----------
    434785

SQL>



select name,ts# from   ts$ where  name='TBS_DROP';

NAME						TS#
---------------------------------------- ----------
TBS_DROP					  7

SQL>

select * from ts$ where ts# = 7;
select *
  from obj$
 where obj# in (select obj# from tab$ where ts# = 7)
 order by 1, 2;
select * from tab$ where ts# = 7;
select *
  from col$
 where obj# in (select obj# from tab$ where ts# = 7)
 order by 1, 2;
select * from file$ where ts# = 7;
select * from seg$ where ts# = 7;


2、模拟破坏

alter system switch logfile;
/
/
alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;
select count(1) from scott.t;

drop tablespace tbs_drop  including contents;
alter system flush shared_pool;
alter system flush buffer_cache;

select count(1) from scott.t
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

实施步骤

3、收集基表


create table ts_20220403 as select * from sys.ts$ where 1=2;

create table obj_20220403 as select * from sys.obj$ where 1=2;

create table tab_20220403 as select * from sys.tab$ where 1=2;

create table col_20220403 as