It depends on the ownership of the table that being dropped by sys.
If SYS user dropped a table that has been owned by some other user, then we can flashback that table.
But what if SYS dropped its on table? Can we flashback ? As recyclebin does not work for objects owned by SYS user, we can't flashback a table that has been owned by SYS.
Here is an example for this.
C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 6 22:36:35 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> sho user;
USER is "SYS"
SQL>
SQL> drop table hr.emp;
Table dropped.
SQL> select OBJECT_NAME,ORIGINAL_NAME from dba_recyclebin;
OBJECT_NAME
--------------------------------------------------------------------------------
ORIGINAL_NAME
--------------------------------------------------------------------------------
BIN$cTyO8WzRQFGqDa97TfzN8A==$0
EMP
SQL> flashback table hr.emp to before drop;
Flashback complete.
SQL> select count(1) from hr.emp;
COUNT(1)
----------
107
SQL> create table ta as select * from dba_users;
Table created.
SQL> select table_name,owner from dba_tables where table_name='TA';
TABLE_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
TA
SYS
SQL> drop table ta;
Table dropped.
SQL> select OBJECT_NAME,ORIGINAL_NAME from dba_recyclebin;
no rows selected
SQL> flashback table ta to before drop;
flashback table ta to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL>
I really appreciate the information shared above. It’s of great help. If someone wants to learn Online (Virtual) instructor lead live training in #TABLEAU, kindly contact us http://www.maxmunus.com/contact
ReplyDeleteMaxMunus Offer World Class Virtual Instructor-led training on #TABLEAU. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ pieces of training in India, USA, UK, Australia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain, and UAE etc.
Avishek Priyadarshi
MaxMunus
E-mail: avishek@maxmunus.com
Skype id: avishek_2.
Ph:(0) 8553177744 / 080 - 41103383
http://www.maxmunus.com/