Introduction

Oracle Data Pump Import (impdp) is one of the most commonly used tools for migrating Oracle schemas and databases. The command itself is simple. A safe production migration is not.

A recent migration involving a roughly 31 GB Data Pump dump file highlighted why a proper import process needs to begin well before the actual impdp command. Dump-file metadata had to be inspected, source schemas and tablespaces discovered, character sets compared, target storage designed, edition-specific features reviewed, and imported objects validated after the load.

The most important rule is:

Do not treat the dump file as a black box and immediately import it into production.

Oracle Data Pump provides enough information to perform a substantial amount of discovery before changing the target database. In particular, SQLFILE can generate the DDL that Data Pump intends to execute without actually executing that DDL.

This article provides a reusable production workflow for Oracle Data Pump imports, particularly when the dump file is all you have and the source database is unavailable.

1. Understand What You Received

Before importing anything, establish what the dump file actually contains.

A file named:

application_export.dmp

could represent:

a single schema export
multiple schemas
selected tables
a full database export
metadata only
data only
a compressed export
an encrypted export

The file size alone tells you very little about the amount of storage required on the target.

A 31 GB dump file does not necessarily produce a 31 GB schema. The exported table data may be compressed, indexes are generally recreated on the target, and object allocation characteristics can differ from the source.

Start by preserving the original file.

mkdir -p /backup/datapump

ls -lh /backup/datapump/application_export.dmp

Generate a checksum:

sha256sum /backup/datapump/application_export.dmp \
  > /backup/datapump/application_export.dmp.sha256

Later you can confirm the file has not changed:

sha256sum -c /backup/datapump/application_export.dmp.sha256

The Oracle operating-system account must also have read access to the file.

chown oracle:oinstall /backup/datapump/application_export.dmp
chmod 640 /backup/datapump/application_export.dmp

2. Create a Data Pump Directory

Oracle Data Pump does not directly use an operating-system pathname supplied to impdp. It uses an Oracle DIRECTORY object that maps to a server-side path.

For example:

CREATE OR REPLACE DIRECTORY DPUMP_DIR
AS '/backup/datapump';

Verify it:

SELECT
    directory_name,
    directory_path
FROM dba_directories
WHERE directory_name = 'DPUMP_DIR';

For production migrations, use a dedicated Data Pump account rather than SYS.

CREATE USER DPIMP
IDENTIFIED BY "<strong-temporary-password>";

GRANT CREATE SESSION TO DPIMP;
GRANT DATAPUMP_IMP_FULL_DATABASE TO DPIMP;

GRANT READ, WRITE
ON DIRECTORY DPUMP_DIR
TO DPIMP;

Oracle specifically advises against starting normal Data Pump imports as SYSDBA except when directed by Oracle Support.

After the migration, the temporary account can be locked or removed.

3. Inspect the Dump Before Importing It

Oracle provides DBMS_DATAPUMP.GET_DUMPFILE_INFO for extracting information from the dump-file header without running an import. It can identify items including the source database version, source platform, source character set, creation date and Data Pump file type.

A simple diagnostic is:

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
    l_info     KU$_DUMPFILE_INFO := KU$_DUMPFILE_INFO();
    l_filetype NUMBER;
BEGIN
    DBMS_DATAPUMP.GET_DUMPFILE_INFO(
        filename   => 'application_export.dmp',
        directory  => 'DPUMP_DIR',
        info_table => l_info,
        filetype   => l_filetype
    );

    DBMS_OUTPUT.PUT_LINE('File type: ' || l_filetype);

    FOR i IN 1 .. l_info.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            l_info(i).item_code || ' : ' || l_info(i).value
        );
    END LOOP;
END;
/

A Data Pump dump should return a file type indicating a Data Pump file.

Pay particular attention to:

Source Oracle version
Dump creation time
Source platform
Source database character set
Compression
Encryption

If the dump came from an Oracle release newer than the target database, verify import compatibility before proceeding.

4. Generate a Non-Destructive SQLFILE

This is arguably the most important pre-import step.

Create a parameter file:

DIRECTORY=DPUMP_DIR
DUMPFILE=application_export.dmp

