1. View the objects in the tablespace

SELECT OWNER AS OWNER,
             SEGMENT_NAME AS SEGMENT_NAME,
             SEGMENT_TYPE AS SEGMENT_TYPE,
             SUM (BYTES) / 1024 / 1024 AS SEGMENT_SIZE
        FROM DBA_SEGMENTS
       WHERE TABLESPACE_NAME = 'TBS'
    GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE;

2. move the ordinary table into the new table space as follows

After move, the index will fail due to the change of rowid, so it needs to be rebuilt.

alter table owner.tablename move tablespace ntbs;
alter index owner.ix_tablename_id rebuild tablespace ntbs online;

3. If it is a partition table, move the partition to the new partition as follows

    SELECT    'ALTER TABLE '
           || table_owner
           || '.'
           || TABLE_NAME
           || ' MOVE PARTITION '
           || PARTITION_NAME
           || ' TABLESPACE NTBS;'
      FROM DBA_TAB_PARTITIONS
     WHERE TABLE_OWNER = 'OWNER' AND TABLE_NAME = 'TABLE_NAMES';

If the table is automatically partitioned, modify the default table space of the new partition

SELECT    'alter table '
       || owner
       || '.'
       || table_name
       || ' modify default attributes tablespace NTBLS;'
  FROM DBA_TABLES
 WHERE OWNER = 'OWNER' AND TABLE_NAME = 'TABLE_NAME';

4. If it is a common index, rebuild the index as follows

alter index owner.tablename REBUILD TABLESPACE NTBS ONLINE;

If online encounters the following error

• Error on line 1:
• ORA-00604: error in recursive SQL level 1
• ORA-01450: maximum key length exceeded (3215)

Without online (oracle specifies that at least one index node should be stored in one data block, for details, please refer to https://blog.csdn.net/yidian815/article/details/16336911)

5. For example, if it is a partition index, rebuild the index as follows

SELECT    'ALTER INDEX '
           || index_owner
           || '.'
           || index_name
           || ' REBUILD PARTITION '
           || PARTITION_NAME
           || ' TABLESPACE NTBS ONLINE;'
      FROM DBA_ind_PARTITIONS
     WHERE index_owner = 'index_owner' AND INDEX_NAME =  'index_name';

If the partition is an auto increasing partition, modify the default tablespace of the partition index

SELECT    'ALTER INDEX '
       || owner
       || '.'
       || index_name
       || ' modify default attributes tablespace ntbs;'
  FROM dba_indexes
 WHERE OWNER = 'index_owner' AND INDEX_NAME = 'index_name';