DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2001
    I have a doubt in extents allocation.

    Assume a tablespace called 'PAYROLL' having following defaults.

    --------------- ----------- ----------- ------------
    PAYROLL 1 505 50

    and I have created on table called TEST by logging in as a user whose default tablespace is this payroll tablespace.

    create table test ( i integer)
    storage (initial 1k next 1k minextents 1 maxextents 2)

    Now if I run the following query

    SQL> select segment_name,sum(blocks),bytes from dba_extents group by segment_name,bytes
    having segment_name = 'TEST'

    -------------------- ----------- ---------
    TEST 2 16384

    And the DB_BLOCK_SIZE = 8192 (8k)

    I assumed that there should be the value 1 in the sum(blocks) and 8192 in the bytes column because
    the block size is 8k and so there should be only one block.

    But it is different and it is taking 2 blocks with 16384.

    Can anyone clear this...?

  2. #2
    Join Date
    Oct 2000
    Halifax, Nova Scotia
    You have raised an interesting issue

    I have followed your example exactly.
    Same block size, same table creation command and here are my results

    1 select segment_name,sum(blocks),(bytes/1024) from dba_extents group by segment_name,bytes
    2* having segment_name = 'TEST'
    3 /

    ---------- ----------- ------------
    TEST 16 128

    I am confused, can anyone shed light on this
    Don't be afraid to try something new. Amateurs built the Ark, professionals built the Titanic

  3. #3
    Join Date
    Jun 2000
    Madrid, Spain
    that is expected behaviour

    quoted from documentation:

    Keywords and Parameters
    Specify in bytes the size of the object's first extent. Oracle allocates space for this extent when you create the schema object. Use K or M to specify this size in kilobytes or megabytes.

    The default value is the size of 5 data blocks. The minimum value is the size of 2 data blocks for nonbitmapped segments or 3 data blocks for bitmapped segments, plus one data block for each free list group you specify. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size for values less than 5 data blocks, and rounds up to the next multiple of 5 data blocks for values greater than 5 data blocks.

  4. #4
    Join Date
    Apr 2001


    That's Excellent...! Any more explanation...? Have any body else experienced this?

  5. #5
    Join Date
    Jun 2001
    Helsinki. Finland
    Why do you expect only 1 block upon creation? Where do you think that the Master Free List will be placed? Oracle needs a separate block for it (exactly after the segment header, if you are curious where it is placed).

    If you run OPS, the situation is a bit different if you use BLOCKING FACTOR in the init.ora parameter GC_FILES_TO_LOCKS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.