Tabellen / Indizes in anderen Tablespace umziehen

Umzug von Objekten in anderen TS
- Update 21.07.2020 -

Um für Wartungsarbeiten einen TS „leer“ zu räumen, sind bisweilen alle Objekte aus diesem TS in einen anderen TS umzuziehen. Manuell ist das sehr mühsam – schneller geht’s mit dynamischem SQL:
a) Per SQL eine Liste von SQL-Befehlen erzeugen
b) Diese Liste am Block ausführen

1) Der einfache Fall:
Grundsätzlicher Umzugsbefehl:
alter table move tablespace;
alter index rebuild tablespace;

Wichtig: zuerst Umzug der Tabellen, dann Umzug der Indizes!

select 'alter table 'owner'.'table_name' move tablespace ;' from dba_tables where TABLESPACE_NAME = 'Y'
select 'alter index 'owner'.'index_name' rebuild tablespace ;' from dba_indexes where TABLESPACE_NAME = 'Y'


2) Tabellen mit LOB-Spalten:
Syntax:
ALTER TABLE foo MOVE LOB(lobcol) STORE AS (TABLESPACE new_tbsp);

/* Bsp.:ALTER TABLE mupsys.INQUIRY MOVE LOB(DESCRIPTION) STORE AS ( TABLESPACE MUPSYS ENABLE STORAGE IN ROW CHUNK 8192PCTVERSION 10NOCACHE );*/

Ermittlung:
SELECT 'alter table 'OWNER'.'TABLE_NAME' move lob('COLUMN_NAME') STORE AS (Tablespace );' FROM dba_lobs WHERE owner = 'MUPSYS';


3) Partitionierte Tabellen mit Indizes:
ALTER TABLE tbl MODIFY DEFAULT ATTRIBUTES TABLESPACE ts;

ALTER TABLE tbl PARTITION partitiona TABLESPACE ts;
ALTER TABLE tbl PARTITION partitionb TABLESPACE ts;
...

ALTER INDEX idx REBUILD PARTITION partitiona TABLESPACE ts;
ALTER INDEX idx REBUILD PARTITION partitionb TABLESPACE ts;
...

Ermittlung der Tbl-Partitionen:
select UT.TABLE_NAME, UTP.PARTITION_NAME, UTP.TABLESPACE_NAME
     , 'alter table '|| UT.TABLE_NAME ||' partition '|| UTP.PARTITION_NAME ||' tablespace new_TS parallel;' Txt
  from USER_TAB_PARTITIONS UTP
  join USER_TABLES UT on UT.TABLE_NAME = UTP.TABLE_NAME
 where UTP.TABLESPACE_NAME = old_TS
;
 
Ermittlung der Index-Partitionen:
select UI.INDEX_NAME, UIP.PARTITION_NAME, UIP.TABLESPACE_NAME, UI.INDEX_TYPE
     , 'alter index '|| UI.INDEX_NAME ||' rebuild partition '|| UIP.PARTITION_NAME ||' tablespace new_TS parallel;' Txt
  from USER_IND_PARTITIONS UIP
  join USER_INDEXES UI on UI.INDEX_NAME = UIP.INDEX_NAME
 where UIP.TABLESPACE_NAME = old_TS
   and UI.INDEX_TYPE in ('NORMAL', 'BITMAP')
;


4) IOT - Index-org. Tabellen

Hinweis: ggs. Overflow-Segmente separat verschieben.
 
alter table myiot move tablespace ts overflow tablespace ts;

Ermittlung:
select UT.TABLE_NAME
     , 'alter table '|| UT.TABLE_NAME ||' move tablespace new_ts overflow tablespace new_ts;' Txt
  from USER_TABLES UT
 where UT.TABLESPACE_NAME is not null
   and UT.IOT_NAME is not null;


4b) IOT - Index-org. Tabellen, patitioniert

alter table myiot move PARTITION partitiona tablespace ts overflow tablespace ts;

Ermittlung:
select UT.TABLE_NAME, UTP.PARTITION_NAME, UTP.TABLESPACE_NAME, UT.IOT_TYPE
     , 'alter table '|| UT.TABLE_NAME ||' move partition '|| UTP.PARTITION_NAME ||' tablespace new_TS;' Txt
  from USER_TAB_PARTITIONS UTP
  join USER_TABLES UT on UT.TABLE_NAME = UTP.TABLE_NAME
 where UT.IOT_TYPE = 'IOT'
;



4c) IOT - Index-org. Tabellen, INDEX_TYPE = 'IOT - TOP'

alter table myiot move PARTITION partitiona tablespace ts overflow tablespace ts;

Ermittlung:
select UI.TABLE_NAME, UI.INDEX_NAME 
     , 'alter table '|| UI.TABLE_NAME ||' move tablespace new_TS;' Txt
  from USER_INDEXES UI
 where UI.INDEX_TYPE = 'IOT - TOP'
  and UI.TABLESPACE_NAME = old_TS;