How to Move objects from one tablespace to another tablespace online using procedure. set serveroutput on set lines 300 DECLARE V_CURRENT_TABLESPACE VARCHAR2(50); V_DEST_TABLESPACE VARCHAR2(50); V_SQL VARCHAR2(1000); V_OVERFLOW NUMBER := 0; BEGIN V_CURRENT_TABLESPACE := '¤t_tablespace'; V_DEST_TABLESPACE := '&destination_tablespace'; FOR LIST_OWNER IN (SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=V_CURRENT_TABLESPACE and OWNER='&my_schema') LOOP DBMS_OUTPUT.PUT_LINE('MOVING TABLES:'); DBMS_OUTPUT.PUT_LINE('------------------'); FOR LIST_TABLES IN (SELECT TABLE_NAME, IOT_NAME FROM DBA_TABLES WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE) LOOP IF LIST_TABLES.IOT_NAME IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('MOVING INDEX ORGANIZED TABLE ' || LIST_TABLES.IOT_NAME || '...'); V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER ...
One blog for all ORACLE DBA scripts & AWS Cloud commands, OCI commands etc