• Questions
  • finding out the source of the data in a table

Breadcrumb

Announcement

Forty years

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, sanjeev.

Asked: October 30, 2019 - 11:18 am UTC

Answered by: Connor McDonald - Last updated: November 01, 2019 - 1:07 am UTC

Category: Database Development - Version: Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Partial indexing - get the dictionary definitions right

You Asked

Hi Tom,

I am working on a database application, and i need to know how a table is being populated in the database schema.

I have tried querying xxx_dependencies and xxx_source but of no use.
I believe that this table might be populated from an external server by means of oracle net service.

Is there a way to find out the source of the data, or the server from which the data is being pushed into oracle.

Thanks in advance.

and we said...

I have tried querying xxx_dependencies and xxx_source but of no use.

which means it is not PLSQL or similar.

A couple of options you could try:

1) enable auditing on that table - we collect some session/program metadata along with the fact that the table was accessed.

audit insert, update, delete on "my_table" by access;

2) Start with V$SQL to find statements that refer to the table. With a list of relevant SQL_ID's, query V$ACTIVE_SESSION_HISTORY to get information on those sessions that issued those statements.


and you rated our response

  (1 rating)

Reviews

LogMiner

October 31, 2019 - 12:10 pm UTC

Reviewer: Mikhail Velikikh from Dublin, Ireland

In addition to what Connor said, sometimes I just use LogMiner to find those sessions populating tables.
It provides certain benefits over the auditing approach:
1. it can be used anytime as long as you have your archive logs and supplemental logging is on, whereas auditing should be enabled beforehand
2. the archive logs can be shipped or copied to any other database for further analysis.

Here is the documentation for LogMiner if you want to give it a try:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html#GUID-3417B738-374C-4EE3-B15C-3A66E01AE2B5
Connor McDonald

Followup  

November 01, 2019 - 1:07 am UTC

Nice input!