oracle中如何定位你解决问题需要访问地视图
这篇文章主要介绍oracle中如何定位你解决问题需要访问地视图,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!
创新互联专注于企业成都全网营销推广、网站重做改版、代县网站定制设计、自适应品牌网站建设、HTML5、商城网站建设、集团公司官网建设、外贸营销网站建设、高端网站制作、响应式网页设计等建站业务,价格优惠性价比高,为代县等各大城市提供网站开发制作服务。
如果你不知道确切要找的目标,可以参考介绍数据字典的文档,或者下面三个视图:
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中如何定位你解决问题需要访问地视图”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注创新互联行业资讯频道!
网页题目:oracle中如何定位你解决问题需要访问地视图
文章分享:http://pwwzsj.com/article/giisgc.html