Re: HELP - Export/Import version 8.05

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 5 Jan 2007 09:52:57 -0800
Message-ID: <1168019577.776646.261820@11g2000cwr.googlegroups.com>

Julie here's the contents of the URL that I cited ...

I can't vouche for it entirely but at a quick glance it looks fairly reasonable to me. Ed Stevens is I believe the author of this.

---

Posting this mostly as a reference in case anyone searches the archives

with a similar problem. Following up on a thread I started a few days

ago.

Task is to clone a schema. The first difficulty came with FROMUSER=schema-A TOUSER=schema-B, in that we want each schema to have it's own tablespace, but the DDL in the .dmp file had hard-coded tablespace references to the TS of the original exported objects. Various solutions found on the web involved extracting the DDL from the

.dmp file and messaging it by hand, correcting the tablespace references and adding the statement terminators. This became untenable

when it was found that with several hundred DDL statements, some were contained on a single line, and others were broken (sometimes badly) across multiple lines. The solution is in the use of the 'indexfiles' parm on import. It produces a text file of good, usable DDL that is easily edited with global changes to a usable file for initially creating the tables and indexes. Here's the full sequence:

1. Create the TS for the new schema
2. Create the user account for the new schema
3. Export the source schema. Can be FULL=Y, but I restrict it to USER=oldschema
4. Run import with the INDEXFILE parm to gen good DDL: file=expdat.dmp fromuser=oldschema touser=newschema indexes=y rows=n constraints=y indexfile=schema.sql
5. Edit the indexfile (schema.sql in this example) to make the following global changes: 5.1 change all occurances of old_tablespace_name to new_tablespace_name 5.2 remove all occurances of "REM", so that all statements are enabled, not just the indexes. 5.3 move the 'connect' statement that occurs just before the first index creation to the top of the file
6. execute the resulting schema.sql file in sqlplus, ****connected as the new schema owner***
7. Disable all FK constraints. I use the following to automate this: set echo off set feedback off set verify off set trimspool on set pagesize 0 set linesize 256 ACCEPT myschema PROMPT 'Schema: ' spool xdoit.sql
   --
   select 'spool disable_constraints_&myschema..log' from dual; select 'alter table '||owner||'.'||table_name|| ' disable constraint '||constraint_name|| ' cascade;' from dba_constraints where owner=upper('&myschema') and constraint_type='R';
   --
   select 'spool off' from dual;
   --
   spool off
   --
   set echo on feedback on verify on trimspool on pagesize 9999 @xdoit.sql
8. Run import to load the tables and create any remaining objects like sequences, packages, triggers, etc. Key parms are: file=expdat.dmp fromuser=oldschema touser=newschema ignore=y grants=y indexes=n rows=y constraints=y

This import should enable all constraints but I found there are some left over. So, this sql script fixes it:

    set echo off
    set feedback off
    set verify off
    set trimspool on
    set pagesize 0
    set linesize 256
--

    ACCEPT myschema PROMPT 'Schema: '
    spool xdoit.sql
--

    select 'spool enable_constraints_&myschema..log' from dual;     select 'alter table '||owner||'.'||table_name||

            ' enable constraint '||constraint_name||
            ';'

    from dba_constraints
    where owner=upper('&myschema')
    and status='DISABLED';
--

    select 'spool off' from dual;
--

    spool off
--

    set echo on feedback on verify on trimspool on pagesize 9999     @xdoit.sql Received on Fri Jan 05 2007 - 11:52:57 CST