Forum Stats

  • 3,733,036 Users
  • 2,246,686 Discussions
  • 7,856,486 Comments

Discussions

Difference Between Object & Tablespace Reorganization

Ramaraju
Ramaraju Member Posts: 981 Bronze Badge

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

Mustafa_KALAYCIandrewmyJonathan Lewis

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited November 2016
    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.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited November 2016

    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.

    Ramaraju
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited November 2016
    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.

    Ramaraju
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited November 2016

    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.

    Ramaraju
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited November 2016
    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.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited November 2016

    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';1792alter 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 ?

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited November 2016
    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
    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 ?

    yes, you are wrong.

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

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited November 2016

    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    193drop 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 rowsalter 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.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited November 2016

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

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited November 2016

    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,601 Gold Crown
    edited November 2016

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

    Regards

    Jonathan Lewis

    Mustafa_KALAYCI
  • ddf_dba
    ddf_dba Member Posts: 1,398 Bronze Trophy
    edited November 2016

    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

  • Unknown
    edited November 2016

    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.

  • Unknown
    edited November 2016
    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.

    andrewmyJonathan Lewis
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited November 2016
    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.

    and this is exactly what I mean, reorganizing table release space "in the datafile".

  • Unknown
    edited November 2016
    Mustafa KALAYCI wrote: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.and this is exactly what I mean, reorganizing table release space "in the datafile".

    No - at most it releases space in the tablespace.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited November 2016

    yes you are right but eventually it is datafile(s) that this table in it(them). anyway just terminology.

  • Unknown
    edited November 2016
    but eventually it is datafile(s) that this table in it(them). anyway just terminology. 

    Not really - a table is in a 'tablespace'. The tablespace is in  one or more datafiles.

    And, IMHO, it is NOT just terminology.

    It is also the difference between LOGICAL and PHYSICAL. Datafiles are physical entities. A tablelspace is a logical entity.

    Terminology, yes. But it is important to use the right terminology especially about freeing up space.

    delete rows from a table and it might free up space in a block - but only for that table. Delete all the rows from the block and it might release the block - but that block will still belong to the same extent which might still be allocated to that same table.

    Even if you release extents you release them back to the logical tablespace. None of that increases the free space in the physical datafile.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited November 2016

    ok @rp0428, I will go what you say which is of course correct. I am a trainer and sometimes I just explain things superficial because it helps trainer to understand it better. of course giving the correct information is important. anyway, you are right and I believe you understood me.

This discussion has been closed.