Creating an external table in autonomous data warehouse with cloud object storage bucket

Anthony Harper

All,

I am having difficulty creating an external table with dbms_cloud and object storage in an autonomous data warehouse.

I have created the following:

storage bucket.

test files in the storage bucket.

dynamic group for accessing the bucket defined with rule to include all autonomous databases.

a policy to allow the dynamic group to access buckets.

a resource principle credential in the autonomous data warehouse and granted to the schema user that will create the external table.

When I try to create the table using dbms_cloud.create_external_table I get the following error:

ORA-06564: object DATA_PUMP_DIR does not exist

I logged into the database as admin and tried to create a directory object data_pump_dir pointed to the object storage bucket. This generated the following error:

ora-65040 operation not allowed from within a pluggable database

When I tried to used the Database Actions | Data Load | Manage Cloud to create a cloud storage link I get an error creating the link:

ORA-20404: Object not found - https://objectstorage.us-phoenix-1.oraclecloud.com/n/xxxxxx/b/adw_ext_data/o/

What am I missing to access the object storage from the autonomous data warehouse?

Regards,

Anthony Harper

Anthony Harper

Update,

When I used the graphical tool to do a test import with the admin account the data_pump_dir was created in the background.

The resource principal credential was created in the database, but it wont work to create external tables. Th e dynamic group looks correct, and I added each autonomous database by oid specifically. The policy for buckets allows this group to manage the bucket.

I created a cloud user based credential using auth token and then was able to create external tables in the admin schema and in the user schema.

Two problems still remain:

1) getting the resource principal to work

2) accessing the data_pump_dir path to clean up log files and bad files from validating external tables that had errors.

I would appreciate help on these issues,

Regards,

Anthony Harper