Skip to Main Content
  • Questions
  • finding out the source of the data in a table

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, sanjeev.

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

Last updated: November 01, 2019 - 1:07 am UTC

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

Viewed 1000+ times

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 Connor 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.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

LogMiner

Mikhail Velikikh, October 31, 2019 - 12:10 pm UTC

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
November 01, 2019 - 1:07 am UTC

Nice input!

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.