Oracle 常用脚本之查看视图、序列、存储过程、触发器、同义词、计划任务等信息

作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net

文章目录

        视图
            列出数据库中的视图
            查看指定视图的定义
            查看指定视图中的字段
        序列
            列出数据库中的序列
        存储过程/函数
            列出数据库中的存储过程/函数
            列出数据库中的程序包
            查看存储过程/函数/子程序的参数
            查看存储过程/函数/程序包的定义
        触发器
            列出数据库中的触发器
            查看指定表上的触发器
        同义词
            列出数据库中的同义词
        计划任务
            列出数据库中的计划任务
            查看指定计划任务的日志信息
        对象依赖
            查看指定视图依赖的对象
            查看依赖于指定序列的对象
            查看指定存储过程/函数/程序包依赖的对象
            查看依赖于指定存储过程/函数/程序包的对象

大家好,我是只谈技术不剪发的 Tony 老师。前文介绍了如何查看 Oracle 中的数据表、字段、约束、索引等信息,今天我们继续讨论如何通过数据字典查看 Oracle 数据库中的视图、序列、存储过程/函数、程序包、触发器、同义词、计划任务以及对象之间的依赖关系等信息。
视图
列出数据库中的视图

系统视图 dba_views、all_views 以及 user_views 包含了数据库中的视图信息。DBA 可以通过以下语句列出指定用户拥有的视图:

select owner, view_name, read_only
from dba_views
where owner = :user_name; -- 用户名



其中,

    OWNER 是视图的拥有者;
    VIEW_NAME 是视图的名称;
    READ_ONLY 表示视图是否只读,Y 或者 N。

普通用户可以直接通过 all_views 查看自己拥有访问权限的视图:

select owner, view_name, read_only
from all_views;


查看指定视图的定义

使用以下语句查看指定视图的定义语句:

select owner, view_name, text, bequeath
from dba_views
where owner = :user_name -- 用户名
and view_name = :view_name; -- 表名



其中,

    TEXT 包含了视图定义的语句;
    BEQUEATH 定义了视图内的函数执行时使用视图的定义者(DEFINER,默认)或者使用者权限(CURRENT_USER)。

查看指定视图中的字段

视图是虚拟的表,所以视图中的字段信息和表一样存储在 dba_tab_columns、all_tab_columns 以及 user_tab_columns 视图中。以下语句可以用于查看指定视图中包含的字段信息:

select v.owner, v.view_name, column_id, column_name, data_type,
       data_length, data_precision, data_scale, nullable, data_default
from dba_tab_columns vc
join dba_views v on (vc.owner = v.owner and vc.table_name = v.view_name)
where v.owner = :user_name -- 用户名
and v.view_name = :view_name -- 表名
order by column_id;



返回字段的含义可以参考上一篇文章。
序列
列出数据库中的序列

系统视图 dba_sequences、all_sequences 以及 user_sequences 包含了数据库中的序列信息。以下查询可以用于查找指定模式下的所有序列:

select sequence_owner, sequence_name, min_value, max_value, increment_by,
       cycle_flag, order_flag, cache_size, last_number
from dba_sequences
where sequence_owner = :user_name; -- 用户名


其中,

    SEQUENCE_OWNER 是序列的拥有者;
    SEQUENCE_NAME 是序列的名称;
    MIN_VALUE 是序列的最小值;
    MAX_VALUE 是序列的最大值;
    INCREMENT_BY 是序列的增量值;
    CYCLE_FLAG 表示序列到达最大值/最小值时是否循环使用,Y 或者 N;
    ORDER_FLAG 表示是否按照顺序生成序列值,Y 或者 N;
    CACHE_SIZE 是缓存的序列值个数;
    LAST_NUMBER 是最后生成的序列值,包括缓存的序列值。

存储过程/函数
列出数据库中的存储过程/函数

系统视图 dba_procedures、all_procedures 以及 user_procedures 包含了数据库中的存储过程/函数、程序包以及触发器的信息。以下查询用于返回指定用户下的存储过程和函数:

select object_name, object_type, aggregate, pipelined, parallel
from dba_procedures
where owner = :user_name -- 用户名
and object_type in ('PROCEDURE', 'FUNCTION');



其中,

    OWNER 是对象的拥有者;
    OBJECT_NAME 是对象名称;
    OBJECT_TYPE是对象的类型,PROCEDURE、FUNCTION、PACKAGE 或者 TRIGGER;
    AGGREGATE 表示该对象是否是聚合函数,Y 或者 N;
    PIPELINED 表示该对象是否是管道表函数,Y 或者 N;
    PARALLEL 表示存储过程/函数是否支持并行,Y 或者 N。

列出数据库中的程序包

Oracle 程序包相关的信息也可以通过 dba_procedures 系列视图进行查看。例如:

select object_name, object_type, subprogram_id, procedure_name
from dba_procedures
where owner = :user_name -- 用户名
and object_type = 'PACKAGE';


