How to move table from one tablespace to another in oracle 11g

Question

I run oracle 11g and need to move table  tbl1  from one tablespace  tblspc1  to another  tblspc2   What is the easiest way to do that

User · Answer

Try this -  ALTER TABLE  lt TABLE NAME to be moved gt  MOVE TABLESPACE  lt destination TABLESPACE NAME gt    Very nice suggestion from IVAN in comments so thought to add in my answer  Note  this will invalidate all table s indexes  So this command is usually followed by  alter index  lt owner gt    lt index name gt   rebuild

User · Answer

Moving tables   First run   SELECT  ALTER TABLE  lt schema name gt       OBJECT NAME     MOVE TABLESPACE       lt tablespace name gt     FROM ALL OBJECTS WHERE OWNER     lt schema name gt   AND OBJECT TYPE    TABLE   lt  gt    lt TABLESPACE NAME gt      -- Or suggested in the comments  did not test it myself   SELECT  ALTER TABLE  lt SCHEMA gt       TABLE NAME     MOVE TABLESPACE      TABLESPACE NAME gt      FROM dba tables  WHERE OWNER     lt SCHEMA gt    AND TABLESPACE NAME  lt  gt    lt TABLESPACE NAME gt    Where  lt schema name gt  is the name of the user  And  lt tablespace name gt  is the destination tablespace   As a result you get lines like   ALTER TABLE SCOT PARTS MOVE TABLESPACE USERS    Paste the results in a script or in a oracle sql developer like application and run it   Moving indexes   First run   SELECT  ALTER INDEX  lt schema name gt     INDEX NAME    REBUILD TABLESPACE  lt tablespace name gt     FROM ALL INDEXES WHERE OWNER     lt schema name gt   AND TABLESPACE NAME NOT LIKE   lt tablespace name gt      The last line in this code could save you a lot of time because it filters out the indexes which are already in the correct tablespace   As a result you should get something like   ALTER INDEX SCOT PARTS NO PK REBUILD TABLESPACE USERS   Paste the results in a script or in a oracle sql developer like application and run it   Last but not least  moving LOBs   First run   SELECT  ALTER TABLE  lt schema name gt     LOWER TABLE NAME     MOVE LOB    LOWER COLUMN NAME      STORE AS  TABLESPACE  lt table space gt     FROM DBA TAB COLS WHERE OWNER     lt schema name gt   AND DATA TYPE like   LOB      This moves the LOB objects to the other tablespace   As a result you should get something like   ALTER TABLE SCOT bin 6t926o3phqjgqkjabaetqg   0 MOVE LOB calendar  STORE AS  TABLESPACE USERS    Paste the results in a script or in a oracle sql developer like application and run it   O and there is one more thing   For some reason I wasn t able to move  DOMAIN  type indexes  As a work around I dropped the index  changed the default tablespace of the user into de desired tablespace  and then recreate the index again  There is propably a better way but it worked for me

User · Answer

I tried many scripts but they didn t work for all objects  You can t move clustered objects from one tablespace to another  For that you will have to use expdp  so I will suggest expdp is the best option to move all objects to a different tablespace   Below is the command   nohup expdp     as sysdba   DIRECTORY test dir DUMPFILE users dmp LOGFILE users log TABLESPACES USERS  amp    You can check this link for details

User · Answer

Use sql from sql   spool output of this to a file   select  alter index    owner       index name    rebuild tablespace TO TABLESPACE NAME   from all indexes where owner  OWNERNAME     spoolfile will have something like this   alter index OWNER PK INDEX rebuild tablespace CORRECT TS NAME

User · Answer

Try this to move your table  tbl1  to tablespace  tblspc2    alter table tb11 move tablespace tblspc2

[oracle] How to move table from one tablespace to another in oracle 11g

Examples related to oracle

Examples related to oracle11g

Examples related to move