This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Mar 9, 2013 7:43 AM by Peter Gjelstrup Go to original post RSS
  • 15. Re: Query for Empty Tables.
    bhatt t Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    I guess there are more urgent things to fix on the Forums ;)
  • 22. Re: Query for Empty Tables.
    sb92075 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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