Table Recover without datapump or rman

How recover a delete table without use of tools like rman or datapump. How do it? FLASHBACK. We have a 2 TB database and acidentaly we drop a simple litle table, we are going to waste lot time looking for that big backup ? even a export or datapump will take time.

SQL> create table TEST as select * from 123_users_adress;

check recyclebin is empty:

SQL> show recyclebin

Now we drop table:

SQL> drop table TEST;

Recycle bin will show us that table

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEST BIN$dLiZjsuLgKLgQ6wQABSAog==$0 TABLE 2014-06-18:9:09:43:47

The original tablet is stored under original tablespace, each time u delete a table, only a change name happens to “BIN$xxx” and go to recycle bin.

Sin embargo, las tablas originales físicamente se encuentran bajo el tablespace de origen… Es decir cuando se eliminan las tablas, estás se cambian de nombre a “BIN$xxx” y lógicamente bajan a estar dentro de la papelera de reciclaje.

SQL>
1 select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 mb
2 from dba_segments
3 where segment_type=’TABLE’ and owner=’TEST’
4* and segment_name in (select object_name from dba_recyclebin)

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB
———- —————————— —————— ——————– ———-
TEST BIN$dLiZjsuKgKLgQ6wQABSAog==$0 TABLE TEST 728

How we can recover?

SQL> flashback table TEST to before drop;

Flashback complete.

SQL> show recyclebin

We can recover table in a easy way, no need that big backup. we can restore table with other name if we wan, maybe u want delete information and
want new table with same name at same time:

SQL> flashback table TEST to before drop rename to TEST2;

Flashback complete.

SQL> select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 mb
2 from dba_segments
3 where segment_type=’TABLE’ and owner=’TEST’
4 and segment_name like ‘TEST%’;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB
———- —————————— —————— ——————– ———-
TEST TEST2 TABLE TEST 728

We want now sure delete table, but table go recyclebin so we empty recycle

SQL> drop table TEST purge;

if we check recycle bin table now is empty:

SQL> show recyclebin

we can use the new recycle bin name that make reference original table to sure drop table.

SQL> purge table “BIN$dLiZjsuLgKLgQ6wQABSAog==$0”;

SQL> show recyclebin

This a ver y basic use of flashback and recycle bin, later post i will write about other uses of flashback more detailed and table creations takeing data from flash data.

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s