4 Replies Latest reply: Jun 3, 2011 2:02 AM by P.Forstmann RSS

    Reg: Find Current stored procedure status in oracle database

    740288
      Hi Gurus,

      How to find the stored procedure status in the current oracle database (ie) whether it is running or not.

      Is there any view or dbms package to find the status of the stored procedure.

      And i also want to find the last execution time of the stored procedure is there any view to find the status

      Thanks in Advance.
        • 1. Re: Reg: Find Current stored procedure status in oracle database
          sb92075
          ora_man_8873786 wrote:
          Hi Gurus,

          How to find the stored procedure status in the current oracle database (ie) whether it is running or not.
          short answer is "no way"
          longer answer is "with much difficulty"

          >
          Is there any view or dbms package to find the status of the stored procedure.
          query DBA_OBJECTS

          >
          And i also want to find the last execution time of the stored procedure is there any view to find the status
          possible if AUDIT was previously enabled
          Thanks in Advance.
          Edited by: sb92075 on Jun 2, 2011 10:50 AM
          • 2. Re: Reg: Find Current stored procedure status in oracle database
            P.Forstmann
            ora_man_8873786 wrote:
            Hi Gurus,

            How to find the stored procedure status in the current oracle database (ie) whether it is running or not.
            You can query DBA_DDL_LOCKS: see http://www.shutdownabort.com/dbaviews/dba_ddl_locks.php.

            And i also want to find the last execution time of the stored procedure
            You need to use audit for this: see Re: procedure last executed

            Edited by: P. Forstmann on 2 juin 2011 19:53
            • 3. Re: Reg: Find Current stored procedure status in oracle database
              864403
              For 10g and above, you can get the information from dba_hist_sqltext. Please be aware that only top SQL's are there in the AWR report but this is the easiest approach


              Look for command_type 47. You can list of command types by querying audit_actions table.


              EGY_MASTER@regy : SQL>select * from dba_hist_sqltext where sql_id='4ksbzcnsx214v';


              DBID SQL_ID SQL_TEXT COMMAND_TYPE
              "----------------------------------------------------------------------------------------------------------------------------------"
              2124122816 4ksbzcnsx214v BEGIN :1 :=xxxxxxxxxxxxxxxxxxxxxxxxxx END 47


              Let me know if this helps

              thanks
              http://swervedba.wordpress.com
              • 4. Re: Reg: Find Current stored procedure status in oracle database
                P.Forstmann
                Note that using DBA_HIST_SQLTEXT requires Diagnostic Pack license:

                in 10G http://download.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm#CIHGFIAF says:
                >
                All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.
                >

                in 11G http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/options.htm#CIHIHDDJ says:
                >
                All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license.