SQLFILE=dump_discovery.sql
LOGFILE=dump_discovery.log

LOGTIME=ALL

Run:

impdp dpimp PARFILE=dump_discovery.par

Because SQLFILE is specified, Data Pump writes the SQL DDL it would normally execute instead of applying it to the database.

Review:

less /backup/datapump/dump_discovery.sql

and:

less /backup/datapump/dump_discovery.log

A schema export might show object paths such as:

SCHEMA_EXPORT/USER
SCHEMA_EXPORT/SYSTEM_GRANT
SCHEMA_EXPORT/ROLE_GRANT
SCHEMA_EXPORT/DEFAULT_ROLE
SCHEMA_EXPORT/SEQUENCE/SEQUENCE
SCHEMA_EXPORT/TABLE/TABLE
SCHEMA_EXPORT/PROCEDURE/PROCEDURE
SCHEMA_EXPORT/TABLE/INDEX/INDEX
SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
SCHEMA_EXPORT/TABLE/TRIGGER
SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

That immediately tells you the dump contains considerably more than table data.

5. Discover the Source Schema and Tablespaces

Search the generated SQL file for user metadata:

grep -Ein 'CREATE USER|ALTER USER' dump_discovery.sql

Find source tablespaces:

grep -oE 'TABLESPACE "[^"]+"' dump_discovery.sql \
| sort -u

Review grants:

grep -Ein \
'GRANT |CREATE ROLE|ALTER USER|DEFAULT ROLE|PROFILE' \
dump_discovery.sql

This is particularly important because schema exports can contain source password verifier metadata such as:

CREATE USER "SOURCE_APP"
IDENTIFIED BY VALUES '...';

For migrations into a differently named target schema, it is generally cleaner to create the target user yourself and use:

EXCLUDE=USER

Oracle documents that EXCLUDE=USER excludes the user definition but does not exclude the objects belonging to that user's schema.

That lets you control the new password, default tablespace, temporary tablespace and account state.

6. Scan for Features Before Import

Do not assume that because a schema successfully exported from one Oracle environment it will be suitable for another edition or configuration.

Search the SQLFILE for features requiring particular attention:

grep -Ein \
'PARTITION BY (RANGE|LIST|HASH)|BITMAP|COMPRESS FOR|INMEMORY|SECUREFILE|BASICFILE|INDEXTYPE|DOMAIN INDEX|MATERIALIZED VIEW|DATABASE LINK|DBMS_SCHEDULER' \
dump_discovery.sql

Also review:

grep -Ein \
'CREATE .*PROCEDURE|CREATE .*FUNCTION|CREATE .*PACKAGE|CREATE .*TRIGGER|CREATE .*TYPE|CREATE .*SYNONYM' \
dump_discovery.sql

Be careful with simple searches for:

PARTITION BY

because stored SQL can legitimately contain analytic expressions such as:

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY event_date
)

That is completely different from physical Oracle table partitioning.

Inspect the actual CREATE TABLE context before concluding that partitioned tables are present.

7. Character Set Validation Is Mandatory

One of the most important lessons from dump-only migrations is that the Data Pump log can reveal source character-set information even when the original database is unavailable.

Check the target database:

SELECT
    parameter,
    value
FROM nls_database_parameters
WHERE parameter IN
(
    'NLS_CHARACTERSET',
    'NLS_NCHAR_CHARACTERSET'
);

You may see something such as:

NLS_CHARACTERSET          <database-character-set>
NLS_NCHAR_CHARACTERSET    <national-character-set>

When Data Pump opens the dump, the log reports both environments:

import done in <target charset> character set
             and <target NCHAR charset> NCHAR character set

export done in <source charset> character set
             and <source NCHAR charset> NCHAR character set

That comparison is extremely valuable.

Oracle recommends minimizing character-set conversions and states that the target character set should be equivalent to or a superset of the source character set. Characters that cannot be represented on the target may otherwise be replaced.

What ORA-39345 Means

If Data Pump displays:

ORA-39345:
Warning: possible data loss in character set conversions

do not ignore it.

Oracle reports this warning when the source character set or national character set differs from the target and the target is not considered a safe superset for the conversion.

