Latest posts by Cyrille Modiano (see all)
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
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.
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) 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
21 thoughts on “Moving objects from one tablespace to another”
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:
The symbol “loop” was substituted for “DBMS_OUTPUT” to continue.
another question: is this will make any downtime?
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.
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
Fixed ! let me know if it works for you.
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 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.
Thank you for quick turnup..
Appreciate it..i have framed a procedure to get this done..thanks again
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!
Thanks you for your feedback, I fixed the script.
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?
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.
I updated the script, can you give a try?
Thanks for help Cyrille!
Now thrown ORA-00997: illegal use of LONG datatype
HI CYRILLE PLEASE POST A QUERY TO FIND THE FRAGMENTATION PERCENT OF THE TABLE IN ORACLE 11GR2.
What problem are you trying to solve, table fragmentation is generally not a problem, do you mean tablespace fragmentation or chained rows?
Hi Cyrille, i’m having this problem:
ORA-00997: illegal use of LONG datatype
Can you tell me more?
What line is throwing the error?
Can you please also include for the table partition & index partitions.
I get this (running against 18.104.22.168)
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
ORA-06512: at line 28
Thanks in advance
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.