[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

Profiling an Oracle 9i DB

I was wondering how I can profile an Oracle 9i database so that I can see all SQL executed (output to a text file is fine) on the database by a user session (similar to the database profiler in SQL Server).    
0
superfly18
Asked:
superfly18
  • 4
  • 3
2 Solutions
 
MikeOM_DBACommented:

You could use the AUDIT statement.
0
 
superfly18Author Commented:
How should it be used in these case?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you can also enable TRACE for that user or session to see what all is happening. The trace file generated will be a raw trace and you need to convert it to readable format by using tkprof utility.

For more details on the above, please refer to 12 chapter Using Oracle Trace in the below link

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/toc.htm

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
superfly18Author Commented:
Is this something that be run from SQLplus, and output to a dump file?  If so what would the syntax be?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i think may be you are looking for enabling trace in sql*plus

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#1018

This can be done by following the steps in the above link "Using the SQL Trace Facility and TKPROF"
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ALTER SESSION SET SQL_TRACE = TRUE;
 
-- you can do that with just a single statement but this has some performance overhead because a lot of data is written to the trace file on what all sql statements are being issued in the session.
0
 
superfly18Author Commented:
And if I want to trace the session of another user?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if you want to know what is happening in a particular session or what are all the operatins done by a user without his knowledge, then either we have to enable sql trace at the init.ora level meaning a lot of trace files will be generated for each session/user and a lot of file system space is also required if you db is being used by many users/programs.

why don't you look at the below link to get an understadning of the log miner facility :

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm 

search for "Using LogMiner to Analyze Redo ogs"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now