A related:

ORA-39346

means a metadata object actually experienced character loss during conversion and replacement characters were used.

8. Database Charset and NCHAR Charset Are Different

Oracle uses the normal database character set for types such as:

CHAR
VARCHAR2
CLOB

The national character set is used by:

NCHAR
NVARCHAR2
NCLOB

Therefore, if only NLS_NCHAR_CHARACTERSET differs, identify whether the schema actually uses national character datatypes.

grep -Ein \
'NVARCHAR2|NCLOB|NCHAR[[:space:]]*\(' \
dump_discovery.sql

You can also count occurrences:

grep -Eic \
'NVARCHAR2|NCLOB|NCHAR[[:space:]]*\(' \
dump_discovery.sql

If those datatypes are present, an NCHAR character-set mismatch deserves particularly careful testing.

Oracle supports AL16UTF16 and UTF8 for national-character data, but Oracle documents AL16UTF16 as the default and recommended national character set and describes Oracle's UTF8 national character set as the older CESU-8 encoding.

For a new lift-and-shift target where exact fidelity is the priority, matching the source character and national character sets is generally the lowest-risk approach.

Character-set modernization, for example moving an old database to AL32UTF8, should normally be treated as a separate migration project with appropriate application and data testing.

Oracle 19c also states that the database or national character set can no longer simply be changed using ALTER DATABASE; a proper character-set migration process must be used.

9. Protect Against Replacement Characters

Data Pump provides another useful safeguard:

DATA_OPTIONS=REJECT_ROWS_WITH_REPL_CHAR

Without this option, rows experiencing character-set conversion loss can be loaded using replacement characters.

With it enabled, Data Pump rejects affected rows rather than silently storing replacement characters.

For migrations where character integrity is important, this is a useful defensive setting:

DATA_OPTIONS=REJECT_ROWS_WITH_REPL_CHAR

It does not replace proper charset planning, but it helps prevent silent data corruption.

10. Size the Target Tablespace Properly

Never assume:

31 GB dump = 31 GB tablespace

The actual imported footprint consists of:

table segments
LOB segments
indexes
constraints
segment overhead
future autoextend growth

The dump may also be compressed.

Check physical filesystem capacity:

df -h /data

Check existing Oracle datafiles:

SELECT
    tablespace_name,
    ROUND(SUM(bytes)/1024/1024/1024,2) current_gb,
    ROUND(SUM(maxbytes)/1024/1024/1024,2) max_gb
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;

11. Understand Smallfile Datafile Limits

A common surprise during target preparation is:

ORA-03206:
maximum file size ... blocks in AUTOEXTEND clause is out of range

For a traditional smallfile tablespace, Oracle permits roughly four million blocks per datafile. With an 8 KB database block size, this means an individual smallfile datafile is limited to approximately 32 GB.

Therefore this can fail:

CREATE TABLESPACE APP_DATA
DATAFILE SIZE 10G
AUTOEXTEND ON NEXT 1G
MAXSIZE 200G;

Instead, use multiple datafiles:

CREATE TABLESPACE APP_DATA
DATAFILE
SIZE 10G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 30G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Add additional files as required:

ALTER TABLESPACE APP_DATA
ADD DATAFILE
SIZE 10G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 30G;

Alternatively, a BIGFILE TABLESPACE can support a much larger single file. The choice should follow the organisation's storage and Oracle operational standards.

12. Validate Tablespace Capacity Before Import

Use:

SELECT
    tablespace_name,
    COUNT(*) file_count,
    ROUND(SUM(bytes)/1024/1024/1024,2) current_gb,
    ROUND(SUM(maxbytes)/1024/1024/1024,2) max_possible_gb,
    ROUND(
        SUM(maxbytes-bytes)/1024/1024/1024,
        2
    ) autoextend_remaining_gb
FROM dba_data_files
WHERE tablespace_name='APP_DATA'
GROUP BY tablespace_name;

Remember that MAXBYTES is only Oracle's configured potential growth. It does not reserve physical filesystem space.

Always compare it with:

df -h

