Retrieving a dropped table on Oracle 12c

brunors


Sometimes developers need to recover some tables and we as Oracle DBAs are asked to recover them.
So in this post I am going to show you how do to that. I will be creating the table named TECHDATABASKET under the SH user using the dba_tables as example and afterwards I am going to use the syntax “FLASHBACK TABLE SH.TECHDATABASKET TO BEFORE DROP;” in order to recover this table. In the meantime, we need to execute a SELECT key on the table dba_recyclebin or simply the usage of the command “SHOW RECYCLEBIN;” to see the dropped table inside of the Oracle Recycle bin before we retrieve it.

1- Creation of the TECHDATABASKET table:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[oracle@vm1 archivelog]$ sqlplus
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 14 05:26:40 2019
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
Enter user-name: sh
Enter password:
Last Successful login time: Thu Mar 14 2019 05:26:20 -04:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL> create table TECHDATABASKET as select * from dba_tables;
 
Table created.

2- Drop of the created table:

?
1
2
3
SQL> drop table TECHDATABASKET;
 
Table dropped.

3- Querying the dropped table using dba_recyclebin table or by “SHOW RECYCLEBIN;” command:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
set pages set pages 2000
set lines 2000
select original_name, object_name, droptime from dba_recyclebin;
 
 
 2019-01-11:22:00:41
WRI$_RCS_40_1                                                            BIN$f0G4UCFjZhjgU0c4qMDLPw==$0                                       2019-01-12:06:00:30
WRI$_RCS_48_1                                                            BIN$gkuQPBaqV4/gU0c4qMASxw==$0                                       2019-02-19:22:00:42
WRI$_RCS_42_1                                                            BIN$gnPPNcXqBb3gU0c4qMA6Bw==$0                                       2019-02-21:22:01:38
WRI$_RCS_54_1                                                            BIN$gpQmLLLsS1/gU0c4qMDyEQ==$0                                       2019-02-23:12:36:35
WRI$_RCS_73_1                                                            BIN$gqctf7yrW7jgU0c4qMAUcw==$0                                       2019-02-24:11:18:43
WRI$_RCS_73_1                                                            BIN$gqqFqsN8ef3gU0c4qMA/yg==$0                                       2019-02-24:15:18:07
WRI$_RCS_76_1                                                            BIN$gq3ggMsCFT/gU0c4qMC4/g==$0                                       2019-02-24:19:18:16
 
ORIGINAL_NAME                                                            OBJECT_NAME                                                  DROPTIME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------
WRI$_RCS_73_1                                                            BIN$grE7cl5YLizgU0c4qMBnmA==$0                                       2019-02-24:23:18:26
TECHDATABASKET                                                           BIN$hAuG7Kvaan7gU0c4qMC1kA==$0                                       2019-03-14:05:27:11
 
13 rows selected.
 
 
SQL> SHOW RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TECHDATABASKET   BIN$hAuG7Kvaan7gU0c4qMC1kA==$0 TABLE        2019-03-14:05:27:11

4- Retrieving the table by “FLASHBACK TABLE SH.TECHDATABASKET TO BEFORE DROP;” (you can restore using the “RECYCLEBIN NAME” wether more the one output with the same name ) command:

?
1
2
3
SQL> FLASHBACK TABLE  SH.TECHDATABASKET  TO BEFORE DROP;
 
Flashback complete.

5- Queyring the TECHDATABASKET table after the restore:

?
1
2
3
4
5
SQL> SELECT COUNT(*) FROM TECHDATABASKET;
 
  COUNT(*)
----------
      2421

Related posts

Leave a Comment