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 (
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
