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

shajju · #1 (**permalink**) 08-24-10, 05:45

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

shammat · #2 (**permalink**) 08-24-10, 05:52

Quote:

Originally Posted by shajju

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?

shajju · #3 (**permalink**) 08-24-10, 07:20

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?

shammat · #4 (**permalink**) 08-24-10, 07:45

Did you check dbms_scheduler?

shajju · #5 (**permalink**) 08-24-10, 07:54

Thanks but I'm using Oracle 9i. Can't see any jobs set up to insert data into TAB1 either.

magicwand · #6 (**permalink**) 08-24-10, 08:25

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

n_i · #7 (**permalink**) 08-24-10, 09:20

Quote:

Originally Posted by shajju

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.

anacedent · #8 (**permalink**) 08-24-10, 09:52

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

shajju · #9 (**permalink**) 08-25-10, 01:42

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?

shajju · #10 (**permalink**) 08-25-10, 01:54

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?

anacedent · #11 (**permalink**) 08-25-10, 09:26

>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

shajju · #12 (**permalink**) 08-28-10, 01:21

Many Thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech