Script to Report Stored Code

Script to Report Stored Code

This report lists all stored code in the database. It can report on all objects, all objects by owner, or on a specific object name.

REM LOCATION:   Object Management\Functions,Procedures, and Packages

REM FUNCTION:   Report on Stored Code

REM TESTED ON:  10.2.0.3, 11.1.0.6 (not tested but should work on previous versions)

REM PLATFORM:   non-specific

REM REQUIRES:   dba_objects, proc_count

REM NOTES:      Must be run from an account with SYSDBA privileges.

REM

REM

REM ********************  Oracle Administration ********************

REM

UNDEF ENTER_OWNER_NAME

UNDEF ENTER_OBJECT_NAME

SET pages 53 lines 80 verify off feedback off echo off

COLUMN owner    format a10

COLUMN name     format a30

COLUMN type     format a9    word_wrapped

COLUMN status   format a7

COLUMN lines    format 9,999

BREAK on owner skip page on type

TTITLE "Report on Stored Code"

WITH proc_count AS

     (SELECT   owner, NAME, TYPE, COUNT (*) lines

          FROM dba_source

         WHERE owner LIKE UPPER ('&&ENTER_OWNER_NAME')

           AND NAME LIKE UPPER ('&&ENTER_OBJECT_NAME')

      GROUP BY owner, NAME, TYPE)

SELECT   b.owner owner, b.object_type TYPE, b.object_name NAME,

         b.status status, b.last_ddl_time modified, lines

    FROM dba_objects b, proc_count a

   WHERE b.owner NOT IN ('SYS', 'SYSTEM')

     AND b.owner = a.owner

     AND b.object_type = a.TYPE

     AND b.object_name = a.NAME

     AND b.owner LIKE UPPER ('&&ENTER_OWNER_NAME')

     AND b.object_name LIKE UPPER ('&&ENTER_OBJECT_NAME')

ORDER BY 1, 2, 3;