ORA-38301 ORA-00955 drop and create table with BLOB

ORA-38301 ORA-00955 drop and create table with BLOB on Oracle 10gR2

ISSUE:

## You create a table containing BLOB for example:
##

SQL> create table uploads(
  2    NAME               VARCHAR(128)   UNIQUE NOT NULL,
  3    MIME_TYPE          VARCHAR(128),
  4    DOC_SIZE           NUMBER,
  DAD_CHARSET        VARCHAR(128),
  5    6    LAST_UPDATED       DATE,
  7    CONTENT_TYPE       VARCHAR(128),
  BLOB_CONTENT       BLOB,
  8    9    created_by    varchar2(30),
 10    notes         varchar2(4000),
 11    doc_id        number(15))
 12  LOB(blob_content) STORE AS upload_lobs (
 13     TABLESPACE fdb_lob
 14     --disable storage in row
 15     enable storage in row
   INDEX fdb_data
 16   17  );

Table created.


## you then drop this table
##

SQL> drop table uploads;

Table dropped.


## and decide to re-create it again (which causes ORA-00955)
##

SQL> create table uploads(
  2    NAME               VARCHAR(128)   UNIQUE NOT NULL,
  3    MIME_TYPE          VARCHAR(128),
  4    DOC_SIZE           NUMBER,
  5    DAD_CHARSET        VARCHAR(128),
  6    LAST_UPDATED       DATE,
  7    CONTENT_TYPE       VARCHAR(128),
  8    BLOB_CONTENT       BLOB,
  9    created_by    varchar2(30),
 10    notes         varchar2(4000),
 11    doc_id        number(15))
 12  LOB(blob_content) STORE AS upload_lobs (
 13     TABLESPACE fdb_lob
 14     --disable storage in row
 15     enable storage in row
 16     INDEX fdb_data
 17  )
 18  TABLESPACE fdb_data;
   INDEX fdb_data
         *
ERROR at line 16:
ORA-00955: name is already used by an existing object


## check of user_objects show that there are some objects 
## in the recycle bin

SQL> select object_name,object_type from user_objects;

OBJECT_NAME
------------------------------------------------
OBJECT_TYPE
-------------------
BIN$Pfqfpv8HFgDgRAAUT5umWA==$0
TABLE

BIN$Pfqfpv8GFgDgRAAUT5umWA==$0
LOB

BIN$Pfqfpv8FFgDgRAAUT5umWA==$0
INDEX


SOLUTION:

SQL> purge recyclebin;

Recyclebin purged.

SQL> select object_name,object_type from user_objects;

no rows selected

SQL> create table uploads(
  2    NAME               VARCHAR(128)   UNIQUE NOT NULL,
  3    MIME_TYPE          VARCHAR(128),
  4    DOC_SIZE           NUMBER,
  5    DAD_CHARSET        VARCHAR(128),
  6    LAST_UPDATED       DATE,
  7    CONTENT_TYPE       VARCHAR(128),
  8    BLOB_CONTENT       BLOB,
  9    created_by    varchar2(30),
 10    notes         varchar2(4000),
 11    doc_id        number(15))
 12  LOB(blob_content) STORE AS upload_lobs (
 13     TABLESPACE fdb_lob
   --disable storage in row
 14   15     enable storage in row
 16     INDEX fdb_data
 17  )
 18  TABLESPACE fdb_data;

Table created.

THANKS!  That was driving me nuts, I couldn't figure it out...

No problem!  Glad to help.