goofy LMT extent allocation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: goofy LMT extent allocation

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    goofy LMT extent allocation

    create tablespace doe_support
    datafile 'D:\ora92\oradata\dcc1\doe_support.dat' size 100M
    autoextend on
    extent management local
    uniform size 500K
    segment space management auto;

    and I imported objects initially exported from this tablespace and I see this:

    OBJECT_NAME.....OBJECT_TYPE.....INITIAL_EXT...NEXT_EXT...

    UTILFUNC...........TABLE..........327,680......524,288...
    SECBRIEF...........TABLE.........4,259,840.....524,288...

    Why is the initial extents are 300K/4MB rather than the uniform size 500K?

    This is a newly created table space not a conversion from DMT?

    Can the create statement in import OVERRIDE LMT extent allocation?
    Last edited by newbie5; 02-07-2003 at 02:42 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    You might want to consider creating the tables ahead of time with dynamic sql and importing into them with ignore=y. This will ensure that the LMT extent sizes are used. If you import data from a database using DMT's than Oracle will allocate enough uniform extents to make it original initial extent. Still this doesn't explain a 300K extent size. But I would create the tables ahead of time with no storage clause and import into them.

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

    Re: goofy LMT extent allocation

    Originally posted by newbie5
    Why is the initial extents are 300K/4MB rather than the uniform size 500K?
    Well, the actual size of extents for those tables in the database are not 300K/4M, they are just what they were supposed to be - 500K. What you are looking is the report from DBA/USER_SEGMENTS view. The column INITIAL_EXTENT in that view does not represent the actual size of the extents created, they realy represent the size of the extents as it is requested in their creation DDL statements. Now in case of LMT the size of the extent you requested in your CREATE statement is something totaly different from what actually gets allocated from the tablespace. If you realy want to see the actual size of the extents, you better query DBA/USER_EXTENTS, not DBA/USER_SEGMENTS.

    In your case the tables that were exported were created with 300K/4M initial extents and in the dump file this is recorded in their CREATE DDLs. When you import those tables into a LMT, they were created with 500K extents, although the size of the original requested size of the initial extents during the creation is recorded in DBA/USER_SEGMENTS view.
    Last edited by jmodic; 02-07-2003 at 05:49 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    Thanks Jurij.

    I quried dba_extents and I got the following results:

    SQL> select segment_name, extent_id, bytes from dba_extents
    2 where segment_name = 'UTILFUNC'
    3 and owner='DOEOWNER';

    UTILFUNC 0 524288

    SQL> ed
    Wrote file afiedt.buf

    1 select segment_name, extent_id, bytes from dba_extents
    2 where segment_name = 'SECBRIEF'
    3* and owner='DOEOWNER'
    SQL> /

    SECBRIEF 0 524288

    SECBRIEF 1 524288

    SECBRIEF 2 524288

    SECBRIEF 3 524288

    SECBRIEF 4 524288

    SECBRIEF 5 524288

    SECBRIEF 6 524288

    SECBRIEF 7 524288

    SECBRIEF 8 524288


    9 rows selected.

    I was really pissed that after all the effort of dropping and recreating the tablespace I still have a table with 4MB initial extent.

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