raiyaans

 Profile Answers by raiyaans Questions by raiyaans

• May 6th, 2013

 

From Oracle 9i Oracle introduced Flashback query feature. It is useful to recover the data which is accidentally deleted.
Suppose a user accidentally deletes rows from any table and commits it, by using a flashback query he can restore the rows back to the table.
Flashback feature depends upon how much undo retention time you have specified.
If the undo retention parameter is set to 2 hours (say for example) then oracle will not overwrite the data in undo table space even after committing until 2 hours.

Code
FOR Example


SQL>SELECT * FROM EMP AS OF TIMESTAMP SYSDATE-1/24;


 


OR


 


SQL> SELECT * FROM EMP AS OF TIMESTAMP 


      TO_TIMESTAMP(2007-06-07 10:00:00, YYYY-MM-DD HH:MI:SS)


 


TO INSERT THE ACCIDENTALLY DELETED ROWS AGAIN IN THE TABLE HE CAN TYPE


 


SQL> INSERT INTO EMP (SELECT * FROM EMP AS OF TIMESTAMP SYSDATE-1/24)


 

lviswanath

 Profile Answers by lviswanath 

• Jul 23rd, 2013

 

FLASHBACK can be used to get the data/objects deleted accidentally.

check http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9011.htm#i2143247
for FLASHBACK DATABASE (normally this is for DBA)

check http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm
for FLASHBACK TABLE

NAVEEN

• Mar 2nd, 2014

 

execute dbms_flashback.enable_at_time(sysdate-5/1440);(for 5 minutes flash back )

Deepika S Verma

• Mar 13th, 2014

 

Its a feature introduced in oracle9i where oracle DB allows user to view the content of table quickly and easily the way it was at a particular time in the past, even when it is modified and committed, be it a single row or the whole table.A flashback query can retrieve rows depends on

1. The size of undo tablespace
2. Or the setting of UNDO Retention system parameters

Before any user can avail the facility of flashback, DBA needs to perform following tasks,

1. UNDO Tablespace should large enough to retain changes made by all users for a specified period of time
2. Must specify how long the undo info will be retained for use by flashback query by using the initialization parameter UNDO_RETENTION
3. FLASHBACK privilege to the user for all or a subset of objects
4. Execute privileges on the dbms_flashback package

Note -
1.UNDO Retention can be defined on basis of Timestamp or SCN.
2.The package involved for all setting related to flashback => DBMS_FLASHBACK. Following are the operations mentioned in DBMS_FLASHBACK package-
a. GET_SYSTEM_CHANGE_NUMBER (Function) => Returns the current SCN as an Oracle number.
We can use the SCN to store specific snapshots
b. ENABLE_AT_SYSTEM_CHANGE_NUMBER (Procedure) => Enables Flashback for the entire session.
Takes an SCN as an Oracle number & sets the session snapshot to the specified number. Inside
Flashback mode, all queries will return data consistent as of the specified wall-clock time or SCN
c. DISABLE (Procedure) => Disables the Flashback mode for the entire session
d. ENABLE_AT_TIME (Procedure) => Enables Flashback for the entire session. The snapshot time is set
to the SCN that most closely matches the time specified in query_time
e. SCN_TO_TIMESTAMP (Function) => Takes the current SCN as an Oracle number datatype and
returns TIMESTAMP.
f. TRANSACTION_BACK_OUT (Function) (Introduced in Oracle 11g)=> Takes a TIMESTAMP as input
and returns the current SCN as an Oracle number datatype
(3) The Error Messages involved in FLASHBACK
a. ORA-08180 =>Time specified is too old.
b. ORA-08181 =>Invalid system change number specified.
c. ORA-08182 =>User cannot begin read-only or serializable transactions in Flashback mode.
d. ORA-08183 =>User cannot enable Flashback within an uncommitted transaction.
e. ORA-08184 =>User cannot enable Flashback within another Flashback session.
f. ORA-08185 =>SYS cannot enable Flashback mode.