共計(jì) 8065 個(gè)字符，預(yù)計(jì)需要花費(fèi) 21 分鐘才能閱讀完成。
這篇文章主要介紹 oracle 中如何定位你解決問(wèn)題需要訪問(wèn)地視圖，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！
如果你不知道確切要找的目標(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)注丸趣 TV 行業(yè)資訊頻道！
