Thursday 10 June 2021

OCI - Moving Data from Autonomous Databases to on Premises database using impdp

OCI - Moving Data from Autonomous Databases to on Premises database using impdp 

  • Login in to autonomous database server with admin user.

C:\Users\varun>sqlplus admin/Cloud$123456@orcladb_medium


SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 19:16:09 2021

Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Last Successful login time: Mon Jun 07 2021 17:53:17 +05:30


Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

  • Provide privileges' to schema to create directory

 

SQL> GRANT CREATE ANY DIRECTORY TO soe;




Grant succeeded.



SQL> GRANT DROP ANY DIRECTORY TO soe;



Grant succeeded.

  • Create root directory first , So inside root directory we will create dump directory for schema bucket .

 


SQL> CREATE OR REPLACE DIRECTORY ROOT_DIR AS '';

Directory created.

SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='ROOT_DIR';

OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
ROOT_DIR
/u03/dbfs/BE1F7D7E50E2922EE0532210000A17B4/data/
           42


  •   Create directory staging2 after login to soe schemas so any directory stored inside data directory


SQL>  CREATE DIRECTORY staging2 AS 'stage2';




 

C:\Users\varun>expdp soe/Abc#123456789@orcladb_medium directory=staging2 dumpfile=adsoe%U.dmp  parallel=2 encryption_pwd_prompt=yes filesize=1G logfile=adsoe_export.log

