How to drop an oracle user starting with a colon (:)

Question

I used impdp and had a typo - now I have a username starting with a colon (:) - for example: my_schema.

How can I delete this user? I tried everything I could to avoid this, but nothing helps.

Edit: clarify - I know how to delete a user. I find it difficult to overcome the problem with a special symbol.

Answer 1

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

Answer 2

Have you tried to include it in double quotes? eg

drop user ":my_schema";

, - ": myschema" ": MYSCHEMA" - ?

Answer 3

I still could not do it, I had to restore it from backups.

Answer 4

You can use:

drop user :my_schema;

or

drop user :my_schema cascade;

If the user has restrictions in other tables, they will not be deleted.

drop table x cascade constraints; etc. 

The only problem is that you lose everything and start all over again. If there is a script in the database, you just need to restart the script to reload it.