Home » SQL & PL/SQL » SQL & PL/SQL » Finding database objects
Finding database objects [message #257146] Tue, 07 August 2007 11:44 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I have a temp table called 'EXL_VENDORS' and this temp table has three columns namely Checkrun_name,Invoice_Num and Invoice_Date.
This table is getting populated when we run the payment batch process in Oracle Applications. I am not sure which process in the back-ground is inserting values dynamically into this temp table.

Is there any way through which we can find the db objects which are referring this temp table?

Thanks
Safeeq
Re: Finding database objects [message #257151 is a reply to message #257146] Tue, 07 August 2007 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which we can find the db objects which are referring this temp table?

Can you precise your question?

You can find static dependencies in dba_dependencies then you have all objects referring this table.
But if you have some dynamic query referring the table in a procedure you will not find here and nowhere but v$access for the current executions.

Regards
Michel
Re: Finding database objects [message #257153 is a reply to message #257146] Tue, 07 August 2007 11:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If the table is accessed by a database object (like procedure, package...) you can find the information from user_dependencies. The query is something like
select * from user_dependencies where reference_name = <TABLE_NAME> and referenced_type = 'TABLE' 
But if the procedure uses dynamic sql like for example 'EXECUTE IMMEDIATE' then there will not be any information held in the user_dependencies.

Hope that helps.

Regards

Rajaram

Michael : You posted it while i am typing...

[Updated on: Tue, 07 August 2007 11:59]

Report message to a moderator

Re: Finding database objects [message #257155 is a reply to message #257146] Tue, 07 August 2007 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
AUDIT INSERT ON EXL_VENDORS
AUDIT update ON EXL_VENDORS
Re: Finding database objects [message #257425 is a reply to message #257155] Wed, 08 August 2007 05:58 Go to previous message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Atlast We have found the piece of code in the "after report" trigger of a report which is inserting the values into this temp table.

Thanks to everyone for their help.
Previous Topic: get max value.
Next Topic: Print code for a query
Goto Forum:
  


Current Time: Sat Dec 03 12:13:28 CST 2016

Total time taken to generate the page: 0.12592 seconds