Prerequisites

Assigned unlimited quota of new tablespace to user, allowing for a flexible and efficient allocation of resources. This ensures that as new data is generated and stored, the system can seamlessly accommodate growth without the need for constant adjustments or limitations. By implementing this strategy, we enhance the performance and reliability of our database operations, providing a robust foundation for future development and data management.

ALTER USER Username QUOTA UNLIMITED ON new_tablespace;

Moving Tables To a New Tablespace:

ALTER TABLE table_name MOVE TABLESPACE new_tablespace ONLINE PARALLEL 16;

Move indexes to a new tablespace:

No parallel is using after this for make index with no parallel options

Select 
  'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD PARALLEL 16 ONLINE;
ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' NOPARALLEL;' 
from   dba_indexes where  owner='SCOTT';

Move Partition of a new tablespace

select 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION ' || PARTITION_NAME || ' TABLESPACE USERS_NEWTBS PARALLEL 16 ONLINE UPDATE INDEXES; ', a.* from dba_tab_partitions a where table_owner='SCOTT'

Move Subpartition of a new tablespace

select 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE SUBPARTITION' || SUBPARTITION_NAME|| ' TABLESPACE USERS_NEWTBS PARALLEL 16 ONLINE UPDATE INDEXES; ', a.* from dba_tab_subpartitions a where table_owner='APP_USER'

Script to Move Complete Object Including Table, Index, and LOB Column of Schema to New Tablespace:

-- Replace MY_SCHEMA with your schema name
-- Replace NEW_TBS with your target tablespace

DECLARE
   v_sql VARCHAR2(4000);
BEGIN
   -- Move all tables
   FOR t IN (
      SELECT table_name
      FROM dba_tables
      WHERE owner = 'MY_SCHEMA'
   )
   LOOP
      v_sql := 'ALTER TABLE MY_SCHEMA.' || t.table_name ||
               ' MOVE TABLESPACE NEW_TBS';
      DBMS_OUTPUT.put_line(v_sql);
      EXECUTE IMMEDIATE v_sql;
   END LOOP;

   -- Move all indexes
   FOR i IN (
      SELECT index_name
      FROM dba_indexes
      WHERE owner = 'MY_SCHEMA'
   )
   LOOP
      v_sql := 'ALTER INDEX MY_SCHEMA.' || i.index_name ||
               ' REBUILD TABLESPACE NEW_TBS';
      DBMS_OUTPUT.put_line(v_sql);
      EXECUTE IMMEDIATE v_sql;
   END LOOP;

   -- Move all LOB segments
   FOR l IN (
      SELECT table_name, column_name
      FROM dba_lobs
      WHERE owner = 'MY_SCHEMA'
   )
   LOOP
      v_sql := 'ALTER TABLE MY_SCHEMA.' || l.table_name ||
               ' MOVE LOB(' || l.column_name ||
               ') STORE AS (TABLESPACE NEW_TBS)';
      DBMS_OUTPUT.put_line(v_sql);
      EXECUTE IMMEDIATE v_sql;
   END LOOP;
END;
/