Moving objects from one tablespace to another

Administration
Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

This script will help you to move all objects from one tablespace to another, note that you will need downtime to move tables to another tablespace with this script, the operation can be done online using the dbms_redefinition package thought.

Moving objects

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)
	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  || '"."' || LIST_TABLES.IOT_NAME || '" MOVE TABLESPACE ' || V_DEST_TABLESPACE;				
				EXECUTE IMMEDIATE V_SQL;
				-- check if an overflow exists
				DBMS_OUTPUT.PUT_LINE('MOVING INDEX ORGANIZED TABLE OVERFLOW ' || LIST_TABLES.IOT_NAME || '...');
				V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.IOT_NAME || '" MOVE OVERFLOW TABLESPACE ' || V_DEST_TABLESPACE;				
				EXECUTE IMMEDIATE V_SQL;
			ELSE		
				DBMS_OUTPUT.PUT_LINE('MOVING TABLE ' || LIST_TABLES.TABLE_NAME || '...');
				V_SQL := 'ALTER TABLE "' || LIST_OWNER.OWNER  || '"."' || LIST_TABLES.TABLE_NAME || '" MOVE TABLESPACE ' || V_DEST_TABLESPACE;				
				EXECUTE IMMEDIATE V_SQL;
			END IF;
		END LOOP;
		
		DBMS_OUTPUT.PUT_LINE(' ');
		DBMS_OUTPUT.PUT_LINE('MOVING LOBS:');
		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
			DBMS_OUTPUT.PUT_LINE('MOVING LOB FROM TABLE ' || LIST_LOBS.TABLE_NAME || ' TO TABLESPACE ' || V_DEST_TABLESPACE || '...');
			V_SQL := 'ALTER TABLE ' || LIST_OWNER.OWNER || '.' ||  LIST_LOBS.TABLE_NAME || ' MOVE LOB(' || LIST_LOBS.COLUMN_NAME || ') STORE AS (TABLESPACE ' ||  V_DEST_TABLESPACE || ')';
			EXECUTE IMMEDIATE V_SQL;
		END LOOP;		
		
		DBMS_OUTPUT.PUT_LINE(' ');
		DBMS_OUTPUT.PUT_LINE('MOVING INDEXES:');
		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
			DBMS_OUTPUT.PUT_LINE('MOVING ' || LIST_INDEXES.SEGMENT_TYPE || ' ' || LIST_INDEXES.SEGMENT_NAME || '...');			
			V_SQL := 'ALTER INDEX ' || LIST_OWNER.OWNER  || '."' || LIST_INDEXES.SEGMENT_NAME || '" REBUILD TABLESPACE ' || V_DEST_TABLESPACE;
			EXECUTE IMMEDIATE V_SQL;
		END LOOP;				
		
		DBMS_OUTPUT.PUT_LINE(' ');		
		
	END LOOP;
END;
/

You can also modify the script to move objects of a specific schema if needed, just modify the following line:

FOR LIST_OWNER IN (SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=V_CURRENT_TABLESPACE and OWNER='&my_schema')

Hope it helps

You may also like:

  1. Tablespace Usage
  2. SYSAUX tablespace objects
  3. Get user or object DDL using DBMS_METADATA
  4. Which archivelog contains your SCN ?
  5. Export data to excel
  6. Keep jobs active with screen command
  7. Datafile space reclaimable report.
  8. Database maintenance tasks
  9. Monitoring long running operations
  10. Datapump in parallel with a shell script

21 thoughts on “Moving objects from one tablespace to another”

  1. Hi, thanks for the post, I got that error , bd_version:12c Release1

    PLS-00103: Encountered the symbol “DBMS_OUTPUT” when expecting one of the following:
    loop
    The symbol “loop” was substituted for “DBMS_OUTPUT” to continue.

    another question: is this will make any downtime?
    Thanks

    Reply

    1. Hi,

      Thanks for reaching out and reading my blog, I made the correction to the script, if you encounter any problem you can contact me.

      This procedure will require downtime.

      Thanks

      Cyrille

      Reply

  2. Hi, Thanks for the information.

    Got an error as below, I am on 10g.

    PL/SQL: Statement ignored
    ORA-06550: line 39, column 22:
    PLS-00201: identifier ‘V_SQL’ must be declared

    Reply

  3. Hi, It looks quite promising, Thank you for this script.
    I was wondering while moving the tables and lobs along with indexes to new tablespaces, do we need downtime. May be you mean downtime while each single table is moved . I believe alter table t move to newtbs will move it on fly and should be like “Move table, then immediately move/rebuild its index).

    My question is : If we have partitioned table then do we need to move partitionwise or it will go in single go?

    If table is in DATA_01 and Index on INDEX_01 and I want to move them to DATA_01_ENCRYPT and INDEX_01_ENCRYPT respectively, What changes i need to do in this script?

    Thanks
    Shah

    Reply

    1. Hi Shah,

      Thanks for reading my blog, when I say it requires downtime you are right, it’s when the table and the lob are moved. As this happens in a loop it’s preferable to assume that the whole process will require downtime.
      If the downtime is not acceptable for you and you have enterprise edition you can change the script to use ONLINE table and index move. For the lob I don’t think you can move it online.
      If you have a partitioned table you will have to move each partition independently they will not be moved along with the table.
      Concerning your case if you have only one table, I would not use the script, just get the commands from it and adapt it to your needs.
      The script uses 3 loops, 1 for the tables, 1 for the lobs and 1 for the indexes.
      If you have a source and destination tablespace different for each, you will have to add some variables for each source and dest tablespaces, then use the correct variable in each loop.

      Thanks

      Cyrille

      Reply

  4. Thank you for quick turnup..
    Appreciate it..i have framed a procedure to get this done..thanks again

    Reply

  5. Looks like there is one small issue where the index tablespace for the current tablespace is hard-coded to be USERS and should use the variable. Other than that, worked great for me!

    Reply

  6. Good Job but I’m having this error:
    ORA-25191: cannot reference overflow table of an index-organized table
    ORA-06512: at line 18
    Can you help?

    Reply

    1. Hi Peter,

      Thanks for your feedback, I will look into it and get back to you, IOT tables need additionnal steps to be moved to another tablespace.
      stay tuned.

      Cyrille

      Reply

    1. Hi,

      What problem are you trying to solve, table fragmentation is generally not a problem, do you mean tablespace fragmentation or chained rows?

      Cyrille

      Reply

      2. Hi Cyrille,
        I get this (running against 11.2.0.4)
        ERROR at line 1:
        ORA-00997: illegal use of LONG datatype
        ORA-06512: at line 28

        Thanks in advance

        Reply

        1. Hi,
          This is because you have a table with a column of type long.
These tables cannot be moved like that, you need to use expdp and impdp with the remap_tablespace clause.
          Thanks
          Cyrille

          Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.