An interesting test

An interesting test

SQL> show recyclebinrows will be truncatedORIGNAME_PLUS_SHOW_RECYC         OBJECTNAME_PLUS_SHOW_RECYC     OBJTYPE_PLUS_S-------------------------------- ------------------------------ --------------EMP_1                            BIN$0Q40up5pfKPgQAB/AQAbjg==$0 TABLETEST                             BIN$4MQeqBIB0bfgQAB/AQAnyw==$0 TABLETEST                             BIN$4MQeqBIA0bfgQAB/AQAnyw==$0 TABLESQL> create table test as select * from "BIN$4MQeqBIA0bfgQAB/AQAnyw==$0";Table created.SQL> show recyclebinrows will be truncatedORIGNAME_PLUS_SHOW_RECYC         OBJECTNAME_PLUS_SHOW_RECYC     OBJTYPE_PLUS_S-------------------------------- ------------------------------ --------------EMP_1                            BIN$0Q40up5pfKPgQAB/AQAbjg==$0 TABLETEST                             BIN$4MQeqBIB0bfgQAB/AQAnyw==$0 TABLETEST                             BIN$4MQeqBIA0bfgQAB/AQAnyw==$0 TABLESQL> select * from "BIN$4MQeqBIA0bfgQAB/AQAnyw==$0";FILE_NAME------------------------------------------------------------------------------/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_users_814jk5bg_.dbf/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_undotbs1_814jk569_.dbf/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_sysaux_814jk53d_.dbf/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_system_814jk4rs_.dbf/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_example_814jq4jj_.dbfSQL> select * from test;FILE_NAME------------------------------------------------------------------------------/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_users_814jk5bg_.dbf/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_undotbs1_814jk569_.dbf/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_sysaux_814jk53d_.dbf/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_system_814jk4rs_.dbf/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_example_814jq4jj_.dbfSQL>

 

As to why recyclebin works, "The recycle bin is actually a data dictionary table containing information about dropped objects. dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. the objects in recyclebin only clear physical files in the following two conditions: 1. Manual purge 2. Space size constraints of the tablespace, if the space is insufficient, oracle automatically clears the objects.