Flashback feature in Oracle database is well known to roll back the table to a specific timestamp (depends on undo retention) or to restore dropped table and many more. We must have used this flashback feature so far on application schema objects and found working without any issues. As an Oracle DBA did you configure any tools (might be monitoring, RMAN catalog or any) with objects created in SYSTEM tablespace? If so, you probably would see some problems later when you need to use Flashback on these objects though they are not Oracle supplied objects.

Let us go through a case study which will clearly explain you the functionality of Flashback table.

Case study:

1. Let us log in to the database as SYSDBA and create a table in the non-system tablespace and insert some data into it.

SQL> create table tab1(c1 number) tablespace adotbs;
Table created.
SQL> insert into tab1 values(&c1); — Assume we inserted some data
SQL>commit;

2. Let us now drop the table and check the details in recycle bin.

SQL> drop table tab1;
Table dropped.
SQL> col object_name for a40
SQL> col OWNER for a5
SQL> col original_name for a5
SQL> set lines 100
SQL> select object_name,OWNER,original_name from dba_recyclebin where original_name=’TAB1′;
OBJECT_NAME OWNER ORIGI
—————————————- —– —–
BIN$Kohgr3qeCc7gUxV6qMDWaQ==$0 SYS TAB1

3. As object is found in recycle bin, we will able to flashback.

SQL> flashback table tab1 to before drop;
Flashback complete.
SQL> select object_name,OWNER,original_name from dba_recyclebin where original_name=’TAB1′;
no rows selected

4. Let us now create table tab2 into SYSTEM tablespace and insert some data.

SQL> create table tab2(c1 number);
Table created.
SQL> insert into tab2 values(&c1); — Assume we inserted some data
SQL>commit;

5. Now drop the table tab2 and check the recycle bin.

SQL> drop table tab2;
Table dropped.
SQL> col object_name for a40
SQL> col OWNER for a5
SQL> col original_name for a5
SQL> set lines 100
SQL> select object_name,OWNER,original_name from dba_recyclebin where original_name=’TAB2′;
no rows selected

6. As object is not found in recycle bin, you cannot flashback the table.

SQL> flashback table tab2 to before drop;
flashback table tab2 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Conclusion:

• Moving objects to recycle bin is disabled by Oracle database for objects in SYSTEM/SYSAUX tablespaces.
• It is not to say that Flashback doesn’t work on objects in SYSTEM tablespace, as objects are not available in recycle bin Flashback will not work.
• I am sure, no application data will be allowed to reside in SYSTEM tablespace.
• If you are in a plan of creating your own tools with objects in the database, always prefer user defined tablespace(s) and not default Oracle tablespaces.

Was this useful? Like it and share it.

Leave your comments!

Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.