If the tablespace can theoretically expand to 150 GB while the filesystem has only 60 GB available, the real limit is approximately 60 GB.

13. Optional Metadata-Only Sizing Rehearsal

When no source database exists, exact pre-import sizing is difficult.

One option is to perform a metadata-only rehearsal in a disposable database or schema:

CONTENT=METADATA_ONLY

This creates object definitions but does not import table rows. Oracle notes that table and index statistics imported during a metadata-only operation are locked afterwards.

You can then inspect source statistics such as:

SELECT
    table_name,
    num_rows,
    blocks,
    avg_row_len
FROM dba_tables
WHERE owner='TARGET_APP'
ORDER BY blocks DESC NULLS LAST;

and:

SELECT
    index_name,
    table_name,
    leaf_blocks,
    blevel
FROM dba_indexes
WHERE owner='TARGET_APP'
ORDER BY leaf_blocks DESC NULLS LAST;

These are estimates only. Statistics may be stale.

SQLFILE remains the preferred first discovery mechanism because it is non-destructive.

14. Check TEMP and UNDO

Large imports can also stress temporary and undo storage, particularly while indexes and constraints are being created.

Check TEMP:

SELECT
    tablespace_name,
    file_name,
    ROUND(bytes/1024/1024/1024,2) current_gb,
    autoextensible,
    ROUND(maxbytes/1024/1024/1024,2) max_gb
FROM dba_temp_files;

Check UNDO:

SELECT
    tablespace_name,
    file_name,
    ROUND(bytes/1024/1024/1024,2) current_gb,
    autoextensible,
    ROUND(maxbytes/1024/1024/1024,2) max_gb
FROM dba_data_files
WHERE tablespace_name =
      (SELECT value
       FROM v$parameter
       WHERE name='undo_tablespace');

Do not arbitrarily increase PGA, SGA, processes or sessions simply because a large Data Pump import is planned. Monitor the actual environment.

15. Create the Target Schema Yourself

Instead of importing the source user definition and password verifier, pre-create the application account.

For example:

CREATE USER TARGET_APP
IDENTIFIED BY "<new-strong-password>"
DEFAULT TABLESPACE APP_DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON APP_DATA
ACCOUNT LOCK;

Keeping the account locked prevents applications or users from connecting while objects are only partially imported.

Data Pump supports remapping all objects from one schema owner to another using:

REMAP_SCHEMA=SOURCE_APP:TARGET_APP

16. Remap the Source Tablespace

If the source schema stored objects in:

SOURCE_TS

but the new environment uses:

APP_DATA

use:

REMAP_TABLESPACE=SOURCE_TS:APP_DATA

Oracle Data Pump will then create imported persistent objects in the target tablespace rather than trying to recreate them in the original location.

If multiple source tablespaces exist, specify each mapping explicitly.

17. Strip Old Storage Settings

The source database may contain storage clauses that make little sense on a modern target.

For example:

INITIAL
NEXT
MINEXTENTS
MAXEXTENTS
PCTINCREASE

Using:

TRANSFORM=STORAGE:N

removes storage clauses while retaining the other segment attributes such as the tablespace clause, allowing REMAP_TABLESPACE to operate cleanly. Oracle documents STORAGE:N specifically for this purpose.

18. Run a Final Remapped SQLFILE Preview

Before importing actual data, generate a second SQLFILE using the exact mappings intended for production.

Example:

DIRECTORY=DPUMP_DIR
DUMPFILE=application_export.dmp

SCHEMAS=SOURCE_APP

REMAP_SCHEMA=SOURCE_APP:TARGET_APP
REMAP_TABLESPACE=SOURCE_TS:APP_DATA

EXCLUDE=USER
EXCLUDE=STATISTICS

TRANSFORM=STORAGE:N

PARALLEL=1

SQLFILE=import_preview.sql
LOGFILE=import_preview.log

LOGTIME=ALL

Run:

impdp dpimp PARFILE=import_preview.par

Now search:

grep -in '"SOURCE_APP"\.' import_preview.sql

and:

grep -in 'TABLESPACE "SOURCE_TS"' import_preview.sql

Any remaining occurrences deserve investigation.

