Identify the Tablespace names from EXPDP dump file

 I got  request from development team to import some old data in UAT region.The expdp dump file received from backup team was without  log file and i have no access to the export file's source DB. I wanted to import that dumpfile into UAT DB and i do not know the tablespace names required to do so.

Solution:

Use the sqlfile option present in impdp utility. This option will write all the SQL DDL to a specified file instead of executing them.

$ impdp scott/tiger directory=exp_dir dumpfile=scott.dmp sqlfile=script.sql

Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 20 February, 2014 21:34:36

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTT"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_SQL_FILE_FULL_01":  scott/******** directory=exp_dir dumpfile=scott.dmp sqlfile=script.sql

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/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTT"."SYS_SQL_FILE_FULL_01" successfully completed at 21:34:53

The content of the scripts.sql file would be like this.

-- CONNECT SCOTT

ALTER SESSION SET EDITION = "ORA$BASE";

-- new object type path: SCHEMA_EXPORT/USER

-- CONNECT SYSTEM

ALTER SESSION SET EDITION = "ORA$BASE";

 CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:D846EA3EB87287A3AED08AF38EB0B4F640F49A9A4A972108BF3917B769;DB1B37F84BDF15E6'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP";

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT UNLIMITED TABLESPACE TO "SCOTT";

-- new object type path: SCHEMA_EXPORT/ROLE_GRANT

 GRANT "DBA" TO "SCOTT";

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

 ALTER USER "SCOTT" DEFAULT ROLE ALL;

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT SCOTT

ALTER SESSION SET EDITION = "ORA$BASE";

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PROD9.DIAMOND.COM', inst_scn=>

'11626845804212');

COMMIT;

END;

/

 .

.

.

.

.

.

After finding all the required tablespaces i used REMAP_TABLESPACE  and then import the dump file .

One can manually create same  tablespaces and import the dump file.