Skip to main content

Posts

Showing posts from June, 2020

How to Move objects from one tablespace to another tablespace online

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 := '&current_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  ...