oracle show procedures code example

Example 1: oracle show procedures

-- Specifically for SQL:Oracle

SELECT * 
FROM ALL_PROCEDURES;

Example 2: oracle show running procedures

SELECT 'CALLED PLSQL' AS STATUT,
       VS.USERNAME,
       VS.OSUSER,
       VS.PROGRAM,
       VS.MODULE,
       VS.ACTION,
       VS.SQL_EXEC_START,
       D_O.OBJECT_NAME,
       D_O.OBJECT_TYPE
FROM DBA_OBJECTS D_O
         INNER JOIN V$SESSION VS ON D_O.OBJECT_ID = VS.PLSQL_ENTRY_OBJECT_ID
UNION ALL
SELECT 'CURRENT PLSQL' AS STATUT,
       VS.USERNAME,
       VS.OSUSER,
       VS.PROGRAM,
       VS.MODULE,
       VS.ACTION,
       VS.SQL_EXEC_START,
       D_O.OBJECT_NAME,
       D_O.OBJECT_TYPE
FROM DBA_OBJECTS D_O
         INNER JOIN V$SESSION VS ON D_O.OBJECT_ID = VS.PLSQL_OBJECT_ID;

Tags: