AboutPeter Choi Expertise I am a senior Oracle DBA, PeopleSoft Administrator and Project Manager with
10+ years experience. I have been working with PeopleSoft (HRMS 5, 7, 7.x and
8.9), Oracle RDBMS (7.3 - 11gR1) on various Unix and Windows platforms, and some Oracle Application Server (9i/10gR2). I also have experience with the configuration and administration of BEA`s Tuxedo and WebLogic for PeopleSoft 8.x.
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
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!
