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:
[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:
SQL> drop table TECHDATABASKET; Table dropped.
3- Querying the dropped table using dba_recyclebin table or by “SHOW RECYCLEBIN;” command:
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:
SQL> FLASHBACK TABLE SH.TECHDATABASKET TO BEFORE DROP; Flashback complete.
5- Queyring the TECHDATABASKET table after the restore:
SQL> SELECT COUNT(*) FROM TECHDATABASKET;
COUNT(*)
----------
2421
*The views expressed here are my own and do not represent those of my employer.*
Hello, I’m Bruno — a dual citizen of Brazil and Sweden. I bring a global perspective shaped by experiences in both South America and Europe, with a strong focus on collaboration and innovation across cultures. I am a Computer Scientist, PhD Candidate in Information and Communication Technologies, focusing on Data Science and Artificial Intelligence, and hold dual Master’s degrees in Data Science and Cybersecurity. With over fifteen years of international experience spanning Brazil, Hungary, and Sweden, I have collaborated with global organizations such as IBM, Playtech, and Oracle, as well as contributed remotely to projects across multiple regions. My professional interests include Databases, Cybersecurity, Cloud Computing, Data Science, Data Engineering, Big Data, Artificial Intelligence, Programming, and Software Engineering, all driven by a deep passion for transforming data into strategic business value.