OBJECT IS NOT USING THE DEFAULT PARAMETERS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: OBJECT IS NOT USING THE DEFAULT PARAMETERS

  1. #1
    Join Date
    Sep 2000
    Posts
    305
    HI FOLKS

    I HAVE CREATED A TABLE WITH THE STORAGE PARAMETER BUT IT IS NOT CONSIDERING THAT PARAMETERS IT IS PICKING UP THE TABLESPACES PARAMETERES

    WHY IS IT HAPPENING EVEN THE EXTENT MANAGEMENT IS ALSO DISCTIONARY.

    SHAILENDRA

  2. #2
    Join Date
    Sep 2000
    Posts
    305
    ANY SUGGESTION

    SHAILENDRA

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by shailendra
    BUT IT IS NOT CONSIDERING THAT PARAMETERS IT IS PICKING UP THE TABLESPACES PARAMETERES
    Did you confirmed quering dba_extents and dba_segments dictionaries?

    Sameer

  4. #4
    Join Date
    Sep 2000
    Posts
    305
    YES I HAVE CONFIRMED


    SHAILENDRA

  5. #5
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Can you paste your create table code with storage parameters and output from these two data dictionaries for your segment


  6. #6
    Join Date
    Sep 2000
    Posts
    305
    CREATE TABLESPACE (TABLESPACE_NAME) DATAFILE (FULL PATH) SIZE 1000M
    STORAGE (INITIAL 100K NEXT 100K)

    CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE) STORAGE(INITIAL 10K NEXT 10K)


  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    is it a locally managed tablespace?

    steve
    I'm stmontgo and I approve of this message

  8. #8
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    How about trying DEFAULT STORAGE
    clause in create tablespace Statement.

    Also Check your dba_tablespace views.

    -- Dilip

  9. #9
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Go through te following examples and check your tables for the storage parametres.

    DB_BLOCK_SIZE = 8192 for all following examples.. Version, 8.1.7

    Code:
    INITIAL:
    
    The default value is the size of **5** data blocks. In
    tablespaces with manual segmentspace management, the
     minimum value is the size of **2** data blocks plus one 
    data block for each free list group you specify.  In ablespaces 
    with automatic segmentspace management, the minimum 
    value  is 5 data blocks. The maximum value depends on your 
    operating system.
    
    
    CASE:1
    ---------------------------------
    
    CREATE TABLESPACE junk DATAFILE 'D:\ORACLE\ORADATA\ORCL\junk01.DBF' SIZE 100M ;
    
    SELECT tablespace_name, initial_extent, next_extent, 
    min_extents, max_extents,  status, extent_management, 
    allocation_type FROM dba_tablespaces WHERE tablespace_name = 'JUNK'
    
    Default 5 block are taken 
    
    TABLESPACE INITIAL NEXT   MIN_EXT MAX_EXT    STATUS  EXTENT_MAN  ALLOC_TYPE
    ----------------------------------------------------------------------------
    JUNK       40960  40960   1       505        ONLINE  DICTIONARY  USER
    
    CREATE TABLE tab_junk (id NUMBER, name VARCHAR2(100)) TABLESPACE junk;
    
    CREATE TABLE tab_junk1 (id NUMBER, name VARCHAR2(100)) STORAGE(INITIAL 10K NEXT 10K) TABLESPACE junk
    
    SELECT segment_name, tablespace_name, 
    bytes, blocks FROM dba_extents WHERE segment_name IN ('TAB_JUNK','TAB_JUNK1')
    
    SEGMENT_NAME   TABLESPACE_NAME   BYTES     BLOCKS
    -------------- ----------------------- ----------
    TAB_JUNK       JUNK              40960          5* 
    TAB_JUNK1      JUNK              16384          2** 
    
    * Default 5 blocks are allocated, no storage parameters 
    specified, tablespace storage settings 
    are used.
    
    ** Manual storage allocation, default 2 blocks are allocated.
    tablespace storage settings are not used. 
    ---------------------------------------------------------------------------------
    
    CASE:2
    
    CREATE TABLESPACE junk 
    DATAFILE 'D:\ORACLE\ORADATA\ORCL\junk01.DBF' SIZE 
    100M DEFAULT STORAGE (INITIAL 100K NEXT 100K 
    MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)
    
    SELECT tablespace_name, initial_extent, next_extent, 
    min_extents, max_extents,  status, extent_management, 
    allocation_type FROM dba_tablespaces WHERE 
    tablespace_name = 'JUNK'
    
    Specified storage parameters are used
    
    
    TABLESPACE INITIAL NEXT   MIN_EXT MAX_EXT    STATUS  EXTENT_MAN  ALLOC_TYPE
    ----------------------------------------------------------------------------
    JUNK       106496  106496 1       2147483645 ONLINE  DICTIONARY  USER
    
    CREATE TABLE tab_junk (id NUMBER, name VARCHAR2(100)) 
    TABLESPACE junk;
    
    CREATE TABLE tab_junk1 (id NUMBER, name VARCHAR2
    (100)) STORAGE(INITIAL 10K NEXT 10K) TABLESPACE junk
    
    SELECT segment_name, tablespace_name, 
    bytes, blocks FROM dba_extents WHERE segment_name IN 
    ('TAB_JUNK','TAB_JUNK1')
    
    SEGMENT_NAME   TABLESPACE_NAME   BYTES     BLOCKS
    -------------- ----------------------- ----------
    TAB_JUNK       JUNK             122880         15*
    TAB_JUNK1      JUNK              16384          2**
    
    * Default 5 blocks are allocated, no storage parameters 
    specified, tablespace storage settings 
    are used. 
    
    In dictionary-managed tablespaces, if MINIMUM EXTENT was 
    specified for the tablespace when it was created, then Oracle 
    rounds the value of INITIAL up to the specified MINIMUM 
    EXTENT size if necessary. If MINIMUM EXTENT was not 
    specified, then Oracle rounds the INITIAL extent 
    size for segments created in that tablespace up to the 
    minimum value (see preceding paragraph), or to multiples 
    of 5 blocks if the requested size is greater than 5 blocks.
    
    Therefore the size for the first table is 122880 (15 block)
    
    ** Manual storage allocation, default 2 blocks are allocated.
    tablespace storage settings are not used.
    Query your table and check the output for your tables.. Hope it comes correct

    HTH

    Sameer

    [Edited by Sameer on 08-27-2002 at 05:39 AM]

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