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.