RE: BIN$ objects
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <jdunn@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Oct 2006 12:43:35 -0700
In addition to what other posters have said, you can find the original names of
the objects in the views
user_recyclebin
dba_recyclebin
Nice thing about recyclebin : you can recover dropped objects until the recycle
bin is emptied (purged).
Also be aware that not all segments in the recylebin have names starting with
BIN$ (e.g. lob partitions - see Example 1) and that in some cases, an object in
the recycle bin can cause errors when trying to create a new object with the
same name (e.g. lob index name - see Example 2).
----------------- Example 1 : lob partition names do not change when object is
in the recycle bin
SQL> create table kubrick (n number, d date, c clob)
2 partition by range (n)
3 (partition kubrick_p1 values less than (maxvalue)) ;
Table created.
SQL> select segment_name, segment_type from user_segments order by 1 ;
SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
KUBRICK TABLE PARTITION
SYS_IL0000108833C00003$$ INDEX PARTITION
SYS_LOB0000108833C00003$$ LOB PARTITION
SQL> drop table kubrick ;
Table dropped.
SQL> select object_name, original_name from user_recyclebin ;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
SYS_IL0000108833C00003$$ SYS_IL0000108833C00003$$
SYS_IL0000108833C00003$$ SYS_IL0000108833C00003$$
SYS_LOB0000108833C00003$$ SYS_LOB0000108833C00003$$
SYS_LOB0000108833C00003$$ SYS_LOB0000108833C00003$$
BIN$IBvdtO4bKWbgRAADuhO6Yw==$0 KUBRICK
BIN$IBvdtO4bKWbgRAADuhO6Yw==$0 KUBRICK
6 rows selected.
SQL>
----------------- Example 2 - if a NAMED lob index is in the recycle bin, it
will prevent creation of a new object trying to reuse the lob index name, until
the old object is purged from the recyclebin
SQL> create table t (n number, c clob)
2 lob (c) store as t_c_lob (index t_c_lobidx) ;
SQL> select segment_name, segment_type from user_segments
2 where segment_name in ('T', 'T_C_LOB', 'T_C_LOBIDX')
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
T TABLE
T_C_LOB LOBSEGMENT
T_C_LOBIDX LOBINDEX
SQL> drop table t ;
Table dropped.
SQL> select object_name, original_name from user_recyclebin ;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
T_C_LOBIDX T_C_LOBIDX
BIN$IBvdtO4QKWbgRAADuhO6Yw==$0 T_C_LOB
BIN$IBvdtO4RKWbgRAADuhO6Yw==$0 T
SQL> create table t (n number, c clob)
2 lob (c) store as t_c_lob (index t_c_lobidx) ;
lob (c) store as t_c_lob (index t_c_lobidx)
*
ERROR at line 2:
ORA-00955: name is already used by an existing object
SQL> purge table t ;
Table purged.
SQL> create table t (n number, c clob)
2 lob (c) store as t_c_lob (index t_c_lobidx) ;
Table created.
_____________________________________________
De la part deJohn Dunn
I seem to have a large number of objects in my schema(Oracle 10 on Linux) with
names beginning BIN$
e.g. BIN$IBQisbCMbGfgQAUKRQFY4g==$0
Anyone know what they are and how they were created?
Other related posts:
