Skip to content

DATAPUMP_EXP_FULL_DATABASE, How and Why

  • by

DATAPUMP_EXP_FULL_DATABASE

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

Furthermore, Oracle develops the role based on an earlier export role EXP_FULL_DATABASE which has enough rights to export full data from a database.

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

SQL> grant datapump_exp_full_database to hr;

Grant succeeded.

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

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

Roles

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

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

There's only 1 role granted to DATAPUMP_EXP_FULL_DATABASE.

  • EXP_FULL_DATABASE

As we can see, DATAPUMP_EXP_FULL_DATABASE is truly inherited from EXP_FULL_DATABASE, the most used role for exporting data prior to data pump.

System Privileges

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

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

There're 2 system privileges granted to DATAPUMP_EXP_FULL_DATABASE.

  • CREATE SESSION
  • CREATE TABLE

In fact, the base role EXP_FULL_DATABASE has already been granted these privileges.

Object Privileges

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

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

There're 2 object privileges granted to DATAPUMP_EXP_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_IMP_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 *