Oracle Training Oracle Tips Oracle Forum Class Catalog Remote DBA Oracle Tuning Emergency 911 RAC Support Apps Support Analysis Design Implementation Oracle Support
SQL Tuning Security Oracle UNIX Oracle Linux Monitoring Remote support Remote plans Remote services Application Server Applications Oracle Forms Oracle Portal App Upgrades SQL Server Oracle Concepts Software Support Remote Support
Consulting Staff Consulting Prices Help Wanted!
Oracle Posters Oracle Books Oracle Scripts Ion Excel-DB
Don Burleson Blog
Question: I have a stored procedure and I want to count the number of times that it was executed during specific time frames. How do I audit the execution times for an Oracle stored procedure?
Answer: Oracle auditing can be used to track the invocation of Oracle functions and Oracle stored procedures and you can audit the number of times a stored procedure was called by defining an Oracle audit on the stored procedure:
SQL> audit execute on scott.testing_proc_audit by access; Audit succeeded. SQL> select os_username, username, obj_name,owner from dba_audit_trail where obj_name = 'TESTING_PROC_AUDIT'; no rows selected --call the stored procedure and trigger the audit operation SQL> select testing_proc_audit from dual; TESTING_P --------- 04-AUG-14 SQL> select os_username, username, obj_name,owner from dba_audit_trail where obj_name = 'TESTING_PROC_AUDIT';
OS_USERNAME USERNAME OBJ_NAME OWNER TIMESTAMP -------------------- ------------------------------ -------------------- -------------------- --------- oracle SCOTT TEST_PROC_AUDIT SCOTT 08-AUG-14
As we see, the audit command will place auditing for any sotred procedure on function into the dba_audit_trail view.
Burleson is the American Team
Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.
Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.
Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:
and include the URL for the page.
Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services Copyright © 1996 - 2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.
Burleson Consulting The Oracle of Database Support Oracle Performance Tuning
Oracle Performance Tuning
Remote DBA Services
Copyright © 1996 - 2020
All rights reserved by Burleson
Oracle