1. Show DDL statements stored in export Data Pump dumpfile.
The Data Definition Language (DDL) statements that are stored in an export Data Pump dumpfile that was created with the Export Data Pump client (release 10.1.x to 11.1.x), can be extracted into a sqlfile with the parameter SQLFILE without actually importing the data. Any version of the Import Data Pump client can be used as long as this Import Data Pump client version is equal to or higher than the version of the Data Pump dumpfile. A connection to a target database is required, even though no data will be imported into that database. The SQL statements are displayed in the order in which import Data Pump would execute them during a real import job. Example:
-- create an export Data Pump dumpfile:
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp /
LOGFILE=expdp_s.log SCHEMAS=scott
-- create a script file of this Data Pump dumpfile with all DDL statements:
% impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp /
NOLOGFILE=y SQLFILE=impdp_s.sql FULL=y
Beware that any existing file that has a name matching the one specified with the SQLFILE parameter is overwritten.
A sample output of the sqlfile impdp_s.sql is:
-- new object type path is: SCHEMA_EXPORT/USER
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE;
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2303625');
COMMIT;
END;
/
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYSTEM
...
Note that the output needs to be manually converted in order to obtain a correct SQL script file:
- replace the lines with 'ALTER SESSION SET CURRENT_SCHEMA ...' and any lines with '-- CONNECT ...' with a correct CONNECT statement including the user's password, e.g.: CONNECT scott/tiger
- place two dash characters (-- = indication that comment line follows) in front of the anonymous PL/SQL blocks that may appear within the logfile for Streams and some other database options
After modification, the statements in the file can be run as a SQL script file. A sample output of the modified sqlfile impdp_s.sql is:
-- new object type path is: SCHEMA_EXPORT/USER
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE;
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
CONNECT SCOTT/tiger
-- BEGIN
-- sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
-- export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2303625');
-- COMMIT;
-- END;
-- /
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
CONNECT SYSTEM/manager
...
2. Show DDL statements stored in classic export dumpfile.
The Data Definition Language (DDL) statements that are stored in an export dumpfile that was created with the classic export client (release 7.3.x to 10.2.x), can be extracted into a logfile with the parameter SHOW without actually importing the data. Any version of the import client can be used as long as this import client version is equal to or higher than the version of the dumpfile. A connection to a target database is required, even though no data will be imported into that database. The SQL statements are displayed in the order in which import would execute them during a real import session. Example:
-- create an export dumpfile with the classic export client:
% exp system/manager FILE=exp_s.dmp LOG=exp_s.log OWNER=scott
-- create a logfile of this dumpfile with all DDL statements:
% imp system/manager FILE=exp_s.dmp LOG=imp_show.log FULL=y SHOW=y
Beware that any existing file that has a name matching the one specified with the LOG parameter is overwritten.
A sample output of the sqlfile imp_show.log is:
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. importing SCOTT's objects into SCOTT
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2301"
"131');"
"COMMIT; END;"
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER"
", "COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
"TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U"
"SERS" LOGGING NOCOMPRESS"
. . skipping table "BONUS"
...
Import terminated successfully without warnings.
Note that the output needs to be manually converted in order to obtain valid DDL statements:
- remove the header lines at the top of the file
- remove the lines that start with: . importing ... objects into ...
- remove the lines that start with: . . skipping table ...
- remove the space and double quote character at the start of each line
- remove the double quote character at the end of each line
- add SQL terminator after each statement: ;
- add PL/SQL terminator after each PL/SQL block: /
- replace the lines with 'ALTER SESSION SET CURRENT_SCHEMA ...' and any lines with '-- CONNECT ...' with a correct CONNECT statement including the user's password, e.g.: CONNECT scott/tiger
- concatenate lines that were hard split in the middle of a word or string at the 80th character, and re-insert the new line character at a correct place
- place two dash characters (-- = indication that comment line follows) in front of the anonymous PL/SQL blocks that may appear within the logfile for Streams and some other database options
- remove the last line at the end of the file.
After modification, the statements in the file can be run as a SQL script file. A sample output of the modified logfile imp_show.log is:
-- sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV',
-- 'CURRENT_SCHEMA'), export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2301131');
-- COMMIT; END;
-- /
CONNECT scott/tiger
CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER,
"COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE
(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"USERS" LOGGING NOCOMPRESS;
...
3. Show internal header information of export (DataPump) dumpfile.
Every export dumpfile starts with a header (usually 4 kb in size) which contains details about the dumpfile. Starting with Oracle10g Release 2 (10.2.0.1.0), this header information can be extracted from the dumpfile by calling the procedure DBMS_DATAPUMP.GET_DUMPFILE_INFO.
The following example creates a user defined procedure SHOW_DUMPFILE_INFO in the schema SYSTEM of an Oracle10g Release 2 or higher database, and which can be called to obtain the export dumpfile header details.
CONNECT system/manager
CREATE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir = directory object where dumpfile can be found
-- p_file = simple filename of export dumpfile (case-sensitive)
v_separator VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
v_path all_directories.directory_path%type := '?';
v_filetype NUMBER; -- 0=unknown 1=expdp 2=exp
v_fileversion VARCHAR2(15); -- 0.1=10gR1 1.1=10gR2 2.1=11g
v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
type valtype IS VARRAY(22) OF VARCHAR2(2048);
var_values valtype := valtype();
no_file_found EXCEPTION;
PRAGMA exception_init(no_file_found, -39211);
BEGIN
-- Show generic info:
-- ==================
dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile. Version: 19-MAR-2008');
dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
dbms_output.put_line('. ' ||
'Export dumpfile version: 7.3.4.0.0 or higher');
dbms_output.put_line('. ' ||
'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
dbms_output.put_line('Usage....: ' ||
'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
dbms_output.put_line('Example..: ' ||
'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
dbms_output.put_line(v_separator);
dbms_output.put_line('Filename.: ' || p_file);
dbms_output.put_line('Directory: ' || p_dir);
-- Retrieve Export dumpfile details:
-- =================================
SELECT directory_path INTO v_path FROM all_directories
WHERE directory_name = p_dir
OR directory_name = UPPER(p_dir);
dbms_datapump.get_dumpfile_info(
filename => p_file, directory => UPPER(p_dir),
info_table => v_info_table, filetype => v_filetype);
var_values.EXTEND(22);
FOR i in 1 .. 22 LOOP
BEGIN
SELECT value INTO var_values(i) FROM TABLE(v_info_table)
WHERE item_code = i;
EXCEPTION WHEN OTHERS THEN var_values(i) := '';
END;
END LOOP;
-- Show dumpfile details:
-- ======================
-- For Oracle10g Release 2 and higher:
-- dbms_datapump.KU$_DFHDR_FILE_VERSION CONSTANT NUMBER := 1;
-- dbms_datapump.KU$_DFHDR_MASTER_PRESENT CONSTANT NUMBER := 2;
-- dbms_datapump.KU$_DFHDR_GUID CONSTANT NUMBER := 3;
-- dbms_datapump.KU$_DFHDR_FILE_NUMBER CONSTANT NUMBER := 4;
-- dbms_datapump.KU$_DFHDR_CHARSET_ID CONSTANT NUMBER := 5;
-- dbms_datapump.KU$_DFHDR_CREATION_DATE CONSTANT NUMBER := 6;
-- dbms_datapump.KU$_DFHDR_FLAGS CONSTANT NUMBER := 7;
-- dbms_datapump.KU$_DFHDR_JOB_NAME CONSTANT NUMBER := 8;
-- dbms_datapump.KU$_DFHDR_PLATFORM CONSTANT NUMBER := 9;
-- dbms_datapump.KU$_DFHDR_INSTANCE CONSTANT NUMBER := 10;
-- dbms_datapump.KU$_DFHDR_LANGUAGE CONSTANT NUMBER := 11;
-- dbms_datapump.KU$_DFHDR_BLOCKSIZE CONSTANT NUMBER := 12;
-- dbms_datapump.KU$_DFHDR_DIRPATH CONSTANT NUMBER := 13;
-- dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
-- dbms_datapump.KU$_DFHDR_DB_VERSION CONSTANT NUMBER := 15;
-- For Oracle11gR1:
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT CONSTANT NUMBER := 16;
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
-- dbms_datapump.KU$_DFHDR_DATA_COMPRESSED CONSTANT NUMBER := 18;
-- dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED CONSTANT NUMBER := 19;
-- dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED CONSTANT NUMBER := 20;
-- For Oracle11gR2:
-- dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED CONSTANT NUMBER := 21;
-- dbms_datapump.KU$_DFHDR_ENCPWD_MODE CONSTANT NUMBER := 22;
-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 22;
dbms_output.put_line('Disk Path: ' || v_path);
IF v_filetype = 1 OR v_filetype = 2 THEN
-- Get characterset name:
BEGIN
SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
')' INTO var_values(5) FROM dual;
EXCEPTION WHEN OTHERS THEN null;
END;
IF v_filetype = 2 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
dbms_output.put_line(v_separator);
SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
'1', '1 (Direct Path)', var_values(13))
INTO var_values(13) FROM dual;
dbms_output.put_line('...Characterset ID.: ' || var_values(5));
dbms_output.put_line('...Direct Path.....: ' || var_values(13));
dbms_output.put_line('...Export Version..: ' || var_values(15));
ELSIF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
SELECT DECODE(var_values(1),
'0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
'1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
'2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
'3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
var_values(19)) INTO var_values(19) FROM dual;
SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
'1', '1 (Unknown)',
'2', '2 (None)',
'3', '3 (Password)',
'4', '4 (Dual)',
'5', '5 (Transparent)',
var_values(22)) INTO var_values(22) FROM dual;
dbms_output.put_line('...File Version....: ' || var_values(1));
dbms_output.put_line('...Master Present..: ' || var_values(2));
dbms_output.put_line('...GUID............: ' || var_values(3));
dbms_output.put_line('...File Number.....: ' || var_values(4));
dbms_output.put_line('...Characterset ID.: ' || var_values(5));
dbms_output.put_line('...Creation Date...: ' || var_values(6));
dbms_output.put_line('...Flags...........: ' || var_values(7));
dbms_output.put_line('...Job Name........: ' || var_values(8));
dbms_output.put_line('...Platform........: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance........: ' || var_values(10));
END IF;
dbms_output.put_line('...Language........: ' || var_values(11));
dbms_output.put_line('...Block size......: ' || var_values(12));
dbms_output.put_line('...Metadata Compres: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed.: ' || var_values(18));
dbms_output.put_line('...Metadata Encrypt: ' || var_values(19));
dbms_output.put_line('...Data Encrypted..: ' || var_values(20));
dbms_output.put_line('...Column Encrypted: ' || var_values(21));
dbms_output.put_line('...Encrypt.pwd. mod: ' || var_values(22));
IF v_fileversion = '2.1' or v_fileversion = '3.1' THEN
dbms_output.put_line('...Master Piece Cnt: ' || var_values(16));
dbms_output.put_line('...Master Piece Num: ' || var_values(17));
END IF;
END IF;
IF v_fileversion >= '1.1' THEN
dbms_output.put_line('...Job Version.....: ' || var_values(15));
END IF;
dbms_output.put_line('...Max Items Code..: ' ||
dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
END IF;
ELSE
dbms_output.put_line('Filetype.: ' || v_filetype);
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Not an export dumpfile.');
END IF;
dbms_output.put_line(v_separator);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Disk Path: ?');
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Directory Object does not exist.');
dbms_output.put_line(v_separator);
WHEN no_file_found THEN
dbms_output.put_line('Disk Path: ' || v_path);
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: File does not exist.');
dbms_output.put_line(v_separator);
END;
/
Note that this procedure can only be used in an Oracle10g Release 2 or any higher release database. The procedure makes a call to DBMS_DATAPUMP.GET_DUMPFILE_INFO which was introduced in release 10.2.0.1.0.
For details about the minimum requirements to run an Data Pump job, see:
Note:351598.1 "Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)"
Also note that the user SYSTEM should have been granted access to the referenced directory object if the SYSTEM user did not create the directory object. If the default Data Pump directory is used or if a user (like SYS) created a different directory object, then access should be granted. E.g.:
-- grant access to directory object:
CONNECT / as sysdba
GRANT read, write ON DIRECTORY my_dir TO system;
A sample screen output of this procedure in Oracle11g for an Export DataPump dumpfile is:
-- call procedure SHOW_DUMPFILE_INFO in SQL*Plus:
SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_s.dmp')
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 19-MAR-2008
Required.: RDBMS version: 10.2.0.1.0 or higher
. Export dumpfile version: 7.3.4.0.0 or higher
. Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: expdp_s.dmp
Directory: my_dir
Disk Path: /bugmnt7/em/celclnx7/user/expdp/work
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...File Version....: 2.1 (Oracle11g Release 1: 11.1.0.x)
...Master Present..: 1 (Yes)
...GUID............: AE9D4A8A85C6444F813600C00199745A
...File Number.....: 1
...Characterset ID.: 46 (WE8ISO8859P15)
...Creation Date...: Wed Mar 19 16:06:45 2008
...Flags...........: 2
...Job Name........: "SYSTEM"."SYS_EXPORT_SCHEMA_01"
...Platform........: x86_64/Linux 2.4.xx
...Instance........: ORCL
...Language........: WE8ISO8859P15
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Data Compressed.: 0 (No)
...Metadata Encrypt: 0 (No)
...Data Encrypted..: 0 (No)
...Master Piece Cnt: 1
...Master Piece Num: 1
...Job Version.....: 11.01.00.00.00
...Max Items Code..: 20
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
Most items are self-explanatory. Some additional details for specific items:
GUID : this value uniquely identifies an Export Data Pump dumpfile (set).
Flags : used internally.
Block Size : standard dumpfile header size is 4 kb (cannot be modified).
Direct Path : only used in a dumpfile created with the classic export client.
Note that the procedure DBMS_DATAPUMP.GET_DUMPFILE_INFO of an Oracle10g Release 2 database will show less items for this Oracle11g dumpfile. Also note that older version dumpfiles, like one created on an Oracle10g Release 2 source database (file version: 1.1) or created on an Oracle10g Release 1 source database (dumpfile version: 0.1), will show less items.
A sample screen output of this procedure in Oracle10g for a classic Export dumpfile (note that the extracted details from a classic export dumpfile are limited):
-- call procedure SHOW_DUMPFILE_INFO in SQL*Plus:
SET serveroutput on SIZE 1000000
exec show_dumpfile_info('my_dir', 'exp_s.dmp')
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 19-MAR-2008
Required.: RDBMS version: 10.2.0.1.0 or higher
. Export dumpfile version: 7.3.4.0.0 or higher
. Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: exp_s.dmp
Directory: my_dir
Disk Path: D:/Work/Expdp
Filetype.: 2 (Classic Export dumpfile)
----------------------------------------------------------------------------
...Characterset ID.: 1 (US7ASCII)
...Direct Path.....: 0 (Conventional Path)
...Export Version..: EXPORT:V10.02.01
----------------------------------------------------------------------------
PL/SQL procedure successfully completed.
To grant other users to run the procedure in SQL*Plus:
CONNECT system/manager
GRANT execute ON show_dumpfile_info TO scott;
CONNECT scott/tiger
SET serveroutput on SIZE 1000000
exec system.show_dumpfile_info('my_dir', 'expdp_s.dmp')
To remove the procedure:
CONNECT system/manager
DROP PROCEDURE show_dumpfile_info;
4. Alternative ways to obtain dumpfile details.
For older classic export dumpfiles on Unix systems, you can also get some unformatted header information with:
% cat exp_s.dmp | head | strings
.EXPORT:V10.02.01
DSYSTEM
RUSERS
8192
Thu Dec 13 13:36:26 2007exp_s.dmp
#G#G
#G#G
+00:00
BYTE
For DataPump dumpfiles, you can also create a trace file with the file header information with:
-- create a SQL file with TRACE parameter value 100300 (trace file layer);
-- although this impdp job will give an error (ORA-39166: Object ... was not found)
-- a trace file will be written with the file header details we are interested in:
D:/Work/Expdp> impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s%U.dmp
NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=notexist TRACE=100300
The header details are located in the DataPump Master trace file: [SID]dm[number]_[PID].trc
This file is written to the directory specified by init.ora/spfile parameter: BACKGROUND_DUMP_DEST.
A sample content of the trace file is:
KUPF: In kupfioReadHeader...
KUPF: 16:31:56.121: newImpFile: EXAMINE_DUMP_FILE
KUPF: 16:31:56.121: ......DB Version = 10.02.00.03.00
KUPF: 16:31:56.121: File Version Str = 1.1
KUPF: 16:31:56.121: File Version Num = 257
KUPF: 16:31:56.131: Version CapBits1 = 32775
KUPF: 16:31:56.131: ......Has Master = 0
KUPF: 16:31:56.131: ........Job Guid = B94302C5DAB344E1876105E3295269C6
KUPF: 16:31:56.131: Master Table Pos = 0
KUPF: 16:31:56.131: Master Table Len = 0
KUPF: 16:31:56.131: .....File Number = 1
KUPF: 16:31:56.131: ......Charset ID = 46
KUPF: 16:31:56.131: ...Creation date = Thu Dec 13 15:25:32 2007
KUPF: 16:31:56.141: ...........Flags = 2
KUPF: 16:31:56.141: ......Media Type = 0
KUPF: 16:31:56.141: ........Job Name = "SYSTEM"."SYS_EXPORT_FULL_01"
KUPF: 16:31:56.141: ........Platform = IBMPC/WIN_NT-8.1.0
KUPF: 16:31:56.141: ........Language = WE8ISO8859P15
KUPF: 16:31:56.141: .......Blocksize = 4096
KUPF: 16:31:56.141: newImpFile: file; D:/Work/Expdp/expdp_s01.dmp, FID; 1
KUPF: 16:31:56.151: In expandwildcard. wildcard count = 1
KUPF: 16:31:56.151: Next wildcard expansion = expdp_s02.dmp
KUPF: In kupfxExmDmpFile...
KUPF: In kupfuExmDmpFile...
KUPF: In kupfioReadHeader...
KUPF: 16:31:56.191: newImpFile: EXAMINE_DUMP_FILE
KUPF: 16:31:56.191: ......DB Version = 10.02.00.03.00
KUPF: 16:31:56.191: File Version Str = 1.1
KUPF: 16:31:56.191: File Version Num = 257
KUPF: 16:31:56.191: Version CapBits1 = 32775
KUPF: 16:31:56.191: ......Has Master = 1
KUPF: 16:31:56.201: ........Job Guid = B94302C5DAB344E1876105E3295269C6
KUPF: 16:31:56.201: Master Table Pos = 2
KUPF: 16:31:56.201: Master Table Len = 7620344
KUPF: 16:31:56.201: .....File Number = 2
KUPF: 16:31:56.201: ......Charset ID = 46
KUPF: 16:31:56.201: ...Creation date = Thu Dec 13 15:26:53 2007
KUPF: 16:31:56.201: ...........Flags = 2
KUPF: 16:31:56.201: ......Media Type = 0
KUPF: 16:31:56.211: ........Job Name = "SYSTEM"."SYS_EXPORT_FULL_01"
KUPF: 16:31:56.211: ........Platform = IBMPC/WIN_NT-8.1.0
KUPF: 16:31:56.211: ........Language = WE8ISO8859P15
KUPF: 16:31:56.211: .......Blocksize = 4096
KUPF: 16:31:56.211: newImpFile: file; D:/Work/Expdp/expdp_s02.dmp, FID; 2
KUPF: 16:31:56.221: In expandwildcard. wildcard count = 1
KUPF: 16:31:56.221: Next wildcard expansion = expdp_s03.dmp
KUPF: In kupfxExmDmpFile...
KUPF: In kupfuExmDmpFile...
KUPF: In kupfioReadHeader...
KUPF: 22:31:56.261: In invalidateWildCard. FID = 1,lastFile = 2,Reason = 99
扫一扫
1306
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
