Database Software

With a DATA_PUMP_DIR showing zero bytes used within a 100+GB volume, i see log entry
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
when exporting a 40GiB database called CANCERLANDSCAPE,
> select OWNER,sum(bytes)/1024/1024/1000 as GiB from dba_segments where owner in ('CANCERLANDSCAPE') group by owner;
CANCERLANDSCAPE 40.5554375

I know the DATA_PUMP_DIR volume has at least 110GB FREE because i deleted files
totalling that amount.
> SELECT SUM(BYTES)/1024/1024/1024 AS FILES_SIZE FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'); = 111.3647664431482553482055664062

1. Why would there be a space related err? What's going on?
2. I looked in the in the oci console for monitoring metrics and did not spot anything obvious. In V$DIAG_ALERT_EXT I see rows of Data Pump modules in an alert table, with trace message text
3. Looking at the 2 trace files mentioned , i see a few clues following , but i'm not sure what it means, any help is appreciated to resolve the fatal error, thanks in advance! clues :
   1. WARNING:1 Oracle process running out of OS kernelI/O resources request_aiolimit=384 set_aiolimit=0
      ORA-39078: unable to dequeue message for agent from queue ""
      ORA-04067: not executed, stored procedure "SYS.KUPC$C_1240459289_0321014 341_0" does not exist

> select * from V$DIAG_ALERT_EXT where module_id like 'Data Pump%' order by originating_timestamp desc;
"DM00 stopped with pid=1953, OS id=359330, job CANCERLANDSCAPE.expls2_CANCERLANDSCAPE_20250320_194008 =
"Errors in file /u02/app/oracle/diag/rdbms/efz1pod/efz1pod7/trace/efz1pod7_dw09_360773.trc:
ORA-39078: unable to dequeue message for agent from queue ""
ORA-06512: at "SYS.KUPW$WORKER", line 2946
ORA-06512: at "SYS.KUPW$WORKER", line 15455
ORA-06512: at "SYS.KUPC$QUEUE_INT", line 830
ORA-06512: at "SYS.KUPC$QUE_INT", line 716
ORA-25326: Array dequeue operation failed for message at index 1.
ORA-04067: not executed, stored procedure "SYS.KUPC$C_1240459289_0321014341_0" does not exist
ORA-06512: at "SYS.DBMS_AQ", line 1181
ORA-06512: at "SYS.KUPC$QUE_INT", line 688
ORA-06512: at "SYS.KUPC$QUE_INT", line 954
ORA-06512: at "SYS.KUPC$QUE_INT", line 2463
ORA-06512: at line 1
ORA-06512: at "SYS.KUPC$QUEUE_INT", line 819
ORA-06512: at "SYS.KUPC$QUEUE", line 56
ORA-06512: at "SYS.KUPW$WORKER", line 15019
ORA-06512: at "SYS.KUPW$WORKER", line 2873
ORA-06512: at line 2
"
"DW09 terminating with fatal err=39078, pid=2504, wid=10, job CANCERLANDSCAPE.expls2_CANCERLANDSCAPE_20250320_194008
"Errors in file /u02/app/oracle/diag/rdbms/efz1pod/efz1pod7/trace/efz1pod7_dw05_360761.trc:
ORA-31673: worker process interrupt for normal exit by master process
ORA-06512: at "SYS.KUPF$FILE", line 9485
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPF$FILE", line 9422
ORA-06512: at line 1
ORA-06512: at "SYS.KUPW$WORKER", line 2946
ORA-06512: at "SYS.KUPW$WORKER", line 15455
ORA-06512: at "SYS.KUPW$WORKER", line 4511
ORA-06512: at "SYS.KUPW$WORKER", line 17056
ORA-06512: at "SYS.KUPW$WORKER", line 12846
ORA-06512: at "SYS.KUPF$FILE", line 10994
ORA-06512: at "SYS.KUPF$FILE_INT", line 1368
ORA-06512: at "SYS.KUPF$FILE", line 10935
ORA-06512: at "SYS.KUPW$WORKER", line 12563
ORA-06512: at "SYS.KUPW$WORKER", line 16876
ORA-06512: at "SYS.KUPW$WORKER", line 4418
ORA-06512: at "SYS.KUPW$WORKER", line 15177
ORA-06512: at "SYS.KUPW$WORKER", line 2873
ORA-06512: at line 2

