| 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; |
|
