Oracle 常用腳本之查看視圖、序列、存儲過程、觸發(fā)器、同義詞、計劃任務(wù)等信息

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        視圖
            列出數(shù)據(jù)庫中的視圖
            查看指定視圖的定義
            查看指定視圖中的字段
        序列
            列出數(shù)據(jù)庫中的序列
        存儲過程/函數(shù)
            列出數(shù)據(jù)庫中的存儲過程/函數(shù)
            列出數(shù)據(jù)庫中的程序包
            查看存儲過程/函數(shù)/子程序的參數(shù)
            查看存儲過程/函數(shù)/程序包的定義
        觸發(fā)器
            列出數(shù)據(jù)庫中的觸發(fā)器
            查看指定表上的觸發(fā)器
        同義詞
            列出數(shù)據(jù)庫中的同義詞
        計劃任務(wù)
            列出數(shù)據(jù)庫中的計劃任務(wù)
            查看指定計劃任務(wù)的日志信息
        對象依賴
            查看指定視圖依賴的對象
            查看依賴于指定序列的對象
            查看指定存儲過程/函數(shù)/程序包依賴的對象
            查看依賴于指定存儲過程/函數(shù)/程序包的對象

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。前文介紹了如何查看 Oracle 中的數(shù)據(jù)表、字段、約束、索引等信息，今天我們繼續(xù)討論如何通過數(shù)據(jù)字典查看 Oracle 數(shù)據(jù)庫中的視圖、序列、存儲過程/函數(shù)、程序包、觸發(fā)器、同義詞、計劃任務(wù)以及對象之間的依賴關(guān)系等信息。
視圖
列出數(shù)據(jù)庫中的視圖

系統(tǒng)視圖 dba_views、all_views 以及 user_views 包含了數(shù)據(jù)庫中的視圖信息。DBA 可以通過以下語句列出指定用戶擁有的視圖：

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



其中，

    OWNER 是視圖的擁有者；
    VIEW_NAME 是視圖的名稱；
    READ_ONLY 表示視圖是否只讀，Y 或者 N。

普通用戶可以直接通過 all_views 查看自己擁有訪問權(quán)限的視圖：

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 定義了視圖內(nèi)的函數(shù)執(zhí)行時使用視圖的定義者（DEFINER，默認）或者使用者權(quán)限（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;



返回字段的含義可以參考上一篇文章。
序列
列出數(shù)據(jù)庫中的序列

系統(tǒng)視圖 dba_sequences、all_sequences 以及 user_sequences 包含了數(shù)據(jù)庫中的序列信息。以下查詢可以用于查找指定模式下的所有序列：

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 表示序列到達最大值/最小值時是否循環(huán)使用，Y 或者 N；
    ORDER_FLAG 表示是否按照順序生成序列值，Y 或者 N；
    CACHE_SIZE 是緩存的序列值個數(shù)；
    LAST_NUMBER 是最后生成的序列值，包括緩存的序列值。

存儲過程/函數(shù)
列出數(shù)據(jù)庫中的存儲過程/函數(shù)

系統(tǒng)視圖 dba_procedures、all_procedures 以及 user_procedures 包含了數(shù)據(jù)庫中的存儲過程/函數(shù)、程序包以及觸發(fā)器的信息。以下查詢用于返回指定用戶下的存儲過程和函數(shù)：

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 表示該對象是否是聚合函數(shù)，Y 或者 N；
    PIPELINED 表示該對象是否是管道表函數(shù)，Y 或者 N；
    PARALLEL 表示存儲過程/函數(shù)是否支持并行，Y 或者 N。

列出數(shù)據(jù)庫中的程序包

Oracle 程序包相關(guān)的信息也可以通過 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 是子程序的名稱，子程序可能是過程或者函數(shù)。

查看存儲過程/函數(shù)/子程序的參數(shù)

