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
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)