Skip to content

DATAPUMP_IMP_FULL_DATABASE, How and Why

  • by

DATAPUMP_IMP_FULL_DATABASE

DATAPUMP_IMP_FULL_DATABASE is one of predefined roles in Oracle database, who provides a bunch of system and object privileges required to perform full database imports using the data pump import utility.

Furthermore, Oracle develops the role based on 2 earlier data migration roles, IMP_FULL_DATABASE and EXP_FULL_DATABASE.

To grant the role to an user who wants to take the job to import data by data pump, we only need a granting like this:

SQL> grant datapump_imp_full_database to hr;

Grant succeeded.

The role is usually granted along with DATAPUMP_EXP_FULL_DATABASE to users who are responsible for data migration.

Here we list roles, system privileges and object privileges in DATAPUMP_IMP_FULL_DATABASE.

Roles

We use the following SQL statement to check the roles in DATAPUMP_IMP_FULL_DATABASE.

SQL> set pagesize 1000;
SQL> select granted_role from dba_role_privs where grantee = 'DATAPUMP_IMP_FULL_DATABASE' order by 1;

There're 2 roles granted to DATAPUMP_IMP_FULL_DATABASE.

  • EXP_FULL_DATABASE
  • IMP_FULL_DATABASE

As we can see, DATAPUMP_IMP_FULL_DATABASE bases on not only IMP_FULL_DATABASE, but also EXP_FULL_DATABASE role.

System Privileges

We use the following SQL statement to check the system privileges in DATAPUMP_IMP_FULL_DATABASE.

SQL> select privilege from dba_sys_privs where grantee = 'DATAPUMP_IMP_FULL_DATABASE' order by 1;

There're 2 system privileges granted to DATAPUMP_IMP_FULL_DATABASE.

  • CREATE SESSION
  • CREATE TABLE

Object Privileges

We use the following SQL statement to check the object privileges in DATAPUMP_IMP_FULL_DATABASE.

SQL> select privilege || ' on ' || owner || '.' || table_name privilege from dba_tab_privs where grantee = 'DATAPUMP_IMP_FULL_DATABASE' order by owner, table_name, privilege;

There're 2 object privileges granted to DATAPUMP_IMP_FULL_DATABASE.

  • EXECUTE on SYS.DBMS_JVM_EXP_PERMS
  • EXECUTE on SYS.SDO_RDF_EXP_IMP

To enable importing full DB data from data pump dump files, you need DATAPUMP_EXP_FULL_DATABASE role, a full-fledged set of privileges to do so.

Leave a Reply

Your email address will not be published. Required fields are marked *