Skip to content

EXP_FULL_DATABASE, How and Why

  • by

EXP_FULL_DATABASE

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 original export utility.

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

SQL> grant exp_full_database to hr;

Grant succeeded.

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

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

Roles

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

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

There're 2 roles granted to EXP_FULL_DATABASE.

  • EXECUTE_CATALOG_ROLE
  • SELECT_CATALOG_ROLE

System Privileges

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

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

There're 14 system privileges granted to EXP_FULL_DATABASE.

  • ADMINISTER RESOURCE MANAGER
  • ADMINISTER SQL MANAGEMENT OBJECT
  • ANALYZE ANY
  • BACKUP ANY TABLE
  • CREATE SESSION
  • CREATE TABLE
  • EXECUTE ANY PROCEDURE
  • EXECUTE ANY TYPE
  • EXEMPT REDACTION POLICY
  • FLASHBACK ANY TABLE
  • READ ANY FILE GROUP
  • RESUMABLE
  • SELECT ANY SEQUENCE
  • SELECT ANY TABLE

Object Privileges

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

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

There're 62 object privileges granted to EXP_FULL_DATABASE.

  • READ on SYS.DATA_PUMP_DIR
  • WRITE on SYS.DATA_PUMP_DIR
  • EXECUTE on SYS.DBMSZEXP_SYSPKGGRNT
  • EXECUTE on SYS.DBMS_AQ_IMPORT_INTERNAL
  • EXECUTE on SYS.DBMS_AUTO_TASK_EXPORT
  • EXECUTE on SYS.DBMS_IREFRESH
  • EXECUTE on SYS.DBMS_PSWMG_IMPORT
  • EXECUTE on SYS.DBMS_RULE_EXIMP
  • EXECUTE on SYS.DBMS_TRANSFORM_EXIMP
  • SELECT on SYS.EXU10LNK
  • SELECT on SYS.EXU8LNK
  • SELECT on SYS.EXU8PHS
  • SELECT on SYS.EXU9LNK
  • DELETE on SYS.INCEXP
  • INSERT on SYS.INCEXP
  • UPDATE on SYS.INCEXP
  • DELETE on SYS.INCFIL
  • INSERT on SYS.INCFIL
  • UPDATE on SYS.INCFIL
  • DELETE on SYS.INCVID
  • INSERT on SYS.INCVID
  • UPDATE on SYS.INCVID
  • SELECT on SYS.KU$_11_2_PSW_HIST_VIEW
  • READ on SYS.KU$_12AUDIT_POLICY_ENABLE_VIEW
  • READ on SYS.KU$_AUDIT_CONTEXT_VIEW
  • READ on SYS.KU$_AUDIT_POLICY_ENABLE_VIEW
  • READ on SYS.KU$_AUDIT_POLICY_VIEW
  • SELECT on SYS.KU$_PSW_HIST_LIST_VIEW
  • SELECT on SYS.KU$_PSW_HIST_VIEW
  • SELECT on SYS._DBA_APPLY
  • SELECT on SYS._DBA_APPLY_BATCH_SQL_STATS
  • SELECT on SYS._DBA_APPLY_CDR_INFO
  • SELECT on SYS._DBA_APPLY_CHANGE_HANDLERS
  • SELECT on SYS._DBA_APPLY_CONF_HDLR_COLUMNS
  • SELECT on SYS._DBA_APPLY_COORDINATOR_STATS
  • SELECT on SYS._DBA_APPLY_DML_CONF_COLUMNS
  • SELECT on SYS._DBA_APPLY_DML_CONF_HANDLERS
  • SELECT on SYS._DBA_APPLY_ERROR
  • SELECT on SYS._DBA_APPLY_ERROR_HANDLER
  • SELECT on SYS._DBA_APPLY_ERROR_TXN
  • SELECT on SYS._DBA_APPLY_HANDLE_COLLISIONS
  • SELECT on SYS._DBA_APPLY_MILESTONE
  • SELECT on SYS._DBA_APPLY_PROGRESS
  • SELECT on SYS._DBA_APPLY_READER_STATS
  • SELECT on SYS._DBA_APPLY_REPERROR_HANDLERS
  • SELECT on SYS._DBA_APPLY_SERVER_STATS
  • SELECT on SYS._DBA_APPLY_SOURCE_OBJ
  • SELECT on SYS._DBA_APPLY_SOURCE_SCHEMA
  • SELECT on SYS._DBA_APPLY_SPILL_TXN
  • SELECT on SYS._DBA_APPLY_TABLE_STATS
  • SELECT on SYS._DBA_CAPTURE
  • SELECT on SYS._DBA_STREAMS_MESSAGE_CONSUMERS
  • SELECT on SYS._DBA_STREAMS_MESSAGE_RULES
  • SELECT on SYS._DBA_STREAMS_MSG_NOTIFICATIONS
  • SELECT on SYS._DBA_STREAMS_PRIVILEGED_USER
  • SELECT on SYS._DBA_STREAMS_RULES
  • SELECT on SYS._DBA_STREAMS_RULES_H
  • SELECT on SYS._DBA_STREAMS_STMTS
  • SELECT on SYS._DBA_STREAMS_TRANSFORMATIONS
  • SELECT on SYS._DBA_XSTREAM_CONNECTION
  • SELECT on SYS._DBA_XSTREAM_OUTBOUND
  • SELECT on SYS._DBA_XSTREAM_PARAMETERS

To enable importing full DB data from original dump files, you need 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 *