When dealing with Oracle database migrations or updates between environments (DEV→TEST, PROD→UAT), a common requirement is to completely overwrite the target database with source data. The default imp behavior doesn't handle this gracefully when tables already exist.

For modern Oracle versions, use Data Pump (expdp/impdp) with these parameters:

expdp system/password DIRECTORY=dpump_dir DUMPFILE=full_A.dmp FULL=Y

impdp system/password DIRECTORY=dpump_dir DUMPFILE=full_A.dmp FULL=Y 

If you must use traditional utilities, combine these switches:

imp system/password file=export_A.dmp fromuser=A touser=B 

• Always take a full backup before replacement operations
• For large databases, consider partitioning the export/import
• Verify object counts with: SELECT COUNT(*) FROM dba_objects WHERE owner='B'

Here's a shell script template for scheduled replacements:

#!/bin/bash
export ORACLE_SID=ORCL
exp userid=system/password file=/backups/full_export_$(date +%Y%m%d).dmp log=/backups/export.log full=y

ssh target_server "imp userid=system/password file=/backups/full_export_$(date +%Y%m%d).dmp log=/backups/import.log full=y ignore=y"

Common errors and solutions:

ORA-39083: Object type TABLE:"B"."EMPLOYEES" failed to create
-> Add  to impdp command

ORA-00955: name is already used by an existing object
-> Use  for Data Pump

When migrating data between identical Oracle database schemas where you need to completely overwrite existing data with newer records, the standard IMP utility behavior might not give you the desired results. Here's why:

-- Typical import command that fails to overwrite
imp username/password file=xxx.dmp fromuser=Auser touser=Buser

This command will either skip existing tables or append data rather than replace it entirely.

To force a complete replacement of the target database (B) with data from the source (A), you need to combine several techniques:

-- First drop all existing objects in target schema
-- Connect as Buser and run:
BEGIN
  FOR cur_rec IN (SELECT object_name, object_type 
                  FROM user_objects
                  WHERE object_type IN ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION','SEQUENCE','TYPE')) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"' ||
                        CASE WHEN cur_rec.object_type = 'TABLE' THEN ' CASCADE CONSTRAINTS PURGE' ELSE '' END;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAILED: DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

-- Then perform the import with full overwrite parameters
imp username/password file=xxx.dmp fromuser=Auser touser=Buser \
  ignore=y commit=y buffer=10485760 feedback=10000

For modern Oracle versions (10g+), Data Pump offers better control:

-- Export from source (A)
expdp system/password schemas=Auser directory=DATA_PUMP_DIR dumpfile=A_exp.dmp logfile=A_exp.log

-- Import with table replacement in target (B)
impdp system/password directory=DATA_PUMP_DIR dumpfile=A_exp.dmp \
  schemas=Auser remap_schema=Auser:Buser \
  table_exists_action=replace content=ALL

For enterprise-scale migrations:

• Consider parallel processing: parallel=4 parameter
• Split the dump file: filesize=2G during export
• Use network_link for direct transfer without intermediate files

Before executing any replacement operation:

1. Take a full backup of the target database
2. Verify schema structure compatibility
3. Schedule during maintenance windows for production systems
4. Monitor tablespace usage during import