Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Unanswered: missing records in table

    Hi,

    I have a table that stores documents and images in a blob field, the table has 100,000 records in and this morning 60 seem to be missing! The front end program offers no option anywhere to delete the records and the clients have no direct access via oracle. I am dumping the table and going to reimport it incase and index has become corrupt or somthing like that to stop me being able to find the missing records but does anyone have any ideas of what could have happened

    cheers
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a way to check for index problems ...

    SELECT a.name, a.obj#, a.dataobj#, b.dataobj#
    FROM obj$ a, ind$ b
    WHERE a.obj# = b.obj#
    AND a.dataobj# != b.dataobj# ;

    ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE;

    -- This will cross check the table with all of its indexes. This can take
    -- a while and will block DML while it runs. It will report an ORA-1499
    -- error if there is a mismatch between the table and one of its indexes.
    -- This will not detect IOT data loss.
    -- An alternative but less rigorous check is to use HINT statements:

    -- OR

    SET TRANSACTION READ ONLY;

    -- Find the total number of entries in the table

    SELECT /*+ FULL(TABLENAME) */ count(*) FROM TABLENAME;

    -- Find the total number of entries in the index

    SELECT /*+ INDEX_FFS(TABLENAME INDEXNAME) */ count(*) FROM TABLENAME;


    HTH
    Gregg

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    thanks for that.

    There were no errors analyzing the table and both counts came back the same so I guess there isn't an index problem. I'll have to wait and see if my import manages to find the missing records!
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Have you tried looking at your archivelogs thru logminer to see what happened when and why ???

    Gregg

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Sorry ... Also with logminer, you can pull out the UNDO statements ...

  6. #6
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    archive logs tunrned off at request of my MD

    They are not in the dump file so I guess there are no problems with the table and some how they have been deleted though I am struggling to see how, I will have to investigate further (and switch the archive logs back on!) ..... must be aliens!

    cheers for your help
    There are 10 types of people in the world, those that know Binary and those that don't.

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Sorry ... Every now and again ... Ghosts ... or Aliens ... or "C" all the above ...

Posting Permissions

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