1 2 Previous Next 19 Replies Latest reply on Nov 8, 2016 6:14 PM by Mustafa KALAYCI

    Difference Between Object & Tablespace Reorganization

    Ramaraju

      Hi All,

      What is the difference between object reorganization and tablespace reorganization?

      Where can we see the released space in both reorganizations? like OS & database..

       

      Thanks,

      Ramaraju

        • 1. Re: Difference Between Object & Tablespace Reorganization
          John Thorton

          Ramaraju wrote:

           

          Hi All,

          What is the difference between object reorganization and tablespace reorganization?

          Where can we see the released space in both reorganizations? like OS & database..

           

          Thanks,

          Ramaraju

          you could reorganize a table without reorganizing the tablespace in which it resides.

           

          please post SQL & results that show how to reorganize a tablespace.

          • 2. Re: Difference Between Object & Tablespace Reorganization
            Mustafa KALAYCI

            Hello,

             

            When you reorganize your table, you release space in oracle data files but file size does not change so your database size does not change but your table size get smaller (probably). you can check this from ALL_SEGMENTS view,

             

            select * from all_segments where segment_name = 'YOUR_TABLE_HERE';

             

            you can see BYTES column here.

             

            when you reorganize your tablespace, oracle will put your data in an order based on your segments (tables and indexes). for example, you have a datafile with 32GB size, and there are only 2 table on this tablespace and datafile(a tablespace can have more than one datafile). these 2 tables have same size, 1GB but one of them at the middle of the datafile and the other at the end of the datafile, so you can not reclaim space from this datafile because at the end there is a segment. so you reorganize this tablespace and oracle simply take these 2 table and put them begining of the datafile so you will have empty space at the end of datafile but your database is still at the same size, because you must reclaim it with alter database datafile X resize Y command.

            1 person found this helpful
            • 3. Re: Difference Between Object & Tablespace Reorganization
              John Thorton

              Mustafa KALAYCI wrote:

               

              Hello,

               

              When you reorganize your table, you release space in oracle data files

               

              table reorganization has ZERO impact on Oracle data files.

              1 person found this helpful
              • 4. Re: Difference Between Object & Tablespace Reorganization
                Mustafa KALAYCI

                hi John,

                 

                maybe I couldn't explain myself enough, let say we have a table with 100 Blocks but half of these blocks are empty because of delete operations, when I reorganize(move) this table block count will be 50. so it will be release 50 blocks in datafile. that is what I mean. there will be more empty space (space that is not belong to any segment) in the datafile.

                1 person found this helpful
                • 5. Re: Difference Between Object & Tablespace Reorganization
                  John Thorton

                  Mustafa KALAYCI wrote:

                   

                  hi John,

                   

                  maybe I couldn't explain myself enough, let say we have a table with 100 Blocks but half of these blocks are empty because of delete operations, when I reorganize(move) this table block count will be 50. so it will be release 50 blocks in datafile. that is what I mean. there will be more empty space (space that is not belong to any segment) in the datafile.

                  After table reorganization the "released" blocks remain in tablespace & datafile experience ZERO change.

                  • 6. Re: Difference Between Object & Tablespace Reorganization
                    Mustafa KALAYCI

                    Hmm, I am not sure maybe we are talking about different things? for ex this:

                     

                    
                    create table tmp1 as select * from dba_objects;
                    delete from tmp1;
                    commit;
                    
                    select blocks from user_Segments where segment_name ='TMP1';
                    1792
                    
                    alter table tmp1 move;
                    select blocks from user_Segments where segment_name ='TMP1';
                    8
                    
                    
                    
                    

                     

                    so this table has 1792 blocks in datafile before move and actually this 1792 blocks was empty. so these block occupy unnecessary space in datafile, when I move the table it become default block count to 8. so the table has smaller space in datafile and this means datafile has more empty space. am I wrong ?

                    • 7. Re: Difference Between Object & Tablespace Reorganization
                      John Thorton

                      Mustafa KALAYCI wrote:

                       

                      Hmm, I am not sure maybe we are talking about different things? for ex this:

                       

                      1. createtabletmp1asselect*fromdba_objects;
                      2. deletefromtmp1;
                      3. commit;
                      4. selectblocksfromuser_Segmentswheresegment_name='TMP1';
                      5. 1792
                      6. altertabletmp1move;
                      7. selectblocksfromuser_Segmentswheresegment_name='TMP1';
                      8. 8

                       

                      so this table has 1792 blocks in datafile before move and actually this 1792 blocks was empty. so these block occupy unnecessary space in datafile, when I move the table it become default block count to 8. so the table has smaller space in datafile and this means datafile has more empty space. am I wrong ?

                      yes, you are wrong.

                       

                      query  DBA_FREE_SPACE  before & after so you see the same value.

                      • 8. Re: Difference Between Object & Tablespace Reorganization
                        Mustafa KALAYCI

                        Hi John,

                         

                        I did,

                        CREATE TABLESPACE "TBS_X" DATAFILE 
                          SIZE 1M
                          AUTOEXTEND ON NEXT 1M MAXSIZE 32767M,
                          LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                          EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
                         NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;    
                        
                        select * from DBA_FREE_SPACE where tablespace_name = 'TBS_X'  and file_id = 193; 
                        TBS_X    193    8    983040    120    193
                        
                        drop table tmp1;
                        
                        create table tmp1 tablespace tbs_x as select * from dba_objects;
                        delete from tmp1;
                        commit;
                        select * from DBA_FREE_SPACE where tablespace_name = 'TBS_X'  and file_id = 193;
                        no rows
                        
                        alter table tmp1 move;
                        
                        select * from DBA_FREE_SPACE where tablespace_name = 'TBS_X'  and file_id = 193; 
                        TBS_X    193    8    983040    120    193
                        

                         

                        After creating table there were no free space, after move operation, there are empty space again.

                        • 9. Re: Difference Between Object & Tablespace Reorganization
                          Mark D Powell

                          Ramaraju, to add to what others have posted on this thread to see the space being used by an object both before and after taking any action related to object allocation look at the following Oracle RDBMS dictionary views:

                          - -

                          DBA_FREE_SPACE  to see where unused extents exist in files/tablepsaces

                          DBA _EXTENTS        to see where allocated extents exist in files/tablespaces

                          DBA_SEGMENTS     to see segment size statistics like total allocated bytes, blocks, and number of extents

                          DBA_DATA_FILES    to see files associated with a tablespace and file attributes

                          - -

                          A general rule with Oracle is once space is allocated to an object/file it remains part of that object until such time as the DBA takes action to reorganize the object.  For example deleting rows from a table does not reduce the table size but only make space within the table available for reuse.  How Oracle allocates and uses space is detailed in the official documentation.

                          - -

                          HTH -- Mark D Powell --

                          • 10. Re: Difference Between Object & Tablespace Reorganization
                            Mustafa KALAYCI

                            I also want to add something, if your table has no empty space in it, which means there is no update or delete operation on the table than probably reorganize won't make the table size smaller. like log tables. I just ignore that because if you want to reorganize your table %90 you believe that you have empty space in your table.

                            • 11. Re: Difference Between Object & Tablespace Reorganization
                              Jonathan Lewis

                              An analogy to answer the first question: https://jonathanlewis.wordpress.com/2016/11/07/reorg/

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Difference Between Object & Tablespace Reorganization
                                ddf_dba

                                Actually you don't:

                                 

                                SQL> create table emp2 as select * From emp;

                                 

                                 

                                Table created.

                                 

                                 

                                SQL>

                                SQL> begin

                                  2          for i in 1..11 loop

                                  3                  insert into emp2

                                  4                  select * From emp2;

                                  5          end loop;

                                  6

                                  7          commit;

                                  8  end;

                                  9  /

                                 

                                 

                                PL/SQL procedure successfully completed.

                                 

                                 

                                SQL>

                                SQL> select count(*)

                                  2  from emp2;

                                 

                                 

                                  COUNT(*)

                                ----------

                                     28672

                                 

                                 

                                SQL>

                                SQL> select blocks from user_segments

                                  2  where segment_name = 'EMP2';

                                 

                                 

                                    BLOCKS

                                ----------

                                       256

                                 

                                 

                                SQL>

                                SQL> select tablespace_name, sum(blocks)

                                  2  from dba_free_space

                                  3  where tablespace_name = 'USERS'

                                  4  group by tablespace_name;

                                 

                                 

                                TABLESPACE_NAME                SUM(BLOCKS)

                                ------------------------------ -----------

                                USERS                                49520

                                 

                                 

                                SQL>

                                SQL> delete from emp2

                                  2  where  rowid not in (

                                  3      select min(rowid)

                                  4      from  emp2

                                  5      group  by empno

                                  6    )

                                  7  /

                                 

                                 

                                28658 rows deleted.

                                 

                                 

                                SQL>

                                SQL> commit;

                                 

                                 

                                Commit complete.

                                 

                                 

                                SQL>

                                SQL> select count(*)

                                  2  from emp2;

                                 

                                 

                                  COUNT(*)

                                ----------

                                        14

                                 

                                 

                                SQL>

                                SQL> alter table emp2 move;

                                 

                                 

                                Table altered.

                                 

                                 

                                SQL>

                                SQL> select blocks from user_segments

                                  2  where segment_name = 'EMP2';

                                 

                                 

                                    BLOCKS

                                ----------

                                         8

                                 

                                 

                                SQL>

                                SQL> select tablespace_name, sum(blocks)

                                  2  from dba_free_space

                                  3  where tablespace_name = 'USERS'

                                  4  group by tablespace_name;

                                 

                                 

                                TABLESPACE_NAME                SUM(BLOCKS)

                                ------------------------------ -----------

                                USERS                                49768

                                 

                                 

                                SQL>

                                 

                                The space is considered free and the total free block count increased by the number of blocks released in the move.

                                 

                                 

                                David Fitzjarrell

                                • 13. Re: Difference Between Object & Tablespace Reorganization
                                  rp0428

                                  The space is considered free and the total free block count increased by the number of blocks released in the move.

                                   

                                   

                                  I believe the point John was making is that your 'space is considered free' means 'free to the tablespace' - not free to the os or datafile.

                                   

                                  The blocks are still part of the extent and still assigned to the tablespace.

                                   

                                  The distinction is between LOGICAL free space (the tablespace) and PHYSICAL free space. That seems to be what Mustafa is not understanding.

                                  • 14. Re: Difference Between Object & Tablespace Reorganization
                                    rp0428

                                    An analogy to answer the first question: https://jonathanlewis.wordpress.com/2016/11/07/reorg/

                                     

                                    Unfortunately that article fails to take into account what neighborhood you park that auto in. Park it in mine for more than 5 minutes and your 'boot' will have PLENTY of free space.

                                    1 2 Previous Next