其中,

    SUBPROGRAM_ID 是程序包中的子程序编号,0 代表程序包自身;
    PROCEDURE_NAME 是子程序的名称,子程序可能是过程或者函数。

查看存储过程/函数/子程序的参数

系统视图 dba_arguments、all_arguments 以及 user_arguments 包含了存储过程/函数的参数信息。以下语句用于查看指定存储过程/函数的参数:

select p.owner,
       p.object_name,
       p.object_type,
       a.position,
       a.argument_name,
       a.in_out,
       a.data_type,
       a.data_length,
       a.data_precision,
       a.data_scale,
       a.defaulted
from dba_procedures p
left join dba_arguments a on (a.object_id = p.object_id)
where p.owner = :user_name -- 用户名
and p.object_name = :procedure_name -- 存储过程/函数名
and p.object_type in ('PROCEDURE', 'FUNCTION')
order by p.object_name, a.position;


使用 left join 是因为没有参数的存储过程在 dba_arguments 中没有记录。其中,

    POSITION 是参数的位置,函数返回值的位置为 0;
    ARGUMENT_NAME 是参数名;
    IN_OUT 表示输入参数或者输出参数;
    DATA_TYPE 是参数的数据类型;
    DATA_LENGTH 是参数的长度,单位为字节;
    DATA_PRECISION 是参数的精度;
    DATA_SCALE 是参数的小数位数;
    DEFAULTED 表示参数是否有默认值。

对于程序包中的子程序,也可以使用相同的方式查看参数,只需要将 object_type 修改为 PACKAGE 即可。
查看存储过程/函数/程序包的定义

系统视图 dba_source、all_source 以及 user_source 包含了数据库中各种存储对象的源码,例如程序过程/函数、触发器、程序包、自定义类型等。以下语句可以用于查看指定存储过程/函数以及程序包等对象的定义语句:

select owner, name, type, listagg(text)
              within group (order by line) as source_text
from dba_source
where owner = :user_name -- 用户名
and name = :procedure_name -- 存储过程/函数/程序包/触发器名
group by owner, name, type;


其中,

    OWNER 是存储对象的拥有者;
    NAME 是存储对象的名称;
    TYPE 是存储对象的类型,PROCEDURE、FUNCTION、TRIGGER、PACKAGE、PACKAGE BODY、TYPE、TYPE BODY 以及 JAVA SOURCE;
    LINE 是每行代码所在的行号;
    TEXT 是每行代码,通过 listagg 函数将每个对象的所有代码行进行合并。

触发器
列出数据库中的触发器

系统视图 dba_triggers、all_triggers 以及 user_triggers 包含了数据库中的触发器信息。以下语句可以用于查看指定用户下的所有触发器:

select trigger_name, trigger_type, triggering_event, base_object_type,
       table_owner, table_name, status, trigger_body
from dba_triggers
where owner = :user_name; -- 用户名



其中,

    OWNER 是触发器的拥有者;
    TRIGGER_NAME 是触发器的名称;
    TRIGGER_TYPE 是触发器的类型,BEFORE STATEMENT、BEFORE EACH ROW、INSTEAD OF 等;
    TRIGGERING_EVENT 是触发事件,INSERT、UPDATE、CREATE、LOGON 等;
    BASE_OBJECT_TYPE 是触发器基础对象的类型,TABLE、VIEW、SCHEMA、DATABASE;
    TABLE_OWNER 表示触发器基础表或视图的拥有者,对于 SCHEMA 和 DATABASE 类型显示为 NULL;
    TABLE_NAME 是触发器基础对象的表名或视图名,对于 SCHEMA 和 DATABASE 类型显示为 NULL;
    STATUS 是触发器的状态,ENABLED 或者 DISABLED;
    TRIGGER_BODY 是触发器执行的语句。

dba_procedures 和系列视图中也包含了触发器的信息,不过内容相对简单:

select object_name, object_type, aggregate, pipelined, parallel
from dba_procedures
where owner = :user_name -- 用户名
and object_type = 'TRIGGER';



字段的解释可以参考上文中的存储过程/函数。
查看指定表上的触发器

以下语句可以用于查看指定表上创建的触发器:

select table_owner, table_name, owner as trigger_owner, trigger_name,
       trigger_type, triggering_event, status
from dba_triggers
where base_object_type = 'TABLE'
and table_owner = :user_name -- 用户名
and table_name = :table_name; -- 表名



同义词
列出数据库中的同义词

系统视图 dba_synonyms、all_synonyms 以及 user_synonyms 包含了数据库中的同义词信息。以下查询返回了指定用户下的同义词:

select owner, synonym_name, table_owner, table_name, db_link
from dba_synonyms
where owner = :user_name; -- 用户名