This is particularly important because Oracle explicitly notes that REMAP_SCHEMA cannot rewrite every schema reference embedded inside objects such as views, procedures, packages and type bodies.

19. Recommended Production impdp Parameter File

For a new target schema, a solid baseline looks like this:

DIRECTORY=DPUMP_DIR
DUMPFILE=application_export.dmp
LOGFILE=import_application.log

JOB_NAME=IMP_APPLICATION_01

SCHEMAS=SOURCE_APP

REMAP_SCHEMA=SOURCE_APP:TARGET_APP
REMAP_TABLESPACE=SOURCE_TS:APP_DATA

EXCLUDE=USER
EXCLUDE=STATISTICS

TRANSFORM=STORAGE:N

DATA_OPTIONS=REJECT_ROWS_WITH_REPL_CHAR

PARALLEL=1

METRICS=YES
LOGTIME=ALL
STATUS=300

Run:

impdp dpimp PARFILE=import_application.par

Using a parameter file rather than a large command line makes the configuration easier to review, repeat and audit.

EXCLUDE=STATISTICS is not mandatory, but it is often sensible when migrating to a different database release or environment. Fresh optimizer statistics can be gathered after the import.

20. Standard Edition Versus Enterprise Edition

Parallelism deserves special attention.

Oracle Data Pump supports:

PARALLEL=n

but Data Pump parallelism is restricted to 1 in Oracle Database Standard Edition.

Therefore an Oracle SE2 import should use:

PARALLEL=1

or simply rely on the default.

On Enterprise Edition, higher parallelism can potentially reduce elapsed time, but it must be balanced against CPU, storage throughput, dump-file count and workload impact.

21. Be Conservative With Archive Logging

Data Pump supports:

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

which can reduce redo generated by table and index creation. Oracle notes that it does not override database FORCE LOGGING.

For a production migration, however, do not enable it automatically.

Keeping normal logging gives you a clearer recovery path.

If reduced logging is deliberately used for a new disposable target, establish a new RMAN recovery point after successful validation.

22. Monitor the Import

Data Pump jobs run inside the database and can be monitored independently of the original terminal.

Check the job:

SELECT
    owner_name,
    job_name,
    operation,
    job_mode,
    state,
    degree,
    attached_sessions,
    datapump_sessions
FROM dba_datapump_jobs;

DBA_DATAPUMP_JOBS shows active Data Pump jobs and their state and degree.

Check worker sessions:

SELECT
    owner_name,
    job_name,
    session_type,
    instance_id
FROM dba_datapump_sessions;

Oracle provides DBA_DATAPUMP_SESSIONS specifically for identifying Data Pump master, worker and attached sessions.

23. Monitor Progress With V$SESSION_LONGOPS

Data Pump table-data transfers publish progress information through V$SESSION_LONGOPS.

For example:

SELECT
    sid,
    serial#,
    opname,
    sofar,
    totalwork,
    units,
    ROUND(
        sofar * 100 / NULLIF(totalwork,0),
        2
    ) pct_complete
FROM v$session_longops
WHERE totalwork > 0
AND sofar < totalwork
AND (
       opname LIKE 'SYS_IMPORT%'
       OR opname LIKE 'IMP_%'
    )
ORDER BY start_time;

Remember that Data Pump often has lengthy metadata phases where a simple percentage does not accurately represent the remaining wall-clock time.

A schema containing tens of thousands of objects may spend substantial time executing metadata even if the raw dump file is only a few tens of gigabytes.

24. Monitor Storage at the Same Time

At operating-system level:

watch -n 30 'df -h /data /fra /backup'

Monitor database datafile growth:

SELECT
    tablespace_name,
    ROUND(SUM(bytes)/1024/1024/1024,2) allocated_gb,
    ROUND(SUM(maxbytes)/1024/1024/1024,2) max_gb
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;

If the database is in ARCHIVELOG mode, also monitor FRA consumption:

SELECT
    name,
    ROUND(space_limit/1024/1024/1024,2) limit_gb,
    ROUND(space_used/1024/1024/1024,2) used_gb,
    ROUND(space_reclaimable/1024/1024/1024,2)
        reclaimable_gb
