CODEWITHSUNDEEP
sqldatabaseoracle-database
John
John

Reputation: 31

How to monitor and log all the SQL insert commands

I am using Oracle SQL Dev 2.1.1.64

I work with application that uses oracle database for storage. Is there any way in SQL Dev. to monitor and log all the insert commands that are "coming" from the web application into database? Can you tell me how to do that?

Upvotes: 3

Views: 2159

Answers (4)

jwenting
jwenting

Reputation: 5663

Create a trigger that writes to a journaling table whenever a change of data in the table happens (insert, update, delete). Before delete, after insert, after update triggers are what you want.

It won't specifically log only the web application, but if you log the user making the change you will be able to filter on that when viewing the data.

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40489

audit insert table by <web-application-user> by access

should get you started.

Be sure to set the parameters audit_trail and audit_file_dest as you need them.

After that, you find the operations either in sys.aud$ or in the directory specified by audit_file_dest.

There is also fine grained auditing into which you might take a look, but from your question, using fine grained auditing (FGA) would seem to be overkill.

Upvotes: 4

diagonalbatman
diagonalbatman

Reputation: 17992

You could query v$SQL, but you would need to have the relevant GRANTS to enable you to do this.

For long running sessions you can also monitor progress using v$session_longops

hope this helps you.

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

You can write a trigger for the tables you want to monitor. If you are only interested on the insert queries coming from the Web Application, you can check on the trigger for some specific username/schema accessing the table, and use that username as your web application credentials.

Alternatively you can also use Oracle's AUDIT feature. It requires a little bit of Oracle Database Administration knowledge to implement though...

Upvotes: 1

Related Questions