how audit session on oracle instance if stop/start is not possible


how audit session on oracle instance if stop/start is not possible, I can't use the following command :
alter system  set audit_sys_operations=true scope=spfile;


Who is Participating?

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

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.

slightwv (䄆 Netminder) Commented:
Some things just require a database bounce.

If you need to use Oracle auditing, I think you need to bounce the database.

If you don't need to use Oracle Auditing you might be able to create a system level trigger but those are pretty dangerous if you mess them up.
bibi92Author Commented:

Thanks, I can't bounce the db. Can I use the following command for tracing SQL Activity :
SQL> alter system set sql_trace=true scope=both;

System altered.

SQL> alter system set timed_statistics=true scope=both;

System altered.

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
slightwv (䄆 Netminder) Commented:
What EXACTLY are you trying to accomplish?

audit_sys_operations, sql_trace and  timed_statistics aren't even close to one another.

sql_trace is a BAD thing to set system wide because of all the information it collects:

timed_statistics just captures (from the docs): "statistics related to time".
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bibi92Author Commented:
I want to trace activity sql on one schema. Thanks
slightwv (䄆 Netminder) Commented:
>> trace activity sql

That doesn't tell me anything.

What information are you hoping to gain by this tracing?
What are you planning to do with the information?
bibi92Author Commented:
To know if the schema is used by users or applications.
slightwv (䄆 Netminder) Commented:
Then none of the options you suggested will work.  Well, tracing ALL SQL might but it will likely generate more information than you have disk space and time to review.

You also need to be careful with what you are asking.  Even if you capture access to schema objects for a period of time, does it really tell you it is no longer used?

What it the objects are only used once a year?  How about once every 5 years?

How long to you audit before you can say with 100% certainty it is obsolete?

All that said:
Check the value of audit_trail in the spfile.  It is likely DB.

If it is anything other than NONE, you can audit without bouncing the database.

I don't know how to audit access to all objects in a schema.  There are ways to audit a schema but I don't think it audits access from outside the schema.

Here is an example of auditing select on a table that I cobbled together:
create user bob identified by bob;
create table bob.tab1(col1 char(1));
insert into bob.tab1 values('a');

audit select on bob.tab1 by session;

select username, obj_name, action_name from dba_audit_trail where owner='BOB';

select count(*) from bob.tab1;

select username, obj_name, action_name from dba_audit_trail where owner='BOB';

Open in new window

Alex [***Alex140181***]Software DeveloperCommented:
If it is anything other than NONE, you can audit without bouncing the database.
Just to add: If audit mode is "just" DB (without EXTENDED), you will NOT see any SQL statements at all, just the objects being accessed (if audit is enabled upon these objects).
bibi92Author Commented:
Thanks, audit_trail string NONE, so it's the reason for that I search another solution.
slightwv (䄆 Netminder) Commented:
I don't think you can get what you want without bouncing the database.

I've thought about a previous comment I made about system triggers and I don't even think that will get you what you want.

Auditing is the way.
bibi92Author Commented:
Thanks, I can't boucing this production database, it's the reason for that I search a solution. Regards
slightwv (䄆 Netminder) Commented:
Schedule the audit fix for a scheduled outage.  EVERY production system has them...

I also remind you of the possible issues with what you want to do:  How long do you look for usage before you can say the schema is not used?

You can't even change the password to test it. Other schemas might have access to it without direct logins.

I can only think of two ways to get what you want:
The only 100% sure way is auditing or dropping it and see who complains.  I would not recommend the second option...
bibi92Author Commented:
Sorry is it not possible. Production has to been available 24x24 7/7 thanks a lot
slightwv (䄆 Netminder) Commented:
I would contact Oracle Support to see what options might be available.
slightwv (䄆 Netminder) Commented:
It is your question and can close it however you want.

However your solution likely will not get you what you stated you wanted:
"To know if the schema is used by users or applications."
bibi92Author Commented:
I can catch sql in the traces files. Thanks regards
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.