Re: tips on moving oracle to new machine

"Brad Whitlock" <brad.whitlock_at_honeywell.NOSPAM.com> wrote in message news:8qarm10plk_at_enews1.newsguy.com...
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:39c86b17_at_news.iprimus.com.au...
> > You might want to consider the INDEXFILE option of the import command:
 it
> > create a text file which contains the SQL statements necessary to
 re-create
> > indexes and tables. The create table statements are all remm'ed out,
 but
> > it's a quick fix to edit the file and remove the rems. You then have a
> > script that will reconstruct all tables and indexes (assuming you
 specified
> > full=y). It can be edited to make the 'tablespace' bits refer to any
> > tablespace you fancy.
>
> This is the way to go, but go further: Get this script, uncomment the
 table
> creation statements, and edit all the tablespace clauses on both table and
> index statements to what you want. Create all the tablespaces and run
 this
> script to create all the objects. Then do the import and tell it to
 ignore
> object creation errors. It'll put each table's data in the tablespace
 where
> you created it earlier.
>
> -- Brad Whitlock
>
>

The only caveat, Brad, to full-on recommendation of this as the solution to all our problems is that the script will ONLY create the tables and indexes mentioned in the scripts. All those triggers and procedures etc etc etc will not be involved. So there'll have to be a mixture of running this script and doing a full import with an 'ignore=y' to get all the other things into the new database.

Myself, I think I'd hack all the tablenames out of the indexfile, and create a new script that has a bunch of 'move tablespace' commands for each listed table.

I'd then do an import and allow the tables to go wherever import wants to put them, but make sure that 'indexes=N'. Then run the 'move tablespace' script created earlier. Then run the original indexfile to get all my indexes back.

Clearly, there's more than one way to skin an omelette. Or to mix metaphors!

Regards
HJR Received on Thu Sep 21 2000 - 00:00:00 CDT