Re: Importing into locally managed tablespaces

How about -

Import the structures only, until imp breaks, then run a script to read all the tablenames and index names and do;

    alter table/index xxx deallocate unused'

then import the structures again
and repeat until all structures are loaded.

The script will probably deallocate back to just one extent per object.

(Coincidences abound - I wrote up the answer to your original question (without having seen the question) on a train-ride this evening to post onto my website later on).

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Vikas Agnihotri wrote in message
<77e87b58.0105220707.7b177319_at_posting.google.com>...


>"Howard J. Rogers" <howardjr_at_www.com> wrote in message

 news:<3b09e30b_at_news.iprimus.com.au>...


>> No.  I don't have much experience with the autoallocate policy, for sure

>> (the general advice is that if you're going to use LMTs, stick with the

>> uniform extent method).  But LMTs have always paid attention to the

 Storage


>> Clause -it's what they decide to do with it that gets interesting.

>>

>> Short story: LMT with 1M uniform extents.  Create table with initial of

>> 3.2M, next 26K.  What do you get?  To start with, 4 extents of 1 meg each

>> (that's your Initial dealt with, with a bit of rounding up just to keep

 you


>> happy).  When the table extends, you'll get a 5th extent of 1 meg (that's

>> the 26K being "ignored" -actually, it's not ignored as such, it's just

>> suffering from a major dose of rounding up).

>

>I see. Well, then let me turn this around a little bit.

>

>My destination database is not as "muscled" as my production database.

>i.e my LMT are about 20-30% of my production databases.

>

>Regardless of 'exp compress=y or n', there is no way that my

>production tables are going to fit in the new tablespaces.

>

>I thought that by creating LMT with autoallocate, Oracle would

>basically ignore the STORAGE clause when creating the tables/indexes

>and autoallocate them when neccesary (when loading data into it).

>Which would be fine, because I dont intend to load production data

>into it. Just some small test data.

>

>Since this is not working out as expected, is there a way to have

>'exp' NOT write the STORAGE clause in its CREATE TABLE/INDEX

>statements?

>

>This way, the objects will get created with just the TABLESPACE <ts>

>and the LMT policy will kick in.

>

>Thanks. (My guess is the answer is NO. Hence my other post about

>parsing 'imp show=y' output! :-)