Oracle DBA Lesson 86 Part 2 — Preview the import and validate the copy

A Data Pump import reads a binary export dump and creates objects and loads data in the connected target database. A useful schema copy needs the intended owner, storage, data and access. Preview the definitions, import into a prepared target, then validate the resulting copy.

Oracle DBA Lesson 86 Part 2 — Preview the import and validate the copy

This is the second and final part of Lesson 86. Part 1 produced a traceable owned-schema export. The source is APP_USER, the copy is APP_COPY, and the existing dump is app_user_01.dmp.

Prepare the target and preserve the source

Prepare the target before the import:

• An existing, empty local target user APP_COPY, with the required object-creation privileges, the intended default and temporary tablespaces, and sufficient quota. The owned-schema export from Part 1 may not contain the metadata needed to create a user automatically.
• A dedicated IMPORT_USER for this cross-schema operation, with DATAPUMP_IMP_FULL_DATABASE, access to the required directory, and the privileges and quota needed for its Data Pump control table. This role is powerful. Oracle also permits an unprivileged user to remap another schema into its own schema, subject to the documented restrictions. That is a different execution identity from this import.
• An existing DP_DIR directory object mapping to the protected physical server folder that contains the dump. The database account needs the directory grants. The database OS account needs filesystem access and space for output. The files referenced here are server files.
• Dictionary access for the validation queries. The later reader test uses the intended reader identity.

Record the database service and container, the source schema, the export time, the dump identity, and the source inventory. Preserve the dump and the original schema. This scope uses ordinary relational tables and stored code. User-defined object types, external dependencies, encryption, and cross-release movement need additional planning. For object types remapped within the same database, review the documented OID collision restriction and any required TRANSFORM=OID:N setting before generating the preview and import commands.

See Data Pump overview.

Preview the definitions

Run the client command from an operating-system shell, using the intended service alias and supplying the password at the prompt:

impdp import_user@APP_SERVICE \
  directory=DP_DIR dumpfile=app_user_01.dmp \
  remap_schema=APP_USER:APP_COPY \
  sqlfile=app_copy_preview.sql \
  logfile=app_copy_preview.log

Parameter	Meaning
APP_SERVICE	The database service receiving the import operation. Resolve and verify the intended container.
DIRECTORY=DP_DIR	Server directory used for the dump and the shown SQL and log output. Individual file parameters can specify another directory object.
DUMPFILE	The unchanged binary dump from the completed export.
REMAP_SCHEMA=APP_USER:APP_COPY	Load source-owned objects under the target owner, subject to remapping restrictions.
SQLFILE	Write the DDL that Import prepares under these parameters to a text file for inspection. The application DDL is not executed and table data is not loaded in this preview.
LOGFILE	A separate log for the preview job.

Choose fresh SQL and log filenames. Existing matching files can be overwritten. Inspect the SQL file in its mapped server directory. Review:

Area	What to check
Ownership	The intended APP_COPY names and any user-level definitions present in the dump.
Storage	Tablespace names exist and are appropriate, and target quota and capacity are sufficient. If needed, review REMAP_TABLESPACE and regenerate the preview.
Access	Object and system grants, grantees, synonyms, and other access relationships match the copy's purpose.
Dependencies	Source-qualified names, stored code strings, external references, and connections point to intended resources.

Resolve the configuration through reviewed import parameters and regenerate the preview. Editing the generated SQL file alone does not change a later impdp operation. Treat SQLFILE as inspection material: it can contain anonymous PL/SQL blocks that Oracle says not to run directly. It is not a complete standalone application deployment script.

See Data Pump Import.

Why ownership remapping needs a dependency review

REMAP_SCHEMA changes the target owner of imported objects. Oracle documents that certain schema references embedded in definitions of types, views, procedures, and packages may be missed. For triggers, the remap affects only the trigger owner.

A procedure proposed for APP_COPY could contain this SQL string:

'SELECT COUNT(*) FROM APP_USER.EMPLOYEES'

That literal names the source table. A procedure owned by APP_COPY could therefore still access APP_USER.EMPLOYEES when it runs, if its privileges permit. Review and adapt the actual definition, then test the intended behavior. A VALID object status describes object validity. Determine the runtime data source through code and dependency review and through meaningful tests.

