initial extent size of tablespaces
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: initial extent size of tablespaces

  1. #1
    Join Date
    Apr 2003
    Posts
    46

    initial extent size of tablespaces

    hi
    while creating tablespaces we can specify the default storage parameters.
    first tablespace app_data-
    when i give the initial size as 256k, next as 256k and pctincrease as 0. logically it means that the third extent would have 256k as its size.
    second tablespace app_data1
    if i change the pctincrease to 50% then why does it change the initial extent size as per the output that i received.
    ------------------------------------------------
    the first query is:
    create tablespace app_data
    datafile 'e:\oracle\oradata\db1\app_data.dbf' size 5m
    default storage( initial 20k next 20k minextents 2 pctincrease 0
    maxextents 20)
    blocksize 4k minimum extent 20k logging online permanent
    the output is:
    TABLESPACE_NAME | BLOCK_SIZE | INITIAL_EXTENT | NEXT_EXTENT| MIN_EXTENTS | MAX_EXTENTS| PCT_INCREASE | MIN_EXTLEN |STATUS CONTENTS | LOGGING | EXTENT_MAN | ALLOCATIO | PLU SEGMEN
    ------ ---------- --------- --- -------------------------------------
    APP_DATA | 4096 | 20480 | 20480 |
    1 | 2147483645| 0 | 20480 |ONLINE PERMANENT | LOGGING | LOCAL | UNIFORM | NO MANUAL
    --------------------------------------------------------------------
    the second query is:
    create tablespace app_data1
    datafile 'e:\oracle\oradata\db1\app_data1.dbf' size 5m
    default storage( initial 20k next 20k minextents 2 pctincrease 50
    maxextents 20)
    blocksize 4k minimum extent 20k logging online permanent
    the output is:
    TABLESPACE_NAME | BLOCK_SIZE | INITIAL_EXTENT | NEXT_EXTENT| MIN_EXTENTS | MAX_EXTENTS| PCT_INCREASE | MIN_EXTLEN |STATUS CONTENTS | LOGGING | EXTENT_MAN | ALLOCATIO | PLU SEGMEN
    ------ ---------- --------- --- -----------------------------------
    APP_DATA1 | 4096 | 65536 | |
    1 | 2147483645| | 65536 |ONLINE PERMANENT | LOGGING | LOCAL | SYSTEM | NO MANUAL
    -------------------------------------------------------------------

    why does the allocation change to system in the second case.
    thanks
    Last edited by ser; 05-29-2003 at 03:01 AM.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Extent Management is LOCAL. And Allocation Type is SYSTEM

    Initial, Next have no significance in LMT.


    Read the manuals.
    Last edited by abhaysk; 05-29-2003 at 03:42 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    The reason you are seeing this is is because in the first example you extent sizes are uniform, yet in your second example this would not be true hence the allocation type is defined as system which has a default extent size of 64k

    Take a look at this document for clarity

    http://support.oracle.co.uk/metalink...T&p_id=93771.1

    HTH
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Thats because the tablespace APP_DATA1 is not a uniform extent tablespace, its an auto allocate or SYSTEM Managed LMT. So no matter what for a SYSTEM or Autoallocate LMT the first few extents will be 64k in size.

    Look at the ALLOCATION_TYPE column value of dba_tablespaces. You will see that its not uniform allocation but SYSTEM allocated.

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

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Apr 2003
    Posts
    46
    by default the extent management is local in oracle9i.
    but in case when i give default storage and no extent management clause as i gave while creating the tablespace what does it use( local or dictionary)

    if i specify default storage clause and extent management local it gives me an error that default storage specifications cannot be specified for locally managed extents.

    jovery i do not have access to metalink , hence i couldn't access it. moreover both the examples used the same size hence it is not clear as to why the server selected system for the second example and uniform for the first.

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by ser
    by default the extent management is local in oracle9i.
    but in case when i give default storage and no extent management clause as i gave while creating the tablespace what does it use( local or dictionary)
    LOCAL

    if i specify default storage clause and extent management local it gives me an error that default storage specifications cannot be specified for locally managed extents.
    Expected normal behavior.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by ser
    moreover both the examples used the same size hence it is not clear as to why the server selected system for the second example and uniform for the first.
    PCTINCREASE 0 ( in 1st case ).



    RTM in Technet.ORACLE.com

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by ser
    by default the extent management is local in oracle9i.
    but in case when i give default storage and no extent management clause as i gave while creating the tablespace what does it use( local or dictionary)
    Remember that when the system tablespace is Locally managed tablespace then all tablespaces created will be locally managed. You are confusing yourself with the Allocation Type. The question should be whether it would be an uniform allocated or system(auto) allocated tablespace. If you do not mention uniform clause while creating the tablespace the tablespace will be System allocated ie the extent management is autoallocate.

    In Auto allocate the first few extents size will be 64K then 1M, then 8M and then 64M and so on. But in case of uniform allocation the tablespace will have uniform extents only ie extents of same size.

    Originally posted by ser

    if i specify default storage clause and extent management local it gives me an error that default storage specifications cannot be specified for locally managed extents.
    You don't need to give storage clause, that does not work in Locally Managed Tablespaces, its either uniform extent or system(autoallocate).

    Code:
    sys@ACME.WORLD> create tablespace uniform_tbs datafile 'E:\ORACLE\ORADATA\ACME\uniform_tbs.dbf' size 10M
      2  extent management local uniform size 2M;
    
    Tablespace created.
    
    sys@ACME.WORLD> exec print_table('select * from dba_tablespaces where tablespace_name=''UNIFORM_TBS''');
    TABLESPACE_NAME               : UNIFORM_TBS
    BLOCK_SIZE                    : 8192
    INITIAL_EXTENT                : 2097152
    NEXT_EXTENT                   : 2097152
    MIN_EXTENTS                   : 1
    MAX_EXTENTS                   : 2147483645
    PCT_INCREASE                  : 0
    MIN_EXTLEN                    : 2097152
    STATUS                        : ONLINE
    CONTENTS                      : PERMANENT
    LOGGING                       : LOGGING
    FORCE_LOGGING                 : NO
    EXTENT_MANAGEMENT             : LOCAL
    ALLOCATION_TYPE               : UNIFORM
    PLUGGED_IN                    : NO
    SEGMENT_SPACE_MANAGEMENT      : MANUAL
    DEF_TAB_COMPRESSION           : DISABLED
    -----------------
    
    PL/SQL procedure successfully completed.
    See here i have mentioned uniform extent of 2M so all extent allocation will always be 2M for uniform_tbs tablespace.

    Code:
    sys@ACME.WORLD> create tablespace system_tbs datafile 'E:\ORACLE\ORADATA\ACME\system_tbs.dbf' size 10M
      2  extent management local;
    
    Tablespace created.
    
    sys@ACME.WORLD> exec print_table('select * from dba_tablespaces where tablespace_name=''SYSTEM_TBS''');
    TABLESPACE_NAME               : SYSTEM_TBS
    BLOCK_SIZE                    : 8192
    INITIAL_EXTENT                : 65536
    NEXT_EXTENT                   :
    MIN_EXTENTS                   : 1
    MAX_EXTENTS                   : 2147483645
    PCT_INCREASE                  :
    MIN_EXTLEN                    : 65536
    STATUS                        : ONLINE
    CONTENTS                      : PERMANENT
    LOGGING                       : LOGGING
    FORCE_LOGGING                 : NO
    EXTENT_MANAGEMENT             : LOCAL
    ALLOCATION_TYPE               : SYSTEM
    PLUGGED_IN                    : NO
    SEGMENT_SPACE_MANAGEMENT      : MANUAL
    DEF_TAB_COMPRESSION           : DISABLED
    -----------------
    
    PL/SQL procedure successfully completed.
    See here i have not mentioned uniform extent clause so the extent allocation will always be autoallocate for system_tbs tablespace.


    Originally posted by ser

    jovery i do not have access to metalink , hence i couldn't access it. moreover both the examples used the same size hence it is not clear as to why the server selected system for the second example and uniform for the first.
    The server selected uniform for the first because the INITIAL=NEXT and PCTINCREASE=0 is what a Uniform extent will have ie all extents are uniform. But in the second case you have INITAIL=NEXT and PCTINCREASE=50, here because of the clause PCTINCREASE=50 the extents allocations cannot be uniform. So the only other option left is Autoallocate and server makes it an System(Autoallocate) managed LMT.

    Do not use storage clauses as it doesnot work with LMT. It has to be either UNIFORM LMT or SYSTEM MANAGED LMT.

    And do not confuse yourself between Dictionary managed and SYSTEM Managed they are two different things.

    HTH
    Last edited by adewri; 05-30-2003 at 03:12 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Amar,
    Your print_table procedure is cool. Wondering what happens if more that one row selected ??
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by SANJAY_G
    Amar,
    Your print_table procedure is cool. Wondering what happens if more that one row selected ??
    It will come below one another

    try it

    Code:
    CREATE OR REPLACE procedure print_table( p_query in varchar2 )
    AUTHID CURRENT_USER
    is
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(4000);
        l_status        integer;
        l_descTbl       dbms_sql.desc_tab;
        l_colCnt        number;
    begin
        dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
        dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);
    
        for i in 1 .. l_colCnt loop
            dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
        end loop;
    
        l_status := dbms_sql.execute(l_theCursor);
    
        while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
            for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )
                                      || ': ' ||
                                      l_columnValue );
            end loop;
            dbms_output.put_line( '-----------------' );
        end loop;
    exception
        when others then
            dbms_sql.close_cursor( l_theCursor );
            RAISE;
    end;
    /
    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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