1 2 Previous Next 23 Replies Latest reply: Mar 9, 2013 9:43 AM by Peter Gjelstrup Go to original post RSS
      • 15. Re: Query for Empty Tables.
        bhatt t
        Hi Frank,

        I am giving you details, step by step;


        *1. Log file of EXP command in 8i;*
        *=================================*
        --------------------------------------------------------------------------------
        Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
        With the Partitioning option
        JServer Release 8.1.6.0.0 - Production
        Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

        About to export specified users ...
        . exporting pre-schema procedural objects and actions
        . exporting foreign function library names for user S1011
        . exporting object type definitions for user S1011
        About to export S1011's objects ...
        . exporting database links
        . exporting sequence numbers
        . exporting cluster definitions
        . about to export S1011's tables via Conventional Path ...
        . . exporting table A 0 rows exported
        . . exporting table A280706 0 rows exported
        . . exporting table AB 0 rows exported
        . . exporting table ACC_INDEX 0 rows exported
        . . exporting table AMNDD 0 rows exported
        . . exporting table AMNDM 0 rows exported
        . . exporting table BACKUP_USER 311 rows exported
        . . exporting table BALSHEETANNX 0 rows exported
        . . exporting table BALSHEETCONFIG 1 rows exported
        . . exporting table BALSHEETDATA 1 rows exported
        . . exporting table BALSHEETDETAIL 0 rows exported
        . . exporting table CHECKINOUT 0 rows exported
        . . exporting table CHECKINOUT1 0 rows exported
        . . exporting table CLOS1 4938 rows exported
        . . exporting table COMPINFO 2 rows exported
        . . exporting table COMPLAINTS 90 rows exported
        . exporting synonyms
        . exporting views
        . exporting stored procedures
        . exporting operators
        . exporting referential integrity constraints
        . exporting triggers
        . exporting indextypes
        . exporting bitmap, functional and extensible indexes
        . exporting posttables actions
        . exporting snapshots
        . exporting snapshot logs
        . exporting job queues
        . exporting refresh groups and children
        . exporting dimensions
        . exporting post-schema procedural objects and actions
        . exporting statistics
        Export terminated successfully without warnings.
        ------------------------------------------------------------------------------------


        *2. Then this dmp file is imported into 11gr2 by IMP utility. Log file is as follow;*
        *================================================*
        F:\>imp s1011/c9596 STATISTICS=none FULL=Y file=f:\C07032013.dmp

        Import: Release 11.1.0.7.0 - Production on Sat Mar 9 16:21:04 2013

        Copyright (c) 1982, 2007, Oracle. All rights reserved.


        Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
        Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options

        Export file created by EXPORT:V08.01.06 via conventional path
        import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
        import server uses AL32UTF8 character set (possible charset conversion)
        export client uses WE8ISO8859P1 character set (possible charset conversion)
        export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversio
        n)
        . importing S1011's objects into S1011
        . . importing table "A" 0 rows imported
        . . importing table "A280706" 0 rows imported
        . . importing table "AB" 0 rows imported
        . . importing table "ACC_INDEX" 0 rows imported
        . . importing table "AMNDD" 0 rows imported
        . . importing table "AMNDM" 0 rows imported
        . . importing table "BACKUP_USER" 311 rows imported
        . . importing table "BALSHEETANNX" 0 rows imported
        . . importing table "BALSHEETCONFIG" 1 rows imported
        . . importing table "BALSHEETDATA" 1 rows imported
        . . importing table "BALSHEETDETAIL" 0 rows imported
        . . importing table "CHECKINOUT" 0 rows imported
        . . importing table "CHECKINOUT1" 0 rows imported
        . . importing table "CLOS1" 4938 rows imported
        . . importing table "COMPINFO" 2 rows imported
        . . importing table "COMPLAINTS" 90 rows imported

        About to enable constraints...
        Import terminated successfully without warnings.


        COUNT(*) OBJECT_TYPE
        ---------- -------------------
        56 SEQUENCE
        15 PROCEDURE
        1 DATABASE LINK
        35 PACKAGE
        34 PACKAGE BODY
        10 LOB
        133 TRIGGER
        679 INDEX
        493 TABLE
        41 FUNCTION
        72 VIEW

        CONSTRAINTS
        --------------------
        729

        Upto this, everything is OK i.e. 100% data is imported into 11g from 8i.



        *3. When we run EXP in 11g with parameter STATISTICS=none, log file is as follow;*
        *====================================================*
        F:\>exp s1011/c9596 STATISTICS=none

        Export: Release 11.1.0.7.0 - Production on Sat Mar 9 17:22:02 2013

        Copyright (c) 1982, 2007, Oracle. All rights reserved.


        Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
        Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options
        Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
        server uses AL32UTF8 character set (possible charset conversion)

        About to export specified users ...
        . exporting pre-schema procedural objects and actions
        . exporting foreign function library names for user S1011
        . exporting PUBLIC type synonyms
        . exporting private type synonyms
        . exporting object type definitions for user S1011
        About to export S1011's objects ...
        . exporting database links
        . exporting sequence numbers
        . exporting cluster definitions
        . about to export S1011's tables via Conventional Path ...
        . . exporting table BACKUP_USER 311 rows exported
        . . exporting table BALSHEETCONFIG 1 rows exported
        . . exporting table BALSHEETDATA 1 rows exported
        . . exporting table CLOS1 4938 rows exported
        . . exporting table COMPINFO 2 rows exported
        . . exporting table COMPLAINTS 90 rows exported
        . exporting synonyms
        . exporting views
        . exporting stored procedures
        . exporting operators
        . exporting referential integrity constraints
        . exporting triggers
        . exporting indextypes
        . exporting bitmap, functional and extensible indexes
        . exporting posttables actions
        . exporting materialized views
        . exporting snapshot logs
        . exporting job queues
        . exporting refresh groups and children
        . exporting dimensions
        . exporting post-schema procedural objects and actions
        . exporting statistics
        Export terminated successfully without warnings.
        -------------------------------------------------------------------------------------------
        It doesn't show any error, but actually doesn't export all the tables i.e. *0-rows tables*.
        Compare this log with above log.




        *4. Then we run IMP utility to import the above 11g dmp file, log is as follow;*
        *================================================*

        log file shows same tables as per EXP log file above, i.e. missing *0-rows tables*.
        ----------------------------------------------------------------------------------------------------------
        Import: Release 11.1.0.7.0 - Production on Sat Mar 9 18:13:06 2013
        Copyright (c) 1982, 2007, Oracle. All rights reserved.
        Username: s1011
        Password:
        Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
        Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options

        Import file: EXPDAT.DMP > f:\expdat.dmp

        Enter insert buffer size (minimum is 8192) 30720>

        Export file created by EXPORT:V11.01.00 via conventional path
        import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
        import server uses AL32UTF8 character set (possible charset conversion)
        List contents of import file only (yes/no): no >
        Ignore create error due to object existence (yes/no): no >
        Import grants (yes/no): yes >
        Import table data (yes/no): yes >
        Import entire export file (yes/no): no > yes

        . importing S1011's objects into S1011
        . . importing table "BACKUP_USER" 311 rows imported
        . . importing table "BALSHEETCONFIG" 1 rows imported
        . . importing table "BALSHEETDATA" 1 rows imported
        . . importing table "CLOS1" 4938 rows imported
        . . importing table "COMPINFO" 2 rows imported
        . . importing table "COMPLAINTS" 90 rows imported
        .....
        .....
        Import terminated successfully with warnings.
        -----------------------------------------------------------------------------------------------------------

        COUNT(*) OBJECT_TYPE
        ---------- -------------------
        56 SEQUENCE
        15 PROCEDURE
        1 DATABASE LINK
        35 PACKAGE
        34 PACKAGE BODY
        6 LOB
        108 TRIGGER
        477 INDEX
        231 TABLE
        72 VIEW
        41 FUNCTION
        502 CONSTRAINTS


        By comparing above two count(*) User_objects, following objects types were short;

        --------------------------
        *04 LOB*
        *25 TRIGGER*
        *202 INDEX*
        *262 TABLE*
        *227 CONSTRAINTS*
        --------------------------

        My main problesm is how these *0-ROWS TABLES* can be imported ? If tables are imported,
        then related CONSTRAINTS, INDEXES will also be imported obviously.


        I have also tried expdp/impdp, but shows following error;
        UDI-00018: Data pump client is incompatible with 11.01.00.07.00.


        Giving you also both NLS_DATABASE_PARAMETERS for your kind reference;

        PARAMETER --- 8i --- 11g
        ------------------------------ ----------------------------------------------------
        NLS_LANGUAGE --- AMERICAN ---      AMERICAN
        NLS_TERRITORY --- AMERICA      ---     AMERICA
        NLS_CHARACTERSET --- WE8ISO8859P1--- AL32UTF8
        NLS_RDBMS_VERSION --- 8.1.6.0.0      --- 11.2.0.1.0
        ------------------------------------------------------------------------------------
        I have also changed these parameters, but all in vein.

        I have given all the details you asked.
        I m a developer not deep in database, So please guide me in details.

        Thanks & Regards.
        • 16. Re: Query for Empty Tables.
          bhatt t
          Hi Justin Cave,

          I am giving you details, step by step;


          *1. Log file of EXP command in 8i;*
          *=================================*
          --------------------------------------------------------------------------------
          Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
          With the Partitioning option
          JServer Release 8.1.6.0.0 - Production
          Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

          About to export specified users ...
          . exporting pre-schema procedural objects and actions
          . exporting foreign function library names for user S1011
          . exporting object type definitions for user S1011
          About to export S1011's objects ...
          . exporting database links
          . exporting sequence numbers
          . exporting cluster definitions
          . about to export S1011's tables via Conventional Path ...

          . . exporting table A 0 rows exported
          . . exporting table A280706 0 rows exported
          . . exporting table AB 0 rows exported
          . . exporting table ACC_INDEX 0 rows exported
          . . exporting table AMNDD 0 rows exported
          . . exporting table AMNDM 0 rows exported
          . . exporting table BACKUP_USER 311 rows exported
          . . exporting table BALSHEETANNX 0 rows exported
          . . exporting table BALSHEETCONFIG 1 rows exported
          . . exporting table BALSHEETDATA 1 rows exported
          . . exporting table BALSHEETDETAIL 0 rows exported
          . . exporting table CHECKINOUT 0 rows exported
          . . exporting table CHECKINOUT1 0 rows exported
          . . exporting table CLOS1 4938 rows exported
          . . exporting table COMPINFO 2 rows exported
          . . exporting table COMPLAINTS 90 rows exported

          . exporting synonyms
          . exporting views
          . exporting stored procedures
          . exporting operators
          . exporting referential integrity constraints
          . exporting triggers
          . exporting indextypes
          . exporting bitmap, functional and extensible indexes
          . exporting posttables actions
          . exporting snapshots
          . exporting snapshot logs
          . exporting job queues
          . exporting refresh groups and children
          . exporting dimensions
          . exporting post-schema procedural objects and actions
          . exporting statistics
          Export terminated successfully without warnings.
          ------------------------------------------------------------------------------------


          *2. Then this dmp file is imported into 11gr2 by IMP utility. Log file is as follow;*
          *================================================*
          F:\>imp s1011/c9596 STATISTICS=none FULL=Y file=f:\C07032013.dmp

          Import: Release 11.1.0.7.0 - Production on Sat Mar 9 16:21:04 2013

          Copyright (c) 1982, 2007, Oracle. All rights reserved.


          Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
          Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options

          Export file created by EXPORT:V08.01.06 via conventional path
          import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
          import server uses AL32UTF8 character set (possible charset conversion)
          export client uses WE8ISO8859P1 character set (possible charset conversion)
          export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversio
          n)
          . importing S1011's objects into S1011

          . . importing table "A" 0 rows imported
          . . importing table "A280706" 0 rows imported
          . . importing table "AB" 0 rows imported
          . . importing table "ACC_INDEX" 0 rows imported
          . . importing table "AMNDD" 0 rows imported
          . . importing table "AMNDM" 0 rows imported
          . . importing table "BACKUP_USER" 311 rows imported
          . . importing table "BALSHEETANNX" 0 rows imported
          . . importing table "BALSHEETCONFIG" 1 rows imported
          . . importing table "BALSHEETDATA" 1 rows imported
          . . importing table "BALSHEETDETAIL" 0 rows imported
          . . importing table "CHECKINOUT" 0 rows imported
          . . importing table "CHECKINOUT1" 0 rows imported
          . . importing table "CLOS1" 4938 rows imported
          . . importing table "COMPINFO" 2 rows imported
          . . importing table "COMPLAINTS" 90 rows imported

          About to enable constraints...
          Import terminated successfully without warnings.


          COUNT(*) OBJECT_TYPE
          ---------- -------------------
          56 SEQUENCE
          15 PROCEDURE
          1 DATABASE LINK
          35 PACKAGE
          34 PACKAGE BODY
          10 LOB
          133 TRIGGER
          679 INDEX
          493 TABLE
          41 FUNCTION
          72 VIEW

          CONSTRAINTS
          --------------------
          729

          Upto this, everything is OK i.e. 100% data is imported into 11g from 8i.



          *3. When we run EXP in 11g with parameter STATISTICS=none, log file is as follow;*
          *====================================================*
          F:\>exp s1011/c9596 STATISTICS=none

          Export: Release 11.1.0.7.0 - Production on Sat Mar 9 17:22:02 2013

          Copyright (c) 1982, 2007, Oracle. All rights reserved.


          Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
          Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
          server uses AL32UTF8 character set (possible charset conversion)

          About to export specified users ...
          . exporting pre-schema procedural objects and actions
          . exporting foreign function library names for user S1011
          . exporting PUBLIC type synonyms
          . exporting private type synonyms
          . exporting object type definitions for user S1011
          About to export S1011's objects ...
          . exporting database links
          . exporting sequence numbers
          . exporting cluster definitions
          . about to export S1011's tables via Conventional Path ...

          . . exporting table BACKUP_USER 311 rows exported
          . . exporting table BALSHEETCONFIG 1 rows exported
          . . exporting table BALSHEETDATA 1 rows exported
          . . exporting table CLOS1 4938 rows exported
          . . exporting table COMPINFO 2 rows exported
          . . exporting table COMPLAINTS 90 rows exported

          . exporting synonyms
          . exporting views
          . exporting stored procedures
          . exporting operators
          . exporting referential integrity constraints
          . exporting triggers
          . exporting indextypes
          . exporting bitmap, functional and extensible indexes
          . exporting posttables actions
          . exporting materialized views
          . exporting snapshot logs
          . exporting job queues
          . exporting refresh groups and children
          . exporting dimensions
          . exporting post-schema procedural objects and actions
          . exporting statistics
          Export terminated successfully without warnings.
          -------------------------------------------------------------------------------------------
          It doesn't show any error, but actually doesn't export all the tables i.e. *0-rows tables*.
          Compare this log with above log.




          *4. Then we run IMP utility to import the above 11g dmp file, log is as follow;*
          *================================================*

          log file shows same tables as per EXP log file above, i.e. missing *0-rows tables*.
          ----------------------------------------------------------------------------------------------------------
          Import: Release 11.1.0.7.0 - Production on Sat Mar 9 18:13:06 2013
          Copyright (c) 1982, 2007, Oracle. All rights reserved.
          Username: s1011
          Password:
          Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
          Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options

          Import file: EXPDAT.DMP > f:\expdat.dmp

          Enter insert buffer size (minimum is 8192) 30720>

          Export file created by EXPORT:V11.01.00 via conventional path
          import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
          import server uses AL32UTF8 character set (possible charset conversion)
          List contents of import file only (yes/no): no >
          Ignore create error due to object existence (yes/no): no >
          Import grants (yes/no): yes >
          Import table data (yes/no): yes >
          Import entire export file (yes/no): no > yes

          . importing S1011's objects into S1011

          . . importing table "BACKUP_USER" 311 rows imported
          . . importing table "BALSHEETCONFIG" 1 rows imported
          . . importing table "BALSHEETDATA" 1 rows imported
          . . importing table "CLOS1" 4938 rows imported
          . . importing table "COMPINFO" 2 rows imported
          . . importing table "COMPLAINTS" 90 rows imported
          .....
          .....
          Import terminated successfully with warnings.
          -----------------------------------------------------------------------------------------------------------

          COUNT(*) OBJECT_TYPE
          ---------- -------------------
          56 SEQUENCE
          15 PROCEDURE
          1 DATABASE LINK
          35 PACKAGE
          34 PACKAGE BODY
          6 LOB
          108 TRIGGER
          477 INDEX
          231 TABLE
          72 VIEW
          41 FUNCTION
          502 CONSTRAINTS


          By comparing above two count(*) User_objects, following objects types were short;

          --------------------------
          *04 LOB*
          *25 TRIGGER*
          *202 INDEX*
          *262 TABLE*
          *227 CONSTRAINTS*
          --------------------------

          My main problesm is how these *0-ROWS TABLES* can be imported ? If tables are imported,
          then related CONSTRAINTS, INDEXES will also be imported obviously.


          I have also tried expdp/impdp, but shows following error;
          UDI-00018: Data pump client is incompatible with 11.01.00.07.00.


          Giving you also both NLS_DATABASE_PARAMETERS for your kind reference;

          PARAMETER --- 8i --- 11g
          ------------------------------ ----------------------------------------------------
          NLS_LANGUAGE --- AMERICAN ---      AMERICAN
          NLS_TERRITORY --- AMERICA      ---     AMERICA
          NLS_CHARACTERSET --- WE8ISO8859P1--- AL32UTF8
          NLS_RDBMS_VERSION --- 8.1.6.0.0      --- 11.2.0.1.0
          ------------------------------------------------------------------------------------
          I have also changed these parameters, but all in vein.

          I have given all the details you asked.
          I m a developer not deep in database, So please guide me in details.

          Thanks & Regards.
          • 17. Re: Query for Empty Tables.
            Peter Gjelstrup
            Hi,

            I smell 11g deferred segment creation here.

            Could you check how this parameter is set: DEFERRED_SEGMENT_CREATION

            If it is TRUE, then the default behaviour is DEFERRED. Meaning you will not get a segment during the import from 8i.

            I am guessing that exp deals with segments, so empty tables (Rather tables with no segment) are not exported.

            IF I am right, one fix would be to set DEFERRED_SEGMENT_CREATION = FALSE prior to the import

            Another fix would be to find those tables (So this is the answer to orginal question on "Query for empty tables"):

            from all_tables
            where segment_created = 'NO'

            And then allocate an extent for each

            Regards
            Peter

            Edit:
            Found some discussion here :
            unable to export empty tables

            Edited by: Peter on Mar 9, 2013 6:28 AM
            • 18. Re: Query for Empty Tables.
              Hoek
              UDI-00018: Data Pump client is incompatible with database version string
              Cause: The Import utility is at a higher version than the database version and is thereby incompatible.
              Action: Use the same version of Import utility as the database.
              http://docs.oracle.com/cd/E11882_01/server.112/e17766/udius.htm#sthref15119

              How are you invoking exp and imp?
              • 19. Re: Query for Empty Tables.
                Hoek
                Oh, and have a look here: https://forums.oracle.com/forums/thread.jspa?threadID=1089481
                http://tsells.wordpress.com/2011/02/10/oracle-11g2-does-not-export-empty-tables-when-using-exp-exe/

                Edited by: Hoek on Mar 9, 2013 3:25 PM
                • 20. Re: Query for Empty Tables.
                  ranit B
                  Hi Hoek,
                  Oh, and have a look here: https://forums.oracle.com/forums/thread.jspa?threadID=1089481
                  Even after striking off, the link still remains active and clickable. ;)
                  Shouldn't they try to fix this?

                  (Sorry guys for going off-track)
                  • 21. Re: Query for Empty Tables.
                    Hoek
                    I guess there are more urgent things to fix on the Forums ;)
                    • 22. Re: Query for Empty Tables.
                      sb92075
                      Peter Gjelstrup wrote:
                      Hi,

                      I smell 11g deferred segment creation here.
                      I mentioned this 2 days ago in this thread but was ignored by all.

                      You can lead some folks to knowledge, but you can't make them think.
                      • 23. Re: Query for Empty Tables.
                        Peter Gjelstrup
                        Hi sb,

                        So you did! - Did not notice since I never followed your link.

                        OP probably never cared to investigate, either.

                        BR
                        Peter
                        1 2 Previous Next