Oracle : supprimer ou déplacer un tablespace (tables, index, LOB)
Vérifier qu'un tablespace est vide, le supprimer, et générer les scripts de déplacement des tables, index et LOB.
Un ensemble de requêtes SQL Oracle pour vérifier si un tablespace est vide, le supprimer, et déplacer tables, index et LOB vers un autre tablespace.
Vérifier si un tablespace est vide
SELECT UT.TABLESPACE_NAME "TABLESPACE", COUNT (US.SEGMENT_NAME) "NUM SEGMENTS"
FROM DBA_TABLESPACES UT, DBA_SEGMENTS US
WHERE UT.TABLESPACE_NAME = US.TABLESPACE_NAME
GROUP BY (UT.TABLESPACE_NAME)
ORDER BY COUNT (US.SEGMENT_NAME) DESC;
Si un tablespace n'apparaît pas dans le résultat, il ne contient aucun segment et peut être considéré comme vide. Pour lister les segments d'un tablespace donné, du plus gros au plus petit :
SELECT * FROM dba_segments WHERE TABLESPACE_NAME = 'MY_TABLESPACE' ORDER BY bytes DESC;
Informations sur les tablespaces et datafiles
select TABLESPACE_NAME "Tablespace", FILE_NAME "Filename", BYTES/1024/1024 "Size MB",
MAXBYTES/1024/1024 "Maximum Size MB", AUTOEXTENSIBLE "Autoextensible"
from SYS.DBA_DATA_FILES;
Supprimer un tablespace
DROP TABLESPACE my_tablespace INCLUDING CONTENTS AND DATAFILES;
purge recyclebin;
Déplacer une table
ALTER TABLE <table_name> MOVE TABLESPACE <destination_tablespace>;
Déplacer une table invalide tous ses index : il faut donc les reconstruire ensuite.
alter index <owner>."<index_name>" rebuild;
Générer les scripts de déplacement pour de nombreuses tables
select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'APP_DATA parallel 8;'
from dba_tables
where owner = 'APP_OWNER' and tablespace_name = 'USERS';
Pour de très grosses tables, l'option parallel accélère le déplacement :
select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'new_tablespace_name parallel 8;'
from dba_tables
where tablespace_name = 'DATA';
Déplacer les index
select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'APP_INDEX online parallel 8;'
from dba_indexes
where owner = 'APP_OWNER' and tablespace_name = 'USERS';
Pour déplacer les tables d'un tablespace, avec le nombre de lignes et de blocs en commentaire, en commençant par celles situées en fin de datafile (utile avant de le réduire) :
select 'ALTER TABLE '||e.owner||'.'|| e.segment_name ||
' MOVE TABLESPACE TBS_MAINT ; -- '||T.NUM_ROWS||' rows, '|| T.blocks||' blocks'
from dba_extents e inner join dba_tables T on e.segment_name = t.table_name
where e.tablespace_name = 'SOURCE_DATA'
group by e.owner, e.segment_name, t.num_rows, T.BLOCKS
order by max(block_id) desc;
Reconstruire les index inutilisables
select 'alter index '||owner||'.'||index_name ||' rebuild ;'
from dba_indexes
where status <> 'VALID'
and PARTITIONED = 'FALSE';
Déplacer les colonnes LOB
SELECT 'ALTER TABLE <schema_name>.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE <table_space>);'
FROM DBA_TAB_COLS
WHERE OWNER = '<schema_name>' AND DATA_TYPE like '%LOB%';
Lister les colonnes LOB stockées dans un tablespace :
SELECT owner, table_name, column_name FROM dba_lobs
WHERE owner = 'APP_OWNER' AND tablespace_name = 'USERS';
Remarques
- Les requêtes génératrices ne font qu'afficher des instructions : relisez-les avant de les exécuter.
DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILESest irréversible : vérifiez d'abord le nombre de segments.ALTER TABLE ... MOVEinvalide les index : reconstruisez-les ensuite.- Les noms (
APP_OWNER,APP_DATA,SOURCE_DATA...) sont des exemples génériques.