Export: Release 19.0.0.0.0 - Production on Mon Jun 7 19:27:35 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Encryption Password:  ( Write and rember encypted password it will be used for impdp  on premises database)
Starting "SOE"."SYS_EXPORT_SCHEMA_01":  soe/********@orcladb_medium directory=staging2 dumpfile=adsoe%U.dmp parallel=2 encryption_pwd_prompt=yes filesize=1G logfile=adsoe_export.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SOE"."ORDER_ITEMS"                         170.4 MB 3735896 rows
. . exported "SOE"."ORDERS"                              114.9 MB 1352070 rows
. . exported "SOE"."INVENTORIES"                         15.27 MB  902171 rows
. . exported "SOE"."LOGON"                               9.065 MB  442208 rows
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "SOE"."CUSTOMERS"                           4.983 MB   45703 rows
. . exported "SOE"."ADDRESSES"                           4.084 MB   55703 rows
. . exported "SOE"."CARD_DETAILS"                        2.402 MB   55703 rows
. . exported "SOE"."PRODUCT_DESCRIPTIONS"                224.1 KB    1000 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
. . exported "SOE"."PRODUCT_INFORMATION"                 186.4 KB    1000 rows
. . exported "SOE"."EMP"                                 51.39 KB     879 rows
. . exported "SOE"."SALES"                               40.79 KB    1000 rows
. . exported "SOE"."WAREHOUSES"                          36.03 KB    1000 rows
. . exported "SOE"."JOB_HISTORY"                         14.61 KB     246 rows
. . exported "SOE"."JOBS"                                5.984 KB      23 rows
. . exported "SOE"."ORDERENTRY_METADATA"                 5.617 KB       4 rows
. . exported "SOE"."DEPT"                                5.640 KB       7 rows
. . exported "SOE"."V"                                       0 KB       0 rows
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table "SOE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SOE.SYS_EXPORT_SCHEMA_01 is:
  /u03/dbfs/BE1F7D7E50E2922EE0532210000A17B4/data/stage2/adsoe01.dmp
  /u03/dbfs/BE1F7D7E50E2922EE0532210000A17B4/data/stage2/adsoe02.dmp
Job "SOE"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 7 13:58:38 2021 elapsed 0 00:00:59
C:\Users\varun>
 

  • Check expdp dump files inside directory
SQL> col OBJECT_NAME for a60
SQL> SELECT OBJECT_NAME, BYTES/1024/1024 MB FROM DBMS_CLOUD.LIST_FILES('STAGING2') WHERE  OBJECT_NAME LIKE 'adsoe%';

OBJECT_NAME                                                          MB
------------------------------------------------------------ ----------
adsoe_export.log                                             .003703117
adsoe01.dmp                                                   .49609375
adsoe02.dmp                                                   322.59375

SQL> sho user
USER is "SOE"
SQL>


  • Copy the dump files from the directory in staging2 database to the staging bucket
  • Note: Provide privilege to staging2 directory by admin user

BEGIN
DBMS_CLOUD.PUT_OBJECT (
CREDENTIAL_NAME => 'ADMIN_CRED_NAME',
OBJECT_URI => 'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/adsoe01.dmp', 
DIRECTORY_NAME => 'staging2',
FILE_NAME => 'adsoe01.dmp');
END;
/ 
BEGIN
DBMS_CLOUD.PUT_OBJECT (
CREDENTIAL_NAME => 'ADMIN_CRED_NAME',
OBJECT_URI => 'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/adsoe02.dmp', 
DIRECTORY_NAME => 'staging2',
FILE_NAME => 'adsoe02.dmp');
END;
/ 



  • Download file to on premises local directory



IMPDP From autonomous db to on premises database
  •  Connect on premises database and create directory 

 

D:\ud\WINDOWS.X64_193000_db_home\bin>sqlplus sys/system123@pdbtest as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 8 15:29:04 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 pdbtest                           READ WRITE NO
SQL> CREATE DIRECTORY STAGING_DIR AS 'C:\Users\varun\OneDrive\Desktop\oci';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY STAGING_DIR TO soe;

Grant succeeded.

-------------------------------------------
SQL> CREATE USER SOE2 IDENTIFIED BY soe2 QUOTA unlimited ON users;

User created.

SQL> GRANT CONNECT TO soe2;

Grant succeeded.

SQL> GRANT CREATE TABLE TO soe2;

Grant succeeded.

SQL> GRANT CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO soe2;

Grant succeeded.

SQL> GRANT ALTER SESSION TO soe2;

Grant succeeded.


SQL> GRANT READ, WRITE ON DIRECTORY STAGING_DIR TO soe2;

Grant succeeded.

SQL> GRANT CREATE CREDENTIAL TO soe2;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_LOCK TO soe2;

Grant succeeded.

---------------------------------------------------------------------------

C:\Windows\system32>tnsping pdbtest

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 09-JUN-2021 19:42:21

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
D:\ud\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-H1NR6BO)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDBTEST)))
OK (20 msec)

  • IMPDP to on premesis database  pdbtest

 

C:\Windows\system32>impdp \"sys/system@pdbtest as sysdba\" directory=STAGING_DIR schemas=soe remap_schema=soe:soe2 dumpfile=adsoe01.dmp,adsoe02.dmp transform=segment_attributes:n encryption_pwd_prompt=yes

Import: Release 19.0.0.0.0 - Production on Wed Jun 9 19:44:05 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Encryption Password:
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "sys/********@pdbtest AS SYSDBA" directory=STAGING_DIR schemas=soe remap_schema=soe:soe2 dumpfile=adsoe01.dmp,adsoe02.dmp transform=segment_attributes:n encryption_pwd_prompt=yes
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SOE2"."ORDER_ITEMS"                        170.4 MB 3735896 rows
. . imported "SOE2"."CUSTOMERS"                          4.983 MB   45703 rows
. . imported "SOE2"."V"                                      0 KB       0 rows
. . imported "SOE2"."DEPT"                               5.640 KB       7 rows
. . imported "SOE2"."PRODUCT_DESCRIPTIONS"               224.1 KB    1000 rows
. . imported "SOE2"."SALES"                              40.79 KB    1000 rows
. . imported "SOE2"."JOB_HISTORY"                        14.61 KB     246 rows
. . imported "SOE2"."INVENTORIES"                        15.27 MB  902171 rows
. . imported "SOE2"."WAREHOUSES"                         36.03 KB    1000 rows
. . imported "SOE2"."EMP"                                51.39 KB     879 rows
. . imported "SOE2"."ORDERENTRY_METADATA"                5.617 KB       4 rows
. . imported "SOE2"."PRODUCT_INFORMATION"                186.4 KB    1000 rows
. . imported "SOE2"."CARD_DETAILS"                       2.402 MB   55703 rows
. . imported "SOE2"."JOBS"                               5.984 KB      23 rows
. . imported "SOE2"."LOGON"                              9.065 MB  442208 rows
. . imported "SOE2"."ORDERS"                             114.9 MB 1352070 rows
. . imported "SOE2"."ADDRESSES"                          4.084 MB   55703 rows
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
ORA-39083: Object type PROCOBJ:"SOE2"."SOE_CREDENTIAL" failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_CLOUD.CREATE_CREDENTIAL' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Failing sql is:
BEGIN
dbms_cloud.create_credential('"SOE_CREDENTIAL"', username=>'SOE', password=>'NULL', enabled=>TRUE, windows_domain=>NULL, comments=>'{"comments":"Created via DBMS_CLOUD.create_credential"}');COMMIT; END;

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
ORA-39082: Object type PACKAGE BODY:"SOE2"."ORDERENTRY" created with compilation warnings

Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Wed Jun 9 19:47:13 2021 elapsed 0 00:03:00


C:\Windows\system32>


Check error in impdp and look into solution. in our case we compile the schema.

 


SQL> select count(*),object_type,status from dba_objects where owner='SOE2' group by status,object_type;

  COUNT(*) OBJECT_TYPE             STATUS
---------- ----------------------- -------
         7 SEQUENCE                VALID
         1 FUNCTION                VALID
         1 PACKAGE BODY            INVALID
         1 PROCEDURE               VALID
        25 TABLE                   VALID
         2 VIEW                    VALID
        33 INDEX                   VALID
         1 PACKAGE                 VALID

8 rows selected.


SQL> EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SOE2');

PL/SQL procedure successfully completed.

SQL> select count(*),object_type,status from dba_objects where owner='SOE2' group by status,object_type;

  COUNT(*) OBJECT_TYPE             STATUS
---------- ----------------------- -------
         7 SEQUENCE                VALID
         1 FUNCTION                VALID
         1 PACKAGE BODY            VALID
         1 PROCEDURE               VALID
        25 TABLE                   VALID
         2 VIEW                    VALID
        33 INDEX                   VALID
         1 PACKAGE                 VALID

8 rows selected.

SQL>



Posted by at No comments:
Labels:

Tuesday 8 June 2021

OCI - Load CSV object data on OCI autonomous database

Loading a CSV file into a Table in the Autonomous Database using DBMS_CLOUD.COPY_DATA


  • Login on premises database and connect soe schema and collect 1000 rows  objects in csv file.

 D:\test\WINDOWS.X64_193000_db_home\bin>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 17:01:14 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> alter session set container=PDB1;
Session altered.
SQL> connect soe/soe@pdb1
Connected.
SQL> select count(*),object_type,status from dba_objects where owner='SOE' group by status,object_type;

  COUNT(*) OBJECT_TYPE             STATUS
---------- ----------------------- -------
         7 SEQUENCE                VALID
         1 FUNCTION                VALID
         1 PACKAGE BODY            INVALID
         1 PROCEDURE               VALID
         2 VIEW                    VALID
        16 TABLE                   VALID
        33 INDEX                   VALID
         1 PACKAGE                 VALID
8 rows selected.
  • Spool output of sales.csv file which is used further for uploading data into OCI bucket
 


 SQL> SET MARKUP CSV ON DELIMITER , QUOTE ON
SQL> SPOOL C:\Users\varun\OneDrive\Desktop\oci\sales.csv
SQL> SELECT ORDER_ID, TO_CHAR(ORDER_DATE,'DD-MM-YYYY HH24:MI:SS')
  2  ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL FROM ORDERS ORDER BY ORDER_ID
  3  FETCH FIRST 1000 ROWS ONLY;

"ORDER_ID","ORDER_DATE","ORDER_MODE","CUSTOMER_ID","ORDER_STATUS","ORDER_TOTAL"
10001,"01-01-2010 01:34:30","direct",12086,6,9962
10002,"01-01-2010 01:38:19",,12968,6,5796
10003,"01-01-2010 01:42:10","online",16404,7,4845
10004,"01-01-2010 01:46:05","online",10911,6,7274
10005,"01-01-2010 01:49:19","direct",15148,7,3987
10006,"01-01-2010 01:53:00",,7119,6,5675
10007,"01-01-2010 01:56:46","online",4694,6,4965
10008,"01-01-2010 02:00:19",,10384,5,6173
10009,"01-01-2010 02:03:53","online",7525,4,4090
10010,"01-01-2010 02:07:26","direct",10580,4,4839
10011,"01-01-2010 02:10:52","online",13234,5,6393
10012,"01-01-2010 02:14:32","direct",2464,5,6460
10013,"01-01-2010 02:17:52","online",2116,5,5822
10014,"01-01-2010 02:21:17",,19616,6,5098
10015,"01-01-2010 02:24:31","online",11045,7,5552
10016,"01-01-2010 02:28:06","direct",10282,6,7047
10017,"01-01-2010 02:31:36",,13133,5,8758
10018,"01-01-2010 02:35:27",,1886,3,7192
10019,"01-01-2010 02:39:32","direct",1881,6,4174
10020,"01-01-2010 02:43:07","online",15767,4,5374
10021,"01-01-2010 02:47:04","direct",5743,5,5176
10022,"01-01-2010 02:51:14","online",6437,4,4639
10023,"01-01-2010 02:55:08","direct",4923,7,8940
10024,"01-01-2010 02:58:35","online",16527,6,7216
10025,"01-01-2010 03:02:21",,9240,7,7397
10026,"01-01-2010 03:05:35",,7920,4,6609
10027,"01-01-2010 03:08:35","direct",17202,6,6295
10028,"01-01-2010 03:12:28",,16942,5,7891
10029,"01-01-2010 03:15:38","direct",10662,6,7446
10030,"01-01-2010 03:19:08","direct",15902,5,6823
10031,"01-01-2010 03:23:24","online",4729,5,6328
  • Upload the data into bucket




  • Connect to Automatic database  and create table sales on soe schema

 C:\Users\varun>sqlplus soe/Abc#123456789@orcladb_medium
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 17:26:02 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Jun 07 2021 16:18:11 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> sho user
USER is "SOE"
SQL> CREATE TABLE SOE.SALES (
  2  ORDER_ID NUMBER(12),
  3  ORDER_DATE DATE,
  4  ORDER_MODE VARCHAR2(8),
  5  CUSTOMER_ID NUMBER(12),
  6  ORDER_STATUS NUMBER(2),
  7  ORDER_TOTAL NUMBER(8,2)
  8  );
Table created.
SQL>



Login with admin user and check the soe schema credential name



  • Connect and check sales object using soe schema.





  • Check load operation information's by copy data method 
SELECT * FROM USER_LOAD_OPERATIONS WHERE TYPE = 'COPY' ORDER BY ID;




Posted by at No comments:
Labels:

OCI : ORA-20003: Reject limit reached, query table "ADMIN"."COPY$8_LOG" for error

 

We are importing object data into OCI autonomous database schema  getting error while uploading csv file. 

Error : ORA-20003: Reject limit reached, query table "ADMIN"."COPY$8_LOG" for error

Solution : Remove 1st  line header 

C:\Users\varun>sqlplus admin/Cloud$123456@orcladb_medium


SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 17:53:16 2021

Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Last Successful login time: Mon Jun 07 2021 17:37:32 +05:30


Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0


SQL> BEGIN

  2  DBMS_CLOUD.COPY_DATA(

  3   TABLE_NAME =>'SALES',

  4  SCHEMA_NAME =>'SOE',

  5  CREDENTIAL_NAME =>'ADMIN_CRED_NAME',

  6  FILE_URI_LIST =>'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/sales_csvsales.csv',

  7  FORMAT => JSON_OBJECT('delimiter' value ',', 'quote' value '"', 'ignoremissingcolumns'

  8  value 'true', 'dateformat' value 'DD-MM-YYYY HH24:MI:SS', 'blankasnull' value 'true')

  9  );

 10  END;

 11  /

BEGIN

*

ERROR at line 1:

ORA-20003: Reject limit reached, query table "ADMIN"."COPY$8_LOG" for error

details

ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1229

ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 3584

ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 3607

ORA-06512: at line 2



SQL>



  • Remove top line 1 . 



  • Run Again using admin user

BEGIN

DBMS_CLOUD.COPY_DATA(

 TABLE_NAME =>'SALES',

SCHEMA_NAME =>'SOE',

 CREDENTIAL_NAME =>'ADMIN_CRED_NAME',

 FILE_URI_LIST =>'https://objectstorage.ap-mumbai-1.oraclecloud.com/n/bmdsoh48o0kf/b/soe_expdp_bucket/o/soe_salessales.csv',

 FORMAT => JSON_OBJECT('delimiter' value ',', 'quote' value '"', 'ignoremissingcolumns' 

value 'true', 'dateformat' value 'DD-MM-YYYY HH24:MI:SS', 'blankasnull' value 'true')

);

END; 

/ 



  • The LOGFILE_TABLE and BADFILE_TABLE columns contain the names of external tables for accessing the log file and bad file related to each load operation.

SELECT * FROM USER_LOAD_OPERATIONS WHERE TYPE = 'COPY' ORDER BY ID;



  • Delete the load operation logs

exec DBMS_CLOUD.DELETE_ALL_OPERATIONS(type => 'COPY');
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)