Forum Stats

  • 3,770,503 Users
  • 2,253,127 Discussions
  • 7,875,489 Comments

Discussions

DBMS_DataPump.OPEN fails in Oracle 18c error:

2»

Answers

  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon
    edited Sep 20, 2021 11:21AM

    @Solomon Yakobson I checked the streams_pool_Size using the sqlplus

    SQL> show parameter STREAMS_POOL_SIZE;

    NAME                 TYPE    VALUE

    ------------------------------------ -----------

    streams_pool_size          big integer 0

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond

    And what about SGA_TARGET? Most likely is also set to zero. If so, you need to set streams pool size to say 40M.

    SY.

  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon

    @Solomon Yakobson Yes, As you said SGA_TARGET is also zero(attached below). I will try to set STREAMS_POOL_SIZE=40M and will let you know.

    NAME    TYPE    VALUE 

    ---------- ----------- ----- 

    sga_target big integer 0   

  • User_TK218
    User_TK218 Member Posts: 14 Green Ribbon

    @Solomon Yakobson Sorry for the late reply altering the streams_pool_size parametet, it didn't work for me either. I also granted privileges like

    grant dba to AIMIM;

    grant all privileges to AIMIM;

    grant sysdba to AIMIM;

    grant sysbackup to AIMIM;

    commit;

    But none of them helped me in creating the job.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond

    Double-check streams pool size:

    SELECT VALUE FROM GV$PARAMETER WHERE NAME = 'streams_pool_size'
    /
    

    I assume

    DBMS_DataPump.OPEN(operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => 'J$AIM_BACKUP', VERSION => 'LATEST');
    

    is in stored procedure, so keep in mind roles are ignored (unless stored procedure is AUTHID CURRENT_USER). Therefore double-check stored procedure owner (not caller) is directly (not via role) granted CREATE TABLE.

    Double-check stored procedure owner (not caller) has quota on its default tablespace.

    Restart database. It could be background process QMNC is dead for some reason (e.g. cancelled shutdown).

    SY.