RE: BIN$ objects

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 18 Oct 2006 12:43:35 -0700
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9ADE@ALVMBXW05.prod.quest.corp>

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?

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 18 2006 - 14:43:35 CDT