Delete an oracle-user with single quotes in the username

Question

Through a faulty script I have created a user with single quotes around his username (i.e. his username is 'username', not username) on an Oracle 9i system. Now I want to remove that user. Neither "DROP USER 'username'" nor "DROP USER \'username\'" nor "DROP USER (SELECT username FROM all_users where user_id = 123)" worked. How do I get rid of that user?

Answer 1

create user "'bla'" identified by bla;

drop user "'bla'";

Answer 2

I don't know Oracle off-hand, but might you try enclosing it in double quotes?

(I'll delete this answer if its wrong)

Answer 3

Try DROP USER "'username'" or DROP USER ''username''. (Note that those last quotes are all single quotes)

Answer 4

According to Oracle's Documentation...

"A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object."

So this...

DROP USER "username" CASCADE;

Answer 5

I know this is an old post, but for anyone stumbling across this as the result of a search on this problem - the issue appears to be that a database trigger is firing on drop user. I've posted the solution I found for Oracle XE ( probably the same for other 10g releases) here

Hope someone finds this useful,

Mike

Answer 6

The following code might help you :

declare

sel_username varchar2(30); r_user_id varchar2(30); r_username varchar2(30); user_cmd varchar2(200);

BEGIN /* This procedure will delete a single user_id and can be used to delete a user with none displayable characters in the name

Replace the user_id in this script to that you want to delete !!

Author: Ulrich Henkenjohann - March 2010 / tested on ORACLE 10.2.0.4

*/
-- select the username for a special user_id. Ther username may contain none displayed characters

select username into sel_username from dba_users where user_id = 34; select user_id, username into r_user_id , r_username from dba_users where username = sel_username ; DBMS_OUTPUT.PUT_LINE('Selected user: ' || r_user_id || ' ' || r_username);

-- If a test is needed, an alter passwort command may be usefull -- user_cmd := 'ALTER USER "' || r_username || '" IDENTIFIED BY PASSWORDX ';

-- Drop the selected user

user_cmd := 'DROP USER "' || r_username || '" CASCADE '; DBMS_OUTPUT.PUT_LINE('Executing user_cmd: ' || user_cmd ); execute immediate user_cmd ;

END; /

Answer 7

Once again with better format:

declare

sel_username varchar2(30);

r_user_id varchar2(30);

r_username varchar2(30);

user_cmd varchar2(200);

BEGIN /*

This procedure will delete a single userid and can be used to delete a user with none displayable characters in the name

Replace the user_id in this script !!

Author: Ulrich Henkenjohann - March 2010 / tested on ORACLE 10.2.0.4

*/
-- select the username for a special user_id. Ther username may contain none displayed characters

select username into sel_username from dba_users where user_id = 34;

select user_id, username into r_user_id , r_username from dba_users where username = sel_username ;

DBMS_OUTPUT.PUT_LINE('Selected user: ' || r_user_id || ' ' || r_username);

-- If a test is needed, an alter passwort command may be usefull

-- user_cmd := 'ALTER USER "' || r_username || '" IDENTIFIED BY PASSWORDX ';

-- Drop the selected user

user_cmd := 'DROP USER "' || r_username || '" CASCADE ';

DBMS_OUTPUT.PUT_LINE('Executing user_cmd: ' || user_cmd );

execute immediate user_cmd ;

END; /