這篇文章主要介紹oracle中如何定位你解決問(wèn)題需要訪問(wèn)地視圖，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

創(chuàng)新互聯(lián)專注于企業(yè)成都全網(wǎng)營(yíng)銷推廣、網(wǎng)站重做改版、代縣網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、HTML5、商城網(wǎng)站建設(shè)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)營(yíng)銷網(wǎng)站建設(shè)、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為代縣等各大城市提供網(wǎng)站開(kāi)發(fā)制作服務(wù)。

如果你不知道確切要找的目標(biāo)，可以參考介紹數(shù)據(jù)字典的文檔，或者下面三個(gè)視圖：

DBA_OBJECTS

DICTIONARY

DICT_COLUMNS

如果你知道你大致要查詢的方向，比如解決物化視圖的問(wèn)題，可以用一下SQL來(lái)進(jìn)行查詢；

select object_name

from dba_objects

where object_name like '%MV%'

and owner ='SYS';

OBJECT_NAME

--------------------------------------------------------------------------------

ALL_BASE_TABLE_MVIEWS

ALL_MVIEWS

ALL_MVIEW_AGGREGATES

ALL_MVIEW_ANALYSIS

ALL_MVIEW_COMMENTS

ALL_MVIEW_DETAIL_PARTITION

ALL_MVIEW_DETAIL_RELATIONS

ALL_MVIEW_DETAIL_SUBPARTITION

ALL_MVIEW_JOINS

ALL_MVIEW_KEYS

ALL_MVIEW_LOGS

...由于篇幅問(wèn)題省略部分輸出

這樣可以確保查詢方向大致是正確的，但是如果你要看每個(gè)視圖的詳細(xì)信息，就要用到DICTIONARY和DICT_COLUMNS這兩個(gè)視圖了；

SQL> desc dictionary

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                         VARCHAR2(128)

 COMMENTS                                           VARCHAR2(4000)

 比如還是要解決物化視圖的問(wèn)題；

 select table_name,comments

 from dictionary

 where table_name like '%MV%';

TABLE_NAME                          COMMENTS

----------------------------------- ----------------------------------------------------------------------------------------------------

DBA_BASE_TABLE_MVIEWS               All materialized views with log(s) in the database

DBA_HIST_MVPARAMETER                Multi-valued Parameter Historical Statistics Information

DBA_MVIEWS                          All materialized views in the database

DBA_MVIEW_AGGREGATES                Description of the materialized view aggregates accessible to dba

DBA_MVIEW_ANALYSIS                  Description of the materialized views accessible to dba

DBA_MVIEW_COMMENTS                  Comments on all materialized views in the database

DBA_MVIEW_DETAIL_PARTITION          Freshness information of all PCT materialized views in the database

DBA_MVIEW_DETAIL_RELATIONS          Description of the materialized view detail tables accessible to dba

DBA_MVIEW_DETAIL_SUBPARTITION       Freshness information of all PCT materialized views in the database

DBA_MVIEW_JOINS                     Description of a join between two columns in the WHERE clause of a materialized view accessible to dba

DBA_MVIEW_KEYS                      Description of the columns that appear in the GROUP BY list of a materialized view accessible to dba

DBA_MVIEW_LOGS                      All materialized view logs in the database

DBA_MVIEW_LOG_FILTER_COLS           All filter columns (excluding PK cols) being logged in the materialized view logs

DBA_MVIEW_REFRESH_TIMES             All fast refreshable materialized views and their last refresh times for each master table

DBA_REGISTERED_MVIEWS               Remote materialized views of local tables

DBA_REGISTERED_MVIEW_GROUPS         Materialized view repgroup registration information

DBA_TUNE_MVIEW                      Catalog View to show the result after executing TUNE_MVIEW() API

USER_BASE_TABLE_MVIEWS              All materialized views with log(s) owned by the user in the database

USER_MVIEWS                         All materialized views in the database

USER_MVIEW_AGGREGATES               Description of the materialized view aggregates created by the user

USER_MVIEW_ANALYSIS                 Description of the materialized views created by the user

USER_MVIEW_COMMENTS                 Comments on materialized views owned by the user

USER_MVIEW_DETAIL_PARTITION         Freshness information of all PCT materialized views in the database

USER_MVIEW_DETAIL_RELATIONS         Description of the materialized view detail tables of the materialized views created by the user

USER_MVIEW_DETAIL_SUBPARTITION      Freshness information of all PCT materialized views in the database

USER_MVIEW_JOINS                    Description of a join between two columns in the WHERE clause of a materialized view created by the user

USER_MVIEW_KEYS                     Description of the columns that appear in the GROUP BY list of a materialized view  created by the user

USER_MVIEW_LOGS                     All materialized view logs owned by the user

USER_MVIEW_REFRESH_TIMES            Materialized views and their last refresh times for each master table that the user can look at

USER_REGISTERED_MVIEWS              Remote materialized views of local tables currently using logs owned by the user

USER_TUNE_MVIEW                     tune_mview catalog view owned by the user

