Docstoc

flash back table sql oracle

Document Sample
flash back table  sql oracle Powered By Docstoc
					FLASHBACK TABLE
       SELECT * FROM RECYCLEBIN;
       SELECT * FROM USER_RECYCLEBIN;


Examples


Restoring a Table to an Earlier State: Examples

The examples below create a new table, employees_demo, with row movement enabled, update
values within the new table, and issue the FLASHBACK TABLE statement.

Create table employees_demo, with row movement enabled, from table employees of the sample
hr schema:

CREATE TABLE employees_demo
  ENABLE ROW MOVEMENT
  AS SELECT * FROM employees;


As a benchmark, list those salaries less than 2500:

SELECT salary
  FROM employees_demo
  WHERE salary < 2500;

    SALARY
----------
      2400
      2200
      2100
      2400
      2200




Issue a 10% salary increase to those employees earning less than 2500:

UPDATE employees_demo
  SET salary = salary * 1.1
  WHERE salary < 2500;

5 rows updated.
COMMIT;


As a second benchmark, list those salaries that remain less than 2500 following the 10%
increase:
SELECT salary
  FROM employees_demo
  WHERE salary < 2500;

    SALARY
----------
      2420
      2310
      2420


Restore the table employees_demo to its state 1 minute prior to the current system time:

FLASHBACK TABLE employees_demo
  TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);


List those salaries less than 2500. After the FLASHBACK TABLE statement issued above, this list
should match the list in the first benchmark.

SELECT salary
  FROM employees_demo
  WHERE salary < 2500;

    SALARY
----------
      2400
      2200
      2100
      2400
      2200

Retrieving a Dropped Table: Example

If you accidentally drop the hr.employees table and wish to retrieve it, issue the following
statement:

FLASHBACK TABLE employees TO BEFORE DROP;


If another employees table has been created in the hr schema, use the RENAME TO clause to
rename the retrieved table:

FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_old;

SELECT object_name, droptime FROM user_recyclebin
   WHERE original_name = 'employees';

OBJECT_NAME                            DROPTIME
------------------------------         -------------------
RB$$45703$TABLE$0                      2003-06-03:15:26:39
RB$$45704$TABLE$0                      2003-06-12:12:27:27
RB$$45705$TABLE$0                      2003-07-08:09:28:01
FLASHBACK DATABASE
conn / as sysdba

SELECT flashback_on, log_mode
FROM v$database;


shutdown immediate;

startup mount exclusive;

alter database archivelog;

alter database flashback on;

alter database open;

SELECT flashback_on, log_mode
FROM v$database;

SHUTDOWN immediate;

startup mount exclusive;


Flashing Back the database

FLASHBACK DATABASE TO SCN 19513917;


FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);

FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';

FLASHBACK DATABASE
TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD
HH24:MI:SS');

-- this will fail
alter database open;

-- this will succeed
alter database open resetlogs;

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:6
posted:5/18/2012
language:English
pages:3