Re: How to precreate tables for migration?

As I impose order on our servers, (moving from project-managed to dba-managed db's) I perform this sort of task for individual schemas. The short form is the same as yours except:

3.5) create schemas where tables or tablespace assignments will change. 4.5) create tables that are to have storage parameters different from the source.

... and ...

5) import using IGNORE=Y parameter

Imp fills the tables as they exist, or creates them using the info in the export file where they do not exist. After all, if you're migrating all the tables you pre-created should be "clean" at the beginning, so you won't get duplicate rows and DESTROY is hardly necessary. One caveat -- IIRC you don't need to create all the tables, but you must create all the tablespaces in order to use this method. Or this could be a local effect as my new servers have different data file paths.

It is useful to compose the creation in scripts, and compose a script to destroy the structures, in case your new storage parameters are flawed somehow and the import ends badly. That way you can iterate more easily. In my shop we move off the old server and into a test server first, then when the iterations are done I can replicate the refined procedure for production. In a few cases the project managers failed to move the db to production when they went live, so we move to different schemas on the same server first, then drop one and move the data (and client connections) to production.

"Jesse, Rich" wrote:

> So,
>
> We're taking the leap from 8.0.6 to 8.1.7. For various reasons, we will be
> using the export/import method of migration.
>
> Since I'll have the DB all to myself for the weekend, I want to also
> partition most of our larger tables (1M-7M rows each -- it's not a huge DB)
> in the migration. My plan to precreate the tables was to:
>
> 1) Export DB.
> 2) Create new 8.1.7 DB.
> 3) Create tablespaces.
> 4) Create partitioned tables.
> 5) Import DB.
>
> (I've left out many migration steps not pertinent to this question)
>
> Obviously, step 4 can't go before step 5, as step 5 creates the schemas.
>
> So, my questions: Has anyone precreated tables before a full import on a
> clean DB? Did you just manually precreate the schemas? How does the import
> deal with this (e.g. the DESTROY arg for imp)? Or would it be OK to skip
> step 4 and delete/rebuild the tables to be partitioned after the import had
> rebuilt them? I would prefer not to do the latter, seeing as this will be
> the first time in three years that the TSs will be nice and contiguous. :)
>
> Also, for brevity, we won't be going LMT for most of our TSs. Need too much
> time to plan for that... :(
>
> TIA!
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Thu Jul 05 2001 - 13:05:06 CDT