Move Objects From One Tablespace to Another Tablespace in Oracle

Sometimes need to move objects from one Tablespace to another in Oracle Databases. We can do this multiple ways.

Move a single object from Multiple Tablespace (one TBS for DATA, another for Index and LOB) to different set of Tablespace
Move all objects of a schema from Multiple Tablespace (one TBS for DATA, another for Index and LOB) to different set of Tablespace
Move all objects from one Tablespace to another Tablespace

Script for moving a single object to one/multiple Tablespace

Instruction to execute the Script:

This is recommended and best practices to use separate tablespace for DATA, Index and LOB Segment.

1st Input : Object Owner Name
2nd Input: Object Name
3rd Input: Target Tablespace Name for DATA
4th Input: Target Tablespace Name for INDEX
5th Input: Target Tablespace Name for LOB
6th Input: Enter “EXECUTE” or 1 to Execute Command Immediately Or Enter “SHOW” to Show / Generate Command as output.

NOTE::: If same tablespace for DATA, INDEX and LOB then provide same tablespace name for all inputs [3-5]

set serveroutput on
set lines 350
DECLARE	
	V_OWNER VARCHAR2(60);
	V_OBJECT_NAME VARCHAR2(60);	
	V_NEW_TBS_DATA VARCHAR2(60);
	V_NEW_TBS_IDX VARCHAR2(60);
	V_NEW_TBS_LOB VARCHAR2(60);
	V_SQL VARCHAR2(1500);
	V_OVERFLOW NUMBER := 0;
	V_EXECUTE_SHOW VARCHAR2(50) := 'SHOW';
	V_TABLE VARCHAR2(30);
	V_IOT VARCHAR2(60);

BEGIN
	V_OWNER := UPPER('&OWNER');
	V_OBJECT_NAME := UPPER('&OBJECT_NAME');
	V_NEW_TBS_DATA := UPPER('&NEW_TBS_DATA');
	V_NEW_TBS_IDX := UPPER('&NEW_TBS_IDX');
	V_NEW_TBS_LOB := UPPER('&NEW_TBS_LOB');
	V_EXECUTE_SHOW := UPPER('&execute_or_show_code');

	IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN	
		DBMS_OUTPUT.PUT_LINE('YOU HAVE CHOSEN EXECUTE.');		
	ELSE
		DBMS_OUTPUT.PUT_LINE('YOU HAVE CHOSEN GENERATE COMMAND/CODE.');		
	END IF;

	DBMS_OUTPUT.PUT_LINE('MOVING TABLES TO ' || V_NEW_TBS_DATA || ' TABLESPACE:');
	DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');
	
	SELECT OWNER, TABLE_NAME, IOT_NAME INTO V_OWNER, V_TABLE, V_IOT FROM DBA_TABLES WHERE OWNER=V_OWNER AND TABLE_NAME=V_OBJECT_NAME;

		IF  V_IOT IS NOT NULL THEN
			V_SQL := 'ALTER TABLE "' || V_OWNER  || '"."' || V_IOT || '" MOVE TABLESPACE ' || V_NEW_TBS_DATA || ';';	

			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;

			-- CHECKING OVERFLOW 

			DBMS_OUTPUT.PUT_LINE('MOVING INDEX ORGANIZED TABLE OVERFLOW ' || V_IOT || '...');
			V_SQL := 'ALTER TABLE "' || V_TABLE  || '"."' || V_IOT || '" MOVE OVERFLOW TABLESPACE ' || V_NEW_TBS_DATA || ';';				
		
			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;	
		ELSE		
			V_SQL := 'ALTER TABLE "' || V_OWNER  || '"."' || V_TABLE || '" MOVE TABLESPACE ' || V_NEW_TBS_DATA || ';';
				
			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;				
		END IF;
		

		DBMS_OUTPUT.PUT_LINE(' ');
		DBMS_OUTPUT.PUT_LINE('MOVING LOBS TO ' || V_NEW_TBS_DATA || ' TABLESPACE:');
		DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');
		
		FOR LIST_LOBS IN (SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE OWNER=V_OWNER AND TABLE_NAME=V_OBJECT_NAME) 
		LOOP
			V_SQL := 'ALTER TABLE ' || V_OWNER || '.' ||  LIST_LOBS.TABLE_NAME || ' MOVE LOB(' || LIST_LOBS.COLUMN_NAME || ') STORE AS (TABLESPACE ' ||  V_NEW_TBS_LOB || ';)';

			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;				
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;

		END LOOP;		
		
		DBMS_OUTPUT.PUT_LINE(' ');
		DBMS_OUTPUT.PUT_LINE('MOVING INDEXES TO ' || V_NEW_TBS_IDX || ' TABLESPACE:');
		DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');
		
		FOR LIST_INDEXES IN (SELECT INDEX_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_COLUMNS WHERE TABLE_NAME = UPPER(V_OBJECT_NAME) AND TABLE_OWNER = UPPER(V_OWNER))
		LOOP
			V_SQL := 'ALTER INDEX ' || LIST_INDEXES.INDEX_OWNER  || '."' || LIST_INDEXES.INDEX_NAME || '" REBUILD TABLESPACE ' || V_NEW_TBS_IDX || ';';

			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;

		END LOOP;					
