4 Replies Latest reply: Jan 22, 2013 4:48 PM by Richard Harrison . RSS

    impdp not loading data !

    AnkitV
      Hi All

      My requirement is to import multiple specific tables only. Database is Oracle 10g R2.

      I ran following 4 imports from command prompt in Win7. First 2 worked fine, last 2 are behaved weirdly and didn;t give intended output in SQLFILE=tt_sql.sql.

      *1) Importing only a function named DFLT. This was OK.*
      C:\windows\system32>impdp hr/ankit@xe SCHEMAS=hr INCLUDE=FUNCTION:"LIKE'DFLT'" DIRECTORY=MY_DIR1 DUMPFILE=HR_27DEC.dmp SQLFILE=tt_sql.sql

      Import: Release 11.2.0.2.0 - Production on Mon Jan 21 12:30:07 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 "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
      Starting "HR"."SYS_SQL_FILE_SCHEMA_01": hr/********@xe SCHEMAS=hr INCLUDE=FUNCT
      ION:LIKE'DFLT' DIRECTORY=MY_DIR1 DUMPFILE=HR_27DEC.dmp SQLFILE=tt_sql.sql
      Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
      Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
      Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 12:30:09

      *2) Importing function named DFLT and a table EMP_33_MV only. This was also OK.*
      C:\windows\system32>impdp hr/ankit@xe SCHEMAS=hr INCLUDE=FUNCTION:"LIKE'DFLT'", INCLUDE=TABLE:"LIKE'EMP_33_MV'" DIRECTORY=MY_DIR1 DUMPFILE=HR_27DEC.dmp SQLFILE=tt_sql.sql

      Import: Release 11.2.0.2.0 - Production on Mon Jan 21 12:30:40 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 "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
      Starting "HR"."SYS_SQL_FILE_SCHEMA_01": hr/********@xe SCHEMAS=hr INCLUDE=FUNCT
      ION:LIKE'DFLT',INCLUDE=TABLE:LIKE'EMP_33_MV' DIRECTORY=MY_DIR1 DUMPFILE=HR_27DEC
      .dmp SQLFILE=tt_sql.sql
      Processing object type SCHEMA_EXPORT/TABLE/TABLE
      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/FUNCTION/FUNCTION
      Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 12:30:46

      *3) Importing2 tables, EMP_33 and EMP_33_MV. This gave an error as shown below "ORA-31655: no data or metadata objects selected for job".*

      C:\windows\system32>impdp hr/ankit@xe SCHEMAS=hr INCLUDE=TABLE:"LIKE'EMP_33'",INCLUDE=TABLE:"LIKE'EMP_33_MV'" DIRECTORY=MY_DIR1 DUMPFILE=HR_27DEC.dmp SQLFILE=tt_sql.sql

      Import: Release 11.2.0.2.0 - Production on Mon Jan 21 12:33:58 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
      ORA-31655: no data or metadata objects selected for job
      Master table "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
      Starting "HR"."SYS_SQL_FILE_SCHEMA_01": hr/********@xe SCHEMAS=hr INCLUDE=TABLE
      :LIKE'EMP_33',INCLUDE=TABLE:LIKE'EMP_33_MV' DIRECTORY=MY_DIR1 DUMPFILE=HR_27DEC.
      dmp SQLFILE=tt_sql.sql
      Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 12:33:59

      *4) Importing function named DFLT and 2 tables, EMP_33 and EMP_33_MV. Interestingly, This loaded only the FUNCTION, as you can see, there is no reference to any TABLE in the logs below.*

      C:\windows\system32>impdp hr/ankit@xe SCHEMAS=hr INCLUDE=FUNCTION:"LIKE'DFLT'",INCLUDE=TABLE:"LIKE'EMP_33'",INCLUDE=TABLE:"LIKE'EMP_33_MV'" DIRECTORY=MY_DIR1 DUMPFILE=HR_27DEC.dmp SQLFILE=tt_sql.sql

      Import: Release 11.2.0.2.0 - Production on Mon Jan 21 12:34:15 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 - Productio
      n
      Master table "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
      Starting "HR"."SYS_SQL_FILE_SCHEMA_01": hr/********@xe SCHEMAS=hr INCLUDE=FUNCT
      ION:LIKE'DFLT',INCLUDE=TABLE:LIKE'EMP_33',INCLUDE=TABLE:LIKE'EMP_33_MV' DIRECTOR
      Y=MY_DIR1 DUMPFILE=HR_27DEC.dmp SQLFILE=tt_sql.sql
      Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
      Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
      Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 12:34:18

      For case 4, SQLFILE contained only following data, i.e. for the function only, no table.

      -- CONNECT HR
      ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
      ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
      -- new object type path: SCHEMA_EXPORT/FUNCTION/FUNCTION
      CREATE function dflt return number is
      cnt number := 0;
      begin cnt := cnt + 1; return 45; end;

      /
      -- new object type path: SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

      ALTER FUNCTION "HR"."DFLT"
      COMPILE
      PLSQL_OPTIMIZE_LEVEL= 2
      PLSQL_CODE_TYPE= INTERPRETED
      PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'

      REUSE SETTINGS TIMESTAMP '2013-01-02 16:06:13'
      /


      1) Can you tell why steps 3 and 4 behaved so ?
      2) I need to know how can I import multiple specifically mentioned tables only. Command should work on windows and AIX both.

      Thanks a lot.
        • 1. Re: impdp not loading data !
          Dean Gagne-Oracle
          INCLUDE=TABLE:"LIKE'EMP_33'",INCLUDE=TABLE:"LIKE'EMP_33_MV'"

          You are missing the % in the like command. The first says only emp_33, the second says emp_ee_mv. If all you want is emp_33_mv, then just use

          Tables=emp_33_mv.

          Dean
          • 2. Re: impdp not loading data !
            AnkitV
            Hi
            Actually EMP_33 and EMP_33_MV both are different tables. And I want to mention specific table names if I want to import several tables. I knowingly didn;t use Operator % as I want to specify full table names. I tried using = operator but got syntax incorrect everytime.
            You can take TABLE_2 in place of EMP_33_MV. Please tell me how to specify function DFLT and tables EMP_33 and TABLE_2 for importing.

            Thanks.
            • 3. Re: impdp not loading data !
              Dean Gagne-Oracle
              To specify multiple tables, you would use the tables= parameter.

              A couple of notes you should know about. If you don't specify a schema on the table parameter, you get the schema running the job.

              Ex: expdp system/manager tables=emp_33 ...

              You would get table system.emp_33

              If you did this:

              Ex: expdp stpystem/manager tables=Scott.emp_33 ...

              You would get Scott.emp_33

              Prior to 11.1.0.6, you could not mix schemas. But 11.1.0.6 and later, you can

              Ex: expdp system/manager tables=scott.emp_33, hr.emp_33_mv

              If you need to export multiple tables from the same schema, do this

              Ex: expdp syste/manager tables=Scott.emp_33, Scott.emp_33_mv ...

              Hope thismhelps.

              Dean
              • 4. Re: impdp not loading data !
                Richard Harrison .
                Hi,
                This note explains why

                http://laurentschneider.com/wordpress/2011/07/datapump-table-like-foo-or-like-bar.html

                Cheers,
                Harry