Can we reuse a .dmp file(expdp) to perform import using data pump utility

datapumpimportoracle-11g

I'm an accidental DBA. We use Oracle 11g database. I had to do a database refresh in the test environment. I created a .dmp file using expdp command and performed an import using impdp command a week ago. I had to make some schema changes in test and then the department decided to revert to how it was before we made changes to the schema. The department wants me to perform impdp using the old .dmp file that I created a week ago. Can I re-use this file to do the import? Can the .dmp files be reused as many times as we can?

Best Answer

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.

Related Solutions

When using expdp to create a snapshot of an oracle schema, how to ensure that no post-export additions are retained upon using impdp to revert

As always with Oracle, there are many ways to reach your goal. You might want to check out flashback database. This enables you to restore your database in a previously saved state and might be the easiest in your case. Using Flashback Database and Restore Points

Drop user cascade could also do the trick, followed by impdb as you described. What was the problem with the drop user action?

Try flashback database.

How to find the schema name of a data pump dmp file

Use the sqlfile= parameter of impdp to generate a file containing all of the DDL/DML in the dump.

For example:

[oracle@oel61 ~]$ impdp phil/phil directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt

Import: Release 11.2.0.2.0 - Production on Wed Mar 13 15:15:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "PHIL"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "PHIL"."SYS_SQL_FILE_FULL_01":  phil/******** directory=oracledmp dumpfile=phil.dmp logfile=phil.log sqlfile=philddl.txt 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PHIL"."SYS_SQL_FILE_FULL_01" successfully completed at 15:15:05

[oracle@oel61 ~]$

Looking for CREATE USER DDL statements in the file should show you what schemas are needed in order to perform an import.

For example, from my test dump:

-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "PHIL" IDENTIFIED BY VALUES 'S:924B2E756404611021428644B4DF06A4A7BAB886837FCCFA510151E0FC44;181446AE258EE2F6'
      DEFAULT TABLESPACE "PHILDATA"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "PHIL";
GRANT CREATE SESSION TO "PHIL";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "PHIL";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "PHIL" DEFAULT ROLE ALL;

The only drawback is that if the .dmp file is large the resulting SQL dump will be huge.

Best Answer

Related Solutions

When using expdp to create a snapshot of an oracle schema, how to ensure that no post-export additions are retained upon using impdp to revert

How to find the schema name of a data pump dmp file

Related Question