This discussion is archived
6 Replies Latest reply: Feb 19, 2013 11:10 PM by onedbguru RSS

Data pump

989658 Newbie
Currently Being Moderated
Hi Guys,

I have used following code to impdb from abc schema and it worked properly but when trying to import data in another schema getting error, i am using oracle 11g express edition.
expdp abc/def tables=emp directory=DIR_EXPDP_IMPDT dumpfile=EMP.dmp logfile=emp.log

+*impdt ghi/jkl tables=emp directory=DIR_EXPDP_IMPDT dumpfile=emp.dmp logfile=emp.log*+

+*impdt ghi/jkl  directory=DIR_EXPDP_IMPDT dumpfile=emp.dmp logfile=emp.log*+
getting error,

Thanks in advance.

Zaid
  • 1. Re: Data pump
    asahide Expert
    Currently Being Moderated
    Hi,

    What error occured?
    Can you try remap_schema option?

    Regards,
  • 2. Re: Data pump
    Niket Kumar Pro
    Currently Being Moderated
    Pleas post error what you are getting and it impdp not impdt
  • 3. Re: Data pump
    989658 Newbie
    Currently Being Moderated
    Hi,

    I am posting here errors;
    impdp ghi/jkl tables=emp directory=dir_expdb_impdt dumpfile=emp.log logfile=emp.log
    
    *log file*
    Import: Release 11.2.0.2.0 - Production on Wed Feb 20 08:26:22 2013
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    ;;; 
    Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
    Master table "GHI"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
    ORA-39166: Object GHI.EMP was not found.
    Job "GHI"."SYS_IMPORT_TABLE_01" successfully completed at 08:26:24
    Job "GHI"."SYS_IMPORT_TABLE_01" successfully completed at 08:26:24
    When skipped table option, then getting following error:
    impdp ghi/jkl directory=dir_expdb_impdt dumpfile=emp.log logfile=emp.log
    
    
    Import: Release 11.2.0.2.0 - Production on Wed Feb 20 08:33:37 2013
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    ;;; 
    Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
    Master table "GHI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "GHI"."SYS_IMPORT_FULL_01":  ghi/******** directory=dir_expdb_impdt dumpfile=emp.dmp logfile=emp.log 
    Processing object type TABLE_EXPORT/TABLE/TABLE
    ORA-39151: Table "SYSTEM"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
    ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
    ORA-06550: line 2, column 2:
    PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
    ORA-06550: line 2, column 2:
    PL/SQL: Statement ignored
    Failing sql is:
    BEGIN 
     SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('XE','IBMPC/WIN_NT-8.1.0');
     END; 
    ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
    ORA-06550: line 2, column 2:
    PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
    ORA-06550: line 2, column 2:
    PL/SQL: Statement ignored
    Failing sql is:
    BEGIN 
     SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('XE','IBMPC/WIN_NT-8.1.0');
     END; 
    ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
    ORA-06550: line 2, column 2:
    PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
    ORA-06550: line 2, column 2:
    PL/SQL: Statement ignored
    Failing sql is:
    BEGIN 
     SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('XE','IBMPC/WIN_NT-8.1.0');
     END; 
    ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
    ORA-06550: line 2, column 2:
    PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
    ORA-06550: line 2, column 2:
    PL/SQL: Statement ignored
    Failing sql is:
    BEGIN 
     SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('XE','IBMPC/WIN_NT-8.1.0');
     END; 
    ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
    ORA-06550: line 2, column 2:
    PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
    ORA-06550: line 2, column 2:
    PL/SQL: Statement ignored
    Failing sql is:
    BEGIN 
     SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('XE','IBMPC/WIN_NT-8.1.0');
     END; 
    ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
    ORA-06550: line 2, column 2:
    PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
    ORA-06550: line 2, column 2:
    PL/SQL: Statement ignored
    Failing sql is:
    BEGIN 
     SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('XE','IBMPC/WIN_NT-8.1.0');
     END; 
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Job "GHI"."SYS_IMPORT_FULL_01" completed with 7 error(s) at 08:33:39
    thanks in advance,

    Zaid
  • 4. Re: Data pump
    onedbguru Pro
    Currently Being Moderated
    the error is self explanatory. Read the error message. You imported the table, then you tried to import it again with the full. What do you not understand about that?

    Processing object type TABLE_EXPORT/TABLE/TABLE
    ORA-39151: Table "SYSTEM"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
    Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
    ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
    ORA-06550: line 2, column 2:
    PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
    ORA-06550: line 2, column 2:
    PL/SQL: Statement ignored

    Drop the table and restart the full.
  • 5. Re: Data pump
    989658 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply, i want to export data to another schema, lets say i want to imp in ghi schema from schema a.

    if you go at the top of the question, i have exported from one schema and want to import into another schema, where this table does not exist.

    Best Regards,
    Zaid
  • 6. Re: Data pump
    onedbguru Pro
    Currently Being Moderated
    look at REMAP_SCHEMAS in impdp.

    expdp ...

    impdp .... remap_schemas=abc,def

    you can also remap_tables and a whole lot more. It's in the documentation which is a great source of information on how to do stuff in Oracle.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points