CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Flashback Table

Version 11.1
 
Flashback To SCN

Flashback to SCN Demo
FLASHBACK TABLE <schema_name.table_name>
TO SCN <scn_number>
[<ENABLE | DISABLE> TRIGGERS]
CREATE TABLE t
ENABLE ROW MOVEMENT AS
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE 1=2;

desc t

SELECT table_name, row_movement
FROM user_tables;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5607529 02-MAY-07 12.46.50.906000 PM -07:00

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'SYS';

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5607537 02-MAY-07 12.47.06.453000 PM -07:00

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'WMSYS';

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5607545 02-MAY-07 12.47.25.359000 PM -07:00

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'CTXSYS';

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5607552 02-MAY-07 12.47.38.187000 PM -07:00

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;

conn / as sysdba

set linesize 121
col owner format a10

FLASHBACK TABLE uwclass.t TO SCN 5607547;

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;

FLASHBACK TABLE uwclass.t TO SCN 5607540;

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;
 
Flashback To TIMESTAMP

Flashback to Timestamp Demo
FLASHBACK TABLE <schema_name.table_name>
TO TIMESTAMP <timestamp>
[<ENABLE | DISABLE> TRIGGERS]
CREATE TABLE t
ENABLE ROW MOVEMENT AS
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE 1=2;

desc t

SELECT table_name, row_movement
FROM user_tables;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5607702 02-MAY-07 12.51.33.390000 PM -07:00

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'SYS';

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5607709 02-MAY-07 12.51.46.187000 PM -07:00

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'WMSYS';

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5607716 02-MAY-07 12.52.00.562000 PM -07:00

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'CTXSYS';

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5607722 02-MAY-07 12.52.13.359000 PM -07:00

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;

conn / as sysdba

set linesize 121
col owner format a10

FLASHBACK TABLE uwclass.t TO TIMESTAMP
TO_TIMESTAMP('02-MAY-07 12.51.52.050000');

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;

FLASHBACK TABLE
uwclass.t TO TIMESTAMP
TO_TIMESTAMP('02-MAY-07 12.51.51.500000');

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;
 
Flashback To Restore Point

Flashback to Restore Point Demo
FLASHBACK TABLE <schema_name.table_name>
TO RESTORE POINT <restore_point>
[<ENABLE | DISABLE> TRIGGERS]
CREATE TABLE t
ENABLE ROW MOVEMENT AS
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE 1=2;

desc t

SELECT table_name, row_movement
FROM user_tables;


CREATE RESTORE POINT zero;

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'SYS';

COMMIT;

CREATE RESTORE POINT one;

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'WMSYS';

COMMIT;

CREATE RESTORE POINT two;

INSERT INTO t
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE owner = 'CTXSYS';

COMMIT;

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;

SELECT scn, time, name
FROM gv$restore_point;

FLASHBACK TABLE t TO RESTORE POINT two;

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;

FLASHBACK TABLE t TO RESTORE POINT one;

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;

FLASHBACK TABLE t TO RESTORE POINT zero;

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;
 
Related Topics
Flashback Archive
Flashback Database
Flashback Drop
Flashback Query
Flashback Transaction Backout
Flashback Transaction Query
Flashback Version Query
Recycle Bin
Restore Points
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [176 users online]    © 2010 psoug.org