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.
开发者_StackOverflowEdit: 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.
精彩评论