Why do I keep seeing tables with garbage names like BIN${something}

The Oracle (tm) Users' Co-Operative FAQ

Why do I keep seeing tables with garbage names like BIN${something}

In 10g, Oracle introduced an option for “undropping” a table, which we can demonstrate with the following script in 10g Release 2:

    create table t1(n1 number);

    create index i1 on t1(n1);

    select table_name from user_tables;

    select index_name from user_indexes;

    select object_name, object_type from user_objects;

    drop table t1;

    select table_name from user_tables;

    select index_name from user_indexes;

    select object_name, object_type from user_objects;

    flashback table t1 to before drop;

    select table_name from user_tables;

    select index_name from user_indexes;

    select object_name, object_type from user_objects;

In my test on 10.2.0.1, the queries after the create table and index showed the following (expectd) results:

    TABLE_NAME

    --------------------------------

    T1

    INDEX_NAME

    --------------------------------

    I1

    OBJECT_NAME                              OBJECT_TYPE

    ---------------------------------------- -------------------

    I1                                       INDEX

    T1                                       TABLE

The queries following the drop showed the following:

    No rows for user_tables – earlier versions may show a strangely named table

    No rows for user_indexes - earlier versions may show a strangely named table

    OBJECT_NAME                              OBJECT_TYPE

    ---------------------------------------- -------------------

    BIN$HULdSlmnRZmbCXAl/pkA9w==$0           TABLE

    BIN$pyWpLnQwTbOUB9rQrbwgPA==$0           INDEX

The table and its indexes have not been eliminated from the database, or from the data dictionary. They have been renamed, and hidden from the table and index data dictionary views (at least, they are hidden in later versions of 10g, you could still see them in some of the earlier versions). You can get them back if you want to, you can clear them out explicitly if you want to, and (most importantly) although they count against your space quota, they will vanish spontaneously – i.e. be “properly dropped” if you account needs more space in the relevant tablespace and it isn’t available.

After the “undrop” command, the queries showed the following:

    TABLE_NAME

    --------------------------------

    T1

    INDEX_NAME

    --------------------------------

    BIN$pyWpLnQwTbOUB9rQrbwgPA==$0

    OBJECT_NAME                              OBJECT_TYPE

    ---------------------------------------- -------------------

    T1                                       TABLE

    BIN$pyWpLnQwTbOUB9rQrbwgPA==$0           INDEX

Note how the table name has re-appeared with the correct name, but Oracle has failed to restore the index name properly – although it has made it visible. If you do “undrop” objects, make sure you check the names of dependent objects (including such things as constraints). You may need to rename them. I assume that Oracle has not renamed the secondary objects because there is a risk that you may have created other objects with conflicting names: manual resolution is the only sensible approach. Bear in mind it is also possible for Oracle to get rid of a dropped index when there is pressure for space, so when a table is “undropped”, some of its indexes could actually be missing. Because there are non-standard naming characters in the index name, you will have to quote the name when renaming it, e.g.

    alter index " BIN$pyWpLnQwTbOUB9rQrbwgPA==$0" rename  to i1;

To get rid of the objects manually, you can use one of three commands (if you have the relevant privilege)

    purge user_recyclebin;

    purge dba_recyclebin;

    purge recyclebin;

Alternatively, if you want to avoid the recycle bin completely, you can change your drop table call:

    drop table t1 purge;

Note – when you drop just an index, it does not go into the recycle bin, it really is dropped.

Further reading: SQL Reference Manual – flashback table