An interesting question about ORACLE FLASHBACK TABLE

We know that,ORACLEThe FLASHBACK feature is added for 10 Gb.FLASHBACK TABLEIt is used to repair tables that are accidentally deleted. However, today we found an interesting problem with the flashback table. Let's take a look at the following: I will use an example to illustrate the interesting problem I encountered. You are welcome to discuss it.

 
 

  
  
SQL> CONNECT SYSDBA/***** AS SYSDBA
  
  
 
  
  
Connected.
  
  
 
  
  
SQL> CREATE TABLE TEST (T VARCHAR2 (10 ));
  
  
 
  
  
The table has been created.
  
  
 
  
  
SQL> DROP TABLE TEST;
  
  
 
  
  
The table has been deleted.
  
  
 
  
  
SQL> FLASHBACK TABLE TEST TO BEFORE DROP;
  
  
 
  
  
FLASHBACK TABLE TEST TO BEFORE DROP
  
  
 
  
  
*
  
  
 
  
  
Row 3 has an error:
  
  
 
  
  
ORA-38305: the object is not in the recycle bin
 
 

ORA-38305 error, at first I thought it was caused by the recycle bin mechanism parameter OFF, Oracle10g, introduced the recycle bin mechanism, the drop data table is saved in the recycle bin. When a data table is deleted by mistake, you can recycle it from the recycle bin. The recycle bin mechanism is similar to the recycle bin mechanism on Windows. In Windows, when we choose to delete a file, the file is not deleted from the hard disk in essence, but the file is renamed in one form, so that you can see it from the recycle bin. So I checked the RECYCLEBIN parameter.

 

The strange thing is that the recycle bin parameter is enabled. The SQL> SHOW RECYCLEBIN; table cannot be deleted. What the hell is going on? It took a long time. Later, I modified the tablespace used to create the table, and everything was OK.

 
 

  
  
SQL> CREATE TABLE TEST (T VARCHAR2 (10) TABLESPACE USERS;
  
  
 
  
  
The table has been created.
  
  
 
  
  
SQL> DROP TABLE TEST;
  
  
 
  
  
The table has been deleted.
  
  
 
  
  
SQL> SHOW RECYCLEBIN;
  
  
 
  
  
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
  
  
 
  
  
-----------------------------------------------------------------------------
  
  
 
  
  
Test bin $ gVtRcd2NTqihW6yM4vs0Hw = $0 TABLE: 12: 40: 42
  
  
 
  
  
SQL> FLASHBACK TABLE TEST TO BEFORE DROP;
  
  
 
  
  
Flash back complete.
  
  
 
  
  
SQL> SELECT * FROM TEST;
  
  
 
  
  
Unselected row
 
 

If you do not specify the table space to be created, the default table space SYSTEM is used. You can view the table space using SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'test. Later, I repeated this experiment multiple times, and the results are all the same. It seems that the flashback table feature cannot be used in the tablespace SYSTEM. It is very interesting. Later I checked the information to know: you cannot 'flashback table to before drop 'a table which has been created in the SYSTEM tablespace. the table is sent to the recyclebin only if it existed in some other tablespace other than SYSTEM tablespace and that tablespace must be locally managed. it seems that ORACLE limits the use of the recycle bin mechanism in the tablespace SYSTEM. I don't know why ORACLE has this restriction.

We will introduce the Oracle database flashback table knowledge here, hoping to help you.