LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon