Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to locate the view where you need to visit to solve the problem in oracle. The article is very detailed and has a certain reference value. Friends who are interested must read it!

If you don't know exactly what you're looking for, you can refer to the documentation that introduces the data dictionary, or the following three views:

DBA_OBJECTS

DICTIONARY

DICT_COLUMNS

If you know the general direction of your query, such as solving the problem of materialized views, you can use SQL to query

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

... Some output is omitted due to the problem of space.

This ensures that the query direction is roughly correct, but if you want to see the details of each view, you will need to use the DICTIONARY and DICT_COLUMNS views

SQL > desc dictionary

Name Null? Type

-

TABLE_NAME VARCHAR2 (128)

COMMENTS VARCHAR2 (4000)

For example, we still need to solve the problem of materialized view.

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 competes for 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.

If this does not get enough information about the column names, you can query the DICT_COLUMNS view, or you can use the following SQL to query

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.

The above is all the content of the article "how to locate the view where you need to visit to solve the problem in oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.