Oracle通过shell脚本查看procedure的信息

在一个schema中，可能含有大量的procedure, 有时候想查看具体的信息，一般得通过toad,plsql dev等工具来查看，有时候在尽可能摆脱图形工具的前提下，想能够尽快的查找一些信息，还是使用shell脚本更快，更准，更直接。可以使用如下的shell脚本来查找procedure的信息。
以下的脚本可以查找是否有需要的prcedure信息。PROC_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<END
set pagesize 40 feedback off verify off heading on echo off
col owner format a20
col object_name format a30
set linesize 150
select owner, object_name,object_id,object_type,aggregate,pipelined,parallel,interface,deterministic,authid from dba_procedures
where owner=upper（"$1"） and object_type="PROCEDURE" and object_name like "%"||upper（"$2"）||"%"
/
exit;
END`if [ -z "$PROC_OWNER" ]; then
echo "no object exists, please check again"
exit 0
else
echo "*******************************************"
echo " $PROC_OWNER "
echo "*******************************************"
fi以下的脚本可以查看对应的procedure信息PROC_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<END
set pagesize 40 feedback off verify off heading on echo off
col owner format a20
col object_name format a30
set linesize 150
select owner, object_name,object_id,object_type,aggregate,pipelined,parallel,interface,deterministic,authid from dba_procedures
where owner=upper（"$1"） and object_type="PROCEDURE" and object_name like "%"||upper（"$2"）||"%"
/
exit;
END`if [ -z "$PROC_OWNER" ]; then
echo "no object exists, please check again"
exit 0
else
echo "*******************************************"
echo " $PROC_OWNER "
echo "*******************************************"
fi
脚本运行的结果如下：[ora11g@rac1 dbm_lite]$ ksh findproc.sh n1
*******************************************

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE AGG PIP PAR INT DET AUTHID
-------------------- ------------------------------ ---------- ------------- --- --- --- --- --- ------------
N1 TEST_DUMP_CSV 15163 PROCEDURE NO NO NO NO NO DEFINER
*******************************************
[ora11g@rac1 dbm_lite]$ ksh showproc.sh n1 test_dmp_csv
no object exists, please check again [ora11g@rac1 dbm_lite]$ ksh showproc.sh n1 test_dump_csv
*******************************************

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE AGG PIP PAR INT DET AUTHID
-------------------- ------------------------------ ---------- ------------- --- --- --- --- --- ------------
N1 TEST_DUMP_CSV 15163 PROCEDURE NO NO NO NO NO DEFINER
*******************************************
.
procedure test_dump_csv
as
l_rows number;
begin
l_rows := dump_csv（ "select *
from t
",
",", "/tmp", "test.dat" ）;
end;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址