Re: tips on moving oracle to new machine

But what about triggers and procedures, Dave? That was my point. I love the indexfile script, but it doesn't do the entire job. That's why, whoever's advice he follows, some extra work is going to be involved...

Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------



"Dave Wotton" <Dave.Wotton_at_dwotton.nospam.clara.co.uk> wrote in message
news:zEhy5.1271$qT6.58156_at_nnrp4.clara.net...


> 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.

>
>
>
>
>
>
>