END;
/

Script for moving all objects of a schema to one/multiple Tablespace

1st Input : Object Owner Name
2nd Input: Target Tablespace Name for DATA
3rd Input: Target Tablespace Name for INDEX
4th Input: Target Tablespace Name for LOB
5th Input: Enter “EXECUTE” or 1 to Execute Command Immediately Or Enter “SHOW” to Show / Generate Command as output.

NOTE::: If same tablespace for DATA, INDEX and LOB then provide same tablespace name for all inputs [2-4]

set serveroutput on
set lines 350
DECLARE	
	V_OWNER VARCHAR2(60);
	V_CURR_TBS VARCHAR2(60);
	V_NEW_TBS_DATA VARCHAR2(60);
	V_NEW_TBS_IDX VARCHAR2(60);
	V_NEW_TBS_LOB VARCHAR2(60);
	V_SQL VARCHAR2(1500);
	V_OVERFLOW NUMBER := 0;
	V_EXECUTE_SHOW VARCHAR2(20) := 'SHOW';
	
BEGIN
	V_OWNER := UPPER('&OWNER');
	V_NEW_TBS_DATA := UPPER('&NEW_TBS_DATA');
	V_NEW_TBS_IDX := UPPER('&NEW_TBS_IDX');
	V_NEW_TBS_LOB := UPPER('&NEW_TBS_LOB');
	V_EXECUTE_SHOW := UPPER('&execute_or_show_code');

	IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN	
		DBMS_OUTPUT.PUT_LINE('YOU HAVE CHOSEN EXECUTE.');		
	ELSE
		DBMS_OUTPUT.PUT_LINE('YOU HAVE CHOSEN GENERATE COMMAND/CODE.');		
	END IF;
	
	DBMS_OUTPUT.PUT_LINE('MOVING TABLES TO ' || V_NEW_TBS_DATA || ' TABLESPACE:');
	DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');

	FOR LIST_TABLES IN (SELECT OWNER, TABLE_NAME, IOT_NAME FROM DBA_TABLES WHERE OWNER=V_OWNER)
	LOOP

		IF  LIST_TABLES.IOT_NAME IS NOT NULL THEN
			V_SQL := 'ALTER TABLE "' || LIST_TABLES.OWNER  || '"."' || LIST_TABLES.IOT_NAME || '" MOVE TABLESPACE ' || V_NEW_TBS_DATA || ';';	
	
			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;				
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;

			-- CHECKING OVERFLOW 

			DBMS_OUTPUT.PUT_LINE('MOVING INDEX ORGANIZED TABLE OVERFLOW ' || LIST_TABLES.IOT_NAME || '...');
			V_SQL := 'ALTER TABLE "' || LIST_TABLES.OWNER  || '"."' || LIST_TABLES.IOT_NAME || '" MOVE OVERFLOW TABLESPACE ' || V_NEW_TBS_DATA || ';';				
	
			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;			
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;	

		ELSE		
			V_SQL := 'ALTER TABLE "' || LIST_TABLES.OWNER  || '"."' || LIST_TABLES.TABLE_NAME || '" MOVE TABLESPACE ' || V_NEW_TBS_DATA || ';';
				
			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;				
		END IF;
	END LOOP;
		
		DBMS_OUTPUT.PUT_LINE(' ');
		DBMS_OUTPUT.PUT_LINE('MOVING LOBS TO ' || V_NEW_TBS_LOB || ' TABLESPACE:');
		DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');
		FOR LIST_LOBS IN (SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE OWNER=V_OWNER)
		LOOP
			V_SQL := 'ALTER TABLE "' || LIST_LOBS.OWNER || '"."' ||  LIST_LOBS.TABLE_NAME || '" MOVE LOB(' || LIST_LOBS.COLUMN_NAME || ') STORE AS (TABLESPACE ' ||  V_NEW_TBS_LOB || ';)';

			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;				
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;

		END LOOP;		
		
		DBMS_OUTPUT.PUT_LINE(' ');
		DBMS_OUTPUT.PUT_LINE('MOVING INDEXES TO ' || V_NEW_TBS_IDX || ' TABLESPACE:');
		DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');
		
		FOR LIST_INDEXES IN (SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE OWNER=V_OWNER AND SEGMENT_TYPE='INDEX')
		LOOP
			V_SQL := 'ALTER INDEX "' || LIST_INDEXES.OWNER  || '"."' || LIST_INDEXES.SEGMENT_NAME || '" REBUILD TABLESPACE ' || V_NEW_TBS_IDX || ';';

			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
				EXECUTE IMMEDIATE V_SQL;
			ELSE 
				DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;

		END LOOP;					
END;
/

Script for moving all objects of a Tablespace to another Tablespace:

