Extra entries in recycle bin when table dropped

Question

I have created a table TESTTABLE28.

Then I dropped the table.DROP TABLE TESTTABLE28; The show parameter recyclebin; value was set to on. When I checked the Recycle Bin, observed some other entries along with TESTTABLE28 are listed,

Once I flashback table TESTTABLE28 to before drop; to restore the table, these extra entries are also moved. What is this values and why it didn't come, when I dropped another table TESTTABLE31 which has only one varchar2 column?

UPDATE

Also how can I restore the constraints which is showing BIN$5lpccCurTNWwCbOSxCK29w==$1 now instead of TESTTABLE28_PK.

Answer 1

Because you have LOB columns in your table you also have segments and indices to them:

SQL> create table t_lb (x int, y clob, z blob);

SQL> col column_name format a10
SQL> col segment_name format a30
SQL> col index_name format a30
SQL> select column_name, segment_name, index_name from user_lobs
  2  where table_name = 'T_LB'
  3  /

COLUMN_NAM SEGMENT_NAME                   INDEX_NAME                            
---------- ------------------------------ ------------------------------        
Y          SYS_LOB0000701495C00002$$      SYS_IL0000701495C00002$$              
Z          SYS_LOB0000701495C00003$$      SYS_IL0000701495C00003$$              

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
recyclebin                           string      ON                             
SQL> drop table t_lb;

SQL> col object_name format a30
SQL> col type format a30
SQL> select object_name, type from recyclebin
  2  /

OBJECT_NAME                    TYPE                                             
------------------------------ ------------------------------                   
BIN$8hw9X/AeBmngQ0QTGKxsAQ==$0 TABLE                                            
SYS_LOB0000701495C00003$$      LOB                                              
SYS_LOB0000701495C00002$$      LOB                                              
SYS_IL0000701495C00003$$       LOB INDEX                                        
SYS_IL0000701495C00002$$       LOB INDEX    

As of constraint/index restoring - you should rename them back:

SQL> create table t (x int, constraint t_pk primary key(x))
  2  /

SQL> drop table t;


SQL> select object_name, type from recyclebin;

OBJECT_NAME                    TYPE                                             
------------------------------ ------------------------------                   
BIN$8hw9X/AiBmngQ0QTGKxsAQ==$0 TABLE                                            
BIN$8hw9X/AhBmngQ0QTGKxsAQ==$0 INDEX                                            

SQL> flashback table t to before drop;

SQL> select constraint_name, index_name, constraint_type from
  2  user_constraints where table_name = 'T';

CONSTRAINT_NAME                INDEX_NAME                     C                 
------------------------------ ------------------------------ -                 
BIN$8hw9X/AgBmngQ0QTGKxsAQ==$0 BIN$8hw9X/AhBmngQ0QTGKxsAQ==$0 P                 

SQL> begin
  2   for cur in (select constraint_name, index_name
  3    from user_constraints where table_name = 'T'
  4  and constraint_type = 'P') loop
  5   execute immediate
  6  'alter table t rename constraint "'||cur.constraint_name||
  7  '" to t_pk';
  8  execute immediate
  9  'alter index "'||cur.index_name||'" rename to t_pk';
 10  end loop;
 11  end;
 12  /


SQL> select constraint_name, index_name from user_constraints
  2  where table_name = 'T';

CONSTRAINT_NAME                INDEX_NAME                                       
------------------------------ ------------------------------                   
T_PK                           T_PK