Re: tips on moving oracle to new machine

Howard J. Rogers wrote in message <39c9168b_at_news.iprimus.com.au>...
>"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.

There are disadvantages to both Brad's and Howard's suggestions.

In Brad's, by creating the indexes before the import, you'll make the import very slow as every row insertion will cause the index to be updated.

In Howard's, you're doing unnecessary work: you're first reloading the data into its original structure, then moving it all around, which can take a very long time with big databases. It also pre-supposes that you've got enough room in your target database for both the old tablespace structure and the new one.

The usual way achieving what you want is to first run imp as follows:

    imp full=y rows=n indexfile=somefile.sql

(check the syntax, I'm working from memory). This creates an SQL script, somefile.sql, containing all the SQL to create the tables and indexes in your database. It doesn't actually import anything.

Next edit somefile.sql to comment out all the index statements and comment in all the create table statements and edit the tablespace clauses for them.

Run this SQL using SQL*Plus. This creates all your tables in the correct place. Next run

     imp full=y indexes=no ignore=y ....

to import the data into the pre-created tables, roles, triggers,grants etc. but don't create the indexes.

Next edit somefile.sql again to comment in all the index statements and comment out all the create table statements and edit the tablespace clauses for the indexes.

Run this script under SQL*Plus to create the indexes in the correct place.

This works for any version of Oracle and is quicker than "move tablespace" commands or duplicating tables because you only load the data once, rather than load it then move it.

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.