The experimental steps are organized:
Conn Scott/tiger
drop table t;
CREATE TABLE T as select * from dept;
View a few undo table spaces
Select Tablespace_name,contents,extent_management from Dba_tablespaces;
Set time on
Show parameter undo confirms the currently used undo tablespace, deleting the unused undo tablespace and preventing confusion
Conn Scott/tiger
Delete from T;
Commit
Alter system checkpoint;
alter system flush Buffer_cache;
SELECT * from t as of timestamp to_timestamp (' 2016-12-08 17:04:05 ', ' yyyy-mm-dd hh24:mi:ss '), data can be found at this time
#select tablespace_name,block_size,status,contents,segment_space_management from Dba_tablespaces;
Alter system checkpoint;
alter system flush Buffer_cache;
Create undo tablespace Undotbs datafile '/u01/app/oracle/oradata/ora11gr2/undotbs.dbf ' size 10m reuse; Create a new undo table space
Alter system set undo_tablespace= ' UNDOTBS2 ';
#select tablespace_name,block_size,status,contents,segment_space_management from Dba_tablespaces;
Alter tablespace UNDOTBS1 offline;
Drop tablespace undotbs1 including contents and datafiles;
Show parameter Undo
Conn Scott/tiger
SELECT * from t as of timestamp to_timestamp (' 2016-12-08 17:04:05 ', ' yyyy-mm-dd hh24:mi:ss ');
Verified If I do not delete the UNDOTBS1, then found the data before the switch can still find, puzzled
Verify that undo switchover will not be able to investigate previous data