Re: Importing into locally managed tablespaces

Jonathan Lewis wrote:
>
> 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! :-)

Similarly its relatively easy to run 'exp rows=n' and then run 'strings' on the dump file to get the DDL. A little sed and awk can remove the storage clauses, change tablespaces, add NOLOGGING etc etc without too much drama. The only problems I've typically had are on massively partitioned tables - because you got a single line of ddl which is too long for sed/awk/et al to handle.

Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"