> select file_name,function_name,payload,component_name from v$diag_trace_file_contents where trace_filename = 'efz1pod7_dw05_360761.trc' order by line_number;

dbkt.c dbktWriteUserEnvChgBucket "*** ACTION NAME:(expls2_CANCERLANDSCAPE_20250320_194008) 2025-03-21T01:44:13.122805+00:00
" rdbms_uts
dbkt.c dbktWriteUserEnvChgBucket "
" rdbms_uts
kwqa.c kwqaGetKsqWithInfo KWQA_ENQ_BLOCKER: inst=0, sid=0, serial=0, pid=, heldsec=0, AQ
kwqa.c kwqaGetKsqWithInfo ", heldmode=0, reqmode=0, blckflags=0
" AQ
kwqa.c kwqalqu "kwqalqu: ADD SUBSCRIBER blocker: sid=0, serial#=0, qschema#=SYS, queue#=KUPC$C_1240459289_0321014341_0, qtobjn#=213324
" AQ
dbkt.c dbktWriteTimestampWCdbInfo "
*** 2025-03-21T01:45:49.127081+00:00 (G41C804E316E971_HEALTHSCIDATADEV1HDWLS(732))
" rdbms_uts
ksdf.c ksdwviz_i "WARNING:1 Oracle process running out of OS kernelI/O resources request_aiolimit=384 set_aiolimit=0
" ksd_uts
dbkt.c dbktWriteTimestampWCdbInfo "
*** 2025-03-21T01:50:04.943026+00:00 (G41C804E316E971_HEALTHSCIDATADEV1HDWLS(732))
" rdbms_uts
dbkt.c dbktPri ORA-31673: worker process interrupt for normal exit by master process rdbms_uts
dbkt.c dbktPri "
" rdbms_uts
dbkt.c dbktPri ORA-06512: at "SYS.KUPF$FILE", line 9485 rdbms_uts

> select file_name,function_name,payload,component_name from v$diag_trace_file_contents where trace_filename = 'efz1pod7_dw09_360773.trc' order by line_number;
ksdf.c ksdwviz_i "29913 error message received from server=3.232(P06G, instance 3) qref:0x5d8cce1138 qrser:1581100551 qrseq:7 mh:0x7cf74db090
" ksd_uts
ksdf.c ksdwviz_i "slvresil_diag9-qerpxFetch: dss=0, dsi=0
" ksd_uts
dbkt.c dbktWriteTimestampWCdbInfo "
*** 2025-03-21T01:50:04.835879+00:00 (G41C804E316E971_HEALTHSCIDATADEV1HDWLS(732))
" rdbms_uts
kxfp.c kxfpqsod_qc_sod "slvresil_diag1: set KXFPQCERS in kxfpqsod_qc_sod. q=0x55e6de3600, slave_inst=0, slave_num=0
" PX_Messaging
dbkt.c dbktWriteTimestampWCdbInfo "
*** 2025-03-21T01:51:18.722026+00:00 (G41C804E316E971_HEALTHSCIDATADEV1HDWLS(732))
" rdbms_uts
dbkt.c dbktPri ORA-39078: unable to dequeue message for agent from queue "" rdbms_uts
dbkt.c dbktPri "
" rdbms_uts
dbkt.c dbktPri ORA-06512: at "SYS.KUPW$WORKER", line 2946 rdbms_uts
dbkt.c dbktPri "
" rdbms_uts
dbkt.c dbktPri ORA-06512: at "SYS.KUPW$WORKER", line 15455 rdbms_uts
dbkt.c dbktPri "
" rdbms_uts
dbkt.c dbktPri ORA-06512: at "SYS.KUPC$QUEUE_INT", line 830 rdbms_uts
dbkt.c dbktPri "
" rdbms_uts
dbkt.c dbktPri ORA-06512: at "SYS.KUPC$QUE_INT", line 716 rdbms_uts
dbkt.c dbktPri "
" rdbms_uts
dbkt.c dbktPri ORA-25326: Array dequeue operation failed for message at index 1. rdbms_uts
dbkt.c dbktPri "
" rdbms_uts
dbkt.c dbktPri ORA-04067: not executed, stored procedure "SYS.KUPC$C_1240459289_0321014 rdbms_uts
dbkt.c dbktPri "
" rdbms_uts
dbkt.c dbktPri 341_0" does not exist rdbms_uts

