OraFAQ Forum: SQL & PL/SQL » Not able to drop users (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Not able to drop users (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not able to drop users (merged) [message #256443]

Sat, 04 August 2007 14:46

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

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: plzzz help ..Not able to drop users... [message #256458 is a reply to message #256444]

Sat, 04 August 2007 17:08

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Michel You are experinced try to read his MIND Wink

Wink

He expecting the same. Razz

Razz

Report message to a moderator

Re: Not able to drop users [message #256462 is a reply to message #256443]

Sat, 04 August 2007 17:28

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;
/

Report message to a moderator

Re: Not able to drop users [message #256467 is a reply to message #256462]

Sat, 04 August 2007 23:46

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Brian has a clearer crystal ball that I have. Laughing

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

Report message to a moderator

Re: Not able to drop users [message #256470 is a reply to message #256467]

Sat, 04 August 2007 23:54

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.

Report message to a moderator

Re: Not able to drop users [message #256472 is a reply to message #256470]

Sat, 04 August 2007 23:56

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So did I but I have a newer version that's the secret. Cool

Cool

Regards
Michel

Report message to a moderator

Re: Not able to drop users [message #256476 is a reply to message #256472]

Sun, 05 August 2007 00:09

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 ?

Report message to a moderator

Re: Not able to drop users [message #256478 is a reply to message #256443]

Sun, 05 August 2007 00:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ebrian ,
I'm curious. What is your local TZ?

HAND!

Report message to a moderator

Re: Not able to drop users [message #256479 is a reply to message #256478]

Sun, 05 August 2007 00:14

ebrian
Messages: 2794
Registered: April 2006

Senior Member

EST...I'm 3 hours ahead of you.

Report message to a moderator

Re: Not able to drop users [message #256480 is a reply to message #256443]

Sun, 05 August 2007 00:16

BlackSwan
Messages: 26766
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!

Report message to a moderator

Re: Not able to drop users [message #256481 is a reply to message #256480]

Sun, 05 August 2007 00:21

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.

Report message to a moderator

Re: Not able to drop users [message #256482 is a reply to message #256479]

Sun, 05 August 2007 00:22

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Hmm, this is midnight, isn't it? Exclamation

Exclamation

I thought I was the greatest Oracle junky, now I have some doubts...
/forum/fa/917/0/

/forum/fa/917/0/

Regards
Michel

Report message to a moderator

Re: Not able to drop users [message #256483 is a reply to message #256443]

Sun, 05 August 2007 00:27

BlackSwan
Messages: 26766
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

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

BlackSwan
Messages: 26766
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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Pardes WIN!

Report message to a moderator

Re: Not able to drop users [message #256510 is a reply to message #256443]

Sun, 05 August 2007 08:01

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

Report message to a moderator

Re: Not able to drop users [message #256514 is a reply to message #256510]

Sun, 05 August 2007 08:28

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

After connecting as a DBA.

Regards
Michel

Report message to a moderator

Re: Not able to drop users [message #256516 is a reply to message #256443]

Sun, 05 August 2007 08:33

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....??????

Report message to a moderator

Re: Not able to drop users [message #256518 is a reply to message #256443]

Sun, 05 August 2007 08:36

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

Report message to a moderator

Re: Not able to drop users [message #256519 is a reply to message #256516]

Sun, 05 August 2007 08:37

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Not able to drop users [message #256520 is a reply to message #256518]

Sun, 05 August 2007 08:39

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Not able to drop users [message #256524 is a reply to message #256519]

Sun, 05 August 2007 09:03

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

Report message to a moderator

Re: Not able to drop users [message #256527 is a reply to message #256489]

Sun, 05 August 2007 09:16

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.

Report message to a moderator

Re: Not able to drop users [message #256529 is a reply to message #256524]

Sun, 05 August 2007 09:30

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Single Quote username user cant be dropped [message #257601 is a reply to message #256443]

Wed, 08 August 2007 13:56

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Single Quote username user cant be dropped [message #257606 is a reply to message #257601]

Wed, 08 August 2007 14:17

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Single Quote username user cant be dropped [message #257960 is a reply to message #257606]

Thu, 09 August 2007 12:26

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

Report message to a moderator

Re: Single Quote username user cant be dropped [message #257966 is a reply to message #257960]

Thu, 09 August 2007 12:35

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no trigger to solve your issue.
There is a trigger generating your problem.

Regards
Michel

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258263 is a reply to message #257966]

Fri, 10 August 2007 13:43

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

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258268 is a reply to message #258263]

Fri, 10 August 2007 13:51

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Search in dba_triggers for all triggers that fire when you drop something.

Regards
Michel

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258283 is a reply to message #258268]

Fri, 10 August 2007 15:30

Maaher
Messages: 7065
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

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258364 is a reply to message #258283]

Sat, 11 August 2007 05:58

surenhr
Messages: 45
Registered: August 2007
Location: gurgoan

Member

Thanks for your precious advice sir

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258374 is a reply to message #258364]

Sat, 11 August 2007 06:27

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

Smile

))

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258441 is a reply to message #258374]

Sat, 11 August 2007 23:44

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You are quite welcome. Glad I could help. Thanks for responding back with your results.

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258567 is a reply to message #258441]

Mon, 13 August 2007 01:48

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I wonder if 11g has resolved this. It's a nasty bug IMO.

MHE

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258573 is a reply to message #258567]

Mon, 13 August 2007 01:54

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Single Quote username user cant be dropped [message #258577 is a reply to message #258573]

Mon, 13 August 2007 02:00

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Good! Thanks for the feedback Michel! Time to upgrade my XE (it's a 10.2.0.1).

MHE

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	Master Detail View
Next Topic:	union query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 15:51:37 CST 2024