| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Exp Imp Overwirte posiible or not?
Azhar,
> Is it possible with exp, imp utilities or i have to delete data
> first and then import using imp utility.
what you describe sounds more like a sqlldr operation.
afaik, the answer is no and you'd have to:
(backup)
(alter database noarchivelog)
- disable FK constraints - disable triggers if PK is already populated in import data - truncate the tables in the schema - import rows - enable constraints - enable disabled triggers - compute statistics - compile schema
do you use a sequence/trigger combination to populate the Primary key or
will the PK already be populated?
if you're populating the PK at insert time, you'll want the insert
trigger firing on the tables
if the PK is already populated, you'll likely want to disable the insert
triggers
Also, if you populate a create_date field in the insert trigger, you'll
overwrite the existing value in the dump file during the insert if the
insert trigger is left enabled.
Here's a script from the O'Reilly Oracle Scripts book for generating the DDL for the constraints.
rem Last Change 08/12/97 by Brian Lomasky
rem
set linesize 132
set pagesize 0
set heading off
set verify off
set echo off
set feedback off
set termout off
spool disconxx.sql
prompt spool discon.lis
select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||CHR(10)||
' DISABLE CONSTRAINT '|| CONSTRAINT_NAME || ';'
from dba_constraints
where constraint_type = 'R' and status = 'ENABLED'
and table_name in
(select table_name from dba_tables where owner != 'SYS'); select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||CHR(10)||
' DISABLE CONSTRAINT '|| CONSTRAINT_NAME || ';'
from dba_constraints
where constraint_type in ('P','U')
and status = 'ENABLED' and table_name in
(select table_name from dba_tables where owner != 'SYS');
prompt spool off
spool off
@disconxx.sql
prompt Check discon.lis log file for any errors
exit
I can understand why you don't want to drop the schemas - if you're tracking dba_objects.last_ddl_time - that info would not be preserved on a drop/re-create.
Paul
azhar_at_mathtech-pk.com wrote:
>
> Hi All,
> I want to import data from a user dump file but when i import data, i want
> to overwrite the existing data . Normally imp utility append data to
> existing rows. Requirement of application is such that we need to
> overwrite existing data without dropping objects and its dependent
> objects. Is it possible with exp , imp utilities or i have to delete data
> first and then import using imp utility.
> Is there any other possibility...
> Environment Oracle8i, Windows NT4
> TIA
>
> Azhar Siddiq
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: paled_at_home.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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 Tue Mar 27 2001 - 09:13:23 CST
 |
 |