Problem to import a .dmp file using imp command
573608Jun 10 2009 — edited Jun 10 2009Hi All,
I have created a procedure to EXPORT data from database using the BDMS_DATAPUMP utility. Code is given below
------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE DATABASE_BACKUP(p_comp varchar2,p_schema varchar2) AS
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
FILE_NAME1 VARCHAR2(50) ;
FILE_NAME2 VARCHAR2(50) ;
comp_name VARCHAR2(20);
vFile_name VARCHAR2(1);
BEGIN
comp_name := Trim(p_comp);
BEGIN
SELECT FILE_NAME
INTO vFile_name
FROM JOB_PARAM
WHERE SRL_NO = (SELECT MAX(SRL_NO) FROM JOB_PARAM);
END;
IF vFile_name ='D' THEN
FILE_NAME1 := comp_name||'_'||to_char(sysdate,'YYYY_MM_DD')||'.dmp';
FILE_NAME2 := comp_name||'_'||to_char(sysdate,'YYYY_MM_DD')||'.log';
ELSIF vFile_name ='H' THEN
FILE_NAME1 := comp_name||'_'||to_char(sysdate,'YYYY_MM_DD')||'_'||to_char(sysdate,'HH_MI_SS')||'.dmp';
FILE_NAME2 := comp_name||'_'||to_char(sysdate,'YYYY_MM_DD')||'_'||to_char(sysdate,'HH_MI_SS')||'.log';
ELSIF vFile_name ='W' THEN
FILE_NAME1 := comp_name||'_'||to_char(sysdate,'YYYY_MM_DD')||'.dmp';
FILE_NAME2 := comp_name||'_'||to_char(sysdate,'YYYY_MM_DD')||'.log';
END IF;
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'DATA_EXPORT_P',
version => 'LATEST');
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => FILE_NAME1,
directory => 'BACKUP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => FILE_NAME2,
directory => 'BACKUP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => '= '''||p_schema||'''');
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);
END DATABASE_BACKUP;
--------------------------------------------------------------------------------------------------------------------------------------------
and this is working fine it creates a dmp file as 'comp1_2009_06_10_11_54_18.DMP'.
Now I want to import this file into a new user using the following commad from command promt
imp username/password file=comp1_2009_06_10_11_54_18.DMP log=imp1.log full=yes
but it gives an error
IMP-00010 not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
Please tell me how it can resolve ASAP.
Thanks in advance
Regards
Sanjit