ALL_BASE_TABLE_MVIEWS               All materialized views with log(s) in the database that the user can see

ALL_MVIEWS                          All materialized views in the database

ALL_MVIEW_AGGREGATES                Description of the materialized view aggregates accessible to the user

ALL_MVIEW_ANALYSIS                  Description of the materialized views accessible to the user

ALL_MVIEW_COMMENTS                  Comments on materialized views accessible to the user

ALL_MVIEW_DETAIL_PARTITION          Freshness information of all PCT materialized views in the database

ALL_MVIEW_DETAIL_RELATIONS          Description of the materialized view detail tables accessible to the user

ALL_MVIEW_DETAIL_SUBPARTITION       Freshness information of all PCT materialized views in the database

ALL_MVIEW_JOINS                     Description of a join between two columns in the  WHERE clause of a materialized view accessible to the user

ALL_MVIEW_KEYS                      Description of the columns that appear in the GROUP BYlist of a materialized view accessible to the user

ALL_MVIEW_LOGS                      All materialized view logs in the database that the user can see

ALL_MVIEW_REFRESH_TIMES             Materialized views and their last refresh times  for each master table that the user can look at

ALL_REGISTERED_MVIEWS               Remote materialized views of local tables that the user can see

GV$MVREFRESH                        Synonym for GV_$MVREFRESH

V$MVREFRESH                         Synonym for V_$MVREFRESH

46 rows selected.

如果這還不能獲得足夠的相關(guān)列名信息，可以查詢DICT_COLUMNS視圖，還可以使用如下SQL來(lái)進(jìn)行查詢；

select column_name,comments

from dict_columns

where table_name = 'DBA_MVIEWS';

COLUMN_NAME                              COMMENTS

---------------------------------------- ----------------------------------------------------------------------------------------------------

UNKNOWN_PLSQL_FUNC                       Indicates if the materialized view contains PL/SQL function

UNKNOWN_EXTERNAL_TABLE                   Indicates if the materialized view contains external tables

UNKNOWN_CONSIDER_FRESH                   Indicates if the materialized view is considered fresh

UNKNOWN_IMPORT                           Indicates if the materialized view is imported

UNKNOWN_TRUSTED_FD                       Indicates if the materialized view used trusted constraints for refresh

COMPILE_STATE                            Indicates the validity of the MV meta-data

USE_NO_INDEX                             Indicates whether the MV uses no index

STALE_SINCE                              Time from when the materialized view became stale

NUM_PCT_TABLES                           Number of PCT detail tables

NUM_FRESH_PCT_REGIONS                    Number of fresh PCT partition regions

NUM_STALE_PCT_REGIONS                    Number of stale PCT partition regions

SEGMENT_CREATED                          Whether the materialized view segment is created or not

EVALUATION_EDITION                       Name of the evaluation edition assigned to the materialized view subquery

UNUSABLE_BEFORE                          Name of the oldest edition eligible for query rewrite

UNUSABLE_BEGINNING                       Name of the oldest edition in which query rewrite becomes perpetually disabled

OWNER                                    Owner of the materialized view

MVIEW_NAME                               Name of the materialized view

CONTAINER_NAME                           Name of the materialized view container table

QUERY                                    The defining query that the materialized view instantiates

QUERY_LEN                                The number of bytes in the defining query (based on the server character set

UPDATABLE                                Indicates whether the materialized view can be updated

UPDATE_LOG                               Name of the table that logs changes to an updatable materialized view

MASTER_ROLLBACK_SEG                      Name of the rollback segment to use at the master site

MASTER_LINK                              Name of the database link to the master site

REWRITE_ENABLED                          Indicates whether rewrite is enabled for the materialized view

REWRITE_CAPABILITY                       Indicates the kind of rewrite that is enabled

REFRESH_MODE                             Indicates how and when the materialized view will be refreshed

REFRESH_METHOD                           The default refresh method for the materialized view (complete, fast, ...)

BUILD_MODE                               How and when to initially build (load) the materialized view container

FAST_REFRESHABLE                         Indicates the kinds of operations that can be fast refreshed for the MV

LAST_REFRESH_TYPE                        Indicates the kind of refresh that was last performed on the MV

LAST_REFRESH_DATE                        The date that the materialized view was last refreshed

LAST_REFRESH_END_TIME                    The time that the last materialized view refresh ended

STALENESS                                Indicates the staleness state of the materialized view (fresh, stale, ...)

AFTER_FAST_REFRESH                       Indicates the staleness state the MV will have after a fast refresh is done

UNKNOWN_PREBUILT                         Indicates if the materialized view is prebuilt

36 rows selected.

以上是“oracle中如何定位你解決問(wèn)題需要訪問(wèn)地視圖”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

文章名稱：oracle中如何定位你解決問(wèn)題需要訪問(wèn)地視圖
轉(zhuǎn)載注明：http://chinadenli.net/article12/giisgc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、服務(wù)器托管、網(wǎng)頁(yè)設(shè)計(jì)公司、外貿(mào)網(wǎng)站建設(shè)、域名注冊(cè)、建站公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)