Dropping quoted users – why sometimes Oracle can’t bear to say goodbye

After messing about with various database objects with unusual usernames, I discovered a little quirk in Oracle XE.
Obviously, you can create usernames containing quotation marks simply by quoting them on creation :

CREATE USER “MR SPOCK” IDENTIFIED BY x;
CREATE USER “MILES O'BRIEN” IDENTIFIED BY x;

And you can drop them in the same way, can’t you ?

DROP USER “MR SPOCK”;

User dropped

Logical ;-)

SQL> drop user "MILES O'BRIEN"; 
drop user "MILES O'BRIEN" 
* 
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 

Sneak attack by the Klingons ? Sabotage by the Romulans ? Orion Syndicate after revenge ?
Continue reading

Why setting Default Tablespace on the Database affects some Oracle Internal Users

Just following on from my previous post, I’ve done a bit of investigation to find out why issuing ALTER DATABASE DEFAULT TABLESPACE tsname affects some Oracle Internal users.
Remember, in the case of my standard Oracle XE installation, the offending users were DIP, MDSYS and TSMSYS.

Apparently, this command will only apply to users that have been created without a default tablespace being specified. This is how Oracle creates these users when it creates the XE database.

So, once you’ve changed the default tablespace on the database in this way, it’s advisable to go through the Oracle internal users and set the default tablespace back to what it was previously.