oracle中如何定位你解决问题需要访问地视图

这篇文章主要介绍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中如何定位你解决问题需要访问地视图"这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注行业资讯频道！