5 Replies Latest reply: Aug 3, 2009 3:02 AM by Pavan DBA RSS

    Move objects to New tablespace

    user564706
      Hi Friends,

      i have a 10g database and i imported a user schema from other database to my database in users tablespace.

      Now i would like to move few of the schema objects to new tablespace.

      1)what are all the objects in database that can be moved to new tablespace?

      2)what is the sql query to move to new tablespace

      3)Any additional setps to be followed after moving certain objects to new tablespace(rebuild index ...)

      Please let me know

      Regards
        • 1. Re: Move objects to New tablespace
          26741
          To identify the segments in a tablespaces :
          select owner, segment_type, segment_name, bytes/1048576 Size_MB
          from dba_segments
          where tablespace_name = '&tablespace_to_check'
          order by 1,2,3
          /
          The generic command to move a table is
          alter table  TABLENAME move NEWTABLESPACENAME nologging;
          If you move a table, you have to rebuild it's indexes. You can also move the indexes with the rebuild
          alter index INDEXNAME rebuild NEWTABLESPACENAME nologging;
          You could generate a move/rebuild script as
          select 'alter  ' || segment_type || '   ' || owner || '.' || segment_name || '  ' || decode (segment_type, table, 'move', index, 'rebuild') || '  tablespace  NEWTABLESPACENAME' || '  ;'
          from dba_segments
          where tablespace_name = '&tablespace_to_check'
          and segment_type in ('TABLE','INDEX')
          order by 1
          Test the generated script for the first few objects to see that the script is correct.


          After moving tables / indexes, you should update the schema statistics with
          exec dbms_stats.gather_schema_stats('SCHEMANAME');
          For other segment types (LOBSEGMENT / LOBINDEX, TABLE PARTITION, INDEX PARTITION), look up the ALTER TABLE and ALTER INDEX syntax documentation.


          TAKE A DATABASE BACKUP BEFORE ATTEMPTING A RE-ORG.

          Hemant K Chitale
          http://hemantoracledba.blogspot.com

          Edited by: Hemant K Chitale on Aug 2, 2009 4:27 PM
          • 2. Re: Move objects to New tablespace
            user564706
            Hi Hemanth,

            Thanks for the reply and one more thing.

            what are all the schema objects that can be moved from one Table space to other.(Is it only tables and Indexes) and what is the syntax for other objects if any?

            Regards
            • 3. Re: Move objects to New tablespace
              26741
              As I said, the ALTER TABLE syntax documentation covers LOB segments.

              See http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2104157

              Similarly, ALTER TABLE and ALTER INDEX also cover TABLE PARTITION and INDEX PARTITION.

              http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#CJAHHIBI

              http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1008.htm#i2050158



              Hemant K Chitale
              http://hemantoracledba.blogspot.com
              • 4. Re: Move objects to New tablespace
                sb92075
                what are all the schema objects that can be moved from one Table space to other.
                Any/every object which resides in "old" tablespace can be moved to "new" tablespace.

                SELECT DISTINCT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS WHERE TABLESPACE_NAME = <OLD_TS_NAME>
                • 5. Re: Move objects to New tablespace
                  Pavan DBA
                  you can move tables and indexes because all other objects will not occupy space in the database.