flashback table

Can any one tell me about flashback table?
is it something like log files?

Hi barbie, the flashback table is the new feature introduced in the oracle database 10g. This feature is used mainly for retrieving an accidentally dropped table. Lets say there is a table named sampletab sql> select * from tab; tname tabtype clusterid ------------------------ ------- ---------- sampletab table if the sampletab table was dropped accidentally, then the tab select will give, sql> select * from tab; tname tabtype clusterid ------------------------------ ------- ---------- bin$04lhckjhsdermaaaaaanpw==$0 table eventhough the sampletab has been dropped, the table has not been disappeared completely. The dropped table will be renamed to a system-defined name. The table and its associated objects are placed in a logical container known as the "recycle bin," which is similar to the windows recycle bin. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. Sql> show recyclebin original name recyclebin name object type drop time ---------------- ------------------------------ ------------ ------------------ sampletab bin$04lhckjhsdermaaaaaanpw==$0 table 2007-01-12:11:31:47 this shows the original name of the table, sampletab, as well as the new name in the recycle bin. To reinstate the table, all you have to do is use the flashback table command: sql> flashback table sampletab to before drop; flashback complete. Sql> select * from tab; tname tabtype clusterid ------------------------------ ------- ---------- sampletab table the table is reinstated effortlessly. If you check the recycle bin now, it will be empty. To free the occupied space in the original tablespace, the recyclebin has to be purged using: sql> purge recyclebin; hopefully the detailed explanation is understandable & clears ur doubts :-) *** mangai varma ***

hi mangaivarma.

can you explain me for what that feature should be used?

hi barbie

i want to tell some points about it!
human are prone to making mistakes. But these errors are extremely difficult to avoid and can be particularly difficult to recover .So we need right technology. Such errors can result in "logical" data corruption".
for eg user by mistake can delete valuable data,dropping table etc.


. Oracle Flashback Technology provides a set of new features to view and rewind data back and forth in time. It offer the capability to query past versions of schema objects, query historical data, perform change analysis or perform self-service repair to recover from logical corruptions . With the Oracle Database 10g Flashback Technology, you can indeed undo the past!

bye
:)

oh.. thanks shenoy.
thats has a big log file to do rollback to a big level. right?

shenoy.. Can we use this flashback table to retrieve the new rows alone? :confused:

select from table_emp where empno not in historical data in flashback table on a particular date

This is just the skeleton of the query i think.(not the exact query)

Then how to query and retrieve the flashback table.
anyone please give me some materials regarding that.

Hi barbie, the flashback table is the new feature introduced in the oracle database 10g. This feature is used mainly for retrieving an accidentally dropped table. Lets say there is a table named sampletab sql> select * from tab; tname tabtype clusterid ------------------------ ------- ---------- sampletab table if the sampletab table was dropped accidentally, then the tab select will give, sql> select * from tab; tname tabtype clusterid ------------------------------ ------- ---------- bin$04lhckjhsdermaaaaaanpw==$0 table eventhough the sampletab has been dropped, the table has not been disappeared completely. The dropped table will be renamed to a system-defined name. The table and its associated objects are placed in a logical container known as the "recycle bin," which is similar to the windows recycle bin. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. Sql> show recyclebin original name recyclebin name object type drop time ---------------- ------------------------------ ------------ ------------------ sampletab bin$04lhckjhsdermaaaaaanpw==$0 table 2007-01-12:11:31:47 this shows the original name of the table, sampletab, as well as the new name in the recycle bin. To reinstate the table, all you have to do is use the flashback table command: sql> flashback table sampletab to before drop; flashback complete. Sql> select * from tab; tname tabtype clusterid ------------------------------ ------- ---------- sampletab table the table is reinstated effortlessly. If you check the recycle bin now, it will be empty. To free the occupied space in the original tablespace, the recyclebin has to be purged using: sql> purge recyclebin; hopefully the detailed explanation is understandable & clears ur doubts. *** mangai varma ***

It cleared my doubt.

thanks mangaivarma.

My earlier post has not been aligned properly, probabaly bcos the content is too long.
Anyway, the following link will be helpful

[URL="http://www.oracle-base.com/articles/10g/Flashback10g.php"]Understanding Flashback Concepts[/URL]
[URL="http://www.psoug.org/reference/tab_flashback.html"]Flashback Queries[/URL]

*** Mangai Varma ***

hi mangai

i was going to write ths same, i.e ur earlier post was not clear.

But the link u provided is really very helpful

bye
:)

thats much useful.

hi friends,

well a lot of things have been discussed on flash back, but i want to add one more thing, this feature is present in [COLOR="Red"][B]oracle 9i also.[/B][/COLOR]
check out for DBMS_FLASHBACK package in oracle 9i.

cheers,

regards,
RSY