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.
I can easily find out which segments I need to move :
SELECT owner, segment_name, segment_type FROM dba_segments WHERE tablespace_name = 'SYSTEM' AND owner NOT IN( 'ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'FLOWS_020100', 'FLOWS_FILES', 'MDSYS', 'OUTLN', 'SYS', 'SYSTEM', 'TSMSYS', 'XDB');
The output is :
MIKE BP1_PK_IDX INDEX MIKE BP2_PK_IDX INDEX MIKE BAD_PLACE1 TABLE MIKE BAD_PLACE2 TABLE
To move a table, we’ll need to issue the command :
ALTER TABLE owner.table_name MOVE TABLESPACE tablespace_name;
For indexes, we’ll need :
ALTER INDEX owner.index_name REBUILD TABLESPACE tablespace_name;
Now, to get good old SQL*Plus to do my bidding :
set heading off set feedback off spool move_segments_slave.sql SELECT 'ALTER '||segment_type||' '||owner ||'.'||segment_name|| CASE segment_type WHEN 'INDEX' THEN ' REBUILD ' WHEN 'TABLE' THEN ' MOVE ' END ||'TABLESPACE users;' FROM dba_segments WHERE tablespace_name = 'SYSTEM' AND owner NOT IN( 'ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'FLOWS_020100', 'FLOWS_FILES', 'MDSYS', 'OUTLN', 'SYS', 'SYSTEM', 'TSMSYS', 'XDB') / spool off set heading on set feedback on
Let’s have a quick look at the SQL*Plus commands in this script :
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 query
spool 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.
The file we’ve generated contains :
ALTER INDEX MIKE.BP1_PK_IDX REBUILD TABLESPACE users; ALTER INDEX MIKE.BP2_PK_IDX REBUILD TABLESPACE users; ALTER TABLE MIKE.BAD_PLACE1 MOVE TABLESPACE users; ALTER TABLE MIKE.BAD_PLACE2 MOVE TABLESPACE users;
All we need to do now is run the script….and reflect on the fact that, just occasionally, computers really do make our lives easier.