be happy and make others to be happy: Flashback

SQL> conn scott/tiger
Connected.

Procedure created.

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP4                           TABLE

7 rows selected.

SQL> select * from emp4;

no rows selected

SQL> flashback table emp4 to before delete;
flashback table emp4 to before delete
                        *
ERROR at line 1:
ORA-00905: missing keyword

SQL> set timeout on
SP2-0158: unknown SET option "timeout"
SQL> set time on
12:02:08 SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP4                           TABLE

7 rows selected.

12:02:20 SQL> drop emp4;
drop emp4
     *
ERROR at line 1:
ORA-00950: invalid DROP option

12:02:33 SQL> drop table emp4;

Table dropped.

12:02:41 SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
BIN$sz8C/7dOT8yd7NPbq8ljWg==$0 TABLE

7 rows selected.

12:02:51 SQL> flashback table emp4 to before drop;

Flashback complete.

12:03:13 SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP4                           TABLE

7 rows selected.

12:03:20 SQL> create table emp5 as select * from emp;

Table created.

12:03:41 SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TBL_NEST                       TABLE
PNUM                           TABLE
EMP4                           TABLE
EMP5                           TABLE

8 rows selected.

12:03:48 SQL> delete emp5;

14 rows deleted.

12:03:59 SQL> select * from emp5;

no rows selected

12:04:09 SQL> desc emp5;
Name
---------------------------------------------------------------------------------------------------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

12:04:18 SQL> ed
Wrote file afiedt.buf

1 FLASHBACK TABLE emp5
2*   TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute)
12:07:04 SQL> /
FLASHBACK TABLE emp5
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

12:07:05 SQL> alter table emp5 enable row movement;

Table altered.

12:07:36 SQL> ed
Wrote file afiedt.buf

1 FLASHBACK TABLE emp5
2*   TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute)
12:07:54 SQL> /

Flashback complete.

12:07:55 SQL> select * from emp5;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

12:08:03 SQL>

Prerequisites
To flash back a table to an earlier SCN or timestamp, you must have either the FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege. In addition, you must have the SELECT, INSERT, DELETE, and ALTER object privileges on the table. Row movement must be enabled for all tables in the Flashback list. Please refer to row_movement_clause for information on enabling row movement.
To flash back a table to before a DROP TABLE operation, you need only the privileges necessary to drop the table.
Syntax

flashback_table::=
Description of flashback_table.gif follows

Description of flashback_table.gif follows

Description of the illustration flashback_table.gif

Semantics
During an Oracle Flashback Table operation, Oracle Database acquires exclusive DML locks on all the tables specified in the Flashback list. These locks prevent any operations on the tables while they are reverting to their earlier state.
The Flashback Table operation is executed in a single transaction, regardless of the number of tables specified in the Flashback list. Either all of the tables revert to the earlier state or none of them do. If the Flashback Table operation fails on any table, then the entire statement fails.
At the completion of the Flashback Table operation, the data in table is consistent with table at the earlier time. However, FLASHBACK TABLE TO SCN or TIMESTAMP does not preserve rowids, and FLASHBACK TABLE TO BEFORE DROP does not recover referential constraints.
Oracle Database does not revert statistics associated with table to their earlier form. Indexes on table that exist currently are reverted and reflect the state of the table at the Flashback point. If the index exists now but did not yet exist at the Flashback point, then the database updates the index to reflect the state of the table at the Flashback point. However, indexes that were dropped during the interval between the Flashback point and the current time are not restored.

schema
Specify the schema containing the table. If you omit schema, then the database assumes the table is in your own schema.

table
Specify the name of one or more tables containing data you want to revert to an earlier version.

Restrictions on Flashing Back Tables

Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).

TO SCN Clause
Specify the system change number (SCN) corresponding to the point in time to which you want to return the table. The expr must evaluate to a number representing a valid SCN.

TO TIMESTAMP Clause
Specify a timestamp value corresponding to the point in time to which you want to return the table. The expr must evaluate to a valid timestamp in the past.

ENABLE | DISABLE TRIGGERS
By default, Oracle Database disables all enabled triggers defined on table during the Flashback Table operation and then reenables them after the Flashback Table operation is complete. Specify ENABLE TRIGGERS if you want to override this default behavior and keep the triggers enabled during the Flashback process.
This clause affects only those database triggers defined on table that are already enabled. To enable currently disabled triggers selectively, use the ALTER TABLE ... enable_disable_clause before you issue the FLASHBACK TABLE statement with the ENABLE TRIGGERS clause.

TO BEFORE DROP Clause
Use this clause to retrieve from the recycle bin a table that has been dropped, along with all possible dependent objects.

See Also:

Oracle Database Administrator's Guide for information on the recycle bin and naming conventions for objects in the recycle bin
PURGE for information on removing objects permanently from the recycle bin

You can specify either the original user-specified name of the table or the system-generated name Oracle Database assigned to the object when it was dropped.

System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database retrieves that specified object.
To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:
```
SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;
```
If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database retrieves the object that was moved to the recycle bin most recently. If you want to retrieve an older version of the table, do one of these things:
- Specify the system-generated recycle bin name of the table you want to retrieve.
- Issue additional FLASHBACK TABLE ... TO BEFORE DROP statements until you retrieve the table you want.

Oracle Database attempts to preserve the original table name. If a new table of the same name has been created in the same schema since the original table was dropped, then the database returns an error unless you also specify the RENAME TO clause.

RENAME TO Clause
Use this clause to specify a new name for the table being retrieved from the recycle bin.

Notes on Flashing Back Dropped Tables

Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)
The database also retrieves all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.
The retrieved indexes, triggers, and constraints have recycle bin names. Therefore it is advisable to query the USER_RECYCLEBIN view before issuing a FLASHBACK TABLE ... TO BEFORE DROP statement so that you can rename the retrieved triggers and constraints to more usable names.
When you drop a table, all materialized view logs defined on the table are also dropped but are not placed in the recycle bin. Therefore, the materialized view logs cannot be flashed back along with the table.
When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.
You cannot flash back a table if it has been purged, either by a user or by Oracle Database as a result of some space reclamation operation.

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

Note:
To allow time for the SCN to propagate to the mapping table used by the FLASHBACK TABLE statement, wait a minimum of 5 minutes prior to issuing the following statement. This wait would not be necessary if a previously existing table were being used in this example.

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;

If you know that the employees table has been dropped multiple times, and you wish to retrieve the oldest version, query the USER_RECYLEBIN table to determine the system-generated name, and then use that name in the FLASHBACK TABLE statement. (System-generated names in your database will differ from those shown here.)

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

be happy and make others to be happy

Infolinks

Sunday 22 July 2012

Flashback

No comments:

Post a Comment