Forum Stats

  • 3,767,858 Users
  • 2,252,726 Discussions
  • 7,874,366 Comments

Discussions

Datapump - what role am I missing?

RLOG
RLOG Member Posts: 30 Red Ribbon

I'm trying to use the datapump api to import over dblink. It works when I assign my user dba role but I need to refine this for production. These are the roles the user has. With this I get

 - ORA-31626: job does not exist

But with dba added it works as intended.

grant datapump_exp_full_database to rds1_user;
grant create session to rds1_user with admin option;
grant resource to rds1_user with admin option;
grant connect to rds1_user with admin option;
grant create user to rds1_user;
grant datapump_imp_full_database to rds1_user;
grant create tablespace to rds1_user;
grant execute on utl_file to rds1_user;

Thanks in advance

Tagged:

Answers

  • Paulzip
    Paulzip Member Posts: 8,489 Blue Diamond
    edited Mar 3, 2021 10:31PM

    Please show your datapump code and the full error message.

    If you are selecting objects over a db link, you'll need select on the remote object if the schema is a different name and / or a different instance.

  • RLOG
    RLOG Member Posts: 30 Red Ribbon

    Here is the template

       c_tablespace       constant varchar2(30) := 'RDS_DEF_TS';
       c_remap_tablespace constant varchar2(30) := 'REMAP_TABLESPACE';
       -- Handles template datapump
       procedure import_template(p_directory      in varchar2
                                ,p_db_link_name   in varchar2
                                ,p_log_file_name  in varchar2
                                ,p_schema_name    in varchar2
                                ,out_status       out varchar2)
       is
          l_datapump_handler number;
          l_datapump_status  varchar2(200);
          l_internal_status  ku$_status; 
          c_template_schema  constant varchar2(30) := 'RDS1_TMPLT';
       begin
          l_datapump_handler := dbms_datapump.open(
             operation   => 'IMPORT'
            ,job_mode    => 'SCHEMA'
            ,remote_link => p_db_link_name
            ,job_name    => null
            ,version     => 'LATEST');
    
    
          dbms_datapump.add_file(
             handle    => l_datapump_handler
            ,filename  => p_log_file_name
            ,directory => p_directory
            ,filetype  => dbms_datapump.ku$_file_type_log_file);
    
    
          dbms_datapump.metadata_filter(
             handle      => l_datapump_handler
            ,name        => 'SCHEMA_EXPR'
            ,value       => 'IN ('''||c_template_schema||''')');
    
    
          dbms_datapump.metadata_remap(
             handle    => l_datapump_handler
            ,name      => 'REMAP_SCHEMA'
            ,old_value => c_template_schema
            ,value     => p_schema_name);
    
    
          dbms_datapump.metadata_remap(
             handle    => l_datapump_handler
            ,name      => c_remap_tablespace
            ,old_value => 'MASTER'
            ,value     => c_tablespace);
    
    
          dbms_datapump.metadata_remap(
             handle    => l_datapump_handler
            ,name      => c_remap_tablespace
            ,old_value => 'INBOUND'
            ,value     => c_tablespace);
    
    
          dbms_datapump.metadata_remap(
             handle    => l_datapump_handler
            ,name      => c_remap_tablespace
            ,old_value => 'OUTBOUND'
            ,value     => c_tablespace);
    
    
          dbms_datapump.metadata_remap(
             handle    => l_datapump_handler
            ,name      => c_remap_tablespace
            ,old_value => 'USERS'
            ,value     => c_tablespace);
    
    
          dbms_datapump.metadata_filter(
             handle => l_datapump_handler
            ,name   => 'EXCLUDE_PATH_EXPR'
            ,value  => 'IN (''ROLE_GRANT'',''SYSTEM_GRANT'',''DB_LINK'',''USER'',''STATISTICS'')');
    
    
          dbms_datapump.start_job(
             handle => l_datapump_handler);
    
    
          begin
             dbms_datapump.wait_for_job(
                handle    => l_datapump_handler
               ,job_state => l_datapump_status);
          exception
             when dbms_datapump.success_with_info then
                -- Get the final status
                dbms_datapump.get_status(
                   handle     => l_datapump_handler
                  ,mask       => dbms_datapump.ku$_status_job_error +
                                 dbms_datapump.ku$_status_job_status +
                                 dbms_datapump.ku$_status_wip
                  ,timeout    => -1 -- Wait for result
                  ,job_state  => l_datapump_status
                  ,status     => l_internal_status);
          end;
    
    
          out_status := l_datapump_status;
    
    
          dbms_datapump.detach(handle => l_datapump_handler); 
       exception
          when others then
             dbms_output.put_line( 'Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);
             dbms_datapump.detach(handle => l_datapump_handler);
    
    
             dbms_datapump.get_status(
                   handle     => l_datapump_handler
                  ,mask       => dbms_datapump.ku$_status_job_error +
                                 dbms_datapump.ku$_status_job_status +
                                 dbms_datapump.ku$_status_wip
                  ,timeout    => -1 -- Wait for result
                  ,job_state  => l_datapump_status
                  ,status     => l_internal_status);
    
    
             dbms_output.put_line('DP STATUS:'||l_datapump_status);
       end import_template;
    

    It is called like this.

       template_utils_pkg.import_template(p_directory      =>'GOLDEN_TEMPLATE_TEST_DIR'
                                         ,p_db_link_name   => 'DBLINK_RDS1'
                                         ,p_log_file_name  =>'RL_TEST_LOG'
                                         ,p_schema_name    =>'RL_TEST'
                                         ,out_status       => l_status);
    


    Error is

    Error raised: ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 1849
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 6829
    ORA-06512: at "RDS1_USER.TEMPLATE_UTILS_PKG", line 16
     - ORA-31626: job does not exist
    DP STATUS:
    
    

    Thanks

  • Paulzip
    Paulzip Member Posts: 8,489 Blue Diamond

    Give your job a name. I tend to create a name based on a sequence.

       is
          l_datapump_handler number;
          l_datapump_status  varchar2(200);
          l_internal_status  ku$_status; 
          c_template_schema  constant varchar2(30) := 'RDS1_TMPLT';
          l_job_name         varchar2(128 byte) := 'MY_JOB'||to_char(MYSEQ.VALUE);
       begin
          l_datapump_handler := dbms_datapump.open(
             operation   => 'IMPORT'
            ,job_mode    => 'SCHEMA'
            ,remote_link => p_db_link_name
            ,job_name    => l_job_name
            ,version     => 'LATEST');
    

    The job will take a while, and takes a little time to instantiate, so you'll probably want to loop on job_state, rather than just a single call on wait for job.

    Look at this example

  • RLOG
    RLOG Member Posts: 30 Red Ribbon

    Thanks, I will make those amendments.

    Any ideas on the privileges?

  • Paulzip
    Paulzip Member Posts: 8,489 Blue Diamond

    Difficult to say. I usually run Datapump activities as a user with elevated privileges, such as a DBA role or SYS. So will have all of the privileges required.

    RLOG