Forum Stats

  • 3,840,350 Users
  • 2,262,592 Discussions
  • 7,901,236 Comments

Discussions

Difference Between Object & Tablespace Reorganization

Ramaraju
Ramaraju Member Posts: 981 Bronze Badge
edited Nov 8, 2016 1:14PM in General Database Discussions

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

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 7, 2016 9:33AM
    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,383 Bronze Crown
    edited Nov 7, 2016 10:23AM

    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 Nov 7, 2016 10:26AM
    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,383 Bronze Crown
    edited Nov 7, 2016 10:33AM

    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 Nov 7, 2016 10:37AM
    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,383 Bronze Crown
    edited Nov 7, 2016 10:45AM

    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 Nov 7, 2016 10:49AM
    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,383 Bronze Crown
    edited Nov 7, 2016 11:04AM

    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 Nov 7, 2016 11:55AM

    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,383 Bronze Crown
    edited Nov 7, 2016 12:26PM

    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.

This discussion has been closed.