Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3643
  • Last Modified:

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!!

0
taveirne
Asked:
taveirne
  • 4
  • 2
  • 2
  • +2
1 Solution
 
paquicubaCommented:
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
0
 
taveirneAuthor Commented:
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.
0
 
paquicubaCommented:
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
taveirneAuthor Commented:
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?
0
 
legendcainCommented:
drop table DBDB."BIN$6YefzzGY8ingMAB/AQBlXA==$0";


0
 
taveirneAuthor Commented:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
0
 
SMartinHamburgCommented:
this seems to be an already dropped table. Oracle 10 puts it in a recycle bin
try PURGE TABLE DBDB.BIN$6YefzzGY8ingMAB/AQBlXA==$0;
to finally get rid of it.
Or PURGE TABLESPACE <table_space_name>;
to get rid rid of all of them for a table space.
See http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html
as a backgrounder.

Regards
0
 
legendcainCommented:
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
0
 
pratikroyCommented:
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.

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

purge table DBDB."funkyname"

thanks guys!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now