系統(tǒng)視圖 dba_arguments、all_arguments 以及 user_arguments 包含了存儲過程/函數(shù)的參數(shù)信息。以下語句用于查看指定存儲過程/函數(shù)的參數(shù)：

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 -- 存儲過程/函數(shù)名
and p.object_type in ('PROCEDURE', 'FUNCTION')
order by p.object_name, a.position;


使用 left join 是因為沒有參數(shù)的存儲過程在 dba_arguments 中沒有記錄。其中，

    POSITION 是參數(shù)的位置，函數(shù)返回值的位置為 0；
    ARGUMENT_NAME 是參數(shù)名；
    IN_OUT 表示輸入?yún)?shù)或者輸出參數(shù)；
    DATA_TYPE 是參數(shù)的數(shù)據(jù)類型；
    DATA_LENGTH 是參數(shù)的長度，單位為字節(jié)；
    DATA_PRECISION 是參數(shù)的精度；
    DATA_SCALE 是參數(shù)的小數(shù)位數(shù)；
    DEFAULTED 表示參數(shù)是否有默認值。

對于程序包中的子程序，也可以使用相同的方式查看參數(shù)，只需要將 object_type 修改為 PACKAGE 即可。
查看存儲過程/函數(shù)/程序包的定義

系統(tǒng)視圖 dba_source、all_source 以及 user_source 包含了數(shù)據(jù)庫中各種存儲對象的源碼，例如程序過程/函數(shù)、觸發(fā)器、程序包、自定義類型等。以下語句可以用于查看指定存儲過程/函數(shù)以及程序包等對象的定義語句：

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 -- 存儲過程/函數(shù)/程序包/觸發(fā)器名
group by owner, name, type;


其中，

    OWNER 是存儲對象的擁有者；
    NAME 是存儲對象的名稱；
    TYPE 是存儲對象的類型，PROCEDURE、FUNCTION、TRIGGER、PACKAGE、PACKAGE BODY、TYPE、TYPE BODY 以及 JAVA SOURCE；
    LINE 是每行代碼所在的行號；
    TEXT 是每行代碼，通過 listagg 函數(shù)將每個對象的所有代碼行進行合并。

觸發(fā)器
列出數(shù)據(jù)庫中的觸發(fā)器

系統(tǒng)視圖 dba_triggers、all_triggers 以及 user_triggers 包含了數(shù)據(jù)庫中的觸發(fā)器信息。以下語句可以用于查看指定用戶下的所有觸發(fā)器：

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 是觸發(fā)器的擁有者；
    TRIGGER_NAME 是觸發(fā)器的名稱；
    TRIGGER_TYPE 是觸發(fā)器的類型，BEFORE STATEMENT、BEFORE EACH ROW、INSTEAD OF 等；
    TRIGGERING_EVENT 是觸發(fā)事件，INSERT、UPDATE、CREATE、LOGON 等；
    BASE_OBJECT_TYPE 是觸發(fā)器基礎(chǔ)對象的類型，TABLE、VIEW、SCHEMA、DATABASE；
    TABLE_OWNER 表示觸發(fā)器基礎(chǔ)表或視圖的擁有者，對于 SCHEMA 和 DATABASE 類型顯示為 NULL；
    TABLE_NAME 是觸發(fā)器基礎(chǔ)對象的表名或視圖名，對于 SCHEMA 和 DATABASE 類型顯示為 NULL；
    STATUS 是觸發(fā)器的狀態(tài)，ENABLED 或者 DISABLED；
    TRIGGER_BODY 是觸發(fā)器執(zhí)行的語句。

dba_procedures 和系列視圖中也包含了觸發(fā)器的信息，不過內(nèi)容相對簡單：

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



字段的解釋可以參考上文中的存儲過程/函數(shù)。
查看指定表上的觸發(fā)器

以下語句可以用于查看指定表上創(chuàng)建的觸發(fā)器：

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; -- 表名



同義詞
列出數(shù)據(jù)庫中的同義詞

系統(tǒng)視圖 dba_synonyms、all_synonyms 以及 user_synonyms 包含了數(shù)據(jù)庫中的同義詞信息。以下查詢返回了指定用戶下的同義詞：

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



