How to move segments

Here are a few commands in one place to move segments:

In first step we move tables without partitions and subpartitions:

SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' move tablespace '
  ||TABLESPACE_NAME
  ||';'
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  ( SELECT TABLE_NAME FROM DBA_TAB_PARTITIONS WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  );

for partitions and subpartitions...

SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' move partition '
  ||PARTITION_NAME
  ||' tablespace '
  ||TABLESPACE_NAME
  ||' UPDATE  INDEXES;'
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS') and SUBPARTITION_COUNT=0;;

SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' move subpartition '
  ||SUBPARTITION_NAME
  ||' tablespace '
  ||TABLESPACE_NAME
  ||' UPDATE  INDEXES;'
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS');

and for LOB objects

SELECT 'alter table '
  ||owner
  ||'.'
  ||table_name
  ||' move lob('
  ||column_name
  ||') store as (tablespace '
  ||TABLESPACE_NAME
  ||');'
FROM dba_lobs
WHERE TABLESPACE_NAME IN ('USERS');
 
and for LOB subpartitions objects

SELECT 'ALTER TABLE '||TABLE_OWNER ||'."'|| TABLE_NAME || '" MOVE SUBPARTITION '|| SUBPARTITION_NAME ||'  TABLESPACE '||TABLESPACE'|| LOB ('||
COLUMN_NAME||') STORE AS (TABLESPACE '||TABLESPACE_NAME||');'
 FROM DBA_LOB_SUBPARTITIONS WHERE TABLESPACE_NAME='USERS';

After that we have to rebuild indexes:

SELECT 'alter index '
  ||owner
  ||'.'
  ||SEGMENT_NAME
  ||' rebuild online;'
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME IN ('USERS')
AND segment_type       ='INDEX'; 

SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild subpartition '
  || SUBPARTITION_NAME
  ||' online;'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || index_name
  ||' rebuild partition '
  || PARTITION_NAME
  ||' online;'
FROM DBA_IND_PARTITIONS
WHERE status='UNUSABLE'; 

Sometimes we want to move subpartitions to another tablespace, but information about tablespace is also on the partition level (despite the fact that there is no segment): 

ALTER TABLE USER1.TABLE1  MODIFY DEFAULT ATTRIBUTES FOR PARTITION DATA_1 TABLESPACE USER_DATA_2;