Oracle – for when it was like that when you got there

Main menu

Post navigation

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.
First off though, I’m going to make sure that all the existing users (as well as any new users I create) use the USERS tablespace as their Default tablespace unless I specify otherwise.
Connect to the database as a user with the DBA role and type :-

ALTER DATABASE DEFAULT TABLESPACE users;

Job done. Well…not quite.
Whilst the Oracle supplied users have retained there default tablespaces ( oddly, apart from MDSYS, DIP and TSMSYS), and the users I created now have USERS as they’re default tablespace, the segments created by those users are still sitting in the SYSTEM tablespace.

As an aside, I’ll have to look into why those three Oracle supplied users had their default tablespaces re-allocated, but that’s for another day.
Right now however, I’ve come over all masterful. I’m not going to move those segments to the correct tablespace, I’m going to get SQL*Plus to do it for me.

A couple of things I should mention at this point.

As I’m doing all this on XE – which does not come with the Partitioning Option – and I haven’t got round to doing anything with LOBs just yet on this instance, the only segment types I need to worry about are Tables and Indexes. On a “proper” Oracle instance, these segment types would need to be accounted for.
The other thing to mention is that I know which tablespace these segments should reside in because all of the segment owners have the same default tablespace assigned – i.e. USERS. Once again, on a “proper” instance, you would want to look up each user’s default tablespace in the script we’re about to write.
Oh, and the list of users you’d want to exclude from this would probably vary between instances, depending on what options / tools etc you have installed.

set heading off – suppresses the output of column headings when outputting the results of the query.set feedback off – suppresses the output of the number of rows returned by the queryspool filename – writes the results to a file. We need to turn spooling off as soon as the query has run so that we don’t have any extraneous text at the end of our slave script file.

The SQL*Plus set command takes effect for the rest of the SQL session, or until you change the settings again, which is why we’re turning the heading and feedback back on straight away.