Re: easiest way to copy one DB to another?

barneytoe wrote:

> This won't work as the target DB is on unix and destination is XP.
>
> I'm assuming I should do something like this then:
>
> 1) import schema, not in FULL mode
> 2) import data only, not the indexes, triggers, grants, and constraints
> 3) import the indexes
> 4) all other objects such as views, constraints, triggers etc
>
> chris
>

Here is the cleanest way I have found to get all the data over as fast as possible (using logical, not physical backups), then process the indexes, constraints, grants, statistics, and PL/SQL compilation in the background (or change any of them to your liking).

Perform the follwing four steps as user SYSTEM.

1. Export your data (only) at table-level including following options (other options not shown). This will create a single output file (although you can break it up into smaller pieces if desired using any of several methods).

consistent=y
indexes=n
constraints=n
statistics=none
triggers=n
grants=n
tables=(<see note * below>)

2. Export the "metadata" (only) for each schema including the following options. This will produce one output file per schema.

rows=n
constraints=y
grants=y
indexes=y
triggers=y
owner=<schema_owner>

3. Import the data only, using the single file from step 1. Schema owner(s) must already exist in target database, of course.

ignore=y (only if you have pre-created the tables, otherwise "n") fromuser=schema_a[,schema_b,...]
touser=schema_a[,schema_b,...]

4. Import the rest of the metadata (including triggers, indexes, constraints, grants, procs) for each schema as desired, using the files from step 2 (once for each file):

rows=n
constraints=y
grants=y
indexes=y
recalculate_statistics=y
ignore=y
fromuser=<schema_owner>
touser=<schema_owner>

• Note on the table list -- use the following query or something similar. I prefer to create my export files by tablespace (under control of a Perl script), and if you have table partitions, you need to tweak the syntax since exp uses a colon to indicate a partition-level export instead of a period.

select distinct owner||'.'||segment_name from sys.dba_segments
where tablespace_name = '<tblspc_name>'
and segment_type like 'TABLE%'
and owner not in
('SYS','SYSTEM','ORDSYS','MDSYS','CTXSYS','ORDPLUGINS','LBACSYS','XDB') -- see Metalink note 217135.1

-Mark Bole Received on Wed Sep 21 2005 - 13:09:24 CDT