How to drop oracle user starting with colon (:)

I've used impdp and had a typo - now I've got a user name starting with colon (:) - e.g :my_schema.

How can I drop this user? I've tried everything I could think of to escape it, but nothing helps.

开发者_StackOverflow

Edit: To clarify - I know how to drop a user. I'm having difficulty overcoming the special character issue.

---

Did you try enclosing it in double quotes? e.g

drop user ":my_schema";

The case is important when you do this - is it ":myschema" or ":MYSCHEMA" or something in between?

---

It seems you can do this with dynamic SQL:

begin
    execute immediate 'create user ":MY_SCHEMA" identified by xxx';
end;
/

PL/SQL procedure successfully completed.

select username, account_status from dba_users where username = ':MY_SCHEMA';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
:MY_SCHEMA                     OPEN

begin
    execute immediate 'drop user ":MY_SCHEMA"';
end;
/

PL/SQL procedure successfully completed.

select username, account_status from dba_users where username = ':MY_SCHEMA';

no rows selected

---

I never managed to do this, had to restore from backups.

---

You could either use:

drop user :my_schema;

or

drop user :my_schema cascade;

if the user has constraints in other tables then they will not be removed.

drop table x cascade constraints; etc. 

the only problem there is that you loose everything and have to start over. If you database has a script then you just to need to re run the script to reload it in.