Oracle: drop or move a tablespace (tables, indexes, LOBs)
Check that a tablespace is empty, drop it, and generate scripts to move tables, indexes and LOBs.
A set of Oracle SQL snippets to check whether a tablespace is empty, drop it, and move tables, indexes and LOBs to another tablespace.
Check whether a tablespace is empty
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;
If a tablespace does not appear in the result, it contains no segment and can be considered empty. To list the segments of a given tablespace, largest first:
SELECT * FROM dba_segments WHERE TABLESPACE_NAME = 'MY_TABLESPACE' ORDER BY bytes DESC;
Tablespace and datafile information
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;
Drop a tablespace
DROP TABLESPACE my_tablespace INCLUDING CONTENTS AND DATAFILES;
purge recyclebin;
Move a table
ALTER TABLE <table_name> MOVE TABLESPACE <destination_tablespace>;
Moving a table invalidates all of its indexes, so follow it with a rebuild:
alter index <owner>."<index_name>" rebuild;
Generate move scripts for many tables
select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'APP_DATA parallel 8;'
from dba_tables
where owner = 'APP_OWNER' and tablespace_name = 'USERS';
For very large tables, the parallel option speeds up the move:
select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'new_tablespace_name parallel 8;'
from dba_tables
where tablespace_name = 'DATA';
Move indexes
select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'APP_INDEX online parallel 8;'
from dba_indexes
where owner = 'APP_OWNER' and tablespace_name = 'USERS';
To move the tables of a tablespace, with row and block counts as a comment, starting with those located at the end of the datafiles (useful before shrinking it):
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;
Rebuild unusable indexes
select 'alter index '||owner||'.'||index_name ||' rebuild ;'
from dba_indexes
where status <> 'VALID'
and PARTITIONED = 'FALSE';
Move LOB columns
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%';
List the LOB columns stored in a tablespace:
SELECT owner, table_name, column_name FROM dba_lobs
WHERE owner = 'APP_OWNER' AND tablespace_name = 'USERS';
Notes
- The generator queries only print statements: review them before running them.
DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILESis irreversible; check the segment count first.ALTER TABLE ... MOVEinvalidates indexes; rebuild them afterwards.- Names (
APP_OWNER,APP_DATA,SOURCE_DATA...) are generic examples.