Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: imp table data, but not PK indexes?

Re: imp table data, but not PK indexes?

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Sun, 20 Jul 2003 17:42:40 +0300
Message-Id: <25968.338592@fatcity.com>


Hi!

> Yes, but IIRC CONSTRAINTS=Y is an exp parameter,
> not imp?

It's imp parameter as well.

> Best way to handle these things is to export twice.
> Once with all the "bells and whistles" turned on but
> ROWS=N (this gives you a small export file with all
> the schema logic added and takes no time at all).
> The next one is with CONSTRAINTS=N, ROWS=Y, INDEXES=N,
> DIRECT=Y. This one is used for bulk data load.

I'd go that way:

  1. export schema structure with rows=n
  2. export data with constraints=n rows=y indexes=n direct=y recordlength=65535
  3. import structure file w. indexes=n constraints=n
  4. import data file w. buffer=high value (constraints=n and indexes=n don't have to specified if export was set not to export them)
  5. import structure file w. constraints=y indexes=y indexfile=indexfile.sql (nothing is actually imported, we just get constraint indexes and excplicit indexes DDL statements into text file)
  6. modify indexfile.sql and add nologging + possibly parallel clauses to your DDL statements. Also you might want to alter session set db_file_multiblock_read_count and sort_area_size to larger values in beginning of your script.
  7. import your data file, with buffer=big value
  8. run your indexfile.sql script
  9. import structure file again with constraints=y indexes=n and ignore=y

Note that indexfile created in step 5 will contain only non-system-generated indexes! (you can check generated field in dba_indexes to see which are system generated ones). Thus, indexes which are implicitly created using primary and unique keys, won't show up in indexfile. But that doesn't kill us - imp with constraints=y will eventually create these indexes anyway, we just can't control the DDL command issued for them (you might want to set tablespaces storing these indexes NOLOGGING temporarily during their creation).

Also, if you plan to shorten your downtime by first exporting/importing the structure from online database and then transferring data during downtime, you have to worry about your sequences, because they have probably incremented after structure export and data export. Thus a simple script has to be done for that (thanks John ;).

Tanel.

>
> With these two exports, one can do just about any
> manipulation needed.
>
> Cheers
> Nuno Souto
> nsouto_at_optusnet.com.au
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Nuno Souto
> INET: nsouto_at_optusnet.com.au
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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 Sun Jul 20 2003 - 09:42:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US