Moving objects from one tablespace to another


Notice: get_currentuserinfo is deprecated since version 4.5.0! Use wp_get_current_user() instead. in /home/dbascrip/public_html/wp-includes/functions.php on line 4435

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

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

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

Hope it helps

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

    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

  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

  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

    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

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

  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!

  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?

    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

      1. 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

        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

Leave a Reply

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