DBA_EDITIONING_VIEW_COLS_AE view tips

Oracle Tips by Burleson Consulting

DBA_EDITIONING_VIEW_COLS_AE

<< Return to the index

Oracle 11g makes the following comments about the DBA_EDITIONING_VIEW_COLS_AE table: "Relationship between columns of all Editioning Views in the database and the table columns to which they map"

Column description of the DBA_EDITIONING_VIEW_COLS_AE view:

OWNER

Description of DBA_EDITIONING_VIEW_COLS_AE.OWNER: "Owner of an Editioning View"

VIEW_NAME

Description of DBA_EDITIONING_VIEW_COLS_AE.VIEW_NAME: "Name of an Editioning View"

VIEW_COLUMN_ID

Description of DBA_EDITIONING_VIEW_COLS_AE.VIEW_COLUMN_ID: "Column number within the Editioning View"

VIEW_COLUMN_NAME

Description of DBA_EDITIONING_VIEW_COLS_AE.VIEW_COLUMN_NAME: "Name of the column in the Editioning View"

TABLE_COLUMN_ID

Description of DBA_EDITIONING_VIEW_COLS_AE.TABLE_COLUMN_ID: "Column number of a table column to which this EV column maps"

TABLE_COLUMN_NAME

Description of DBA_EDITIONING_VIEW_COLS_AE.TABLE_COLUMN_NAME: "Name of a table column to which this EV column maps"

EDITION_NAME

Description of DBA_EDITIONING_VIEW_COLS_AE.EDITION_NAME: "Name of the Application Edition where the Editioning View is defined"

DBA_EDITIONING_VIEW_COLS_AE View Source

Oracle 11g's data dictionary defines the DBA_EDITIONING_VIEW_COLS_AE view using the following source query:

select ev_user.name,
ev_obj.name,
view_col.col#,
view_col.name,
tbl_col.col#,
tbl_col.name,
ev_obj.defining_edition
from sys."_ACTUAL_EDITION_OBJ" ev_obj, sys.obj$ base_tbl_obj,
sys.ev$ ev, sys.evcol$ ev_col, sys.col$ view_col, sys.col$ tbl_col,
sys.user$ ev_user
where /* get all columns of a given EV */
ev.ev_obj# = ev_col.ev_obj#
/* join EVCOL$ to COL$ on EV id and column id to obtain EV column */
/* name */
and ev_col.ev_obj# = view_col.obj#
and ev_col.ev_col_id = view_col.col#
/* join EV$ to OBJ$ on base table owner id and base table name so we */
/* can determine base table id */
and ev.base_tbl_owner# = base_tbl_obj.owner#
and ev.base_tbl_name = base_tbl_obj.name
/* exclude [sub]partitions by restricting base_tbl_obj.type# to */
/* "table"; since COL$ will not contain rows for [sub]partitions, */
/* this restriction is not, strictly speaking, necessary, but it */
/* does ensure that the above join will return exactly one row */
and base_tbl_obj.type# = 2
/* join EVCOL$ row and OBJ$ row describing the EV's base table to */
/* COL$ to obtain base table column id */
and base_tbl_obj.obj# = tbl_col.obj#
and ev_col.base_tbl_col_name = tbl_col.name
/* join EV$ to _ACTUAL_EDITION_OBJ on EV id so we can determine */
/* name of the EV and id of its owner */
and ev_obj.obj# = ev.ev_obj#
/* join _ACTUAL_EDITION_OBJ row describing the EV to USER$ to get */
/* owner name */
and ev_obj.owner# = ev_user.user#

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books