4

I am attempting to write a procedure (within a package) that calls DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a procedure I get the following error.

[Error] Execution (1: 1): ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285
ORA-06512: at "UTILITY.MANAGE_SCHEMA", line 71
ORA-06512: at line 1

This error is being generated from the DBMS_DATAPUMP.OPEN call. Since it works in the anonymous block, I'm assuming this is actually a permissions issue (i.e. one or more permissions granted via a role are required). However, I can't find any documentation about what permissions are necessary to use datapump.

This problem boils down to: which permissions need to be explicitly granted to a user/schema in order for a package in that schema to be able to call DBMS_DATAPUMP?


The owner of the package has both the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles, as well as the DBA role.

The procedure code is below.

   PROCEDURE copy_schema (p_source_schema VARCHAR2,
                          p_target_schema VARCHAR2,
                          p_asynchronous BOOLEAN := FALSE,
                          p_link_name VARCHAR2 := 'prddb') IS
      dph NUMBER;
      v_source_schema VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.simple_sql_name (p_source_schema));
      v_target_schema VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.simple_sql_name (p_target_schema));
      v_link_name VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.qualified_sql_name (p_link_name));
      v_job_name VARCHAR2 (30) := UPPER ('IMPORT_' || p_target_schema);
      v_state VARCHAR2 (30);
   BEGIN
      DBMS_OUTPUT.put_line (
            'Starting copy: source_schema = '
         || v_source_schema
         || '; target_schema = '
         || v_target_schema
         || '; link_name = '
         || v_link_name
         || '; job_name = '
         || v_job_name);
      dph :=
         DBMS_DATAPUMP.open ('IMPORT',
                             'SCHEMA',
                             v_link_name,
                             v_job_name);
      DBMS_OUTPUT.put_line ('dph = ' || dph);
      DBMS_DATAPUMP.metadata_filter (dph,
                                     'SCHEMA_LIST',
                                     '''' || v_source_schema || '''');
      DBMS_DATAPUMP.metadata_remap (dph,
                                    'REMAP_SCHEMA',
                                    v_source_schema,
                                    v_target_schema);
      DBMS_DATAPUMP.set_parameter (dph, 'TABLE_EXISTS_ACTION', 'REPLACE');
      DBMS_DATAPUMP.create_job_view (dph, UPPER (v_job_name || '_vw'));
      DBMS_DATAPUMP.start_job (dph);

      IF NOT p_asynchronous THEN
         DBMS_DATAPUMP.wait_for_job (dph, v_state);
      END IF;
   END copy_schema;

I have found one solution to creating a procedure that calls datapump, but it solves the problem by putting the procedure in the SYSTEM schema, which I would prefer not to do.

5

Short answer: CREATE TABLE must be explictly granted to the object owner for DBMS_DATAPUMP to be called from within a package or stored procedure.


Long Answer:

In an effort to solve this question, I interrogated the data dictionary to get all of the object and system privileges granted by the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles (and all roles assigned to those two roles). Attempting to explicitly grant all 2683 privileges resulted in 2566 failures, but the package was able to call DBMS_DATAPUMP successfully.

The question then became: which of the remaining 117 privileges are actually needed for DBMS_DATAPUMP? I then grouped the remaining privileges and revoked them one group at a time (I assumed that it was a group of related permissions that would ultimately be needed). After running through nearly all of the groups, only the set of CREATE ANY privileges remained. When I revoked that group, the package stopped working. I then granted each of those permissions, calling the package after each grant. It was finally CREATE ANY TABLE that restored functionality to the package. A little further tinkering determined that CREATE TABLE is, in fact, sufficient to call DBMS_DATAPUMP from within a package.


CREATE TABLE is the only permission that I've found needs to be granted for the functionality found in the procedure shown in the question. Other procedures/datapump settings may require additional privileges. dbms_datapump.create_job_view, for instance, requires CREATE VIEW. In that case, the error message when the privilege is missing is, more reasonably, ORA-01031: insufficient privileges.

0

Privileges acquired via roles are not effective in a stored procedure. You need to grant the required privileges to the account that creates the package.

  • Yeah, that's why I'm assuming that it's a permissions issue. The gist of the question is which permissions are needed to use DBMS_DATAPUMP? – Allan Oct 14 '14 at 18:46
-1

it's also worth noting that a lack of tablespace quota will also generate the same error :

ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

although if this is the cause it happens in both anon blocks and procedures.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.