Oracle Flash-back queries, flash-back tables, flash-back databases

/* First, to use the flash-back query, the database must be open automatic undo management, must have the undo table space, you must set the rollback segment retention/--in sqlplus to see if the Undo_management parameter value is auto,
If it is "MANUAL" manual, need to be modified to "AUTO"; --View the retention time of the rollback segment undo_retention, default 900 seconds (15 minutes) show parameter undo--View all rollback segments Select segment_id, segment_name from Dba_rollbac
K_segs;
--Specifies that a transaction uses a rollback segment, and if the rollback segment is not artificially specified, the database is weighed against the transaction in the rollback segment to make the transaction pressure as average as possible in all rollback segments.
Set TRANSACTION use rollback segment RBS6; --Modified undo_management parameter value is Auto/* Oracle has a spfile dynamic parameter file, which sets various Oracle parameters.
The so-called dynamic, is that you can not close the database, change the database parameters, recorded in the SPFile inside.
When you change a parameter, there are 4 scope options, scope is range. Scope=spfile only changes the records inside the SPFile, does not change the memory, that is, not immediately effective, but the next time the database starts to take effect, some parameters are allowed to change only in this way; scope=memory only change the memory, do not change the SPFile,
That is, the next time it starts, the Scope=both memory and SPFile are changed, and no scope parameter is specified, equivalent to Scope=both.
* Alter system set undo_management= ' AUTO ' scope=spfile;
--Modify Undo_retention to 1-hour alter system set UNDO_RETENTION=3600 Scope=both;
--see if the changes take effect immediately show parameter undo Select Name,value from V$spparameter where name= ' undo_management '; --Restart the database for the change to take effect shutdown immediate startup show parameter undo/* Test flash back query/SELect * from T1 where id<10;
Delete from T1 where id<10;
Commit
--Query table data before 15 minutes select * from T1 as of timestamp (sysdate-15/1440) where id<10;
--Restore data INSERT INTO T1 SELECT * T1 as of timestamp (sysdate-15/1440) where id<10;
Commit
/* Based on the flashback of the time is essentially the SCN-based flashback/-The Dbms_flashback execution authority is delegated to Scott user grant execute on Dbms_flashback to Scott; 
--Query the current system change number SCN and record it, 2363533 select Dbms_flashback.get_system_change_number from dual;
--delete data delete from T1 where id<10;
Commit
--Query the data before deletion by deleting the SCN before the data point-in-time select * from T1 as of SCN (2363533) where id<10;
--Restore data INSERT INTO T1 SELECT * T1 as of SCN (2363533) where id<10;
Commit --Use ORA_ROWSCN pseudo-columns to view the SCN select ora_rowscn,t1.* from T1 associated with each row--View the transaction submission time of the SCN map select Scn_to_timestamp (2363533) from Dua
L

--View the latest transaction submission time for each row of data select Scn_to_timestamp (ORA_ROWSCN), t1.* from T1;
/* Two, flash return table/drop table T1;
SELECT * from T1;
--After deleting the T1 table, the block of the table is still in the tablespace, and the query Recycle Bin can see the deleted object select * from RecycleBin; --flash back table before deletion flashback table T1 to BEfore drop;
/* flash-back table to a certain point in time/update T1 set contents= ' abc ';
Commit
--You must enable the row Movement feature of the table ALTER TABLE T1 enable row movement;

Flashback table T1 to timestamp (systimestamp-5/1440);
/* Three, Flash back to the database/-you need to have SYSDBA permissions to use the Flashback Database command, you must mount the databases in exclusive mode, but do not open the database.
--Put the database in a flashback mode and perform a flash back.
startup Mount Exclusive;
ALTER DATABASE Archivelog;
ALTER DATABASE flashback on;
ALTER DATABASE open;
--See if the Flash-back mode opens select Current_scn, flashback_on from V$database;
Shutdown
startup Mount Exclusive;
--Flash back to database until 1 hours ago Flashback db to timestamp sysdate-1/24;
--After the successful flash-back, open the database, while resetlogs open the database Write access permission alter DATABASE open resetlogs;
startup Mount Exclusive;
ALTER DATABASE flashback off;
 ALTER DATABASE open;