Qs about Storage clause parameters
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Qs about Storage clause parameters

Hybrid View

  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unhappy

    Can anyone help me on the following questions:

    Q1. Does storage clause only used in dictionary managed tablespace?

    Q2. CREATE TABLE test_storage
    ( . . . )
    STORAGE (INITIAL 100K NEXT 120K
    MINEXTENTS 2 MAXEXTENTS 5
    PCTINCREASE 50);

    In this case, what is the 1st extent size? 100k or 200k? What's the 2nd? 120k or 240k (because the MINEXTENTS is 2)?
    If the answer is "100k and 120k", what's the use of MINEXTENTS?

    Thanks very much!

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    A1.
    No, it is also used in LMT

    A2.
    First extent will be 100K, second will be 120K (if oracle won't round the values up to the 5 blocks multiplication). The usage of MINEXTENTS - you are telling oracle that you want to prealocate 2 extents at table creation time. Those two extents will be sized based on INITAL and NEXT parameters.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    Jmodic, thanks very much for your quick reply.

    However for Q1, pls see the following result running in 9i and 8i:
    ===========================================================
    In 9i, the default is LMT
    SQL> create tablespace local_storage datafile 'd:\testlocal.dbf' size 2m
    2 default storage (initial 100k next 120k minextents 2);

    Tablespace created.

    SQL> select INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, EXTENT_MANAGEMENT from dba_tablespaces
    2 where TABLESPACE_NAME='LOCAL_STORAGE';

    INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS EXTENT_MAN
    -------------- ----------- ----------- ----------
    65536                   &nbsp 1 LOCAL

    Though the storage can be used, but it doesn't really affect the space allocating.


    In 8i, the default is DMT
    SQL> create tablespace local_storage datafile '$ORACLE_BASE/oradata/local_storage.dbf'
    2 size 2m extent management local
    3 default storage (initial 120k);
    create tablespace local_storage datafile '$ORACLE_BASE/oradata/local_storage.dbf'
    *
    ERROR at line 1:
    ORA-25143: default storage clause is not compatible with allocation policy

    error return
    ===========================================================

    For Q2:
    You said "Oracle will prealocate 2 extents at table creation time", do you mean oracle will preallocate "100k+120k" space, because the first 2 extents is "100k and 120k". Am I right?

    Thanks!

    [Edited by ly on 10-10-2002 at 05:01 AM]

  4. #4
    Join Date
    Oct 2002
    Posts
    8

    storage clause discrepancy

    Hello

    This is to bring to the notice of all who are using Sybex for 031 test preparation.

    On page 195, Sybex mentions


    Oracle ILT recommends locally managed extents for Temporary tablespace, while Sybex advises against it.

    I do not know if Sybex has published any errata on this points.

    Request you all to clarify these discrepancies, as I am planning to take this test soon.

    Thanks

  5. #5
    Join Date
    Oct 2002
    Posts
    8

    amendment to previous mail

    On page 195, Sybex mentions should read as follows:

    " You cannot specify DEFAULT STORAGE, TEMPORARY, and MINIMUM EXTENT clauses of the CREATE TABLESPACE in a LMT "

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    That would be consistent with Oracle's documentation on create tablespace.

    http://tahiti.oracle.com/pls/db92/db...?section=99122

    If you do not specify the extent_management_clause, then Oracle interprets the COMPATIBLE setting, the MINIMUM EXTENT clause and the DEFAULT storage_clause to determine extent management. If the COMPATIBLE initialization parameter is less than 9.0.0, then Oracle creates a dictionary managed tablespace. If COMPATIBLE = 9.0.0 or higher:

    If you do not specify the DEFAULT storage_clause at all, then Oracle creates a locally managed autoallocated tablespace.
    If you did specify the DEFAULT storage_clause:
    If you specified the MINIMUM EXTENT clause, then Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
    If you did not specify MINIMUM EXTENT clause, then Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

    Restrictions on extent management:

    A permanent locally managed tablespace can contain only permanent objects. If you need a locally managed tablespace to store temporary objects (for example, if you will assign it as a user's temporary tablespace, use the CREATE TEMPORARY TABLESPACE statement.

    If you specify LOCAL, then you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.

  7. #7
    Join Date
    Oct 2002
    Posts
    8
    Hello Stecal


    " If you specify LOCAL, then you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY. "

    I apologize for harping on above statement but can I safely assume for examination purpoe that above statement applies to both Temporary and Permanenet tablespaces.

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