其中,

    OWNER 是同义词的拥有者;
    SYNONYM_NAME 是同义词的名称;
    TABLE_OWNER 是被同义词引用的对象的拥有者或者创建者;
    TABLE_NAME 是被同义词引用的对象名,被引用对象可能是表、视图、序列、存储过程、同义词等;
    DB_LINK 是远程引用时的数据库链接,可能为空。

计划任务
列出数据库中的计划任务

系统视图 dba_scheduler_jobs、all_scheduler_jobs 以及 user_scheduler_jobs 包含了数据库中的计划任务信息。以下语句可以用于查看指定用户下的所有计划任务:

select owner, job_name, job_style, job_type, program_name,      
       job_action, start_date, repeat_interval, schedule_name, schedule_type,
       last_start_date, next_run_date, end_date, state
from dba_scheduler_jobs
where owner = :user_name -- 用户名
order by owner, job_name;



其中,

    OWNER 是计划任务的拥有者;
    JOB_NAME 是计划任务的名称;
    JOB_STYLE 是计划任务的类型,例如 REGULAR、LIGHTWEIGHT 等;
    JOB_TYPE 是内联任务的操作类型,例如 PLSQL_BLOCK、STORED_PROCEDURE、EXECUTABLE 等;
    PROGRAM_NAME 是计划任务关联程序的名称;
    JOB_ACTION 是内联任务的执行的操作;
    START_DATE 是计划任务第一次启动时间(内联计划);
    REPEAT_INTERVAL 是内联计划的 PL/SQL 表达式或日历;
    SCHEDULE_NAME 是任务使用的计划名称;
    SCHEDULE_TYPE 是任务使用的计划类型,例如 IMMEDIATE 、ONCE、CALENDAR 等;
    LAST_START_DATE 是计划任务最近一次启动的时间;
    NEXT_RUN_DATE 是计划任务下一次启动的时间;
    END_DATE 是计划任务的终止时间(内联计划);
    STATE 是计划任务的当前状态,例如 DISABLED、SCHEDULED、RUNNING、SUCCEEDED 等。

查看指定计划任务的日志信息

all_scheduler_job_log 以及系列视图记录了计划任务的日志信息。以下语句返回了指定计划任务的日志信息:

select log_id, log_date, operation, status, additional_info
from dba_scheduler_job_log
where owner = :user_name
and job_name = :job_name;


其中,

    LOG_ID 是日志编号;
    LOG_DATE 是日志生成的时间戳;
    OPERATION 是日志对应的操作,例如 CREATE、ENABLE、RUN 等;
    STATUS 是操作的状态,例如 SUCCEEDED、FAILED 等;
    ADDITIONAL_INFO 包含了一些可能的额外信息。

关于计划任务运行的详细信息,例如运行时长、占用 CPU、错误描述等,可以查看 all_scheduler_job_run_details 和系列视图。
对象依赖

系统视图 dba_dependencies、all_dependencies 以及 user_dependencies 包含了存储过程/函数、程序包/包体、触发器之间的依赖关系,包括没有使用数据库链接创建的视图的依赖关系。
查看指定视图依赖的对象

以下查询返回了指定视图依赖的对象,例如数据表、其他视图等:

select owner, name as view_name,
       referenced_owner, referenced_name, referenced_type
from dba_dependencies
where type = 'VIEW'
and owner = :user_name -- 用户名
and name = :view_name; -- 视图名



其中,

    OWNER 是对象的拥有者;
    NAME 是对象的名称;
    TYPE 是对象的类型,包括 VIEW、SYNONYM、PROCEDURE、TRIGGER 等;
    REFERENCED_OWNER 是被引用对象的拥有者;
    REFERENCED_NAME 是被引用对象的名称;
    REFERENCED_TYPE 是被引用对象的类型,包括 TABLE、VIEW、SYNONYM、PROCEDURE、TRIGGER 等。

查看依赖于指定序列的对象

通过 dba_dependencies 和系列视图可以找出依赖于指定序列的其他对象:

select owner, name, type
from dba_dependencies
where referenced_type = 'SEQUENCE'
and referenced_owner = :user_name -- 用户名
and referenced_name = :sequence_name; -- 序列名


其中,type 是对象的类型,例如 PROCEDURE、TRIGGER 等。
查看指定存储过程/函数/程序包依赖的对象

以下可以找出指定存储过程/函数/程序包依赖的对象:

select owner, name,
       referenced_owner, referenced_name, referenced_type
from dba_dependencies
where type in ('PROCEDURE', 'FUNCTION', 'PACKAGE')
and owner = :user_name -- 用户名
and name = :procedure_name; -- 存储过程/函数/程序包名


查看依赖于指定存储过程/函数/程序包的对象

以下可以找出依赖于指定存储过程/函数/程序包的对象:

select owner, name,
       referenced_owner, referenced_name, referenced_type
from dba_dependencies
where referenced_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE')
and referenced_owner = :user_name -- 用户名
and referenced_name = :procedure_name;



如果觉得文章对你有用,欢迎关注❤️、评论📝、点赞👍!