In this post, you will learn how to import a dump file into the adwc_hr schema in the Oracle Autonomous Data Warehouse Cloud (ADWC) service instance using data pump (impdp).

Login with ADWC instacne and invoke SQL*plus then create the user adwc_hr.

C:\Users\skagupta>sqlplus admin@skantorcldb_high

SQL*Plus: Release 18.0.0.0.0 Production on Thu Feb 21 18:14:16 2019
Version 18.2.0.0.0

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

Enter password:
Last Successful login time: Thu Feb 21 2019 18:13:35 +05:30

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> create user adwc_hr identified by Oracle123890;

User created.

SQL> Grant dwrole to adwc_hr;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Run the import command.

[oracle@edvmr1p0 ~]$ impdp admin@skantorcldb_high_proxy directory=DATA_PUMP_DIR remap_schema=hr:adwc_hr credential=DEF_STORE_CRED DUMPFILE=https://objectstorage.us-ashburn-1.oraclecloud.com/p/eSgOhh-vg-pEFppc78WZ5QUBULDK8dBHK5PvCMcGdTk/n/ocuocictrng22/b/NEWBUCKET/o/export_hr_adwc.dmp partition_options=merge transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y exclude=index, cluster,indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Import: Release 18.0.0.0.0 - Production on Thu Feb 21 13:44:50 2019
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_FULL_01":  admin/********@skantorcldb_high_proxy directory=DATA_PUMP_DIR remap_schema=hr:adwc_hr credential=DEF_STORE_CRED DUMPFILE=https://objectstorage.us-ashburn-1.oraclecloud.com/p/eSgOhh-vg-pEFppc78WZ5QUBULDK8dBHK5PvCMcGdTk/n/ocuocictrng22/b/NEWBUCKET/o/export_hr_adwc.dmp partition_options=merge transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y exclude=index, cluster,indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ADWC_HR" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-39083: Object type TABLESPACE_QUOTA:"ADWC_HR"."USERS" failed to create with error:
ORA-00959: tablespace 'USERS' does not exist

Failing sql is:
DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "ADWC_HR" QUOTA UNLIMITED ON "USERS"';  EXECUTE IMMEDIATE SQLSTR;EXCEPTION   WHEN OTHERS THEN    IF SQLCODE = -30041 THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY''';      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;      IF TEMP_COUNT = 1 THEN RETURN;       ELSE RAISE;       END IF;    ELSE      RAISE;    END IF;END;

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 "ADWC_HR"."EMPLOYEES"                       17.08 KB     107 rows
. . imported "ADWC_HR"."LOCATIONS"                       8.437 KB      23 rows
. . imported "ADWC_HR"."JOB_HISTORY"                     7.195 KB      10 rows
. . imported "ADWC_HR"."JOBS"                            7.109 KB      19 rows
. . imported "ADWC_HR"."DEPARTMENTS"                     7.125 KB      27 rows
. . imported "ADWC_HR"."COUNTRIES"                       6.367 KB      25 rows
. . imported "ADWC_HR"."REGIONS"                         5.546 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT SELECT ON "ADWC_HR"."COUNTRIES" TO "OE"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT REFERENCES ON "ADWC_HR"."COUNTRIES" TO "OE"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT SELECT ON "ADWC_HR"."LOCATIONS" TO "OE"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT REFERENCES ON "ADWC_HR"."LOCATIONS" TO "OE"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT SELECT ON "ADWC_HR"."DEPARTMENTS" TO "OE"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT SELECT ON "ADWC_HR"."JOBS" TO "OE"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT SELECT ON "ADWC_HR"."EMPLOYEES" TO "OE"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT REFERENCES ON "ADWC_HR"."EMPLOYEES" TO "OE"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist

Failing sql is:
GRANT SELECT ON "ADWC_HR"."JOB_HISTORY" TO "OE"

Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "ADMIN"."SYS_IMPORT_FULL_01" completed with 11 error(s) at Thu Feb 21 13:47:11 2019 elapsed 0 00:01:35

[oracle@edvmr1p0 ~]$
[oracle@edvmr1p0 ~]$

After successfully completion of import. Now, we can perform post checks.

ON-Premise Machine

[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 21 19:24:51 2019
Version 19.2.0.0.0

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


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

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

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

  COUNT(*) OBJECT_TYPE
---------- -----------------------
         8 INDEX
         2 TRIGGER
         2 PROCEDURE
         3 SEQUENCE
        10 TABLE
         1 VIEW

6 rows selected.

ADWC Instance

C:\Users\skagupta>sqlplus admin@skantorcldb_high

SQL*Plus: Release 18.0.0.0.0 Production on Thu Feb 21 19:25:48 2019
Version 18.2.0.0.0

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

Enter password:
Last Successful login time: Thu Feb 21 2019 19:22:14 +05:30

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> select count(*) from adwc_hr.employees;

  COUNT(*)
----------
       107

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

  COUNT(*) OBJECT_TYPE
---------- -----------------------
         8 INDEX
         2 TRIGGER
         2 PROCEDURE
         3 SEQUENCE
         10 TABLE
         1 VIEW

6 rows selected.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.