其中，

    OWNER 是同義詞的擁有者；
    SYNONYM_NAME 是同義詞的名稱；
    TABLE_OWNER 是被同義詞引用的對象的擁有者或者創(chuàng)建者；
    TABLE_NAME 是被同義詞引用的對象名，被引用對象可能是表、視圖、序列、存儲過程、同義詞等；
    DB_LINK 是遠程引用時的數(shù)據(jù)庫鏈接，可能為空。

計劃任務(wù)
列出數(shù)據(jù)庫中的計劃任務(wù)

系統(tǒng)視圖 dba_scheduler_jobs、all_scheduler_jobs 以及 user_scheduler_jobs 包含了數(shù)據(jù)庫中的計劃任務(wù)信息。以下語句可以用于查看指定用戶下的所有計劃任務(wù)：

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 是計劃任務(wù)的擁有者；
    JOB_NAME 是計劃任務(wù)的名稱；
    JOB_STYLE 是計劃任務(wù)的類型，例如 REGULAR、LIGHTWEIGHT 等；
    JOB_TYPE 是內(nèi)聯(lián)任務(wù)的操作類型，例如 PLSQL_BLOCK、STORED_PROCEDURE、EXECUTABLE 等；
    PROGRAM_NAME 是計劃任務(wù)關(guān)聯(lián)程序的名稱；
    JOB_ACTION 是內(nèi)聯(lián)任務(wù)的執(zhí)行的操作；
    START_DATE 是計劃任務(wù)第一次啟動時間（內(nèi)聯(lián)計劃）；
    REPEAT_INTERVAL 是內(nèi)聯(lián)計劃的 PL/SQL 表達式或日歷；
    SCHEDULE_NAME 是任務(wù)使用的計劃名稱；
    SCHEDULE_TYPE 是任務(wù)使用的計劃類型，例如 IMMEDIATE 、ONCE、CALENDAR 等；
    LAST_START_DATE 是計劃任務(wù)最近一次啟動的時間；
    NEXT_RUN_DATE 是計劃任務(wù)下一次啟動的時間；
    END_DATE 是計劃任務(wù)的終止時間（內(nèi)聯(lián)計劃）；
    STATE 是計劃任務(wù)的當前狀態(tài)，例如 DISABLED、SCHEDULED、RUNNING、SUCCEEDED 等。

查看指定計劃任務(wù)的日志信息

all_scheduler_job_log 以及系列視圖記錄了計劃任務(wù)的日志信息。以下語句返回了指定計劃任務(wù)的日志信息：

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 是日志對應(yīng)的操作，例如 CREATE、ENABLE、RUN 等；
    STATUS 是操作的狀態(tài)，例如 SUCCEEDED、FAILED 等；
    ADDITIONAL_INFO 包含了一些可能的額外信息。

關(guān)于計劃任務(wù)運行的詳細信息，例如運行時長、占用 CPU、錯誤描述等，可以查看 all_scheduler_job_run_details 和系列視圖。
對象依賴

系統(tǒng)視圖 dba_dependencies、all_dependencies 以及 user_dependencies 包含了存儲過程/函數(shù)、程序包/包體、觸發(fā)器之間的依賴關(guān)系，包括沒有使用數(shù)據(jù)庫鏈接創(chuàng)建的視圖的依賴關(guān)系。
查看指定視圖依賴的對象

以下查詢返回了指定視圖依賴的對象，例如數(shù)據(jù)表、其他視圖等：

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 等。
查看指定存儲過程/函數(shù)/程序包依賴的對象

以下可以找出指定存儲過程/函數(shù)/程序包依賴的對象：

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; -- 存儲過程/函數(shù)/程序包名


查看依賴于指定存儲過程/函數(shù)/程序包的對象

以下可以找出依賴于指定存儲過程/函數(shù)/程序包的對象：

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;



如果覺得文章對你有用，歡迎關(guān)注??、評論??、點贊??！