How to get the Master Table from a Data Pump expdp?

Interesting question a customer had last week during the Upgrade Workshop in Munich. He’s getting export dump files from several customers and often not much information describing the contents. So how can ge find out what’s in there, which was the source characterset etc.

This seems to be a simple question but it did cost me a few searches and tests to come back with some (hopefully) useful information.

First attempt: $strings expdp.dmp > outexpdp.txt

I bet there are better ways to do this but in my case this will give me:

"APP"."SYS_EXPORT_SCHEMA_01"
x86_64/Linux 2.4.xx
WE8ISO8859P15
LBB EMB GHC JWD SD EBE WMF DDG JG SJH SRH JGK CL EGM BJM RAP RLP RP KR PAR MS MRS JLS CET HLT
10.02.00.05.00

This does look interesting as it tells me the exporting user (‘APP’) , the source OS and – more important – the characterset of the source database (WE8ISO8859P15).

But Data Pump has also a Master Table – and how do I read this table? We’ll do a dummy import with impdp and keep the master table 🙂

impdp system/oracle dumpfile=app.dmp SQLFILE=sql.txt nologfile=Y keep_master=y directory=DATA_PUMP_DIR

And voilà … we have a sql file with all DMLs – and we have the master table:

Import: Release 11.2.0.2.0 - Production on Tue Jul 19 09:56:05 2011

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, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_02":  system/******** dumpfile=app.dmp SQLFILE=sql.txt logfile=nologfile keep_master=y:q!
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_SQL_FILE_FULL_02" successfully completed at 09:56:06

So the master table “SYSTEM”.”SYS_SQL_FILE_FULL_02″ is still kept and can be read to access information about the dump file.

16-AUG-2011:
Thanks to Marco Patzwahl from MuniQSoft for correcting my example to:
impdp system/oracle
dumpfile=app.dmp SQLFILE=sql.txt nologfile=Y keep_master=y directory=DATA_PUMP_DIR
Using LOGFILE=NOLOGFILE will simply create a logfile called NOLOGFILE.log which was not my intention. And not specifying the directory will require that you start impdp from the actual Data Pump OS directory – by default $ORACLE_BASE/admin/<SID>/dpdump.