select object_name ||' ('|| object_type ||')' "Name"
  from dba_OBJECTS
 where status = 'DBMS_ASSERT'
   and owner='OE'
order by object_name, object_type

select * from DBA_SOURCE_AE where upper(text) like '%DBMS_ALERT%'

select TIME_REMAINING, ELAPSED_SECONDS, opname, SOFAR, TOTALWORK from V$SESSION_LONGOPS

select name,
       type,
       decode(usage,'DECLARATION', 'body only', 'DEFINITION', 'spec and body', usage) defined_on,
       line body_line
  from user_identifiers ui
  where type in ('PROCEDURE', 'FUNCTION')
  and usage_context_id = (select usage_id
    from user_identifiers
      where object_name = ui.object_name
        and object_type = ui.object_type
        and usage_context_id = 0)
  and object_name = 'STANDARD'
  and object_type = 'PACKAGE BODY'
  order by name


select o.object_type
         , case
               when o.object_type in ( 'PACKAGE', 'TYPE') then p.object_name
               else null
           end as object_name
         , case
               when o.object_type in ( 'PACKAGE', 'TYPE') then p.procedure_name
               else p.object_name
           end as procedure_name
        , case
              when a.position != 0 then a.argument_name
              when a.position = 0 then '** return value **'
              else null
           end as argument_name
        , a.data_type
 from user_arguments a
       , user_procedures p
      , user_objects o
 where o.object_type in ( 'PACKAGE', 'TYPE', 'FUNCTION', 'PROCEDURE')
 and   p.object_name = o.object_name
 and   p.subprogram_id != 0
 and   a.object_id (+) = p.object_id
 and   a.subprogram_id (+) = p.subprogram_id
 and   a.data_level(+) = 0
 and   p.OBJECT_NAME = 'DBMS_ADDM'
 order by o.object_type, o.object_name, p.subprogram_id, a.position
