You Asked

Tried all the steps nothing is helping : Gave all grants :
Used this as an Admin :
select * from dba_credentials;
grant write on directory data_pump_dir to X_GC_DW;
grant read on directory data_pump_dir to X_GC_DW ;
SELECT * FROM dba_tab_privs WHERE table_name = 'DBMS_CLOUD';
SELECT * FROM dba_role_privs WHERE --grantee = 'ADMIN' and
Granted_Role = 'DWROLE'; -- X_GC_DW DWROLE YES NO YES NO NO
GRANT EXECUTE ON DBMS_CLOUD TO X_GC_DW;

All looks good still no access to DBMS_CLOUD.COPY_DATA While LIST_OBJECTS work against any schema user from ADW .

Please share steps to fix this :

Thanks

and Connor said...

Here's a top to bottom test case from my cloud account.... Compare and contrast to yours and see what's different. (You didn't give us any output so we can't really see what you're doing)

SQL> CREATE TABLE customers
  2  (
  3     cust_id                  NUMBER         NOT NULL,
  4     cust_first_name          VARCHAR2(20)   NOT NULL,
  5     cust_last_name           VARCHAR2(40)   NOT NULL,
  6     cust_gender              CHAR(1)        NOT NULL,
  7     cust_year_of_birth       NUMBER(4)      NOT NULL,
  8     cust_marital_status      VARCHAR2(20),
  9     cust_street_address      VARCHAR2(40)   NOT NULL,
 10     cust_postal_code         VARCHAR2(10)   NOT NULL,
 11     cust_city                VARCHAR2(30)   NOT NULL,
 12     cust_city_id             NUMBER         NOT NULL,
 13     cust_state_province      VARCHAR2(40)   NOT NULL,
 14     cust_state_province_id   NUMBER         NOT NULL,
 15     country_id               NUMBER         NOT NULL,
 16     cust_main_phone_number   VARCHAR2(25)   NOT NULL,
 17     cust_income_level        VARCHAR2(30),
 18     cust_credit_limit        NUMBER,
 19     cust_email               VARCHAR2(50),
 20     cust_total               VARCHAR2(14)   NOT NULL,
 21     cust_total_id            NUMBER         NOT NULL,
 22     cust_src_id              NUMBER,
 23     cust_eff_from            DATE,
 24     cust_eff_to              DATE,
 25     cust_valid               VARCHAR2(1),
 26     CONSTRAINT customers_pk  PRIMARY KEY (cust_id)
 27  );

Table created.


SQL> begin
  2    dbms_cloud.create_credential (
  3      credential_name => 'OBJ_STORE_DB_BUCKET',
  4      username        => 'OracleIdentityCloudService/myusername@mydomain.com',
  5      password        => 'myauthkey'
  6      ) ;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select *
  2  from   dbms_cloud.list_objects(
  3          credential_name => 'OBJ_STORE_DB_BUCKET',
  4          location_uri    => 'https://objectstorage.~~~~~~oraclecloud.com/n/~~~~~~~~~~~~~/o/');

OBJECT_NAME        BYTES CHECKSUM                            CREATED                             LAST_MODIFIED
------------- ---------- ----------------------------------- ----------------------------------- -----------------------------------
customers.csv   12671219 2704544fb343dc50728bb09bdd6fbcbb                                        25-SEP-25 06.43.49.986000 AM +00:00

SQL> BEGIN
  2    DBMS_CLOUD.copy_data(
  3      table_name      => 'CUSTOMERS',
  4      credential_name => 'OBJ_STORE_DB_BUCKET',
  5      file_uri_list   => 'https://objectstorage.~~~~~~~~.oraclecloud.com/n/~~~~~~~~~~~~~~~/o/customers.csv',
  6      format          => json_object('ignoremissingcolumns' value 'true',
  7                                     'removequotes' value 'true',
  8                                     'delimiter' value ',',
  9                                     'skipheaders' value 1,
 10                                     'dateformat' value 'yyyy-mm-dd')
 11   );
 12  END;
 13  /

PL/SQL procedure successfully completed.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

Data Pump

Starting COPY_DATA for file into table xxxx COPY_DATA failed: ORA-20000: ORA-01031: insufficient privileges

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

Data Pump