See all table modifications by user in Oracle SQL

Question

I'm trying to fetch a list of table modifications by user, but in view ALL_TAB_MODIFICATIONS I'm only seeing tables with their modifications by type.

Is there a way to know how many modifications have happened by user? The same view but adding the USER column?

EdStevens · Accepted Answer · 2020-06-04 01:22:29Z

2

That's what Oracle's AUDIT functionality is designed for.

First, you have to set the AUDIT_TRAIL init parm - https://docs.oracle.com/database/121/REFRN/GUID-BD86F593-B606-4367-9FB6-8DAB2E47E7FA.htm#REFRN10006

Then you enable auditing on whatever action you want to audit, with the AUDIT sql statement - https://docs.oracle.com/database/121/REFRN/GUID-BD86F593-B606-4367-9FB6-8DAB2E47E7FA.htm#REFRN10006

Then, when audited action occur, you see them when you query DBA_AUDIT_TRAIL - https://docs.oracle.com/database/121/REFRN/GUID-A9993FAC-12D3-4725-A37D-938CC32D74CC.htm#REFRN23023

The above is the simplest way to get started. It does not cover the new 'unified auditing'. There are also other DBA_* views the give filtered versions of DBA_AUDIT_TRAIL. But the above will give you the starting points.

answered Jun 4, 2020 at 1:22

EdStevens

3,9203 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

See all table modifications by user in Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related