共计 8065 个字符，预计需要花费 21 分钟才能阅读完成。
这篇文章主要介绍 oracle 中如何定位你解决问题需要访问地视图，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！
如果你不知道确切要找的目标，可以参考介绍数据字典的文档，或者下面三个视图：
DBA_OBJECTS
DICTIONARY
DICT_COLUMNS
如果你知道你大致要查询的方向，比如解决物化视图的问题，可以用一下 SQL 来进行查询；
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
… 由于篇幅问题省略部分输出
这样可以确保查询方向大致是正确的，但是如果你要看每个视图的详细信息，就要用到 DICTIONARY 和 DICT_COLUMNS 这两个视图了；
SQL desc dictionary
Name Null? Type
—————————————– ——– —————————-
TABLE_NAME VARCHAR2(128)
COMMENTS VARCHAR2(4000)
比如还是要解决物化视图的问题；
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.
如果这还不能获得足够的相关列名信息，可以查询 DICT_COLUMNS 视图，还可以使用如下 SQL 来进行查询；
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 中如何定位你解决问题需要访问地视图”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！