This is the PLSQL i used to kick off the export API and watch the datapump logs

CREATE OR REPLACE EDITIONABLE FUNCTION EXPORTSCHEMA(
JOB IN NVARCHAR2 DEFAULT '',
SCHEMANAME IN NVARCHAR2 DEFAULT '',
VERSION IN NVARCHAR2 DEFAULT '')
RETURN NUMBER AUTHID CURRENT_USER AS
ind          NUMBER;              -- Loop index
h1           NUMBER;               -- Data Pump job handle
percent_done NUMBER;     -- Percentage of job complete
job_state    VARCHAR2(30);  -- To keep track of job state
jobstate     VARCHAR2(30);  -- To keep track of job state
le           ku$_logentry;         -- For WIP and error messages
js           ku$_jobstatus;        -- The job status from get_status
jd           ku$_jobdesc;          -- The job description from get_status
sts          ku$_status;          -- The status object returned by get_status
logtype      NUMBER := dbms_datapump.ku$_file_type_log_file;
logfile      VARCHAR2(200);         -- log filename
l_clob        clob; -- log content
errors_array DBMS_SQL.VARCHAR2_TABLE; -- errors to search in log, if they exist fail this procedure
BEGIN
--dbms_output.enable();
errors_array(1) := 'fatal error';
errors_array(2) := 'ORA-39095'; -- Dump file space has been exhausted';
dbms_output.put_line('EXPORTSCHEMA JOB='||JOB||', SCHEMANAME='||SCHEMANAME||', VERSION='||VERSION);
-- Create a (user-named) Data Pump job to do a schema export.
dbms_output.put_line('BEFORE dbms_datapump.open '||JOB);
h1 := dbms_datapump.open('EXPORT', 'SCHEMA', NULL, JOB, 'LATEST');
dbms_output.put_line('AFTER dbms_datapump.open ');
-- A metadata filter is used to specify the schema that will be exported.
dbms_datapump.metadata_filter(h1, 'SCHEMA_EXPR', 'IN ('''||SCHEMANAME||''')');
-- SET_PARAMETER procedure is used to specify job-processing options.
dbms_output.put_line('BEFORE dbms_datapump.metadata_filter ');
dbms_datapump.set_parameter(h1, 'COMPRESSION', 'ALL');
dbms_output.put_line('AFTER dbms_datapump.metadata_filter ');
-- Specify a single dump file for the job (using the handle just returned)
-- and a directory object, which must already be defined and accessible
-- to the user running this procedure.
logfile := JOB||'.log';
dbms_datapump.add_file(h1, logfile, 'DATA_PUMP_DIR', NULL, logtype);
dbms_datapump.add_file(h1, JOB||'.dmp', 'DATA_PUMP_DIR');
dbms_output.put_line('AFTER dbms_datapump.add_file log '||logfile||' and job '||JOB);
-- Start the job. An exception will be generated if something is not set up
-- properly.
dbms_datapump.start_job(h1);
-- The export job should now be running. In the following loop, the job
-- is monitored until it completes. In the meantime, progress information is
-- displayed.
-- Now grab output from the job and write to standard out.
--
jobstate := 'UNDEFINED';
WHILE
( jobstate != 'COMPLETED' )
AND ( jobstate != 'STOPPED' )
LOOP
BEGIN
dbms_datapump.get_status(h1, dbms_datapump.ku$_status_job_error + dbms_datapump.ku$_status_job_status + dbms_datapump.ku$_status_wip, -1, jobstate, sts);
js := sts.job_status;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION Unexpected dbms_datapump.get_status SQLCODE SQLERRM '||to_char(SQLCODE)||' '||to_char(SQLERRM));
DBMS_DATAPUMP.LOG_ENTRY(h1,'EXCEPTION Unexpected dbms_datapump.get_status SQLCODE SQLERRM '||to_char(SQLCODE)||' '||to_char(SQLERRM),1);
END;
--
-- If we received any WIP or Error messages for the job, display them.
--
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;
-- Detach from job.
dbms_datapump.detach(h1);
-- Looking for ways to indicate if the export file is incomplete,
-- DBMS_DATAPUMP.GET_DUMPFILE_INFO does not validate the dump file; it retrieves information
-- about the dump file, such as version, character set, and creation date, but it
-- doesn't perform any integrity checks or validation.  instead we'll load the log text and look for any
-- common errs we experience related to directory free space including ORA-39095 or "fatal error"
BEGIN
l_clob := DATAPUMP_LOG_LOADER(logfile,'DATA_PUMP_DIR');
DBMS_OUTPUT.PUT_LINE ('filename = '||logfile||', logtext= '||l_clob);
ind := errors_array.first;
while ind is not null loop
logtype := dbms_lob.instr( l_clob, errors_array(ind) );
dbms_output.put_line('AFTER dbms_lob.instr checking for err='||errors_array(ind)||' return='||logtype );
if ( logtype != 0 ) then
dbms_output.put_line ('ERROR '||errors_array(ind)||' detected in log, returning failure');
return 3;
end if;
ind := errors_array.next ( ind );
end loop;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION message '||to_char(SQLERRM));
return 2;
END;
return 0;
--
-- Any exceptions that propagated to this point will be captured.
-- The details are retrieved from get_status and displayed.
EXCEPTION WHEN OTHERS THEN
BEGIN
dbms_datapump.get_status(h1, dbms_datapump.ku$_status_job_error, 0, jobstate, sts);
IF ( bitand(sts.mask, dbms_datapump.ku$_status_job_error) != 0 ) THEN
le := sts.error;
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 IF;
BEGIN
dbms_datapump.stop_job(h1, 1, 0, 0);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION message '||to_char(SQLERRM));
END;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION message '||to_char(SQLERRM));
END;
DBMS_OUTPUT.PUT_LINE ('EXCEPTION message '||to_char(SQLERRM));
return 1;
END;
CREATE or replace FUNCTION DATAPUMP_LOG_LOADER(
in_log_file IN NVARCHAR2 DEFAULT '',
in_directory IN NVARCHAR2 DEFAULT '' )
RETURN clob AUTHID CURRENT_USER AS
l_clob clob;
src_bfile bfile;
l_length number;
dest_offset NUMBER;
src_offset NUMBER;
bfile_csid NUMBER;
lang_context NUMBER;
warn NUMBER;
begin
dest_offset:=1;
src_offset:=1;
bfile_csid:=0;
lang_context:=0;
warn:=0;
--dbms_output.enable();
DBMS_OUTPUT.PUT_LINE ('DATAPUMP_LOG_LOADER(filename, dir) function returns the log text without using a table or sequence');
DBMS_OUTPUT.PUT_LINE ('Opening from dir  '||in_directory||' filename = '||in_log_file);
src_bfile := bfilename( in_directory, in_log_file );
dbms_output.put_line('AFTER '||in_directory||' bfilename '||in_log_file);
dbms_lob.fileopen( src_bfile );
dbms_output.put_line('AFTER fileopen ');
l_length := dbms_lob.getlength( src_bfile );
dbms_output.put_line('AFTER dbms_lob.getlength '||to_char(l_length) );
DBMS_LOB.CREATETEMPORARY(l_clob, TRUE, DBMS_LOB.SESSION);
dbms_output.put_line('AFTER l_clob inialization ');
dbms_lob.LOADCLOBFROMFILE( l_clob, src_bfile, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, bfile_csid, lang_context, warn );
dbms_output.put_line('AFTER dbms_lob.LOADCLOBFROMFILE ');
dbms_lob.fileclose( src_bfile );
dbms_output.put_line('AFTER dbms_lob.fileclose ');
DBMS_OUTPUT.PUT_LINE ('filename = '||in_log_file||', filelength = '||to_char(l_length));
return l_clob;
end;