FROM v$recovery_file_dest;

A large Data Pump import may produce substantial redo.

25. What If the SSH Session Disconnects?

Do not immediately start another import.

Because Data Pump is server-side, the import job may still be running.

Check:

SELECT
    owner_name,
    job_name,
    state
FROM dba_datapump_jobs;

Reattach:

impdp dpimp ATTACH=IMP_APPLICATION_01

At the Data Pump prompt:

Import> STATUS

The ATTACH facility is specifically designed to reconnect to existing Data Pump jobs.

26. Common Data Pump Import Errors

ErrorTypical MeaningRecommended Action
ORA-39345Potential character-set conversion lossCompare source/target charset and NCHAR charset before continuing
ORA-39346Metadata experienced character conversion lossIdentify affected object and correct charset strategy
ORA-39083Object creation failedRead the following ORA error; this is often only the wrapper error
ORA-01950Target user has no tablespace quotaGrant appropriate quota
ORA-01653Table cannot extendIncrease target tablespace/datafile capacity
ORA-01654Index cannot extendIncrease target tablespace capacity
ORA-03206Datafile MAXSIZE exceeds block/file limitsReduce smallfile MAXSIZE or add more files/use BIGFILE
ORA-31684Object already existsDetermine whether it was deliberately pre-created
ORA-39070 / ORA-29283Directory or file access problemCheck Oracle DIRECTORY and OS permissions

Do not judge success solely by:

Job successfully completed

Always inspect the complete import log.

27. Analyse the Import Log

Immediately after completion:

grep -Ei \
'ORA-|ERROR|FAIL|WARNING' \
/backup/datapump/import_application.log

Also inspect the final section:

tail -200 /backup/datapump/import_application.log

Some ORA messages may be expected, for example an object deliberately pre-created on the target.

Every error should nevertheless be understood and documented.

28. Validate Imported Objects

Start with object counts:

SELECT
    object_type,
    COUNT(*) object_count
FROM dba_objects
WHERE owner='TARGET_APP'
GROUP BY object_type
ORDER BY object_type;

This quickly confirms the presence of:

TABLE
INDEX
SEQUENCE
VIEW
PROCEDURE
FUNCTION
PACKAGE
TRIGGER
SYNONYM
TYPE

depending on the application.

Check schema size:

SELECT
    ROUND(SUM(bytes)/1024/1024/1024,2) schema_size_gb
FROM dba_segments
WHERE owner='TARGET_APP';

29. Check Invalid Objects

SELECT
    object_type,
    COUNT(*) invalid_count
FROM dba_objects
WHERE owner='TARGET_APP'
AND status='INVALID'
GROUP BY object_type
ORDER BY object_type;

Compile them:

EXEC UTL_RECOMP.RECOMP_SERIAL('TARGET_APP');

Then repeat the query.

Some objects can remain invalid because a dependency is genuinely absent, so never assume recompilation alone fixes the underlying issue.

30. Check Indexes

Normal indexes:

SELECT
    index_name,
    table_name,
    status
FROM dba_indexes
WHERE owner='TARGET_APP'
AND status='UNUSABLE';

For partitioned indexes:

SELECT
    index_name,
    partition_name,
    status
FROM dba_ind_partitions
WHERE index_owner='TARGET_APP'
AND status='UNUSABLE';

Any unusable index should be understood and rebuilt before application release.

31. Check Constraints

SELECT
    table_name,
    constraint_name,
    constraint_type,
    status,
    validated
FROM dba_constraints
WHERE owner='TARGET_APP'
AND (
       status <> 'ENABLED'
       OR validated <> 'VALIDATED'
    )
ORDER BY table_name, constraint_name;

Review foreign keys, primary keys and unique constraints carefully.

32. Find Hard-Coded Source Schema References

This is critical when REMAP_SCHEMA was used.

Stored code:

SELECT
    name,
    type,
    line,
    text
FROM dba_source
WHERE owner='TARGET_APP'
AND UPPER(text) LIKE '%SOURCE_APP.%'
ORDER BY name, type, line;

Dependencies:

SELECT
    name,
    type,
    referenced_owner,
    referenced_name
