asked on

unable to manipulate oracle table name containing special characters

hey all -
a modeling program (erwin) created this table, which i am currently unable to drop. i am using dbvisualizer to connect to the db. the table name is:

DBDB.BIN$6YefzzGY8ingMAB/AQBlXA==$0

trying to kill it:

drop table DBDB.BIN$6YefzzGY8ingMAB/AQBlXA==$0
ORA-00933: SQL command not properly ended

drop table "DBDB.BIN$6YefzzGY8ingMAB/AQBlXA==$0"
ORA-00972: Identifier is too long

drop table 'DBDB.BIN$6YefzzGY8ingMAB/AQBlXA==$0'
drop table '"DBDB.BIN$6YefzzGY8ingMAB/AQBlXA==$0"'
drop table 'DBDB.BIN$6YefzzGY8ingMAB/AQBlXA\=\=$0'
drop table '"DBDB.BIN$6YefzzGY8ingMAB/AQBlXA\=\=$0"'
ORA-00903: invalid table name

i have played with some other things as well. still can't kill it - any suggestions?? tell me how to kill this thing!!

paquicuba

SQL> create table "$MAB/AQBlXA==$0" (col1 number);

Table created.

SQL> drop table "$MAB/AQBlXA==$0";

Table dropped.

It worked, I just couldn't use the whole name, got identifier is too long error

taveirne

ASKER

i was able to
SELECT DUMMY_FLD || 'BIN$6YefzzGY8ingMAB/AQBlXA==$0' FROM DBSB.DUMMY_TBL having no problems so it doesn't seem like it is an invalid character in there;

with your query and the one i tried earlier that brings us back to:

drop table "DBDB.BIN$6YefzzGY8ingMAB/AQBlXA==$0"
ORA-00972: Identifier is too long

how can i correct this? the table name is 30 characters. i'm not able to view indexes or other information with dbvisualizer, it doesn't seem to be able to execute SQL on the table to populate several of the panes in the application.

paquicuba

You don't need the Schema here:

SQL> create table "BIN$6YefzzGY8ingMAB/AQBlXA==$0"(col1 number);

Table created.

SQL> drop table "BIN$6YefzzGY8ingMAB/AQBlXA==$0";

Table dropped.

taveirne

ASKER

i get a 942 - table or view does not exist if i try that. when i log in with the dbvisualizer client i see multiple databases so i believe the DB name is necessary; but it shouldn't be the component the complaint is about is it?

legendcain

drop table DBDB."BIN$6YefzzGY8ingMAB/AQBlXA==$0";

taveirne

ASKER

ORA-38301: can not perform DDL/DML over objects in Recycle Bin

ASKER CERTIFIED SOLUTION

SMartinHamburg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

legendcain

oooohhhh... you gotta purge it! :)

silly me, it's a deleted table...

select ORIGINAL_NAME from RECYCLEBIN where OBJECT_NAME='BIN$6YefzzGY8ingMAB/AQBlXA==$0';

then, whatever name that returns to you, perform the following:

purge table [ORIGINAL_NAME];

example:

sqlplus> create table T (X integer);
Table created.
sqlplus> drop table t;
Table dropped.sqlplus> select original_name, object_name from recyclebin;
ORIGINAL_NAME OBJECT_NAME
-------------------------------- ------------------------------
T BIN$DLsVweqdRDaoyjTfAdfFnA==$0
sqlplus> purge table t;
Table purged.

hope that does the trick

pratikroy

The BIN$... Object is a dropped trigger (it appears to be anyway), that is in the recycle bin, and apparently the drop user statement seems to be having trouble deleting the user with an associated object in the recycle bin.

To try and get around it, try going the web-enable enterprise manager, the administration page, then Tables (or actaully any object), filter for the owner of the object, the the recycle bin button appears. Or, from the sql prompt, you can do a "purge dba_recyclebin;" (to empty the recycle bin for all users, or connect as the user and issue a "purge recyclebin;".

I think this will get around the problem you are experiencing.

taveirne

ASKER

i did a select * from recyclebin and got nothing, but what is working for me is

purge table DBDB."funkyname"

thanks guys!