Optimizing Oracle Autonomous Database (ADB) DataPump

Optimizing Oracle Autonomous Database (ADB) Data Pump Export: Roles & Permissions

Introduction

Oracle Data Pump is a robust tool for efficient data and metadata movement within Oracle databases. However, in Oracle Autonomous Database (ADB), conventional roles like EXP_FULL_DATABASE are not available. Instead, specific predefined roles must be assigned to facilitate seamless export and import operations.

This guide covers:

• The required privileges for Data Pump operations in ADB
• Fixing ORA-31631 and ORA-39109 errors
• Correct SQL grants to enable export and import

Common Export Errors in Oracle ADB

While attempting an export in Oracle Autonomous Database, you may encounter these errors:

Error: ORA-31631 – Privileges Required

ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas

Causes of This Error:

• The user does not have the necessary DATAPUMP_CLOUD_EXP role.
• Schema exports are restricted unless explicitly authorized.
• The legacy EXP_FULL_DATABASE role is not supported in Autonomous Database.

Required Roles for Oracle Data Pump in ADB

To execute Data Pump Export (expdp) and Import (impdp) in Oracle Autonomous Database, grant the following roles:

1. Granting Export Permissions

Enable export operations by assigning:

GRANT DATAPUMP_CLOUD_EXP TO test;

2. Granting Import Permissions

Enable import operations by assigning:

GRANT DATAPUMP_CLOUD_IMP TO test;

3. Key Privileges Included in These Roles

Oracle’s predefined DATAPUMP_CLOUD_EXP and DATAPUMP_CLOUD_IMP roles include various necessary permissions. For reference, these roles contain:

GRANT CONNECT TO test;
GRANT SELECT_CATALOG_ROLE TO test;
ALTER USER test DEFAULT ROLE ALL;

ALTER USER test QUOTA 512M ON TOOLS;

GRANT RESUMABLE TO test;
GRANT BACKUP ANY TABLE TO test;
GRANT EXECUTE ANY TYPE TO test;
GRANT SELECT ANY TABLE TO test;
GRANT READ ANY FILE GROUP TO test;
GRANT SELECT ANY SEQUENCE TO test;
GRANT EXECUTE ANY PROCEDURE TO test;

BEGIN
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
GRANTEE_NAME => 'test',
PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION => FALSE
);
END;
/

GRANT EXECUTE_CATALOG_ROLE TO test;
GRANT SELECT_CATALOG_ROLE TO test;

Understanding Key Oracle Data Pump Roles

DATAPUMP_CLOUD_EXP (Export Role)

• Enables users to run Data Pump exports in Oracle ADB.
• Without this role, exporting database objects is restricted.

DATAPUMP_CLOUD_IMP (Import Role)

• Grants permission to import data into Oracle ADB.
• Necessary for database restoration or migrations.

Privileges Included in These Roles:

• CONNECT: Allows users to establish a database session.
• SELECT_CATALOG_ROLE: Grants read access to Data Dictionary views.
• BACKUP ANY TABLE: Permits exporting any table across schemas.
• SELECT ANY TABLE: Enables querying tables from other schemas.
• EXECUTE ANY PROCEDURE: Allows running stored procedures from other schemas.
• SELECT ANY SEQUENCE: Essential for exporting sequences.
• RESUMABLE: Supports handling long-running export operations.

Validating Role Assignments in Oracle ADB

After assigning roles, verify the user’s privileges by executing:

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'TEST';

For checking specific system privileges:

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'TEST';

Executing an Export in Oracle ADB

Once privileges are configured, initiate an export using Oracle Data Pump:

expdp test@adb_high \
DIRECTORY=DATA_PUMP_DIR \
DUMPFILE=export_data.dmp \
LOGFILE=export_log.log \
SCHEMAS=TEST;

Export Parameters Explained:

• DIRECTORY=DATA_PUMP_DIR → ADB’s managed storage directory for Data Pump.
• DUMPFILE=export_data.dmp → Name of the generated export file.
• LOGFILE=export_log.log → Logs all export activities.
• SCHEMAS=TEST → Specifies the schema to be exported.

Final Thoughts

In Autonomous Database (ADB), the traditional EXP_FULL_DATABASE role is not applicable. Instead, Oracle provides DATAPUMP_CLOUD_EXP and DATAPUMP_CLOUD_IMP for managing data movement operations.

By following the outlined steps, you can effectively bypass errors like ORA-31631 and ORA-39109, ensuring smooth Data Pump operations within Oracle Autonomous Database.

For more in-depth Oracle ADB documentation, refer to: Oracle Data Pump Guide.

Troubleshooting Common Export Issues

If errors persist, verify:

1. The correct user is executing the export command.
2. Roles are properly assigned using DBA_ROLE_PRIVS.
3. Data Pump is referencing the correct directory (DATA_PUMP_DIR).

Need further assistance? Drop your queries in the comments below! fixing ora-31631 ora-39109 errors oracle autonomous database export import error ora-39109 errors fixing ora-31631 oracle autonomous database export import error oracle autonomous database export import error fixing ora-31631

Author: Rohit Mishri