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.

Slave Driving – Getting SQL*Plus to do it for you

In my haste to play around in my shiny new Oracle XE instance, I’ve forgotten to set the Default Tablespace for any new users I create.
As a result I’ve got few tables and indexes in the SYSTEM tablespace. Fortunately, I don’t have to go through the drudgery of moving all of them by hand, I can get SQL*Plus to do it for me. Continue reading