tbl

 

SELECT 'TABLE' AS object_type, table_name AS object_name, tablespace_name

FROM user_tables

WHERE tablespace_name = 'USERS'

UNION ALL

SELECT 'INDEX' AS object_type, index_name AS object_name, tablespace_name

FROM user_indexes

WHERE tablespace_name = 'USERS'

ORDER BY object_type, object_name, tablespace_name;

SELECT 'TABLE' AS object_type, table_name AS object_name, tablespace_name

FROM user_tables

UNION ALL

SELECT 'INDEX' AS object_type, index_name AS object_name, tablespace_name

FROM user_indexes

ORDER BY object_type, object_name, tablespace_name;

DECLARE

  v_app_tablespace VARCHAR2(30) := 'APP_TBS'; -- Replace with your target tablespace name

  v_sql VARCHAR2(4000);

BEGIN

  -- Generate statements for tables in USERS tablespace

  DBMS_OUTPUT.PUT_LINE('-- Move Tables:');

  FOR rec IN (

    SELECT owner, table_name 

    FROM all_tables 

    WHERE tablespace_name = 'USERS'

      AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'ORDSYS', 'MDSYS')

  ) 

  LOOP

    v_sql := 'ALTER TABLE ' || DBMS_ASSERT.SCHEMA_NAME(rec.owner) || '.' || 

             DBMS_ASSERT.SQL_OBJECT_NAME(rec.table_name) || 

             ' MOVE TABLESPACE ' || DBMS_ASSERT.SQL_OBJECT_NAME(v_app_tablespace);

    DBMS_OUTPUT.PUT_LINE(v_sql || ';');

    -- Commented out execution

    -- EXECUTE IMMEDIATE v_sql;

    -- DBMS_OUTPUT.PUT_LINE('Moved table: ' || rec.owner || '.' || rec.table_name || ' to ' || v_app_tablespace);

  END LOOP;

  -- Generate statements for indexes in USERS tablespace

  DBMS_OUTPUT.PUT_LINE('-- Move Indexes:');

  FOR rec IN (

    SELECT owner, index_name, table_name 

    FROM all_indexes 

    WHERE tablespace_name = 'USERS'

      AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'ORDSYS', 'MDSYS')

  ) 

  LOOP

    v_sql := 'ALTER INDEX ' || DBMS_ASSERT.SCHEMA_NAME(rec.owner) || '.' || 

             DBMS_ASSERT.SQL_OBJECT_NAME(rec.index_name) || 

             ' REBUILD TABLESPACE ' || DBMS_ASSERT.SQL_OBJECT_NAME(v_app_tablespace);

    DBMS_OUTPUT.PUT_LINE(v_sql || ';');

    -- Commented out execution

    -- EXECUTE IMMEDIATE v_sql;

    -- DBMS_OUTPUT.PUT_LINE('Rebuilt index: ' || rec.owner || '.' || rec.index_name || 

    --                      ' on table ' || rec.table_name || ' to ' || v_app_tablespace);

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('-- Error: ' || SQLERRM);

    RAISE;

END;

/