import to clone schema revisited
EdStevens
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