FROM dba_dependencies
WHERE owner='TARGET_APP'
AND referenced_owner='SOURCE_APP'
ORDER BY type, name;

Synonyms:

SELECT
    synonym_name,
    table_owner,
    table_name
FROM dba_synonyms
WHERE owner='TARGET_APP'
AND table_owner='SOURCE_APP';

Oracle documents that embedded schema references are one of the limitations of REMAP_SCHEMA.

33. Review Database Links

Database links can inadvertently point a migrated test or production environment at an old system.

Check:

SELECT
    owner,
    db_link,
    username,
    host
FROM dba_db_links
WHERE owner='TARGET_APP';

Do not simply assume an imported database link should remain active.

Validate every destination.

34. Review Scheduler and Legacy Jobs

Scheduler jobs can be more dangerous than failed objects because they may begin processing data immediately.

Check:

SELECT
    owner,
    job_name,
    enabled,
    state,
    job_type,
    job_action
FROM dba_scheduler_jobs
WHERE owner='TARGET_APP'
ORDER BY job_name;

Legacy DBMS_JOB jobs:

SELECT
    job,
    schema_user,
    what,
    broken
FROM dba_jobs
WHERE schema_user='TARGET_APP';

Review jobs that:

send email
connect over database links
delete/archive records
call external programs
move files
integrate with production systems

before allowing them to run.

On an isolated migration target, some DBAs temporarily set:

ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;

during import.

Because that setting affects the whole database, use it only when the impact on every schema is understood, and restore the original value afterwards.

35. Validate Roles and Privileges

Schema exports can include system privileges, role grants and default-role settings.

Review what the target account actually received:

SELECT
    privilege
FROM dba_sys_privs
WHERE grantee='TARGET_APP'
ORDER BY privilege;

Roles:

SELECT
    granted_role,
    default_role
FROM dba_role_privs
WHERE grantee='TARGET_APP'
ORDER BY granted_role;

Object grants:

SELECT
    owner,
    table_name,
    privilege,
    grantor
FROM dba_tab_privs
WHERE grantee='TARGET_APP'
ORDER BY owner, table_name;

Do not blindly reproduce unnecessary privileges from an older source environment.

36. Validate Sequences

Verify imported sequences exist:

SELECT
    sequence_name,
    increment_by,
    cache_size,
    last_number
FROM dba_sequences
WHERE sequence_owner='TARGET_APP'
ORDER BY sequence_name;

For business-critical sequences, application testing should confirm that new generated identifiers do not collide with imported data.

37. Gather Fresh Optimizer Statistics

If statistics were deliberately excluded during import:

EXCLUDE=STATISTICS

gather fresh statistics after objects and data have been validated:

BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(
        ownname          => 'TARGET_APP',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        degree           => 1,
        cascade          => TRUE
    );
END;
/

The appropriate statistics strategy ultimately depends on application workload, database edition and maintenance-window requirements.

38. Validate Character Data After Import

If the schema contains:

NCHAR
NVARCHAR2
NCLOB

identify those columns:

SELECT
    table_name,
    column_name,
    data_type,
    data_length,
    char_length
FROM dba_tab_columns
WHERE owner='TARGET_APP'
AND data_type IN ('NCHAR','NVARCHAR2','NCLOB')
ORDER BY table_name, column_id;

Then perform application-specific sampling of data expected to contain:

accented characters
non-Latin languages
symbols
supplementary Unicode characters

This is especially important when the Data Pump log reported any character-set conversion warning.

39. Application Validation Is Still Required

Database-level checks cannot prove that an application migration is functionally complete.

Perform application smoke tests covering:

login
read operations
writes
updates
deletes
sequence-generated keys
stored procedures
triggers
reports
batch jobs
interfaces
database links
Unicode data

Where the source database is unavailable, this becomes even more important because direct source-to-target row-count comparisons are impossible.

Use whatever independent evidence is available, including export logs, expected business totals and application-level reconciliation.

40. Unlock the Target Schema Only After Validation

If the account was created locked:

GRANT CREATE SESSION TO TARGET_APP;

ALTER USER TARGET_APP ACCOUNT UNLOCK;

