6 Replies Latest reply: Feb 19, 2013 5:10 PM by onedbguru RSS

    Data pump

    989658
      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
          Hi,

          What error occured?
          Can you try remap_schema option?

          Regards,
          • 2. Re: Data pump
            Niket Kumar
            Pleas post error what you are getting and it impdp not impdt
            • 3. Re: Data pump
              989658
              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
                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
                  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
                    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.