Enter value for current_tablespace: TS_DATA01
old   8:        V_CURRENT_TABLESPACE := UPPER('&current_tablespace');
new   8:      V_CURRENT_TABLESPACE := UPPER('TS_DATA01');
Enter value for new_tablespace: TS_DATA
old   9:        V_NEW_TABLESPACE := UPPER('&new_tablespace');
new   9:      V_NEW_TABLESPACE := UPPER('TS_DATA');
Enter value for execute_or_show: SHOW
old  11:        V_EXECUTE_SHOW := UPPER('&execute_or_show_');
new  11:      V_EXECUTE_SHOW := UPPER('SHOW');

1st Input : Current Tablespace Name
2nd Input: New / Target Tablespace Name
3rd Input: Enter “EXECUTE” or 1 to Execute Command Immediately Or Enter “SHOW” to Show / Generate Command as output.

set serveroutput on
set lines 350
DECLARE	
	V_CURRENT_TABLESPACE VARCHAR2(60);
	V_NEW_TABLESPACE VARCHAR2(60);
	V_SQL VARCHAR2(1500);
	V_OVERFLOW NUMBER := 0;
	V_EXECUTE_SHOW VARCHAR2(50) := 'SHOW';
BEGIN
	V_CURRENT_TABLESPACE := UPPER('&current_tablespace');
	V_NEW_TABLESPACE := UPPER('&new_tablespace');
	V_EXECUTE_SHOW := UPPER('&execute_or_show_code');

	IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN	
		DBMS_OUTPUT.PUT_LINE('YOU HAVE CHOSEN EXECUTE.');		
	ELSE
		DBMS_OUTPUT.PUT_LINE('YOU HAVE CHOSEN GENERATE COMMAND/CODE.');		
	END IF;

	FOR LIST_OWNER IN (SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=V_CURRENT_TABLESPACE)
	LOOP
		DBMS_OUTPUT.PUT_LINE('MOVING TABLES FROM TABLESPACE ' || V_CURRENT_TABLESPACE || ' TO ' || V_NEW_TABLESPACE || ':');
		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
				V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.IOT_NAME || '" MOVE TABLESPACE ' || V_NEW_TABLESPACE || ';';	

				IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
					EXECUTE IMMEDIATE V_SQL;
				ELSE 
					DBMS_OUTPUT.PUT_LINE(V_SQL);
				END IF;

				-- CHECKING OVERFLOW 

				V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.IOT_NAME || '" MOVE OVERFLOW TABLESPACE ' || V_NEW_TABLESPACE || ';';				
				IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
					EXECUTE IMMEDIATE V_SQL;
				ELSE 
					DBMS_OUTPUT.PUT_LINE(V_SQL);
				END IF;	
			ELSE		
				V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.TABLE_NAME || '" MOVE TABLESPACE ' || V_NEW_TABLESPACE || ';';
				
				IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
					EXECUTE IMMEDIATE V_SQL;
				ELSE 
					DBMS_OUTPUT.PUT_LINE(V_SQL);
				END IF;				
			END IF;
		END LOOP;
		
		DBMS_OUTPUT.PUT_LINE(' ');
		DBMS_OUTPUT.PUT_LINE('MOVING LOBS FROM TABLESPACE ' || V_CURRENT_TABLESPACE || ' TO ' || V_NEW_TABLESPACE || ':');
		DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');
		
		FOR LIST_LOBS IN (SELECT TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE)
		LOOP
			V_SQL := 'ALTER TABLE ' || LIST_OWNER.OWNER || '.' ||  LIST_LOBS.TABLE_NAME || ' MOVE LOB(' || LIST_LOBS.COLUMN_NAME || ') STORE AS (TABLESPACE ' ||  V_NEW_TABLESPACE || ';)';
			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
					EXECUTE IMMEDIATE V_SQL;
			ELSE 
					DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;	
		END LOOP;		
		
		DBMS_OUTPUT.PUT_LINE(' ');
		DBMS_OUTPUT.PUT_LINE('MOVING INDEXES FROM TABLESPACE ' || V_CURRENT_TABLESPACE || ' TO ' || V_NEW_TABLESPACE || ':');
		DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');
		
		FOR LIST_INDEXES IN (SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE OWNER=LIST_OWNER.OWNER AND TABLESPACE_NAME=V_CURRENT_TABLESPACE AND SEGMENT_TYPE='INDEX')
		LOOP
			V_SQL := 'ALTER INDEX ' || LIST_OWNER.OWNER  || '."' || LIST_INDEXES.SEGMENT_NAME || '" REBUILD TABLESPACE ' || V_NEW_TABLESPACE || ';';
			IF (V_EXECUTE_SHOW = 'EXECUTE' OR V_EXECUTE_SHOW = '1') THEN			
					EXECUTE IMMEDIATE V_SQL;
			ELSE 
					DBMS_OUTPUT.PUT_LINE(V_SQL);
			END IF;	
		END LOOP;					
		
	END LOOP;
END;
/

Move Objects From One Tablespace to Another Tablespace in Oracle

About Me

Categories

Archives

VISITORS