Database Reference
In-Depth Information
Next, referential integrity constraints are disabled, since these would be violated during
the import process. The
imp
utility re-enables them at the end of the import run. Disabling
constraints is achieved by generating a SQL script that contains
ALTER
TABLE
DISABLE
CONSTRAINT
statements with SQL*Plus. The SQL script is named
gen_disable_sp_constr.sql
and its contents
are shown here:
set linesize 200
set trimout on
set trimspool on
set heading off
set pagesize 0
set feedback off
set heading off
spool disable.sql
select 'ALTER TABLE perfstat.' || table_name || ' DISABLE CONSTRAINT ' ||
constraint_name || ';'
from dba_constraints
where owner='PERFSTAT' and constraint_type='R';
prompt exit
exit
Next, run the script as user
PERFSTAT
.
$ sqlplus -s perfstat/secret
@gen_disable_sp_constr.sql
ALTER TABLE perfstat.STATS$BG_EVENT_SUMMARY
DISABLE CONSTRAINT STATS$BG_EVENT_SUMMARY_FK;
…
ALTER TABLE perfstat.STATS$WAITSTAT DISABLE CONSTRAINT STATS$WAITSTAT_FK;
SQL*Plus writes the generated
ALTER
TABLE
statements to the file
disable.sql
. Running
disable.sql
disables all referential integrity constraints in schema
PERFSTAT
.
$ sqlplus -s perfstat/secret @disable
Table altered.
…
Table altered.
At this point, the schema is ready for importing past snapshot data. Note that the import
option
IGNORE=Y
is used to import into existing tables. Import will signal several ORA-00001 and
ORA-02264 errors. These are irrelevant and should be ignored.
$ imp system/secret file=perfstat.dmp full=y ignore=y log=imp.log
Import: Release 9.2.0.1.0 - Production on Wed Sep 19 18:09:00 2007
. importing PERFSTAT's objects into PERFSTAT
…
ORA-00001: unique constraint (PERFSTAT.STATS$IDLE_EVENT_PK) violated
Column 1 smon timer
…
. . importing table "STATS$STATSPACK_PARAMETER" 1 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "STATS$STATSPACK_PARAMETER" ADD CONSTRAINT "STATS$STATSPACK_P_P"
