1 2 Previous Next 15 Replies Latest reply: Apr 5, 2010 12:53 PM by 623219 RSS

    Move Table same tablespace

    623219
      Hi There!

      I got a table with 5.7Tb and it´s fragmented, so I need to reclaim space in that datafiles.

      How the table is stored in a LMT Tablespace, I´m unable to do a shrink space and hence, I´m thinking in to do a rebuild in this table, issuing the move command to the same tablespace.

      I don´t have more space to create a new tablespace with the same size as the concerned tablespace to do a reorganization.

      I dropped old partitions, in order to release some space in tablespace and now I got 2.3Tb free.

      This is a partitioned table and my concern is how I don´t have enough space to create another tablespace, if I run the move to the same tablespace, I´ll use the relesed space in the tablespace or do I have to increase space in my temp tablespace?

      Other doubt is if I run this online, will I lock the table exclusively?

      Thanks in advance!

      Regards
        • 1. Re: Move Table same tablespace
          Mark D Powell
          Why exactly do you need to reorganize the table? That is do you believe that the table blocks are poorly utilized and that the individual partitions will be smaller use fewer extents after a reorganization or are you just trying to move the allocated extents toward the logical front of the data files so that you can shrink the files and release space back to the OS?

          Is this an auto-allocate or uniform extent locally managed tablespace?

          If you want to move the partitions toward the front of the file one approach might be to use the swap table into a partition feature to allow you to create a table, populate it from a partition, drop the partition, swap the table into the partitioned table.

          But I am not sure there is any real benefit to all this work unless you know the partitioned table will not need to grow and reclaim the freed space in the near future. Where the extents are located probably does not matter very much. With uniform extents it would not matter at all, ever.

          HTH -- Mark D Powell --
          chg wording and in number to use fewer extents to be clear on intent of question

          Edited by: Mark D Powell on Apr 1, 2010 6:45 AM
          • 2. Re: Move Table same tablespace
            729338
            May be Segment Advisor will help you. See the following link.

            http://www.dba-oracle.com/t_segment_advisor_10g.htm

            You can also do Online segment Shink.

            http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php

            Regards

            Edited by: skvaish1 on Apr 1, 2010 9:55 AM
            • 3. Re: Move Table same tablespace
              737979
              I think you may be suffering from a common malady - the belief that a fragmented table is somehow bad.
              What is it that you want to accomplish by "de-fragmenting" your 5 TB table?

              Check out this thread from AskTom. There is a lot good information about how Oracle works here.
              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8593101954988
              • 4. Re: Move Table same tablespace
                737979
                I think you may be suffering from a common malady - the belief that a fragmented table is somehow bad.
                What is it that you want to accomplish by "de-fragmenting" your 5 TB table?

                Check out this thread from AskTom. There is a lot good information about how Oracle works here.
                http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8593101954988
                • 5. Re: Move Table same tablespace
                  KSG
                  Hi,

                  I faced the similar issue so you may go throught the link

                  Can't able to Shrink tables (datafiles)

                  But finally I took export and import back.

                  Regards
                  KSG
                  • 6. Re: Move Table same tablespace
                    623219
                    I tried to move the table to the same tablespace, but I got the error:

                    SQL> ALTER TABLE AUDIT_RECORD MOVE
                    2 /
                    ALTER TABLE AUDIT_RECORD MOVE
                    *
                    ERROR at line 1:
                    ORA-14511: cannot perform operation on a partitioned object


                    Is there another way to move the partitions to the same tablespace??
                    • 7. Re: Move Table same tablespace
                      Mark D Powell
                      Look in our SQL manual under ALTER TABLE at the move table partition clause.

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

                      HTH -- Mark D Powell --
                      • 8. Re: Move Table same tablespace
                        729338
                        Hi,

                        Why are you trying moving a table into same tablespace. You can move to different tablespace. Instead of move, you can use shrink.

                        ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

                        -- Recover space and amend the high water mark (HWM).
                        ALTER TABLE scott.emp SHRINK SPACE;

                        Regards
                        • 9. Re: Move Table same tablespace
                          Hemant K Chitale
                          1. Is it really 5.7TB in size ?

                          2. How are you managing or propose to manage the outage when MOVing the table ?

                          3. You do know that you will also have to REBUILD the indexes on the table ?

                          4. What sound reason do you have for attempting to rebuild the table ?

                          5. Does your manager approve of this ?


                          Hemant K Chitale
                          • 10. Re: Move Table same tablespace
                            367117
                            Did you look into Online Data Reorganization & Redefinition ?

                            http://www.oracle.com/technology/deploy/availability/pdf/ha_10gR2_online_reorg_twp.pdf

                            Did you explore the option of export and import ?
                            • 11. Re: Move Table same tablespace
                              EdStevens
                              Hemant K Chitale wrote:
                              1. Is it really 5.7TB in size ?

                              2. How are you managing or propose to manage the outage when MOVing the table ?

                              3. You do know that you will also have to REBUILD the indexes on the table ?

                              4. What sound reason do you have for attempting to rebuild the table ?

                              5. Does your manager approve of this ?
                              Heck, his manager probably ordered him to do it - because that's the only tool in the manager's toolkit from when he was a DBA at Oracle 5.0.

                              ;-)
                              >
                              Hemant K Chitale
                              • 12. Re: Move Table same tablespace
                                avramits
                                Yes you can rebuild partition by partition in same tablespace. Unfortunately no one guaranteed that oracle would start from beginning of TS. There is a big chance that after you finished table rebuild, you will not be able to shrink tablespace. There is some trick can be used, you can run query to find which objects at the end of tablespace then move that object and shrink tablespace as much as you can, then start over. This is a long time project, but if you are want to do this anyway, then do it.
                                select de.* 
                                  from  dba_extents de,
                                       (select file_id, max(block_id) block_id
                                          from dba_extents 
                                         where tablespace_name= 'LMT'
                                         group by file_id) t
                                 where tablespace_name= 'LMT' 
                                   and de.file_id = t.file_id
                                   and de.block_id = t.block_id
                                /
                                
                                ALTER TABLE <t_owner>.<t_name> MOVE PARTITION <p_name> TABLESPACE LMT PARALLEL;
                                Also do not forget to rebuild unusable indexes after you move partition.
                                select 'alter INDEX ' || index_OWNER || '.' || INDEX_NAME 
                                || ' REBUILD PARTITION ' || PARTITION_NAME || ' TABLESPACE ' || TABLESPACE_NAME || ' NOLOGGING PARALLEL;'
                                from all_ind_partitions 
                                where status='UNUSABLE'
                                order by  partition_name DESC, index_name
                                • 13. Re: Move Table same tablespace
                                  623219
                                  Thanks for your help, Avramits!!

                                  Now, a I got another shot:

                                  I finished to move some partitions of the mentioned table and now I´m rebuilding the all indexes involved.

                                  I´m doing just "ALTER INDEX <INDEX_NAME> REBUILD PARTITION <PARTITION_NAME>" and the first index has around 530Gb.

                                  I got a huge doubt:

                                  How this process is slow, is it a good idea to use the parallel option in this rebuild, toward to improve performance in the environment?

                                  My environment is:

                                  Oracle 10.2.0.4.0 - 64 bit

                                  Server:

                                  Dell PowerEdge 2950 III - 2 Quad-Core Intel Xeon 5300 3,0 GHz - 16Gb Ram

                                  CPU most part of time 75% idle

                                  Doing the rebuild now, I got 60% CPU idle.



                                  Thanks in advance.

                                  Regards,
                                  • 14. Re: Move Table same tablespace
                                    sb92075
                                    How this process is slow, is it a good idea to use the parallel option in this rebuild, toward to improve performance in the environment?
                                    What's the rush?
                                    What benefit is gained by having it complete sooner rather than later?
                                    1 2 Previous Next