Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: How on earth is this table getting it's data!

    Hi guys

    I have an oracle 9i table which was getting data once everyday till July 12. But since July 12 no data has been inserted into the table. The irony is I don't know how this table is/was getting it's data. I have tried the following queries but to no avail:

    Code:
    select *
    from dba_dependencies 
    where referenced_name like '%TAB1%';
    
    select *
    from user_dependencies 
    where referenced_name like '%TAB1%';
    
    SELECT * FROM USER_SOURCE WHERE TEXT LIKE '%TAB1%';
    
    select * from v$sql where SQL_TEXT like '%TAB1%'
    I have an inclin towards a procedure doing the job but cannot see any such procedure inserting data into TAB1 or scheduled in the crontab.

    Could someone kindly advise on any more methods to find out how TAB1 is/was getting poplulated with data?

    Many Thanks
    Shajju

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    Could someone kindly advise on any more methods to find out how TAB1 is/was getting poplulated with data?
    A shell script using SQL*Loader?

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks for the suggestion however, I can't see any such script in the crontab. See, the front-end isn't giving any clue as to how data was being inserted/loaded into TAB1. I was hoping there was some backend way?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Did you check dbms_scheduler?

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Thanks but I'm using Oracle 9i. Can't see any jobs set up to insert data into TAB1 either.

  6. #6
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    This is not necessarily from a cron job on the database server. Can you rule out inserts and/or data loads from another machine ?

    Check your "listener.log" file for candidates
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by shajju View Post
    any more methods to find out how TAB1 is/was getting poplulated with data?
    I'd suggest a "wall plug test". Rename the table and see who comes to you complaining about a failing job.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You could write a trigger that fires when DML occurs against this table.
    It could produce a simple audit record to provide clues regarding the culprit
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Aug 2008
    Posts
    464
    Many thanks for your replies.
    As I said no data has been inserted into the table since July 12 so would a trigger still work?
    Where is the listener.log located on a HPUX Itanium machine?

  10. #10
    Join Date
    Aug 2008
    Posts
    464
    Not sure if this will help but the table in question has daily partitions with a week's worth of partitions created in advance so after the 12th, no partitions have been created and I can see the last partition created is for the 19th of July. So no partitions are getting created either. Partitions should continue to be created regardless of data being inserted into the table, right?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Partitions should continue to be created regardless of data being inserted into the table, right?
    Only if job to add partitions actually run & run without error.
    Partition creation task could be 100% independent of data INSERT job
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Aug 2008
    Posts
    464
    Many Thanks.

Posting Permissions

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