Home » SQL & PL/SQL » SQL & PL/SQL » Not able to drop users (merged)
icon9.gif  Not able to drop users (merged) [message #256443] Sat, 04 August 2007 14:46 Go to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
Hi guys ,
i have created a user containing special chracter quote in username.
create user "test's" identified by test.
user was succesfully created and confirmed in all_users table.

i face problem wen i drop this user

drop user "suren's" cascade;
its not allowing me to drop this user
users with any other special character can be created and dropped similarly without any help ..
omly this single quote is causing problem .
Any help ..guyzzz

i m waiitng anxiously ..
Smile

[Edit: remove garbage in title]

[Updated on: Sat, 04 August 2007 15:26] by Moderator

Report message to a moderator

Re: plzzz help ..Not able to drop users... [message #256444 is a reply to message #256443] Sat, 04 August 2007 15:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
its not allowing me to drop this user

What does this mean? What is the error?

Quote:
create user "test's" identified by test.

Quote:
drop user "suren's" cascade;

As this is not the same name, it is logical you can't drop it.

Instead of blabla, copy and paste your screen.

Regards
Michel
Re: plzzz help ..Not able to drop users... [message #256458 is a reply to message #256444] Sat, 04 August 2007 17:08 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Michel You are experinced try to read his MIND Wink
He expecting the same. Razz
Re: Not able to drop users [message #256462 is a reply to message #256443] Sat, 04 August 2007 17:28 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This is a known bug. You could connect as MDSYS and recreate the trigger SDO_DROP_USER as:
CREATE OR REPLACE TRIGGER "MDSYS"."SDO_DROP_USER" AFTER
DROP ON DATABASE declare
   stmt varchar2(200);
BEGIN
 if dictionary_obj_type = 'USER' THEN
   stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
	   ' WHERE SDO_OWNER = "' || dictionary_obj_name || '"' ;
   EXECUTE IMMEDIATE stmt;
 end if;
 end;
/

and then drop the users with the single quotes in their name.

To recreate the original trigger use:
CREATE OR REPLACE TRIGGER "MDSYS"."SDO_DROP_USER" AFTER
DROP ON DATABASE declare
   stmt varchar2(200);
BEGIN
     if dictionary_obj_type = 'USER' THEN
       stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
               ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
       stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
               ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
       stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
       stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
       stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
           ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
    end if;
end;
/

Re: Not able to drop users [message #256467 is a reply to message #256462] Sat, 04 August 2007 23:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Brian has a clearer crystal ball that I have. Laughing

A better version, using bind variables is:
CREATE OR REPLACE TRIGGER "MDSYS"."SDO_DROP_USER" 
after drop on DATABASE
declare
   stmt varchar2(200);
BEGIN
     if dictionary_obj_type = 'USER' THEN
       stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
     ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;

       stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
    ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;

       stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
    ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;

       stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
   ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;

       stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
   ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;

       stmt := 'DELETE FROM SDO_TOPO_METADATA_TABLE ' ||
   ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;

    end if;
end;

Regards
Michel
Re: Not able to drop users [message #256470 is a reply to message #256467] Sat, 04 August 2007 23:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Agree Michel, bind is the way to go. I actually just retrieved the code from the data dictionary itself.
Re: Not able to drop users [message #256472 is a reply to message #256470] Sat, 04 August 2007 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So did I but I have a newer version that's the secret. Cool

Regards
Michel
Re: Not able to drop users [message #256476 is a reply to message #256472] Sun, 05 August 2007 00:09 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Michel Cadot wrote on Sun, 05 August 2007 00:56
So did I but I have a newer version that's the secret.

11g I presume ?
Re: Not able to drop users [message #256478 is a reply to message #256443] Sun, 05 August 2007 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
ebrian ,
I'm curious. What is your local TZ?

HAND!
Re: Not able to drop users [message #256479 is a reply to message #256478] Sun, 05 August 2007 00:14 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
EST...I'm 3 hours ahead of you.
Re: Not able to drop users [message #256480 is a reply to message #256443] Sun, 05 August 2007 00:16 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
Oh MY!
You are much more of a night owl than I am.
Your efforts are appreciated.


HAND!
Re: Not able to drop users [message #256481 is a reply to message #256480] Sun, 05 August 2007 00:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
LOL...yeah and because of that it's hard to be an early bird. Typically, I try to get to bed by 11pm to get up at 4am.
Re: Not able to drop users [message #256482 is a reply to message #256479] Sun, 05 August 2007 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmm, this is midnight, isn't it? Exclamation
I thought I was the greatest Oracle junky, now I have some doubts...
./fa/917/0/
Regards
Michel
Re: Not able to drop users [message #256483 is a reply to message #256443] Sun, 05 August 2007 00:27 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
In round numbers it is 01:30 EDT (ebrian)& it is 10:30PM PDT (me) & I am fading fast; but I want to see how the SD Padres do 'cuz it tied & into extra innings.

http://sandiego.padres.mlb.com/index.jsp?c_id=sd

[Updated on: Sun, 05 August 2007 00:28] by Moderator

Report message to a moderator

Re: Not able to drop users [message #256486 is a reply to message #256483] Sun, 05 August 2007 00:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Well I hope Bonds doesn't break the record there. I think they may save it for his home stadium.

[Updated on: Sun, 05 August 2007 00:40]

Report message to a moderator

Re: Not able to drop users [message #256487 is a reply to message #256443] Sun, 05 August 2007 00:42 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
Bonds tied the 755 record tonite aganst SD.
I hope he sits out tomorrow.
I was glad that A-Rod got 500 today!

[Updated on: Sun, 05 August 2007 00:44] by Moderator

Report message to a moderator

Re: Not able to drop users [message #256489 is a reply to message #256443] Sun, 05 August 2007 01:07 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
Pardes WIN!
Re: Not able to drop users [message #256510 is a reply to message #256443] Sun, 05 August 2007 08:01 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
Hi..,
Thanks a tonn.. for such a prompt response.I am a novice to this field..,just a starter..please ignore my mistakes.
i didnt get what shud i do ?
where to write that trigger which u have suggested??
plzz elaborate a little.
i am using sqlplus /nolog

Thanks and regards,
surenhr
Re: Not able to drop users [message #256514 is a reply to message #256510] Sun, 05 August 2007 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After connecting as a DBA.

Regards
Michel
Re: Not able to drop users [message #256516 is a reply to message #256443] Sun, 05 August 2007 08:33 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
i have connected as
SQL> conn sys/tcs1234@suren as sysdba;
Connected.

after that what shud i do??????
should i paste that trigger code over there....??????
Re: Not able to drop users [message #256518 is a reply to message #256443] Sun, 05 August 2007 08:36 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
SQL> conn sys/tcs1234@suren as sysdba;
Connected.
SQL> CREATE OR REPLACE TRIGGER "MDSYS"."SDO_DROP_USER"
2 after drop on DATABASE
3 declare
4 stmt varchar2(200);
5 BEGIN
6 if dictionary_obj_type = 'USER' THEN
7 stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE
8 ' WHERE SDO_OWNER = :owner ';
9 EXECUTE IMMEDIATE stmt USING dictionary_obj_n
10
11 stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
12 ' WHERE SDO_OWNER = :owner ';
13 EXECUTE IMMEDIATE stmt USING dictionary_obj_n
14
15 stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
16 ' WHERE SDO_OWNER = :owner ';
17 EXECUTE IMMEDIATE stmt USING dictionary_obj_n
18
19 stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
20 ' WHERE SDO_OWNER = :owner ';
21 EXECUTE IMMEDIATE stmt USING dictionary_obj_n
22
23 stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE '
24 ' WHERE SDO_OWNER = :owner ';
25 EXECUTE IMMEDIATE stmt USING dictionary_obj_n
26
27 stmt := 'DELETE FROM SDO_TOPO_METADATA_TABLE
28 ' WHERE SDO_OWNER = :owner ';
29 EXECUTE IMMEDIATE stmt USING dictionary_obj_n
30
31 end if;
32 end;
33
34
35
36
37 !!!!!!!!!! after this what shud i do ...i m not getting how to proceed ?????]

thanks and regards
surenhr
Re: Not able to drop users [message #256519 is a reply to message #256516] Sun, 05 August 2007 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't connect as SYSDBA, connect with a login with DBA-like privilege.
Then, yes, you just have to paste the code.

Regards
Michel
Re: Not able to drop users [message #256520 is a reply to message #256518] Sun, 05 August 2007 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you should stop using Oracle before reading some basic books.
A procedure/trigger/package/... ends with "/" in first column.

Regards
Michel
Re: Not able to drop users [message #256524 is a reply to message #256519] Sun, 05 August 2007 09:03 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
SQL> conn surenhr/surenhr@suren;
Connected.
SQL> create user "tests's" identified by test;

User created.

SQL> select username from all_users;

USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
WMSYS
WKSYS
ORDSYS
ORDPLUGINS
MDSYS
CTXSYS
XDB

USERNAME
------------------------------
ANONYMOUS
ODM
ODM_MTR
WKPROXY
QS_ADM
HR
OE
PM
SH
RMAN
QS

USERNAME
------------------------------
QS_WS
QS_ES
QS_OS
QS_CBADM
QS_CB
QS_CS
SCOTT
SURENHR
tests's

31 rows selected.

SQL> drop user "tests's" cascade;
drop user "tests's" cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00933: SQL command not properly ended
ORA-06512: at line 7

still i m getting the same error..
Re: Not able to drop users [message #256527 is a reply to message #256489] Sun, 05 August 2007 09:16 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
anacedent wrote on Sun, 05 August 2007 02:07
Pardes WIN!

Congrats! Go figure, I ended up crashing in the middle of the 12th.
Re: Not able to drop users [message #256529 is a reply to message #256524] Sun, 05 August 2007 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for another trigger.
If you are not able to do this, then stop doing such things (using non capitals identifier) till you have a sufficient knowledge.

Regards
Michel
Single Quote username user cant be dropped [message #257601 is a reply to message #256443] Wed, 08 August 2007 13:56 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
Hi guys ,

i have created a user containing special chracter quote in username.

create user "suren's" identified by test.

user was succesfully created and confirmed in all_users table.

i face problem wen i drop this user

drop user "suren's" cascade;

its not allowing me to drop this user

users with any other special character can be created and dropped similarly without any issue ..

only this single quote is causing problem .

Any help

[Updated on: Wed, 08 August 2007 14:38] by Moderator

Report message to a moderator

Re: Single Quote username user cant be dropped [message #257602 is a reply to message #257601] Wed, 08 August 2007 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
error? what error? I don't see any error.

You're On Your Own (YOYO)!

Please read & FOLLOW posting guidelines as stated in the STICKY post at top of forum.
Re: Single Quote username user cant be dropped [message #257606 is a reply to message #257601] Wed, 08 August 2007 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't start another topic, continue on the previous.
About this one you don't answer my last question.

Regards
Michel
Re: Single Quote username user cant be dropped [message #257960 is a reply to message #257606] Thu, 09 August 2007 12:26 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
I tried but couldnot figure out any trigger to solve my purpose.
thats why i asked for help

Thanks and Regards
Surenhr
Re: Single Quote username user cant be dropped [message #257966 is a reply to message #257960] Thu, 09 August 2007 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no trigger to solve your issue.
There is a trigger generating your problem.

Regards
Michel
Re: Single Quote username user cant be dropped [message #258263 is a reply to message #257966] Fri, 10 August 2007 13:43 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
Thats what i want to know sir,How to solve it
what should i do So that username containing single quote gets dropped.
Thanks and regards
surenhr
Re: Single Quote username user cant be dropped [message #258268 is a reply to message #258263] Fri, 10 August 2007 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search in dba_triggers for all triggers that fire when you drop something.

Regards
Michel
Re: Single Quote username user cant be dropped [message #258283 is a reply to message #258268] Fri, 10 August 2007 15:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
surenhr,
although you can put whatever you want in a quoted user name (up to 30 bytes), it doesn't mean you should. I reproduced this particular case on my 10gR2 XE. It's best to use non quoted identifiers. That way, you're sure you don't run into this problem.

MHE
Re: Single Quote username user cant be dropped [message #258364 is a reply to message #258283] Sat, 11 August 2007 05:58 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
Thanks for your precious advice sir
Re: Single Quote username user cant be dropped [message #258374 is a reply to message #258364] Sat, 11 August 2007 06:27 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
@ebrian
Thank you very much gentlemen,
Your advice has solved my problem .
i am very very thankful for your kind support Sir Smile))
Re: Single Quote username user cant be dropped [message #258441 is a reply to message #258374] Sat, 11 August 2007 23:44 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You are quite welcome. Glad I could help. Thanks for responding back with your results.
Re: Single Quote username user cant be dropped [message #258567 is a reply to message #258441] Mon, 13 August 2007 01:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I wonder if 11g has resolved this. It's a nasty bug IMO.

MHE
Re: Single Quote username user cant be dropped [message #258573 is a reply to message #258567] Mon, 13 August 2007 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is already fixed in 10.2.0.3.
SQL> create user "tests's" identified by test;

User created.

SQL> drop user "tests's" ;

User dropped.

SQL> @v

Version Oracle : 10.2.0.3.0

Regards
Michel
Re: Single Quote username user cant be dropped [message #258577 is a reply to message #258573] Mon, 13 August 2007 02:00 Go to previous messageGo to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Good! Thanks for the feedback Michel! Time to upgrade my XE (it's a 10.2.0.1).

MHE
Previous Topic: Master Detail View
Next Topic: union query
Goto Forum:
  


Current Time: Fri Dec 09 11:57:24 CST 2016

Total time taken to generate the page: 0.10395 seconds