goofy LMT extent allocation

[newbie5]

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?

[gandolf989]

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.

[jmodic]

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.

[newbie5]

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.