This discussion is archived
1 2 Previous Next 21 Replies Latest reply: May 22, 2011 3:04 AM by Hemant K Chitale RSS

Uniform extent and initial extent / HWM - why am i left with 24 blocks ?

user12050668 Newbie
Currently Being Moderated
Hi guys,

Recently, I have table that is occupied a few hundred megabytes even after i truncate and deallocate unused.

So I decided to issue this statement to remove freespace under the HWM.
<i>alter table ain.scb_grp_user_priority DEALLOCATE UNUSED KEEP 1K;</i>


After this statement is completed, i issue this

<i>select segment_name, segment_type, bytes/1024/1024, blocks, tablespace_name from dba_segments where tablespace_name = 'AIN_CC' and segment_name = 'SCB_GRP_USER_PRIORITY' order by bytes desc;</i>

SCB_GRP_USER_PRIORITY     TABLE     0.1875     <b>24</b>
I realize i am still left with 24 blocks.


q1) i specified to keep 1K, why am i still left with 24 blocks * 8192 bytes which is more then 1k ?

q2) How do i know the default number of block per extent ?
everytime i create a table without any storage clause or alter table allocate extend, the amount of block added = 8.

3) I am using a locally managed tablespace, so i believe extent allocated are of uniform size.

When i check the dba_extents table
select owner,segment_name, segment_type, tablespace_name, extent_id, bytes, blocks from dba_extents where segment_name = 'SCB_GRP_USER_PRIORITY' and owner = 'AIN';

AIN     SCB_GRP_USER_PRIORITY     TABLE     AIN_CC     0     196608     24

I saw EXTEND_ID 0 with 24 blocks.
Shouldnt there be 3 extend, each with 8 blocks ?

