In an earlier post, I mentioned the new data dictionary views that support editioning. Here is a list of the DBA views (they also exist as USER and ALL, as most other DD views):
SYS.DBA_VIEWS_AE
SYS.DBA_SOURCE_AE
SYS.DBA_OBJECTS_AE
SYS.DBA_ERRORS_AE
SYS.DBA_EDITIONING_VIEW_COLS_AE
SYS.DBA_EDITIONING_VIEWS_AE
See anything missing? Most of the other views you might want are easily derivable by joining the traditional view (say dba_triggers) to the dba_objects_ae view. It still might be useful to have something like a dba_triggers_ae view, but I haven’t found it hard to work around that.Usually, I switch my session to the edition I want to work in if I really need to see something like the state of the triggers.
There are 2 views that I really wish were available, dba_dependencies_ae and dba_tab_privs_ae. I missed them so much that I ended up writing my own DD views for these. Switching between editions just doesn’t give me the ability to compare the way that I want to.
To create these new views, I compared the source for dba_objects with dba_objects_ae to see that changes would be needed, then created a new view to serve the purpose of dba_dependencies_ae. Do not use this name because it is very likely that a future upgrade from Oracle will recognize this shortcoming and create the view. Having a view with the same name already present could cause the upgrade to fail, so it’s better to make a name they won’t likely use, let’s say dba_dependencies_ae_dh.
Once you have this view, you can query for cross-edition dependencies with something like this: SELECT * FROM sys.dba_dependencies_ae_dh WHERE edition_name <> referenced_edition_name;
Cross-edition dependencies are a “feature” of editioning that seems to have been a root cause of much of the instability that I experienced in the early days of implementing editioning. If you think about this a bit you can probably envision scenarios where the dependencies can get a bit confusing. My policy has become to never allow cross-edition dependencies, but to fully instantiate all the objects in each edition. That has the downside of increasing the object counts in your data dictionary, but I will cover how I deal with that in some future post.
Now for the source to create the view. Just copy all this and run it as SYS in your db.
create or replace view dba_dependencies_ae_dh
(owner, name, type, edition_name, object_id, referenced_owner, referenced_name, referenced_type, referenced_edition_name, referenced_object_id, referenced_link_name, dependency_type)
as
select u.name, o.name,
decode(o.type#, 0, ‘NEXT OBJECT’, 1, ‘INDEX’, 2, ‘TABLE’, 3, ‘CLUSTER’,
4, ‘VIEW’, 5, ‘SYNONYM’, 6, ‘SEQUENCE’, 7, ‘PROCEDURE’,
8, ‘FUNCTION’, 9, ‘PACKAGE’, 10, ‘NON-EXISTENT’,
11, ‘PACKAGE BODY’, 12, ‘TRIGGER’,
13, ‘TYPE’, 14, ‘TYPE BODY’, 22, ‘LIBRARY’,
28, ‘JAVA SOURCE’, 29, ‘JAVA CLASS’,
32, ‘INDEXTYPE’, 33, ‘OPERATOR’,
42, ‘MATERIALIZED VIEW’, 43, ‘DIMENSION’,
46, ‘RULE SET’, 55, ‘XML SCHEMA’, 56, ‘JAVA DATA’,
59, ‘RULE’, 62, ‘EVALUATION CONTXT’, 87, ‘ASSEMBLY’,
92, ‘CUBE DIMENSION’, 93, ‘CUBE’,
94, ‘MEASURE FOLDER’, 95, ‘CUBE BUILD PROCESS’,
‘UNDEFINED’),
o.defining_edition,
o.obj#,
decode(po.linkname, null, pu.name, po.remoteowner), po.name,
decode(po.type#, 0, ‘NEXT OBJECT’, 1, ‘INDEX’, 2, ‘TABLE’, 3, ‘CLUSTER’,
4, ‘VIEW’, 5, ‘SYNONYM’, 6, ‘SEQUENCE’, 7, ‘PROCEDURE’,
8, ‘FUNCTION’, 9, ‘PACKAGE’, 10, ‘NON-EXISTENT’,
11, ‘PACKAGE BODY’, 12, ‘TRIGGER’,
13, ‘TYPE’, 14, ‘TYPE BODY’, 22, ‘LIBRARY’,
28, ‘JAVA SOURCE’, 29, ‘JAVA CLASS’,
32, ‘INDEXTYPE’, 33, ‘OPERATOR’,
42, ‘MATERIALIZED VIEW’, 43, ‘DIMENSION’,
46, ‘RULE SET’, 55, ‘XML SCHEMA’, 56, ‘JAVA DATA’,
59, ‘RULE’, 62, ‘EVALUATION CONTXT’, 87, ‘ASSEMBLY’,
92, ‘CUBE DIMENSION’, 93, ‘CUBE’,
94, ‘MEASURE FOLDER’, 95, ‘CUBE BUILD PROCESS’,
‘UNDEFINED’),
po.editionname,
po.obj#,
po.linkname,
decode(bitand(d.property, 3), 2, ‘REF’, ‘HARD’)
from sys.”_ACTUAL_EDITION_OBJ” o, sys.dependency$ d, sys.user$ u, sys.user$ pu,
(select co.obj#, co.owner#, co.name, co.type#, co.remoteowner, co.linkname, co.defining_edition editionname from sys.”_ACTUAL_EDITION_OBJ” co
union all
select kobjn_kqfp, 0, name_kqfp, decode(type_kqfp, 1, 9, 2, 11, 3, 10, 0), NULL, NULL, NULL from sys.x$kqfp
union all
select kqftaobj, 0, kqftanam, 2, NULL, NULL, NULL from sys.x$kqfta
union all
select kqfviobj, 0, kqfvinam, 4, NULL, NULL, NULL from sys.x$kqfvi) po
where o.obj# = d.d_obj#
and o.owner# = u.user#
and po.obj# = d.p_obj#
and po.owner# = pu.user#;
comment on column dba_dependencies_ae_dh.OWNER is ‘Owner of the object’;
comment on column dba_dependencies_ae_dh.NAME is ‘Name of the object’;
comment on column dba_dependencies_ae_dh.TYPE is ‘Type of the object’;
comment on column dba_dependencies_ae_dh.EDITION_NAME is ‘Name of edition for which this object is active’;
comment on column dba_dependencies_ae_dh.OBJECT_ID is ‘ID of this object’;
comment on column dba_dependencies_ae_dh.REFERENCED_OWNER is ‘Owner of referenced object (remote owner if remote object)’;
comment on column dba_dependencies_ae_dh.REFERENCED_NAME is ‘Name of referenced object’;
comment on column dba_dependencies_ae_dh.REFERENCED_TYPE is ‘Type of referenced object’;
comment on column dba_dependencies_ae_dh.REFERENCED_EDITION_NAME is ‘Name of edition for which referenced object is active’;
comment on column dba_dependencies_ae_dh.REFERENCED_OBJECT_ID is ‘ID of referenced object’;
comment on column dba_dependencies_ae_dh.REFERENCED_LINK_NAME is ‘Name of dblink if this is a remote object’;
create or replace public synonym dba_dependencies_ae_dh for sys.dba_dependencies_ae_dh;