Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    2

    Unanswered: Could not see data from imported dump(.dmp) using select count(*) from sqlplus

    Hi,
    This is a very strange problem i am facing. i could import the oracle dump using imp utility with few warnings related to create force view statements. i am using Oracle 9.2.0.1.0-windows.After import , i could see the the no. of rows in each table via Oracle Enteprise Manager console. However, on issuing select count(*) against a table from sqlplus shows 0 rows.
    Could anyone provide pointers/help in this direction ?This is a large database consuming around 8GB space after loading the dump.The dump is of size 13344kb (arnd 13.3 MB).

    Thanks,
    Rahul

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It sounds very strange to me; if import log file shows that, for a certain schema, table gets imported (and you see number of records), why wouldn't it show the correct record number from SQL*Plus? Commit shouldn't be the issue here as import implicitly commits inserted records.

    I guess it is unncessary to ask, but - did you connect to the right database, issued correct credentials and queried the right table?

  3. #3
    Join Date
    Sep 2006
    Posts
    2
    Hi,
    I forgot to mention that the original dump(exported) is from Oracle SuSE Linux environment.
    Regards,

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This shouldn't make any difference; Oracle import and export utilities use a proprietary binary file format and, therefore, can be used only between Oracle databases. Operating system really doesn't matter (as far as I can tell).

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by rahulkale11
    I forgot to mention that the original dump(exported) is from Oracle SuSE Linux environment.
    As littlefoot said, OS should not make a difference, IMP & EXP are designed to handle OS specific differences (32 vs. 64 bit, byte ordering ...)

    What does make a difference is the version of the EXP & IMP tools and the version of the database. But IMP would have told you if it could not proces the .dmp file.

    I would say, you are connecting to a different database from SQL*Plus than from Enterprise Manager. Did you check that your tnsnames.ora is correct?

    I think EM can connect without using tnsnames (as it is a Java application) but SQL*Plus needs it, so maybe you have a mismatch there.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Also, probably the row no. you're seeing from EM are just from old statistics ?

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    -- OR -- maybe you imported the data to the wrong schema, you may have forgot to use the FROMUSER/TOUSER options if the export was done using different userid.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Sep 2006
    Posts
    1
    OEM only shows the number of rows stored in the dba_tables view, col num_rows, which is populated by the dbms_stats or analyse table. The reasons, apart from the fromuser touser mentioned above are all too numerous. If the table existed before importing and ignore=y was not specified, the data would not be loaded. Also, were the rows included in the export? And so on.
    Enda.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This promises to be one of the most guessing-type threads here; without OP's feedback, I doubt we'll find a solution before my birthday. Why my birthday? Why not?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •