Expert: Peter Choi - 9/26/2009

Question
I want to know that how to use flash back query if anybook

please tell me I want to rollback The DDL commands.

Answer
Hi Sandeep,

Here's an excellent reference to Oracle Flashback. Reference: http://www.orafaq.com/node/50
In it, here's what it says:

CONFIGURATION
In order to use this feature, the database instance has to be configured.
Log on to the database where the test is to be performed and run the following command at the SQL prompts:

SQL> show parameter UNDO;


NAME TYPE VALUE
----------------------- -------------- -----------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean TRUE
undo_tablespace string UNDO_TBSPC

This command displays all the necessary parameters for using the Flashback Queries. The value for UNDO_RETENTION is set to 900 seconds in this example which is the default value and represents at least how long the system retains undo. UNDO_RETENTION and UNDO_TABLESPACE are dynamic parameters, but UNDO_MANAGEMENT is not, requiring shut down and re-start of database instance in order for automatic undo management to take effect.

In addition to the above your DBA will have to grant:

  1. FLASHBACK privilege to the user for all or a subset of objects
  2. Execute privileges on the dbms_flashback package

For Oracle 11g, here's the reference and steps: http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/backrest.htm#ADMQ...

A note of caution, when using flashback, you need to make sure that you have enough storage (either for tablespace and OS filesystem) as you could be loading a lot of data very quickly!

Hope this helps.

Peter