tablespace maintenance tasks

I just had to guide one of our DBA this morning.

How to get free of segments in SYSTEM tablespace :

1. tables
   
SQL> select owner, table_name from dba_tables where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER TABLE_NAME
----- ----------
SCOTT T

SQL> alter table scott.t move tablespace users;

Table altered.

SQL> select owner, table_name from dba_tables where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');

no rows selected

   
   If the table has a LONG datatype, than I need to exp/drop/create/imp the table
   
   
SQL> select owner, table_name from dba_tables where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER TABLE_NAME
----- ----------
SCOTT T

SQL> alter table scott.t move tablespace users;
alter table scott.t move tablespace users
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL> ! exp file=t.dmp tables=t userid=scott/tiger

Export: Release 10.2.0.2.0 - Production on Mon Aug 14 14:24:29 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table T 3 rows exported
Export terminated successfully without warnings.

SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','T','SCOTT')
---------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "L" LONG
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

SQL> drop table scott.t;

Table dropped.

SQL> CREATE TABLE "SCOTT"."T"
2 ( "L" LONG
3 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6 TABLESPACE "USERS";

Table created.

SQL> ! imp file=t.dmp tables=t userid=scott/tiger ignore=y

Import: Release 10.2.0.2.0 - Production on Mon Aug 14 14:27:05 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T" 3 rows imported
Import terminated successfully without warnings.

   
   
   
2. Indexes
   

SQL> select owner, index_name from dba_indexes where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER INDEX_NAME
----- ----------
SCOTT I

SQL> alter index scott.i rebuild tablespace users;

Index altered.

SQL> select owner, index_name from dba_indexes where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');

no rows selected



   
   
3. lob
   warning: in 9i you must join with dba_tables to get tablespace_name
   
SQL> select owner, table_name, column_name from dba_lobs where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER TABLE_NAME COLUMN_NAME
----- ---------- -----------
SCOTT T          C

SQL> alter table scott.t move lob(c) store as (tablespace users);

Table altered.

SQL> select owner, table_name, column_name from dba_lobs where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');

no rows selected

   
   
4. table partition
   
SQL> select table_owner, table_name, partition_name from dba_tab_partitions where tablespace_name='SYSTEM' and table_owner not in ('SYS','SYSTEM','OUTLN');
TABLE_OWNER TABLE_NAME PARTITION_NAME
----------- ---------- --------------
SCOTT       T          P1

SQL> alter table scott.t move partition p1 tablespace users;

Table altered.

SQL> select table_owner, table_name, partition_name from dba_tab_partitions where tablespace_name='SYSTEM' and table_owner not in ('SYS','SYSTEM','OUTLN');

no rows selected

   
   
5. index partition
   
SQL> select index_owner, index_name, partition_name from dba_ind_partitions where tablespace_name='SYSTEM' and index_owner not in ('SYS','SYSTEM','OUTLN');
INDEX_OWNER INDEX_NAME PARTITION_NAME
----------- ---------- --------------
SCOTT       I          SYS_P32

SQL> alter index scott.i rebuild partition SYS_P32 tablespace users;

Index altered.

SQL> select index_owner, index_name, partition_name from dba_ind_partitions where tablespace_name='SYSTEM' and index_owner not in ('SYS','SYSTEM','OUTLN');

no rows selected

   
6. table subpartition
   
SQL> select table_owner, table_name, subpartition_name from dba_tab_subpartitions where tablespace_name='SYSTEM' and table_owner not in ('SYS','SYSTEM','OUTLN');
TABLE_OWNER TABLE_NAME SUBPARTITION_NAME
----------- ---------- ------------------------------
SCOTT       T          SYS_SUBP30

SQL> alter table scott.t move subpartition SYS_SUBP30 tablespace users;

Table altered.

SQL> select table_owner, table_name, subpartition_name from dba_tab_subpartitions where tablespace_name='SYSTEM' and table_owner not in ('SYS','SYSTEM','OUTLN');

no rows selected


   
   
7. index subpartition
   
SQL> select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name='SYSTEM' and index_owner not in ('SYS','SYSTEM','OUTLN');
INDEX_OWNER INDEX_NAME SUBPARTITION_NAME
----------- ---------- ------------------------------
SCOTT       I          SYS_SUBP31

SQL> alter index scott.i rebuild subpartition SYS_SUBP31 tablespace users;

Index altered.

SQL> select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name='SYSTEM' and index_owner not in ('SYS','SYSTEM','OUTLN');

no rows selected

   
   
8. IOT
   
SQL> select owner, table_name, index_name, index_type from dba_indexes where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE
----- ---------- ----------------- ----------
SCOTT T          SYS_IOT_TOP_10406 IOT - TOP

SQL> alter table scott.t move tablespace users;

Table altered.

SQL> select owner, table_name, index_name, index_type from dba_indexes where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');

no rows selected

   
   
9. IOT Overflow
   
SCOTTSQL> select owner, table_name, iot_name, iot_type from dba_tables where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER TABLE_NAME IOT_NAME IOT_TYPE
----- ------------------ -------- ------------
SCOTT SYS_IOT_OVER_10406 T        IOT_OVERFLOW

SQL> alter table scott.t move overflow tablespace users;

Table altered.

SQL> select owner, table_name, iot_name, iot_type from dba_tables where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');

no rows selected

   
   
10. cluster
    I did not find out yet :-( Probably not possible, so use exp+drop+create+imp as for long
    
    
11. nested tables
    warning: according to the sql reference manual (not tested), in 9i, the nested table was moved with the parent table automatically
    
SQL> select owner, table_name from dba_tables where nested='YES' and tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER TABLE_NAME
----- ----------
SCOTT CTT

SQL> alter table scott.ctt move tablespace users;

Table altered.

SQL> select owner, table_name from dba_tables where nested='YES' and tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');

no rows selected


    
    
12. Lob partitions and subpartitions
    This is getting tricky... I did not achieve to change the default tablespace specification for lob, nor to change a HASH partition. Again, when all failed, use exp/drop/create/imp
    
SQL> select owner, table_name, column_name from dba_lobs where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER TABLE_NAME COLUMN_NAME
----- ---------- -----------------
SCOTT T         C

SQL> alter table scott.t move lob(c) store as (tablespace users);
alter table scott.t move lob(c) store as (tablespace users)
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object

SQL> select table_owner,table_name,column_name,partition_name from dba_lob_partitions where tablespace_name='SYSTEM' and table_owner not in ('SYS','SYSTEM','OUTLN');
TABLE_OWNER TABLE_NAME COLUMN_NAME PARTITION_NAME
----------- ---------- ----------- --------------
SCOTT       T         C           P1

SQL> alter table scott.t move partition p1 lob(c) store as (tablespace users);

Table altered.

SQL> select table_owner,table_name,column_name,partition_name from dba_lob_partitions where tablespace_name='SYSTEM' and table_owner not in ('SYS','SYSTEM','OUTLN');

no rows selected

SQL> select owner, table_name, column_name from dba_lobs where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');
OWNER TABLE_NAME COLUMN_NAME
----- ---------- -----------------
SCOTT T          C

    
    the table was ranged partitioned. With a hash partition, i would get an ORA-22877
    
SQL> alter table scott.t move partition sys_p47 lob(c) store as (tablespace users);
alter table scott.t move partition sys_p47 lob(c) store as (tablespace users)
*
ERROR at line 1:
ORA-22877: invalid option specified for a HASH partition or subpartition of a LOB column


    

The DBA also wanted to separate indexes and tables for tuning IOs. I had to redirect him to the doc :
Performance Tuning Guide
One popular approach to manual I/O distribution suggests separating a frequently used table from its index. This is not correct.
well, he shows me his Oracle 9i Tuning Course, Chapter 3, Slice 6, put table and indexes on different tablespaces... Ok, this is not a new thema, just google to find explanations !