Re: Export/Import

From: Fraser McCallum <fmcc_at_NOSPAModbaguru.com>
Date: Wed, 25 Jul 2001 08:02:21 +0100
Message-ID: <I7u77.328$c13.75@NewsReader>

Zeyad

If you were doing this to re-arrange your tables across multiple tablespaces, for the purposes of load balancing or using different extent sizes with locally managed tablespaces, you can use the index creation script.

Do an import with indexes =Y, indexfile=filename.sql and rows=N in your import parameter file and the end of this import the filename.sql file will include all the tables that are going to be created.

As this file is intended by Oracle for index creation the table creates are commented out. This requires a little work with an editor to change, remove the index creation statements first and then un-comment the table creation statements. Then by changing the tablespace in the table create statements you will be able to pre-create them split them between multiple tablespaces.

Now run another import without the indexfile parameter set, rows=Y and ignore=Y set and all will be done.

NOTE: You can also extract the table pre-creation statements using a spooled PL/SQL command selecting from the dba_tables and dba_tab_columns. I use the one for the O'Reilly scripts book.

Regards

Fraser McCallum
MVP Oracle
www.BrainBench.com

"Zeyad S" <sweidanz_at_yahoo.com> wrote in message news:3B5E0711.D848A6FE_at_yahoo.com...
> Thanks Sybrand, that makes it clear.
>
> Thanks all,
> ZS
>
> Sybrand Bakker wrote:
>
> > "Yaroslav Perventsev" <p_yaroslav_at_cnt.ru> wrote in message
> > news:9jjit2$l18$1_at_news247.cnt.ru...
> > > Hello!
> > > See original question!
> > >
> > > Yaroslav.
> > >
> > > andrew_webby at hotmail <spam_at_no.thanks.com> ÓÏÏÂÝÉÌ × ÎÏ×ÏÓÔÑÈ
> > > ÓÌÅÄÕÀÝÅÅ:995968737.8742.0.nnrp-07.c30bdde2_at_news.demon.co.uk...
> > > > But now the table is owned by a different schema.
> > > >
> > > > As Erwin said, all he has to do is create the table layout with the
 new
 TS
> > > > first, then run the import.
> > > >
> > > > "Yaroslav Perventsev" <p_yaroslav_at_cnt.ru> wrote in message
> > > > news:9jjdja$k45$1_at_news247.cnt.ru...
> > > > > Hello!
> > > > >
> > > > > Erwin Dondorp <erwindon_at_wxs.nl> ñîîáùèë â íîâîñòÿõ
> > > > > ñëåäóþùåå:3B5D33F5.C32D5D35_at_wxs.nl...
> > > > > > Yaroslav Perventsev wrote:

 <snip>


> > > > > imp user2/test_at_db2 file=user1.dmp fromuser=user1 touser=user2

> > > > >
> > > > > Import successful imported test table into tablespace ts2 of
 user2;
> > > > >
> > > > > What wrong?!
> > > > >
> > > > > Best regards!
> > > > > Yaroslav.
> > > >
> > > >
> > > >
> > >
> > >
> >
> > Import imports in
> > a) the tablespace of the create tablespace
> > b) the default tablespace if
> > - the orginal tablespace doesn't exist
> > ---------> The user has no quota on the tablespace
> > So to move a table by exp/imp
> > make sure : the target tablespace is the default tablespace of the owner
 of
> > the tables.
> > This account doesn't have unlimited tablespace privilege,
> > and doesn't have quota on the original tablespace.
> >
> > Sorry to say so, but all answers were incomplete.
> >
> > Regards,
> >
> > Sybrand Bakker, Senior Oracle DBA
>
>
Received on Wed Jul 25 2001 - 02:02:21 CDT