[20160501]查看包参数脚本.txt

[20160501]查看包参数脚本.txt

--我以前写的脚本

SCOTT@book> @ &r/desc_proc sys dbms_stats get_table%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DATA_TYPE            D
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- -
SYS        DBMS_STATS           GET_TABLE_STATS                         1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        2 TABNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        2 TABNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        3 PARTNAME             VARCHAR2             IN        VARCHAR2             Y
                                                                        3 PARTNAME             VARCHAR2             IN        VARCHAR2             Y
                                                                        4 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        4 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        5 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        5 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        6 NUMROWS              NUMBER               OUT       NUMBER               N
                                                                        6 NUMROWS              NUMBER               OUT       NUMBER               N
                                                                        7 NUMBLKS              NUMBER               OUT       NUMBER               N
                                                                        7 NUMBLKS              NUMBER               OUT       NUMBER               N
                                                                        8 AVGRLEN              NUMBER               OUT       NUMBER               N
                                                                        8 AVGRLEN              NUMBER               OUT       NUMBER               N
                                                                        9 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                        9 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                       10 CACHEDBLK            NUMBER               OUT       NUMBER               N
                                                                       11 CACHEHIT             NUMBER               OUT       NUMBER               N

20 rows selected.

--很明显这样的输出参数排序了,不是自己需要的.删除排序操作看看:


SCOTT@book> @ &r/desc_proc sys dbms_stats get_table%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DATA_TYPE            DEFAULTED
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- ----------
SYS        DBMS_STATS           GET_TABLE_STATS                         1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        2 TABNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        3 PARTNAME             VARCHAR2             IN        VARCHAR2             Y
                                                                        4 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        5 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        6 NUMROWS              NUMBER               OUT       NUMBER               N
                                                                        7 NUMBLKS              NUMBER               OUT       NUMBER               N
                                                                        8 AVGRLEN              NUMBER               OUT       NUMBER               N
                                                                        9 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                        1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        2 TABNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        3 PARTNAME             VARCHAR2             IN        VARCHAR2             Y
                                                                        4 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        5 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        6 NUMROWS              NUMBER               OUT       NUMBER               N
                                                                        7 NUMBLKS              NUMBER               OUT       NUMBER               N
                                                                        8 AVGRLEN              NUMBER               OUT       NUMBER               N
                                                                        9 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                       10 CACHEDBLK            NUMBER               OUT       NUMBER               N
                                                                       11 CACHEHIT             NUMBER               OUT       NUMBER               N


$ cat desc_proc.sql
column owner format a10
column package_name format a20
column object_name format a30
column data_type format a20
column ARGUMENT_NAME format a20
column defaulted format a10
set verify off
break on owner on package_name on object_name  skip 1
PROMPT INPUT OWNER PACKAGE_NAME OBJECT_NAME
PROMPT sample : @desc_proc sys dbms_stats gather_%_stats
Prompt
SELECT owner,
         package_name,
         object_name,
         sequence,
         argument_name,
         data_type,
         in_out,
         data_type,
         defaulted
    FROM dba_arguments
   WHERE     owner = NVL(upper('&1'),'SYS')
         AND package_name = NVL(upper('&2'),'DBMS_STATS')
         AND object_name like NVL(upper('&3'),object_name)
AND data_level = 0
--ORDER BY owner,package_name,object_name,sequence;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2091423/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2091423/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值