Oracle Database Import Guide: Step-by-Step Procedure to Restore from .DMP File


2 views

Before importing the .DMP file, ensure you have:

  • Oracle Database 10g (or compatible version) installed and running
  • Sufficient disk space (typically 2-3x the .DMP file size)
  • Administrative privileges (SYSDBA or IMP_FULL_DATABASE role)
  • The original character set information (if different from your current NLS_LANG setting)

Configure your environment variables first:

# For Linux/Unix:
export ORACLE_HOME=/path/to/oracle/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=your_SID
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8  # Match the source DB charset

Choose the appropriate import method based on your requirements:

Method Command When to Use
Full Import imp system/password full=y file=export.dmp Complete database restoration
Schema Import imp system/password schemas=SCHEMA_NAME file=export.dmp Single schema migration
Table Import imp system/password tables=(TABLE1,TABLE2) file=export.dmp Partial data restoration

For a full database restoration from a .DMP file:

imp system/manager@ORCL full=y file=/path/to/export.dmp 
     log=import.log buffer=10485760 
     commit=y feedback=1000 
     ignore=y

Key parameters explained:

  • full=y: Import entire database contents
  • ignore=y: Continue if object exists (recommended for first-time imports)
  • buffer: Size of data buffer (adjust based on available RAM)
  • commit=y: Commit after each array insert (prevents rollback segment issues)

Common problems and their solutions:

-- Error: IMP-00058: ORACLE error 1658 encountered
-- Solution: Add this parameter to your import command:
tablespaces=USERS,EXAMPLE  # Specify all tablespaces used in the .DMP

-- Error: IMP-00010: not a valid export file
-- Solution: Verify file integrity and Oracle version compatibility

For large databases, consider these optimizations:

# Parallel import example (Oracle Enterprise Edition only):
imp system/password file=export.dmp full=y 
     parallel=4 
     cluster=n 
     statistics=none

# Transportable Tablespace alternative (faster for very large DBs):
-- First move datafiles manually, then:
imp system/password transport_tablespace=y 
     tablespaces=TS_DATA,TS_INDEX 
     datafiles='/oradata/ts_data01.dbf','/oradata/ts_index01.dbf' 
     file=tts_export.dmp

After import completes, verify critical objects:

SELECT owner, object_type, COUNT(*) 
FROM dba_objects 
WHERE owner IN ('SCHEMA1','SCHEMA2') 
GROUP BY owner, object_type 
ORDER BY owner, object_type;

Before proceeding with the import operation, ensure you have:

  • Oracle Database 10g (or later) installed
  • Sufficient disk space (typically 2-3x the DMP file size)
  • SYSDBA or IMP_FULL_DATABASE privileges
  • Compatible character sets between source and target

First, create a directory object pointing to your dump file location:

CREATE OR REPLACE DIRECTORY dmp_dir AS '/path/to/your/dumpfile';
GRANT READ, WRITE ON DIRECTORY dmp_dir TO system;

The modern way to import Oracle databases:

impdp system/password@db10g \
directory=dmp_dir \
dumpfile=export.dmp \
logfile=import.log \
full=y \
parallel=4

Key parameters:

  • full=y: Imports entire database
  • parallel=4: Speeds up import using 4 processes
  • remap_schema: Use if changing schema names (e.g., remap_schema=old_user:new_user)

For older dump files (pre-10g):

imp system/password@db10g \
file=/path/to/export.dmp \
log=import.log \
full=y \
buffer=10000000 \
commit=y

Error: ORA-31655 - Typically indicates dump file incompatibility. Verify versions:

impdp system/password@db10g \
sqlfile=dump_info.sql \
dumpfile=export.dmp \
directory=dmp_dir

Space issues - Monitor during import:

SELECT tablespace_name, sum(bytes)/1024/1024 "Free MB"
FROM dba_free_space
GROUP BY tablespace_name;

For large databases:

  • Set STREAMS_POOL_SIZE in init.ora
  • Increase db_writer_processes
  • Disable logging for large tables: ALTER TABLE schema.table NOLOGGING;