Flashback table, query and drop

View All Scripts Login to Run Script

Script Name Flashback table, query and drop
Description Examples of using flashback to: - Restore a whole table to a previous state - Recover a few rows from a table - Undrop a table! For further details, see the blog post How to Recover Data (Without a Backup!): https://blogs.oracle.com/sql/entry/how_to_recover_data_without
Area SQL General
Contributor Chris Saxon (Oracle)
Created Tuesday May 03, 2016

Statement 1

create table t (x int, y date)

Table created.

Statement 2

insert into t values (1, sysdate)

1 row(s) inserted.

Statement 3
```
commit
```
```
Statement processed.
```

Statement 4

Cause a delay so you can view the effects of flashback

declare  
  n number;  
begin  
  for i in 1 .. 500000 loop  
    n := ln(power(sqrt(i), 3));  
  end loop;  
end;

Statement processed.

Statement 5

insert into t values (2, sysdate)

1 row(s) inserted.

Statement 6

insert into t values (3, sysdate)

1 row(s) inserted.

Statement 7
```
commit 
```
```
Statement processed.
```

Statement 8

select * from t


X Y
1 05-MAY-16
2 05-MAY-16
3 05-MAY-16

3 rows selected.

X	Y
1	05-MAY-16
2	05-MAY-16
3	05-MAY-16

Statement 9

Row movement is not enabled, so you can't flashback the table.

flashback table t to timestamp systimestamp - interval '1' second

ORA-08189: cannot flashback the table because row movement is not enabled

More Details: https://docs.oracle.com/error-help/db/ora-08189

Statement 10

alter table t enable row movement

Table altered.

Statement 11

Now row movement is enabled, you can flashback.

flashback table t to timestamp systimestamp - interval '1' second

Statement processed.

Statement 12
The two rows added just before the flashback are lost
```
select * from t 
```
```
X Y
1 05-MAY-16
```
Statement 13
```
delete t 
```
```
1 row(s) deleted.
```
Statement 14
```
commit
```
```
Statement processed.
```
Statement 15
```
select * from t 
```
```
no data found
```

X	Y
1	05-MAY-16

Statement 16

A basic flashback query, enabling you to view the deleted row.

select * from t as of timestamp systimestamp - interval '1' second


X Y
1 05-MAY-16

X	Y
1	05-MAY-16

Statement 17

You can insert the result of the flashback query back into the table, restoring the row!

insert into t   
  select * from t as of timestamp systimestamp - interval '1' second

1 row(s) inserted.

Statement 18

You can also use flashback query in update statements to restore old column values

update t cur  
set (y) = (  
  select y from t as of timestamp systimestamp - interval '1' second old  
  where cur.x = old.x  
)

1 row(s) updated.

Statement 19
```
select * from t 
```
```
X Y
1 05-MAY-16
```
Statement 20
This drops the table without the purge option, so it's in the recyclebin.
```
drop table t
```
```
Table dropped.
```

X	Y
1	05-MAY-16

Statement 21

The original_name column shows the names of the tables you've dropped

select * from recyclebin


OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
BIN$MhVXYbPqWOzgU5Le5wrKYQ==$0 T DROP TABLE LIVESQL_USERS 2016-05-05:01:46:31 2016-05-05:01:46:39 291195812  - YES YES 164863 164863 164863 8

Statement 22
This recovers the table from the recyclebin so you can use it again.
```
flashback table t to before drop
```
```
Statement processed.
```
Statement 23
```
select * from t 
```
```
X Y
1 05-MAY-16
```
Statement 24
```
select * from recyclebin 
```
```
no data found
```
Statement 25
```
drop table t
```
```
Table dropped.
```
Statement 26
Clear table t out of the recyclebin and it's gone for good!
```
purge table t
```
```
Statement processed.
```
Statement 27
```
select * from recyclebin 
```
```
no data found
```

OBJECT_NAME	ORIGINAL_NAME	OPERATION	TYPE	TS_NAME	CREATETIME	DROPTIME	DROPSCN	PARTITION_NAME	CAN_UNDROP	CAN_PURGE	RELATED	BASE_OBJECT	PURGE_OBJECT	SPACE
BIN$MhVXYbPqWOzgU5Le5wrKYQ==$0	T	DROP	TABLE	LIVESQL_USERS	2016-05-05:01:46:31	2016-05-05:01:46:39	291195812	-	YES	YES	164863	164863	164863	8

X	Y
1	05-MAY-16

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database