SQL> select SID,WAIT_CLASS,EVENT from v$session where SADDR in (select SADDR from dba_datapump_sessions);
SID WAIT_CLASS EVENT
———- ——————– —————————————-
38 enqueue Streams AQ: enqueue blocked on low memory
SQL> select component,current_size/1024/1024,last_oper_type,last_oper_time from v$sga_dynamic_components;
Here we can see that streams pool got shrink-ed due to which it was in hung state.
SOLUTION:
As s workaround, explicitly set the streams_pool_size to a fixed (large enough) value, e.g. 150 Mb (or 300 MB if needed) that will be used as a minimum value, e.g.:
CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=both;
And re-run the Export or Import Data Pump job.
If you cannot modify the STREAMS_POOL_SIZE dynamically, then you need to set the value in the spfile, and restart the database.
CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=spfile;
SHUTDOWN IMMEDIATE
STARTUP
NOTE:
If the problem is not fixed after implementing one of the above solutions, a fix for unpublished Bug 24560906 must be also installed before reporting the issue to Oracle Support.
Possible solutions for unpublished Bug 24560906 are:
Set the below parameter and restart the job.
alter system set “_disable_streams_pool_auto_tuning”=TRUE;
SHUTDOWN IMMEDIATE
STARTUP