Regards,
Noob
  • 1. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    860384 Newbie
    Currently Being Moderated
    Hi Noob,

    With LMT we can have AUTOALLOCATE or UNIFORM size.

    If you are using UNIFORM size, alll extents should be of the same size. You specifiy the size, else default I think is 1M. Also, each extent should be atleast have 5 blocks.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref377
  • 2. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Zoltan Kecskemethy Expert
    Currently Being Moderated
    Check INITIAL and MINEXTENTS paragraphs here:
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/clauses009.htm#SQLRF30013
    BTW you didn't give us db version so linking 11gr2 docs.

    a1. this is tablespace dependent. see previously mentioned docs. initial size is always allocated to a table AFAIK.

    a2. also tablespace dependent

    a3. check it.
     select tablespace_name, extent_management, allocation_type from dba_tablespaces; 
    in allocation type SYSTEM means auto allocate AFAIK.
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5060.htm#REFRN23287

    HTH, Zoltan
  • 3. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Kecskemethy wrote:

    a3. check it.
     select tablespace_name, extent_management, allocation_type from dba_tablespaces; 
    in allocation type SYSTEM means auto allocate AFAIK.
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5060.htm#REFRN23287
    Good suggestion - but in this case perhaps we need to see the blocksize as well.

    Regards
    Jonathan Lewis
  • 4. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    jgarry Guru
    Currently Being Moderated
    initial size is always allocated to a table AFAIK.
    New feature: [url http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/tables002.htm#insertedID8]Deferred Segment Creation.
  • 5. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Collect this information :
    For that tablespace :
    select block_size, extent_management, allocation_type, initial_extent, min_extents, next_extent 
    from dba_tablespaces where tablespace_name = '&tbs_name';
    For the table :
    select tablespace_name, initial_extent, min_extents, next_extent
    from dba_tables
    where owner = 'AIN'
    and table_name = 'SCB_GRP_USER_PRIORITY';
    Hemant K Chitale
  • 6. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    user12050668 Newbie
    Currently Being Moderated
    Hi all,

    Sorry for the late reply.

    select tablespace_name, initial_extent, min_extents, next_extent
    from ALL_tables
    where owner = 'AIN'
    and table_name = 'SCB_GRP_USER_PRIORITY';


    AIN_CC     382730240     1     NULL


    select block_size, extent_management, allocation_type, initial_extent, min_extents, next_extent
    from user_tablespaces where tablespace_name = 'AIN_CC';

    8192     LOCAL     SYSTEM     65536     1     

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

    1) I understand now that my allocation_type is SYSTEM.
    2) I have a large INITIAL_EXTENT

    so now i wish to deallocate that space since i have 0 rows and keep only 1K.

    alter table AIN.SCB_GRP_USER_PRIORITY deallocate unused keep 1k;

    select tablespace_name, initial_extent, min_extents, next_extent
    from ALL_tables
    where owner = 'AIN';


    AIN_CC     196608     1     
    ---------------------------------------------------------------

    I have 196608 bytes (approx 192kb) for initial which is 24 blocks and that is more larger then 1kb isnt it ?

    Regards,
    Noob
  • 7. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Your table has 1 (initial) extent of 192K. You cannot deallocate below the 192K -- i.e. the initial extent does not get 'shrunk'.

    (Even otherwise, if you specify a KEEP smaller than the initial extent size, the initial extent (which, in most cases, is at least 64K) is "kept").

    Why would the table have a 192K extent ? It is likely that this table was created with a CREATE TABLE statement that specified an INITIAL clause and/or was created by an import from a dump file which, itself, had been exported with compress=y causing the INITIAL to be specified in the dump file.



    However, are these results from the same database ?
    select tablespace_name, initial_extent, min_extents, next_extent
    from ALL_tables
    where owner = 'AIN'
    and table_name = 'SCB_GRP_USER_PRIORITY';
    
    AIN_CC 382730240 1 NULL
    These show a table with an INITIAL defined as 365MB.


    Hemant K Chitale
  • 8. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    user12050668 Newbie
    Currently Being Moderated
    Hi Hemant,

    Exactly the same database..
    Before I issue the deallocate unused keep 1K, it is 382730240

    After issuing,
    select tablespace_name, initial_extent, min_extents, next_extent
    from ALL_tables
    where owner = 'AIN'
    and table_name = 'SCB_GRP_USER_PRIORITY';

    IT is 196608.


    q1) Which is why i am wondering why 196608 ? and not 64k ?

    Regards,
    Noob
  • 9. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Lubiez Jean-Valentin Guru
    Currently Being Moderated
    Hello,

    Which is why i am wondering why 196608 ? and not 64k ?
    *192K = 3 x 64K*

    In general (for a LMT with AUTOALLOCATE option) it's a multiple of *64K* (for the 16 first Extents) and as the INITIAL was set to *192K*, Oracle keeps *3* Extents of *64K* so as to satisfy the Clause Storage.


    Hope this help.
    Best regards,
    Jean-Valentin
  • 10. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user12050668 wrote:

    Exactly the same database..
    Before I issue the deallocate unused keep 1K, it is 382730240

    After issuing,
    select tablespace_name, initial_extent, min_extents, next_extent
    from ALL_tables
    where owner = 'AIN'
    and table_name = 'SCB_GRP_USER_PRIORITY';

    IT is 196608.


    q1) Which is why i am wondering why 196608 ? and not 64k ?
    The call to deallocate/keep can only deallocate space down to the table highwater mark - and then it will mess around with boundaries like 64KB or 1MB dictated by the restrictions of the autoallocate option.

    In your case I would guess that the highwater mark had reached something between 128KB and 192KB (i.e. between 2 * 64KB and 3 * 64KB), even though the data has since been deleted. if this is the case then deallocate further you will need to reduce the highwater mark by using 'alter table XXX shrink space'; before doing the deallocate/keep.

    Regards
    Jonathan Lewia
  • 11. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    See :
    SQL> create tablespace X datafile '/usr/tmp/X.dbf' size 100M extent management local autoallocate;
    
    Tablespace created.
    
    SQL> create table X_TABLE (col_1 number, col_2 varchar2(400)) tablespace X storage (initial 192K);
    
    Table created.
    
    SQL> 
    SQL> select initial_extent, next_extent, min_extents from dba_tablespaces
      2  where tablespace_name = 'X';
    
    INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
    -------------- ----------- -----------
             65536                       1
    
    SQL> 
    SQL> insert into X_TABLE values (0, dbms_random.string('X',250));
    
    1 row created.
    
    SQL> select bytes/1024, extents from user_segments where segment_name = 'X_TABLE';
    
    BYTES/1024    EXTENTS
    ---------- ----------
           192          3
    
    SQL> 
    SQL> truncate table X_TABLE;
    
    Table truncated.
    
    SQL> alter table X_TABLE deallocate unused;
    
    Table altered.
    
    SQL> select bytes/1024, extents from user_segments where segment_name = 'X_TABLE';
    
    BYTES/1024    EXTENTS
    ---------- ----------
           192          3
    
    SQL> select initial_Extent from user_tables where table_name = 'X_TABLE';
    
    INITIAL_EXTENT
    --------------
            196608
    
    SQL> 
    If a table has created with an INITIAL extent size, Oracle retains that definition through TRUNCATE and DEALLOCATE UNUSED.

    However, as suggested by Jonathan, you could SHRINK SPACE :
    SQL> alter table X_TABLE enable row movement;
    
    Table altered.
    
    SQL> alter table X_TABLE shrink space;
    
    Table altered.
    
    SQL> select bytes/1024, extents from user_segments where segment_name = 'X_TABLE';
    
    BYTES/1024    EXTENTS
    ---------- ----------
            64          1
    
    SQL> select initial_Extent from user_tables where table_name = 'X_TABLE';
    
    INITIAL_EXTENT
    --------------
            196608
    
    SQL> 
    Hemant K Chitale
  • 12. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    user12050668 Newbie
    Currently Being Moderated
    Hi guys,

    I am just very puzzled over the term "KEEP 1K" and oracle keeping 24 blocks for me.
    Even though i have shrink, it still the same.

    select segment_name, segment_type, tablespace_name, extent_id, bytes, blocks from USER_EXTENTS where segment_name = 'SMS_BOX';

    SMS_BOX     TABLE     AIN_CC     0     8388608     1024
    SMS_BOX     TABLE     AIN_CC     1     8388608     1024
    SMS_BOX     TABLE     AIN_CC     2     8388608     1024
    SMS_BOX     TABLE     AIN_CC     3     8388608     1024
    SMS_BOX     TABLE     AIN_CC     4     8388608     1024
    SMS_BOX     TABLE     AIN_CC     5     8388608     1024
    SMS_BOX     TABLE     AIN_CC     6     8388608     1024
    SMS_BOX     TABLE     AIN_CC     7     8388608     1024
    SMS_BOX     TABLE     AIN_CC     8     8388608     1024
    SMS_BOX     TABLE     AIN_CC     9     8388608     1024
    SMS_BOX     TABLE     AIN_CC     10     8388608     1024
    SMS_BOX     TABLE     AIN_CC     11     8388608     1024
    SMS_BOX     TABLE     AIN_CC     12     8388608     1024
    SMS_BOX     TABLE     AIN_CC     13     8388608     1024
    SMS_BOX     TABLE     AIN_CC     14     8388608     1024


    SELECT SEGMENT_NAME, BYTES/1024/1024 mb FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'SMS_BOX';
    SMS_BOX     128

    SELECT TABLE_NAME, INITIAL_EXTENT FROM USER_TABLES WHERE TABLE_NAME = 'SMS_BOX';
    SMS_BOX     134217728


    ALTER TABLE SMS_BOX DEALLOCATE UNUSED;


    SELECT SEGMENT_NAME, BYTES/1024/1024 mb FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'SMS_BOX';
    SMS_BOX     128

    SELECT TABLE_NAME, INITIAL_EXTENT FROM USER_TABLES WHERE TABLE_NAME = 'SMS_BOX';
    SMS_BOX     134217728

    ------------------------------------------
    Nothing has changed. So we are sure deallocate unused only deallocate until the HWM and the HWM is only reset until the initial extent
    -------------------------------------------

    ALTER TABLE sms_box ENABLE ROW MOVEMENT;
    ALTER TABLE sms_box SHRINK SPACE;

    SELECT SEGMENT_NAME, BYTES/1024/1024 mb FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'SMS_BOX';
    SMS_BOX     0.1875


    select segment_name, segment_type, tablespace_name, extent_id, bytes, blocks from USER_EXTENTS where segment_name = 'SMS_BOX';
    SMS_BOX     TABLE     AIN_CC     0     196608     24

    ----------------------------
    Good. My space is reduce to 0.18mb. But I am still left with 24 blocks !
    ---------------------------

    ALTER TABLE SMS_BOX DEALLOCATE UNUSED KEEP 1K;

    select segment_name, segment_type, tablespace_name, extent_id, bytes, blocks from USER_EXTENTS where segment_name = 'SMS_BOX';
    SMS_BOX     TABLE     AIN_CC     0     196608     24

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


    Q1) Why do Oracle keep allocating 24 blocks to me, even though i already shrink the space . I have also explicitly stated i want to keep 1k, so shouldnt 64k (minimum) be allocated to me instead ?


    Q2) Does truncate and deallocate unused only freed space up until the INITIAL EXTENT ?


    Rgds,
    Noob
  • 13. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Are there rows present in SMS_BOX ?


    Hemant K Chitale
  • 14. Re: Uniform extent and initial extent / HWM - why am i left with 24 blocks ?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user12050668 wrote:


    Q1) Why do Oracle keep allocating 24 blocks to me, even though i already shrink the space . I have also explicitly stated i want to keep 1k, so shouldnt 64k (minimum) be allocated to me instead ?


    Q2) Does truncate and deallocate unused only freed space up until the INITIAL EXTENT ?

    Your setting for INITIAL, along with the extent sizing is the clue.
    It looks like you created the table with INITIAL = 128M, which made Oracle start with 8MB extents - and the first 24 blocks are the space management (bitmap) blocks and segment header, rounded up to a multiple of 8. (I think you'll have 16 level1 Bitmap Blocks, one level 2, and the segment header plus 6 data blocks).

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points