[20140820]显示存储过程的参数.txt-阿里云开发者社区

开发者社区> lfreeali> 正文

[20140820]显示存储过程的参数.txt

简介: [20140820]显示存储过程的参数.txt --上午写一个显示存储过程参数的例子,便于自己以后使用。 --主要是desc显示的信息太大,查看不是很方便。
+关注继续查看

[20140820]显示存储过程的参数.txt

--上午写一个显示存储过程参数的例子,便于自己以后使用。
--主要是desc显示的信息太大,查看不是很方便。


SCOTT@test> desc dbms_stats
PROCEDURE ALTER_DATABASE_TAB_MONITORING
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
MONITORING                     BOOLEAN                 IN     DEFAULT
SYSOBJS                        BOOLEAN                 IN     DEFAULT
PROCEDURE ALTER_SCHEMA_TAB_MONITORING
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN     DEFAULT
MONITORING                     BOOLEAN                 IN     DEFAULT
PROCEDURE ALTER_STATS_HISTORY_RETENTION
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
RETENTION                      NUMBER                  IN
....

PROCEDURE UPGRADE_STAT_TABLE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
STATTAB                        VARCHAR2                IN


SCOTT@test> desc dbms_stats.gather_table_stats
ERROR:
ORA-04043: object dbms_stats.gather_table_stats does not exist
--如果支持这种显示方式就好了。

$ 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
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;

SCOTT@test> @desc_proc sys dbms_stats gather_s%_stats
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           GATHER_SCHEMA_STATS                     1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        2 ESTIMATE_PERCENT     NUMBER               IN        NUMBER               Y
                                                                        2 ESTIMATE_PERCENT     NUMBER               IN        NUMBER               Y
                                                                        3 BLOCK_SAMPLE         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        3 BLOCK_SAMPLE         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        4 METHOD_OPT           VARCHAR2             IN        VARCHAR2             Y
                                                                        4 METHOD_OPT           VARCHAR2             IN        VARCHAR2             Y
                                                                        5 DEGREE               NUMBER               IN        NUMBER               Y
                                                                        5 DEGREE               NUMBER               IN        NUMBER               Y
                                                                        6 GRANULARITY          VARCHAR2             IN        VARCHAR2             Y
                                                                        6 GRANULARITY          VARCHAR2             IN        VARCHAR2             Y
                                                                        7 CASCADE              PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        7 CASCADE              PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        8 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        8 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        9 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        9 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                       10 OPTIONS              VARCHAR2             IN        VARCHAR2             Y
                                                                       10 OPTIONS              VARCHAR2             IN        VARCHAR2             Y
                                                                       11 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                       11 OBJLIST              TABLE                OUT       TABLE                N
                                                                       12 NO_INVALIDATE        PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       13 GATHER_TEMP          PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       14 GATHER_FIXED         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       15 STATTYPE             VARCHAR2             IN        VARCHAR2             Y
                                                                       16 FORCE                PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       17 OBJ_FILTER_LIST      TABLE                IN        TABLE                Y
                                                                       18 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                       19 NO_INVALIDATE        PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       20 GATHER_TEMP          PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       21 GATHER_FIXED         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       22 STATTYPE             VARCHAR2             IN        VARCHAR2             Y
                                                                       23 FORCE                PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       24 OBJ_FILTER_LIST      TABLE                IN        TABLE                Y

                                GATHER_SYSTEM_STATS                     1 GATHERING_MODE       VARCHAR2             IN        VARCHAR2             Y
                                                                        2 INTERVAL             NUMBER               IN        NUMBER               Y
                                                                        3 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        4 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        5 STATOWN              VARCHAR2             IN        VARCHAR2             Y


40 rows selected.

SCOTT@test> @desc_proc sys dbms_metadata get_ddl
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_METADATA        GET_DDL                                 1                      CLOB                 OUT       CLOB                 N
                                                                        2 OBJECT_TYPE          VARCHAR2             IN        VARCHAR2             N
                                                                        3 NAME                 VARCHAR2             IN        VARCHAR2             N
                                                                        4 SCHEMA               VARCHAR2             IN        VARCHAR2             Y
                                                                        5 VERSION              VARCHAR2             IN        VARCHAR2             Y
                                                                        6 MODEL                VARCHAR2             IN        VARCHAR2             Y
                                                                        7 TRANSFORM            VARCHAR2             IN        VARCHAR2             Y


7 rows selected.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
[20130105]expdp的include和exclude参数.txt
[20130105]expdp的include和exclude参数.txthttp://www.itpub.net/thread-1754104-1-1.html如果include,exclude参数很长,可以通过建立一张表来实现。
780 0
[20121023]改变show parameter的显示宽度.txt
[20121023]改变show parameter的显示宽度.txt 当登录sqlplus 执行: SQL> show parameter control_files NAME                                 TYPE     ...
628 0
关于安卓开发通过Toast显示消息提示框
Toast用于在屏幕中显示一个提示信息栏,该消息栏没有任何控制按钮,并且不会获得焦点,经过一定时间后自动消失。 作用:用于显示一些快速提示信息 有两种方式可以显示提示信息框 一: 调用Toast类的make Text()方法创建一个名称为toast(自定义)的Toast对象 关键代码 1 Toast toast = Toast.
854 0
Android图形显示系统——一张图片的显示流程
Android设备上一张图片的显示过程 应用示例 假如我们现在有一张这样的风景照 想在Android设备(比如一个小米pad)上显示出来。首先想到的是写一个应用,用一个ImageView,把这张照片附到ImageView上显示,如下面的demo。 MainActivity.java package com.example.pictureshow; imp
2408 0
数据库存储图像及使用Image控件显示
在数据库中存取并显示到页面Image控件的功能。原来认为并不复杂,但是实际中却遇到了一些问题。 首先来看看保存图片: 图片在数据库中使用二进制方式存储,所以要把图片转化为二进制流文件在存储到数据库中,代码如下:     private void Save()     {         MyImage img = new MyImage();         img.
677 0
[20180224]exp参数RECORDLENGTH.txt
[20180224]exp参数RECORDLENGTH.txt --//虽然已经很少使用exp导致,如果加入direct=y参数,设置RECORDLENGTH参数能加快数据导出.
1426 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
2122
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载