Oracle Flashback does not work for tables on the system tablespace

I was playing around with Oracle Flashback, to see when what happens when we have flashback enabled and we drop a table and on how it can be retrieved.

I connected to the database as user sys

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Tue May 8 10:33:43 2012 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user; USER is "SYS" SQL>

I then did the following to start using Flashback. I first put the database in archivelog mode, and then set up
the DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST_SIZE, and DB_RECOVERY_FILE_DEST

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 6948155392 bytes Fixed Size                  2138944 bytes Variable Size            3758099648 bytes Database Buffers         3154116608 bytes Redo Buffers               33800192 bytes Database mounted. SQL> SQL>  alter database archivelog; Database altered.

The flashback retention was set for 2880 minutes(2 days), with a recovery destination size of 4 G, on filesystem /bigfs.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=2880; System altered. SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=4G; System altered. SQL> alter system set DB_RECOVERY_FILE_DEST='/bigfs'; System altered. SQL> alter database flashback off; Database altered. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered. SQL>

After the database was opened I created a table called beatles_table, and entered some data into it.

SQL> create table beatles_table (   2  id number(2),   3  name varchar2(30)   4  ); SQL> insert into beatles_table values (1, 'John'); 1 row created. SQL> insert into beatles_table values (2, 'Paul'); 1 row created. SQL> insert into beatles_table values (3, 'George'); 1 row created. SQL> insert into beatles_table values (4, 'Ringo'); 1 row created. SQL>

As we see the owner of the table is SYS and the table is on the system tablespace

SQL> select owner, table_name, tablespace_name   2  from all_tables where   3  table_name='BEATLES_TABLE'; OWNER                          TABLE_NAME ------------------------------ ------------------------------ TABLESPACE_NAME ------------------------------ SYS                            BEATLES_TABLE SYSTEM SQL> SQL> drop table BEATLES_TABLE; Table dropped. SQL> SQL> flashback table "BEATLES_TABLE" to before drop; flashback table "BEATLES_TABLE" to before drop * ERROR at line 1: ORA-38305: object not in RECYCLE BIN SQL> select * from RECYCLEBIN; no rows selected SQL> SQL> select * from DBA_RECYCLEBIN where ORIGINAL_NAME='BEATLES_TABLE'; no rows selected SQL> SQL> select * from BEATLES_TABLE; select * from BEATLES_TABLE               * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> create table beatles_table (   2  id number(2),   3  name varchar2(30)   4  ) tablespace users; Table created. SQL> SQL> select owner, table_name, tablespace_name   2  from all_tables where   3  table_name='BEATLES_TABLE'; OWNER                          TABLE_NAME ------------------------------ ------------------------------ TABLESPACE_NAME ------------------------------ SYS                            BEATLES_TABLE USERS SQL> insert into beatles_table values (1, 'John'); 1 row created. SQL> insert into beatles_table values (2, 'Paul'); 1 row created. SQL> insert into beatles_table values (3, 'George'); 1 row created. SQL> insert into beatles_table values (4, 'Ringo'); 1 row created. SQL> SQL> drop table BEATLES_TABLE; Table dropped. SQL> select * from RECYCLEBIN; OBJECT_NAME                    ORIGINAL_NAME                    OPERATION ------------------------------ -------------------------------- --------- TYPE                      TS_NAME                        CREATETIME ------------------------- ------------------------------ ------------------- DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN ------------------- ---------- -------------------------------- --- ---    RELATED BASE_OBJECT PURGE_OBJECT      SPACE ---------- ----------- ------------ ---------- BIN$v4sirVl4AHrgQwkLUw0Aeg==$0 BEATLES_TABLE                    DROP TABLE                     USERS                          2012-05-08:10:57:50 2012-05-08:10:58:35    2786532                                  YES YES      72633       72633        72633          8 SQL> select * from BEATLES_TABLE; select * from BEATLES_TABLE               * ERROR at line 1: ORA-00942: table or view does not exist SQL> flashback table "BEATLES_TABLE" to before drop; Flashback complete. SQL> select * from BEATLES_TABLE;         ID NAME ---------- ------------------------------          1 John          2 Paul          3 George          4 Ringo SQL> SQL> select * from RECYCLEBIN; no rows selected SQL>