Do this only when:

import errors are understood
invalid objects are resolved
indexes are usable
constraints are valid
jobs have been reviewed
DB links have been reviewed
statistics are ready
application testing can begin

41. Establish a New Recovery Point

Once the imported database has been validated, take a new RMAN backup.

This is particularly important after a large migration because it creates a clean recovery point containing the fully imported and validated schema.

For example:

BACKUP DATABASE PLUS ARCHIVELOG;

Use the organisation's normal RMAN retention, backup destination and recovery procedures.

A backup should also be restore-validated as part of production commissioning.

A Reusable Oracle Data Pump Import Checklist

Before every significant impdp migration, confirm:

  1. Preserve the original dump and generate a checksum; verify Oracle can access it.
  2. Inspect the dump header and source database version.
  3. Generate SQLFILE before executing any imported DDL.
  4. Identify source schemas, tablespaces, users, privileges and object types.
  5. Scan for edition-specific features, database links and scheduler jobs.
  6. Compare NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET.
  7. Stop and investigate ORA-39345 or other conversion warnings.
  8. Search for NCHAR, NVARCHAR2 and NCLOB when NCHAR sets differ.
  9. Size the target tablespace, TEMP, UNDO and underlying filesystem.
  10. Pre-create and lock the target schema where appropriate.
  11. Use REMAP_SCHEMA and REMAP_TABLESPACE deliberately.
  12. Generate a second SQLFILE using the final remapping parameters.
  13. Use a parameter file and meaningful JOB_NAME.
  14. Enable LOGTIME, METRICS and periodic STATUS.
  15. Monitor Data Pump jobs, V$SESSION_LONGOPS, filesystems and FRA.
  16. Do not start a duplicate import if a terminal connection is lost; check and reattach.
  17. Review every ORA error in the finished Data Pump log.
  18. Validate objects, indexes, constraints, privileges, jobs, links and hard-coded source references.
  19. Recompile invalid objects and gather appropriate optimizer statistics.
  20. Run application-level reconciliation and character-data testing.
  21. Unlock the application schema only after validation.
  22. Take and validate a fresh RMAN backup after the successful migration.

Recommended Baseline Parameter File

For a straightforward schema-to-schema migration, this is a useful starting template:

DIRECTORY=DPUMP_DIR
DUMPFILE=application_export.dmp
LOGFILE=import_application.log

JOB_NAME=IMP_APPLICATION_01

SCHEMAS=SOURCE_APP

REMAP_SCHEMA=SOURCE_APP:TARGET_APP
REMAP_TABLESPACE=SOURCE_TS:APP_DATA

EXCLUDE=USER
EXCLUDE=STATISTICS

TRANSFORM=STORAGE:N

DATA_OPTIONS=REJECT_ROWS_WITH_REPL_CHAR

PARALLEL=1

METRICS=YES
LOGTIME=ALL
STATUS=300

This should always be adjusted for the database edition, source metadata, application design and recovery requirements.

Final Thoughts

A successful Oracle Data Pump migration is not defined by whether impdp reaches:

Job successfully completed

It is successful when the database has been discovered, planned, imported, reconciled, validated and made recoverable.

The most valuable pre-import tools are often not complex:

DBMS_DATAPUMP.GET_DUMPFILE_INFO
SQLFILE
the Data Pump log
character-set comparison
tablespace capacity analysis

The 31 GB migration scenario behind this guide demonstrated why these checks matter. The dump itself revealed user definitions, grants, sequences, tables, procedures, triggers, indexes, constraints and statistics. More importantly, the Data Pump discovery run identified a source-versus-target national character-set difference before any application data was loaded.

Finding that issue during discovery is cheap.

Finding it after a production application has started using incorrectly converted data is not.

For production Oracle migrations, use Data Pump as a controlled migration workflow rather than simply a file-import utility.

How Onsys Can Help

Onsys Technologies provides Oracle database migration, upgrade, performance tuning, backup and recovery, health-check and managed DBA services. For complex Data Pump migrations, our approach includes source/dump discovery, target design, character-set assessment, migration execution, validation, performance checks and recovery verification before production handover.