Re: Importing into a LMT?

Tim Kearsley <tim.kearsley_at_milton-keynes.gov.uk> schreef in berichtnieuws 725736ef.0304020716.3adff8f2_at_posting.google.com...
| Hi all,
|
| Environment:
|
| Oracle 8.1.7 on AIX 4.3.3 on RS6000 SP node
|
| I've created a new, test database using LMTs exclusively. In all
| cases I've used a uniform extent size.
|
| I'm importing data into this new database from a database which uses
| DMTs. Additionally, the export used the COMPRESS=Y option to compress
| the extents.
|
| What will be the effect when I import into the new database? I
| assumed that the storage clause, at least in terms of INITIAL_EXTENT
| and NEXT_EXTENT, would be ignored, and the segment would be allocated
| however many extents would be required of the UNIFORM size.
|
| However, when I query DBA_SEGMENTS for the imported segments, I find
| the INITIAL extent is certainly NOT the UNIFORM size, whereas the
| NEXT_EXTENT always is.
|
| Am I interpreting this result correctly, and is this, in your opinion,
| sensible behaviour for this scenario?
|
| Many thanks,
|
| Tim Kearsley
| Database Manager
| Milton Keynes Council

Query DBA_EXTENTS and you will see that the really allocated extents for a segment all have the uniform extent size of the tablespace. But the initial extent size is still used in my experience. If I recall it well as many extents as needed are allocated to meet the initial extent. Because of this I would really advice to make an export with COMPRESS=N. You can waste a lot of space if a table had many rows once and the space is not deallocated. Maybe "alter table ... deallocate unused" frees up space but the tablespace had to be large enough to import it first. I'am sure that in an autoallocated LMT the mechanism works very intelligent. Suppose you create a table with initial 50M, then Oracle does not create 16K extents and than switches to 64K extents but creates 1M extents from the beginning. So there is a purpose in specifying initial extent size, it is not ignored totally. Received on Wed Apr 02 2003 - 10:05:46 CST