Create the copy

Proceed after reviewing the preview and checking that the target application schema is empty. Keep the reviewed parameters, omit SQLFILE, and use a new import log:

impdp import_user@APP_SERVICE \
  directory=DP_DIR dumpfile=app_user_01.dmp \
  remap_schema=APP_USER:APP_COPY \
  logfile=app_copy_import.log

This operation creates objects and loads rows. Import DDL commits. ROLLBACK cannot undo the operation as a whole. Keep a scoped reset plan for the target.

For this normal import, the default TABLE_EXISTS_ACTION=SKIP leaves an existing table unchanged and moves on to another object. CONTENT=DATA_ONLY has a different default, APPEND, and is not selected here. Investigate unexpected target objects before starting or retrying. Do not add TRUNCATE or REPLACE just to bypass collisions: those options remove existing data or objects. If a partial import must be retried, first reconcile the log and the actual target contents with the reset plan.

See COMMIT and Data Pump Import.

Validate what arrived

Read the final import outcome and account for every error, warning, and skipped object. Reconcile the expected object inventory. Finishing the import process is only one part of the validation.

Query the copy's objects:

SELECT object_type, status, COUNT(*) AS objects
FROM dba_objects
WHERE owner = 'APP_COPY'
GROUP BY object_type, status
ORDER BY object_type, status;

A matching inventory can show:

OBJECT_TYPE	STATUS	OBJECTS
PROCEDURE	VALID	1
TABLE	VALID	4
VIEW	VALID	1

Investigate unexpected invalid objects and missing types or counts. Check object names and definitions against the inventory too: totals alone can hide a missing object and an unintended extra object. A connected owner can use USER_OBJECTS without an owner filter for its own inventory.

See ALL_OBJECTS.

For the employee table:

SELECT COUNT(*) AS employee_count,
       SUM(salary) AS salary_total
FROM app_copy.employees;

Compare with the source values recorded for the export snapshot:

COMPARE	EMPLOYEE_COUNT	SALARY_TOTAL
Source export snapshot	100	1,250,000
APP_COPY	100	1,250,000

Equal counts and sums are useful screening checks. Critical data needs the appropriate key, row, and value comparisons and domain checks. If the source changed after export, compare against its captured export-time values or another consistent reference.

Check constraints and grants:

SELECT table_name, constraint_name, constraint_type, status, validated
FROM dba_constraints
WHERE owner = 'APP_COPY'
ORDER BY table_name, constraint_name;

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

Interpret STATUS and VALIDATED against the intended design: check whether constraints are enabled and whether existing rows have been validated. Resolve unintended differences, including disabled constraints and missing or excessive grants.

See DBA_CONSTRAINTS and ALL_CONSTRAINTS.

Connect as the intended reader, such as APP_READER, and run a meaningful application query:

SELECT department_id, COUNT(*) AS employees
FROM app_copy.employees
GROUP BY department_id;

The result should match the expected department distribution. Exercise the required stored procedures, views, synonyms, and other application paths with the intended accounts. Confirm the imported code accesses the copy's intended data and resources. An untested path stays outside the accepted result until it is tested.

Quiz

1. What does SQLFILE do in this example?

Loads only table rows. Executes all DDL but skips the log. Writes the prepared DDL for inspection without importing the application objects or data. Converts the binary dump into a backup.

2. Does REMAP_SCHEMA guarantee every reference inside stored code points to the new owner?

Yes, including every string. No. Review source-qualified references and test behavior. Only when the dump has one file. Only when the source user is connected.

3. An unexpected table already exists in the target. What should you do first?

Add REPLACE immediately. Ignore it because rows always overwrite. Delete the source table. Investigate the target and the log, then use the reviewed retry and reset plan.

4. Counts and salary totals match. What follows?

Continue the required key, value, object, access, and application checks. All possible row differences are excluded. Every procedure uses the intended owner. Grants are certainly correct.

5. Which is the strongest handoff basis?

The client exited. The dump filename exists. The log is reviewed, scope and data are reconciled, required constraints and grants are checked, and application paths are tested. The object view contains at least one valid table.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily