oracle-databaseoracle-apexdatabase-administration
import oracle 11g database dump file into 18c XE , success import but can't find the data or the users
I'm trying to import oracle 11g DB dump file into my new 18c XE by following these instructions: To import data in your 18c XE database, perform the following steps:
Connect to the 18c XE database as user SYS using the SYSDBA privilege.
Create directory object DUMP_DIR and grant READ and WRITE privileges on the directory to the SYSTEM user.
sqlplus / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=XEPDB1;
SQL> CREATE DIRECTORY DUMP_DIR AS '\dump_folder';
SQL> GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;
- Import data to the 18c XE database from the dump folder.
impdp system/system_password@localhost:listnerport/xepdb1 full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=DUMP_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log
and I'm getting the following response witch looks like success:
but when I connect to xepdb1, I find that REPORTS schema and users are not imported, non of the data are loaded
am I missing something?
update 1: The full text log
;;;
Import: Release 18.0.0.0.0 - Production on Tue Jun 23 11:26:23 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@localhost:1521/xepdb1 full=Y EXCLUDE=SCHEMA:"LIKE \'APEX_%\'",SCHEMA:"LIKE \'FLOWS_%\'" directory=DUMP1_DIR dumpfile=DATABASE2.dmp logfile=DATABASE2.log
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "REPORTS"."TV_REPORTS" 14.14 KB 13 rows
. . imported "REPORTS"."DEMO_PRODUCT_INFO" 25.38 KB 10 rows
. . imported "REPORTS"."EBA_DP_DATA_SOURCES" 11.34 KB 2 rows
. . imported "REPORTS"."Y_AUDIT" 17.78 KB 78 rows
. . imported "REPORTS"."A7BAR1" 9.648 KB 184 rows
. . imported "REPORTS"."A7BAR_TEST" 8.656 KB 86 rows
. . imported "REPORTS"."CENTER" 5.5 KB 5 rows
. . imported "REPORTS"."CONSTRAINT_LOOKUP" 5.515 KB 1 rows
. . imported "REPORTS"."DEMO_CONSTRAINT_LOOKUP" 5.679 KB 4 rows
. . imported "REPORTS"."DEMO_CUSTOMERS" 11.01 KB 7 rows
. . imported "REPORTS"."DEMO_ORDERS" 7.367 KB 10 rows
. . imported "REPORTS"."DEMO_ORDER_ITEMS" 7.734 KB 49 rows
. . imported "REPORTS"."DEMO_STATES" 6.242 KB 51 rows
. . imported "REPORTS"."DEMO_TAGS" 8.25 KB 6 rows
. . imported "REPORTS"."DEMO_TAGS_SUM" 5.476 KB 3 rows
. . imported "REPORTS"."DEMO_TAGS_TYPE_SUM" 5.906 KB 3 rows
. . imported "REPORTS"."DEPT" 5.937 KB 4 rows
. . imported "REPORTS"."DISTENY_REPORT" 5.414 KB 1 rows
. . imported "REPORTS"."EBA_DP_ACCESS_LEVELS" 5.468 KB 3 rows
. . imported "REPORTS"."EBA_DP_CATEGORIES" 8.359 KB 1 rows
. . imported "REPORTS"."EBA_DP_DATASRC_TYPES" 8.492 KB 2 rows
. . imported "REPORTS"."EBA_DP_DEMO_PROJECTS" 17.32 KB 73 rows
. . imported "REPORTS"."EBA_DP_DEMO_TABLE" 14.75 KB 121 rows
. . imported "REPORTS"."EBA_DP_ERROR_LOOKUP" 5.882 KB 1 rows
. . imported "REPORTS"."EBA_DP_FORMAT_MASKS" 6.875 KB 25 rows
. . imported "REPORTS"."EBA_DP_PREFERENCES" 8.101 KB 10 rows
. . imported "REPORTS"."EBA_DP_RESERVED_NAMES" 7.562 KB 1 rows
. . imported "REPORTS"."EBA_DP_RPT_TYPES" 10.28 KB 5 rows
. . imported "REPORTS"."EBA_DP_TAGS" 8.382 KB 8 rows
. . imported "REPORTS"."EBA_DP_TAGS_SUM" 5.5 KB 6 rows
. . imported "REPORTS"."EBA_DP_TAGS_TYPE_SUM" 5.968 KB 6 rows
. . imported "REPORTS"."EBA_DP_USERS" 8.328 KB 1 rows
. . imported "REPORTS"."EBA_DP_WHITELIST_OBJECTS" 8.484 KB 2 rows
. . imported "REPORTS"."EBA_DP_WIDGET_TYPES" 15.90 KB 4 rows
. . imported "REPORTS"."EMP" 8.570 KB 14 rows
. . imported "REPORTS"."EMPLOYEE" 7.914 KB 3 rows
. . imported "REPORTS"."GUEST" 6.765 KB 5 rows
. . imported "REPORTS"."JOB_TITLE" 5.476 KB 3 rows
. . imported "REPORTS"."REPORTEMPLOYEELINK" 5.859 KB 3 rows
. . imported "REPORTS"."REPORTEVAL" 5.976 KB 6 rows
. . imported "REPORTS"."REPORT_GUESTLINK" 5.453 KB 3 rows
. . imported "REPORTS"."REPORT_SHOW" 5.851 KB 2 rows
. . imported "REPORTS"."REPORT_TAPELINK" 5.875 KB 4 rows
. . imported "REPORTS"."REPORT_TYPE" 5.421 KB 1 rows
. . imported "REPORTS"."R_ORGANIZATION" 6.367 KB 4 rows
. . imported "REPORTS"."TAPETYPE" 5.421 KB 2 rows
. . imported "REPORTS"."TAPE_INFO" 5.843 KB 3 rows
. . imported "REPORTS"."TVPROGRAM" 5.882 KB 3 rows
. . imported "REPORTS"."USERS_YARA" 6.257 KB 2 rows
. . imported "REPORTS"."APEX$_ACL" 0 KB 0 rows
. . imported "REPORTS"."APEX$_WS_FILES" 0 KB 0 rows
. . imported "REPORTS"."APEX$_WS_HISTORY" 0 KB 0 rows
. . imported "REPORTS"."APEX$_WS_LINKS" 0 KB 0 rows
. . imported "REPORTS"."APEX$_WS_NOTES" 0 KB 0 rows
. . imported "REPORTS"."APEX$_WS_ROWS" 0 KB 0 rows
. . imported "REPORTS"."APEX$_WS_TAGS" 0 KB 0 rows
. . imported "REPORTS"."APEX$_WS_WEBPG_SECTIONS" 0 KB 0 rows
. . imported "REPORTS"."APEX$_WS_WEBPG_SECTION_HISTORY" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_CALENDARS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_DATA_SOURCE_PERMS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_ERRORS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_FAVORITES" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_FILES" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_FILTER_REPORT" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_FILTER_RPT_FILTERS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_HISTORY" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_INVOCATIONS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_NOTES" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_NOTIFICATIONS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_PDF_RPT" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_PDF_RPT_SRC" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_PDF_RPT_SRC_COLS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_REPORTS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_REPORT_PERMS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_REPORT_VALIDATIONS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_RPT_VAL_DEPENDENCYS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_USER_PREF" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_VIEWERS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_VIEWER_GROUPS" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_VIEWER_GROUP_REF" 0 KB 0 rows
. . imported "REPORTS"."EBA_DP_WIDGETS" 0 KB 0 rows
. . imported "REPORTS"."REPORT_ORGALINK" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/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/COMMENT
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Jun 23 11:27:00 2020 elapsed 0 00:00:29
Solution
To check imported data in a pluggable database, you need to be connected to the target pluggable database with:
alter session set container=<pdb>;
- Orcale SQL select next and previous 3 rows of a specific row in groups
- Oracle SQL developer error: could not install some modules
- How to exit sqlplus when error happens
- What is the difference between -Xss and -XX:ThreadStackSize?
- EF query to Oracle throwing "ORA-12704: character set mismatch"
- Load data to remote DB using sqlldr
- Oracle ORDERED hint cost vs speed
- Oracle Merge Statement Raises "ORA-02012: missing USING keyword" Error
- How to debug a SP response in Quarkus using EntityManager
- How to avoid variable substitution in Oracle SQL Developer
- How to use Function inside of MERGE USING clause in Oracle?
- ORA-01861 Error When Trying to Create WHERE Date is 6 Months From Current Date
- Identify the content type within a BLOB field
- Does Connection pool also closes associated Session on the Database?
- Is it possible to use GROUP BY with bind variables?
- How to insert an "Optimizer hint" to Hibernate criteria api query
- Spring boot with Oracle AQ and Universal Connection Pool
- SQL/Oracle: Is it possible to have a group function in a check constraint?
- With python-oracledb what does 'DPY-4027: no configuration directory to search for tnsnames.ora' mean
- Return multiple ref cursors in single OUT parameter of stored procedure
- Creating triggers with table referencing
- Does Oracle have an equivalent of SQL Server's table variables?
- Oracle: What does `(+)` do in a WHERE clause?
- Oracle database 19C, In SQL query how to compare '["a", "b"]' = '["b", "a"]' is TRUE
- How to treat MAX() of an empty table as 0 instead of NULL
- Optimizing OLE DB Destination for Fast load from Oracle to SQL Server for SSIS
- Oracle client appears to be transmitting all 4000 varchar characters for each row
- Java JDBC executeUpdate not working on Delete SQL with TRUNC function in where clause
- Oracle thinks table doesn't exist during alter
- Update statement using with clause