if you drop table you can use flashback table
FLASHBACK TABLE table TO BEFORE DROP RENAME TO table_old;
or if you delete any rows you can use flashback query
insert into table SELECT *
AS OF TIMESTAMP TO_TIMESTAMP('02-MAY-07 10.55.20.000000');
What do you mean by "restore a table?"
If you dropped the table and you want to "undrop it" - then flashback to before drop doesn't need flashback enabled. As long as the table hasn't been purged from the recycle bin, it's still there.
If you mean something else - describe what you mean. You can always restore an old backup to a separate location and then grab what you need from there.
Yes we don't have enough undo_retention we set only 15 min.how can we restore the last day table? Please suggest for any future mishappening.
we can set flashback_on parameter on full database and keep the last day flashback log for any this kind of mishappeing..am i rite?
undo_retention does not have any relation to undrop the table , nor flashback log has any relation to undrop the table , to undrop the table there is one logical container which is "Recycle Bin" , you can restore any dropped table unless and until you have not purge this table and there is no preallocated space set aside for the Recycle Bin.This makes the Recycle Bin space dependent on the space available in the existing tablespaces.
There is no guaranteed timeframe for how long an object will be stored in the Recycle Bin.The time is determined by system activity that impacts space utilization.
Please do not compare an orange to apple.
If you wana use flashback table with the means of flashback log then be ready for flashback all yours objects within database , flashback log is for a broad spectrum to rewind the database not for a restoring a pitty table.
I mean that you want to restore some of all table records.You don t wanna restore whole table
Do you have a backup before deleting rows? if yes, you can duplicate database and export and import table.
create a duplicate database from production database using until log sequence
3> SET UNTIL SEQUENCE 11 THREAD 1;
4> DUPLICATE TARGET DATABASE TO "clone" NOFILENAMECHECK;
export the users table from clone(duplicate) database.
exp rajesh/rajesh file=/u01/app/oracle/test_rcv.dmp tables=test rows=y
import the users table to the production database.
[oracle@rac1 ~]$ imp rajesh/rajesh file=/u01/app/oracle/test_rcv.dmp fromuser=rajesh touser=rajesh tables=test
Thankyou sir for your prompt and clear answer.
but i want to know about recycle bin standards.how old we go back using flashback technology?
when i am querying user_recyclebin table after deleteing some rows it return's with "no rows selected".what is this behaviour?
Done..when i use the dba_recyclebin object it show the recycle object in recycle bin.
My another question is how old we go back for e.g if i delete some rows and drop the table in database.