database account monitoring

Is there any SQL queries that could be used to go through audit logs and filter for any activity in the database by a specific account, to get an idea if its being used, and for what purposes?
LVL 4
pma111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicIT ArchitectCommented:
Hi,

you can use view DBA_AUDIT_TRAIL on AUD$ table and there you have username columns so you view the audit logs on object that have enabled auditing, i.e. if you did not enable auditing on database and you did not specify audit policy then the audit table will not be helpful.

Since this is the view and if you have huge AUD$ table it will take a while.

select * from DBA_AUDIT_TRAIL where usernema=USERNAME;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
if you don't have auditing turned on, then you can try log miner.  You'll need to work with your dba to get it set up
Mark GeerlingsDatabase AdministratorCommented:
LogMiner is slower than queries of DBA_AUDIT_TRAIL, and LogMiner *DOES NOT* capture queries ("select..." statements.  LogMiner only captures: insert, update and delete activity plus some DLL actions (create, alter, grant, etc.).

You may need to turn on some auditing options to make sure that what you are looking for can be found in DBA_AUDIT_TRAIL from now on.  By default, this view will be empty.  If you do turn on some auditing options, be aware that you then also need to plan to manage the large volume of data that will be captured, or you may fill up a tablespace and/or a disk partition, etc.
sdstuberCommented:
log miner was for deciding "after the fact" that you wanted to audit.

it's not a guarantee, but if you don't have anything else, it's a place to start.

kind of like auditing.  you might have it turned on, but not for all the stuff you decide later you wanted to check.
So then you're stuck.

also consider looking through your application logs.  If your application is well instrumented,  you might get far better results from them than with oracle features because your logs can capture context, not just action.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.