DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: initial extent size

  1. #1
    Join Date
    Dec 2002
    Posts
    18

    initial extent size

    Hi,

    I have the following tablespace:

    SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT, MIN_EXTENTS,
    EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT
    FROM USER_TABLESPACES;

    TABLESPACE BLOCK_SIZE INITIAL_EXTENT MIN_EXTENTS EXTENT_MAN
    ---------- ---------- -------------- ----------- ----------
    EXAMPLE 8192 65536 1 LOCAL


    When I query my tables, I have the following:

    SELECT TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT, MIN_EXTENTS
    FROM USER_TABLES;

    TABLE_NAME TABLESPACE INITIAL_EXTENT MIN_EXTENTS
    ------------------------------ ---------- -------------- -----------
    TABLE_E EXAMPLE 16384 1


    If you look at the initial extent of TABLE_E, it shows 16384. I am wondering how oracle derived this. I've read it somewhere that in a Locally-Managed Tablespace, the minimum extent is 3 data blocks. In my example above, the data block is 8K, so I am assuming that any table created in the tablespace will have at least 24K. But this does not seem to be the case as showed by TABLE_E which has 16K.

    Any Ideas?

    Thanks
    toshi

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Well is this Happening with all tables or just this one table...

    This is possible if you have explicitly mention the storage parameter in the create table command like.

    Code:
    SQL> SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT, MIN_EXTENTS,
      2  EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT
      3  FROM USER_TABLESPACES WHERE TABLESPACE_NAME LIKE 'EXAMPLE';
    
    
    TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT MIN_EXTENTS EXTENT_MANAGEMENT              SEGMENT_SPACE_MANA
    --------------- ---------- -------------- ----------- ------------------------------ ------------------
    EXAMPLE               8192          65536           1 LOCAL                          AUTO
    
    SQL> CREATE TABLE TEST (x number)
      2  STORAGE (INITIAL 16k NEXT 16k MINEXTENTS 1);
    
    Table created.
    
    SQL> SELECT TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT, MIN_EXTENTS
      2  FROM USER_TABLES where table_name like 'TEST';
    
    TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT MIN_EXTENTS
    ---------- --------------- -------------- -----------
    TEST      EXAMPLE                  16384           1
    Here you see i have 8192 BLOCK_SIZE and i have create a table the way you have is TEST table having 16384 as initial extent...

    Just check how you created the table...

    Default will be like

    Code:
    SQL> create table TEST1 (x number)
    
    Table created.
    
    SQL> SELECT TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT, MIN_EXTENTS
      2  FROM USER_TABLES where table_name like 'TEST1';
    
    TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT MIN_EXTENTS
    ---------- --------------- -------------- -----------
    TEST1      EXAMPLE                  65536          1

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Jan 2003
    Location
    Thailand
    Posts
    29
    When you created table, the first extent is (INITIAL * MINEXTENT) that you specific at SEGMENT or TABLESPACE level.
    Regards,
    P.Peach

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: initial extent size

    Originally posted by toshi
    If you look at the initial extent of TABLE_E, it shows 16384.
    Yes, it shows 16K, because this initial size was requested in your CRATE TABLE command. However the actual size of the initial (and all subsequent) extents created for this table in your LMT tablespace is 64K, not 16K. You can check this by querying USER_EXTENTS, eg

    SELECT segment_name, tablespace_name, extent_id, bytes
    FROM user_extents
    WHERE segment_name = 'TABLE_E' AND segment_type = 'TABLE;

    Read the following thread for more details:
    http://www.dbasupport.com/forums/sho...threadid=33703
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2002
    Posts
    18
    Thank you all for your ideas!

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203

    Re: initial extent size

    Originally posted by toshi
    Hi,

    I've read it somewhere that in a Locally-Managed Tablespace, the minimum extent is 3 data blocks.
    Thanks
    toshi
    I know on a LMT UNIFORM ts, the smallest UNIFORM extent you can have is 5 * Blocksize.
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore
    Posts
    52
    Hi..


    In LMT,If you dont specify the initial extent size oracle automatically allocates it which is equal to db_block_size * db_file_multiblock_read_count.In your case its 8K * 8K ie 64K.

    Since your tablespace is locally managed,the tables created on it will of extent size 64K.

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