プロシージャ情報取得

プロシージャ情報取得


set heading off
col OWNER for a10
col OBJECT_TYPE for a15
col OBJECT_NAME for a30
col hi for a20
select OWNER
    , OBJECT_NAME
    , OBJECT_TYPE
    , to_char(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI') as hi
 from dba_objects
where OBJECT_TYPE = 'PROCEDURE'
--  and status = 'INVALID'
order by hi;


OWNER      OBJECT_NAME                    OBJECT_TYPE     LAST_DDL
---------- ------------------------------ --------------- --------
SYS        AW_DROP_PROC                   PROCEDURE       03-06-27
SYS        DBMS_LOGMNR_FFVTOLOGMNRT       PROCEDURE       03-06-27
SYS        DBMS_LOGMNR_OCTOLOGMNRT        PROCEDURE       03-06-27


TYPE 
    FUNCTION    PACKAGE    PACKAGE BODY    PROCEDURE    TRIGGER
    
    
    
    select text from all_source  where name = 'XXXXX'  ;
    select text from user_source where name = 'XXXXX'  ;    
    





プロシージャソース表示
# SYS権限

set heading off
select text
  from dba_source
 where name = upper('&procedure_name')
   and owner = upper('&OWNER')
--   and type  = upper('&type')
 order by line;


プロシージャソース表示
# ユーザ権限

set heading off
set lines 1000
select text
  from user_source
 where name = upper('&procedure_name')
--   and type  = upper('&type')
 order by line;


# SQL全文の取得方法

V$SQLからADDRESS列、HASH_VALUE列の値を参照しセット。

set pages 100 feed off timing off echo off lines 140
SELECT sql_text
  FROM v$sqltext
 WHERE hash_value = 1273901568
   AND address    = '54B4DF20' 
 ORDER BY piece;