4 Replies Latest reply: Apr 12, 2013 2:32 AM by BluShadow RSS

    Procedure execution logging..

      Hi All,
      Is there any oracle supplied tables or views whiich logs details about all the stored routines (function , procedures, packages ) execution in database?
      Actually, client wants to move the business logic from plsql to app server (java) and to do that I first need to find out that out of hundreds of stored routines , how many are actually being used by application.

      Our database version is 11gR2.
        • 1. Re: Procedure execution logging..
          Purvesh K
          No, there isn't any, unless you have created one of your own to trace the execution of your stored methods.

          See reply from Tom Kyte for How to get Dependencies among Stored Objects?
          • 2. Re: Procedure execution logging..
            SQL> desc dba_source
             Name                                      Null?    Type
             ----------------------------------------- -------- ----------------------------
             OWNER                                              VARCHAR2(30)
             NAME                                               VARCHAR2(30)
             TYPE                                               VARCHAR2(12)
             LINE                                               NUMBER
             TEXT                                               VARCHAR2(4000)
            SQL> desc dba_objects
             Name                                      Null?    Type
             ----------------------------------------- -------- ----------------------------
             OWNER                                              VARCHAR2(30)
             OBJECT_NAME                                        VARCHAR2(128)
             SUBOBJECT_NAME                                     VARCHAR2(30)
             OBJECT_ID                                          NUMBER
             DATA_OBJECT_ID                                     NUMBER
             OBJECT_TYPE                                        VARCHAR2(19)
             CREATED                                            DATE
             LAST_DDL_TIME                                      DATE
             TIMESTAMP                                          VARCHAR2(19)
             STATUS                                             VARCHAR2(7)
             TEMPORARY                                          VARCHAR2(1)
             GENERATED                                          VARCHAR2(1)
             SECONDARY                                          VARCHAR2(1)
             NAMESPACE                                          NUMBER
             EDITION_NAME                                       VARCHAR2(30)
            • 3. Re: Procedure execution logging..
              Nimish Garg
              If you know a starting point/s this may be helpful
              • 4. Re: Procedure execution logging..
                Not sure if using the PL/Scope functionality is of any use to you, as I'm not quite sure what output you're looking for...


                It can be used to see what calls what and what variables are referenced etc. throughout the code.