Thursday, October 5, 2017

Can we flashback a table dropped by sys user ?


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>