Error executing DBMS_DATAPUMP
in [Oracle]
|
From: sjoshi on 22 Jan 2007 12:09 I'm trying to execute a simple script and it keeps telling me this: ERROR at line 1: ORA-39001: invalid argument value ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 2926 ORA-06512: at "SYS.DBMS_DATAPUMP", line 3958 ORA-06512: at line 20 The script is DECLARE jobNo NUMBER; ind NUMBER; schemas VARCHAR2(30); percent_done NUMBER; -- Percentage of job complete job_state VARCHAR2(30); -- Keeps track of job state le ku$_LogEntry; -- work-in-progress and error messages js ku$_JobStatus; -- Job status from get_status jd ku$_JobDesc; -- Job description from get_status sts ku$_Status; -- Status object returned by get_status BEGIN jobNo := DBMS_DATAPUMP.OPEN('EXPORT', 'SCHEMA', NULL, 'DAO2112007-114594','LATEST'); DBMS_OUTPUT.PUT_LINE('Handle: ' || jobNo); DBMS_DATAPUMP.ADD_FILE(jobNo,'SIEMENSSITE.dmp','EXPSUNIT'); DBMS_OUTPUT.PUT_LINE('Added file'); DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','SIEMENSSITE, SIEMENSSITE_SCHEMA'); DBMS_OUTPUT.PUT_LINE('Added filter for schema list'); DBMS_DATAPUMP.SET_PARAMETER(jobNo, 'FLASHBACK_SCN', 15383693); DBMS_OUTPUT.PUT_LINE('Set parameter for SCN'); DBMS_OUTPUT.PUT_LINE('Starting job...'); DBMS_DATAPUMP.START_JOB(jobNo); percent_done := 0; job_state := 'UNDEFINED'; while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop DBMS_DATAPUMP.get_status(jobNo, DBMS_DATAPUMP.ku$_status_job_error + DBMS_DATAPUMP.ku$_status_job_status + DBMS_DATAPUMP.ku$_status_wip,-1,job_state,sts); js := sts.job_status; -- As the percentage-complete changes in this loop, the new value displays. if js.percent_done != percent_done then DBMS_OUTPUT.PUT_LINE('*** Job percent done = ' || to_char(js.percent_done)); percent_done := js.percent_done; end if; -- Displays any work-in-progress or error messages received for the job. if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0) then le := sts.wip; else if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) then le := sts.error; else le := null; end if; end if; if le is not null then ind := le.FIRST; while ind is not null loop DBMS_OUTPUT.PUT_LINE(le(ind).LogText); ind := le.NEXT(ind); end loop; end if; end loop; -- When the job finishes, display status before detaching from job. DBMS_OUTPUT.PUT_LINE('Job has completed'); DBMS_OUTPUT.PUT_LINE('Final job state = ' || job_state); DBMS_DATAPUMP.DETACH(jobNo); END; / Any hints are appreciated. Also how do I find out the job number of an already submitted job since if I execute the script again it tells that job already exists. I would like to stop that job using DBMS_DATAPUMP.Stop thanks Sunit
From: MTNorman on 22 Jan 2007 12:25 sjoshi wrote: > Any hints are appreciated. Also how do I find out the job number of an > already submitted job since if I execute the script again it tells that > job already exists. I would like to stop that job using > DBMS_DATAPUMP.Stop DBA_DATAPUMP_JOBS lists all active jobs. You can attach to the job and stop it using dbms_datapump package.
From: sjoshi on 22 Jan 2007 14:13 Got the ADD_FILE to work. It seems I need to specify all args as in: DBMS_DATAPUMP.ADD_FILE(jobNo,'SIEMENSSITE.dmp','EXPSUNIT', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE); Now this part fails. I'm trying to use SCHEMA_LIST DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','SJSITE, SJSITE_SCHEMA'); thanks Sunit MTNorman wrote: > sjoshi wrote: > > Any hints are appreciated. Also how do I find out the job number of an > > already submitted job since if I execute the script again it tells that > > job already exists. I would like to stop that job using > > DBMS_DATAPUMP.Stop > > DBA_DATAPUMP_JOBS lists all active jobs. You can attach to the job and > stop it using dbms_datapump package.
From: Andy Hassall on 22 Jan 2007 14:51 On 22 Jan 2007 11:13:53 -0800, "sjoshi" <sjoshi(a)ingr.com> wrote: >Got the ADD_FILE to work. It seems I need to specify all args as in: > >DBMS_DATAPUMP.ADD_FILE(jobNo,'SIEMENSSITE.dmp','EXPSUNIT', NULL, >DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE); > >Now this part fails. I'm trying to use SCHEMA_LIST > >DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','SJSITE, >SJSITE_SCHEMA'); The filter parameters need to be valid SQL expressions, so something like: DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST', 'IN (''SJSITE'', ''SJSITE_SCHEMA'')'); -- Andy Hassall :: andy(a)andyh.co.uk :: http://www.andyh.co.uk http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
From: sjoshi on 22 Jan 2007 15:01
I tried this and it worked. Is this fine ? DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','''ERLS'', ''ERLS_SCHEMA''', NULL, NULL); thanks Sunit Andy Hassall wrote: > On 22 Jan 2007 11:13:53 -0800, "sjoshi" <sjoshi(a)ingr.com> wrote: > > >Got the ADD_FILE to work. It seems I need to specify all args as in: > > > >DBMS_DATAPUMP.ADD_FILE(jobNo,'SIEMENSSITE.dmp','EXPSUNIT', NULL, > >DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE); > > > >Now this part fails. I'm trying to use SCHEMA_LIST > > > >DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST','SJSITE, > >SJSITE_SCHEMA'); > > The filter parameters need to be valid SQL expressions, so something like: > > DBMS_DATAPUMP.METADATA_FILTER(jobNo, 'SCHEMA_LIST', > 'IN (''SJSITE'', ''SJSITE_SCHEMA'')'); > > -- > Andy Hassall :: andy(a)andyh.co.uk :: http://www.andyh.co.uk > http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool |