In 9i ,table init_extent is 1.2G ,is it acceptable ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: In 9i ,table init_extent is 1.2G ,is it acceptable ?

  1. #1
    Join Date
    May 2002
    Posts
    34
    Hi: I use create_table.sql script to create table in 9i ,I found when I designate initial is 20M minextent 60M ,9i amalgamate it in one extent 1.2G, Do it acceptable ?Thanks!!!

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    Hi,

    I'm not sure to really understand you question. But I will try to give some infos


    If the problem is the size of 1.2G:

    You don't set minextent to 60 MB but to 60 which means when you create your table it will directly generate 60 extents. 20MB*60=1200MB is about 1.2GB.
    If you want to have 60MB directly reserved for your table when you create it you should set miniextent to 3 (assuming that pctincrease=0 and next=20M).


    If the problem is that it seems to generate one extent:

    If you have only one datafile for the tablespace used for this table it doesn't change much if it generates 1 extent of 1.2Gb or 60 of 20Mb. But if you have several datafiles it would be surprising that Oracle creates only one extent and not one or several on each file.
    The way Oracle creates the extents my also depend on the type of extent management your tablespace is using.


    Hope that helps
    Mike

  3. #3
    Join Date
    May 2002
    Posts
    34

    extent cross file

    Hi: I create a tbs with two files both size is 40M and uniform extent is 10M, I create a table with minextents 6 on it,and the result is
    SQL> select initial_extent,min_extents from user_tables where table_name='TEST1234';

    INITIAL_EXTENT MIN_EXTENTS
    -------------- -----------
    62914560 1
    so the extent is cross two files!

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    I tried it out and got a similar result. But when I execute a:
    select extend_id, file_id, blocks from dba_extents where segment_name='MY_TABLE_NAME' and owner='MYSELF'
    I got again 6 extents.

    maybe it's something like a small bug. If you have a look a the sql of the views dba_extents and dba_tables you may find out why you got different results.

  5. #5
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    I imagine you created a table in Tablespace with EXTENT MANAGEMENT LOCAL AUTOALLOCATE.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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