1 2 Previous Next 21 Replies Latest reply: May 22, 2011 5:04 AM by Hemant K Chitale RSS

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

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