After applying 13543062, we ran ADZDSHOWDDLS.sql to see if all tables and materialized views had editioning views. Below, an excerpt of ADZDSHOWDDLS.out shows the editioning results for tables and materialized views:
Phase Count Status
UPGRADE_TABLE 18355 S – Successfully Executed
UPGRADE_MVIEW 1 N – Not Executed 1 R – Running 113 S – Successfully Executed 11 E – Error
Run the following SQL statement to check the count of editioning views versus the count of tables and materialized views:
select * from dba_objects where (object_type in (‘VIEW’) and object_name like ‘%#’)
union
select * from dba_objects where object_type in (‘MATERIALIZED VIEW’, ‘TABLE’);
Of course, we can do it an easier way and compare VIEW_NAME and TABLE_NAME in DBA_EDITIONING_VIEWS and do a “count” to see if the result agrees with the results from above.
describe dba_editioning_views
OWNER NOT NULL VARCHAR(128)
VIEW_NAME NOT NULL VARCHAR(128)
TABLE_NAME NOT NULL VARCHAR(128)
Or, select table_name where the view_name doesn’t exist.
Or, use the Section 20 SQL from ADZDDBCC.sql to find tables that don’t have an editioning view. In order to patch custom tables using online patching, custom tables require an editioning view and APPS synonym. The following SQL will find tables that may not have an editioning view.
**********************************************************************
SECTION-20 [full]
**********************************************************************
“Table must have an editioning view.”
– P2: These tables may not be patched using online patching.
– Fix: Execute the table upgrade procedure.
SQL> exec ad_zd_table.upgrade(table_owner, new_table_name)
Note: Tables that are dynamically created by application runtime can be ignored.
Note: Tables that end with “_A” are typically audit tables by the Audit Trail feature, and can be ignored.
Note: This check is only active after Online Patching Enablement.
#
select tab.owner owner, tab.table_name table_name
from dba_tables tab
where tab.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag in (‘A’, ‘B’, ‘E’) )
and tab.temporary = ‘N’
and tab.secondary = ‘N’
and tab.iot_type is null
and tab.tablespace_name not in (‘APPS_TS_NOLOGGING’, ‘APPS_TS_QUEUES’)
and tab.table_name not like ‘%$’
and not regexp_like(tab.table_name, ‘^AQ\$’, ‘c’)
and not regexp_like(tab.table_name, ‘^AW\$’, ‘c’)
and not regexp_like(tab.table_name, ‘^MLOG\$’, ‘c’)
and not regexp_like(tab.table_name, ‘^BSC_DI_[0-9_]+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^BSC_D_.+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_ADJUSTMENT_.+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_DETAIL_.+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_SUMMARY_.+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^GL_DAILY_POST_INT_.+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^GL_INTERCO_BSV_INT_[0-9]+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^GL_MOVEMERGE_BAL_[0-9]+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^GL_MOVEMERGE_INTERIM_[0-9]+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^XLA_GLT_[0-9]+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^ICX_POR_C[0-9]+.*$’, ‘c’)
and not regexp_like(tab.table_name, ‘^ICX_POR_UPLOAD_[0-9]+.*$’, ‘c’)
and not regexp_like(tab.table_name, ‘^IGI_SLS_[0-9]+$’, ‘c’)
and not regexp_like(tab.table_name, ‘^JTF_TAE_[0-9]+.*$’, ‘c’)
and not regexp_like(tab.table_name, ‘^JTY_[0-9]+_.*$’, ‘c’)
and not regexp_like(tab.table_name, ‘^ZPBDATA[0-9]+_EXCPT_T$’, ‘c’)
and not regexp_like(tab.table_name, ‘^ZX_DATA_UPLOAD_.*$’, ‘c’)
/* Note: Exclusion list for AD varies by release */
and ( ( tab.table_name not in
(‘TXK_TCC_RESULTS’,
‘AD_DEFERRED_JOBS’,
‘AD_TABLE_INDEX_INFO’,
‘FND_INSTALL_PROCESSES’,
‘AD_UTIL_PARAMS’,
‘AD_PATCHED_TABLES’,
‘AD_ZD_DDL_HANDLER’,
‘AD_OBSOLETE_OBJECTS’,
‘FND_PRODUCT_INSTALLATIONS’)
and
( select codelevel from ad_trackable_entities
where upper(abbreviation)=’AD’ ) < ‘C.1’ )
or
( tab.table_name not in
(‘TXK_TCC_RESULTS’,
‘AD_DEFERRED_JOBS’,
‘AD_TABLE_INDEX_INFO’,
‘FND_INSTALL_PROCESSES’,
‘AD_UTIL_PARAMS’ )
and
( select codelevel from ad_trackable_entities
where upper(abbreviation)=’AD’ ) >= ‘C.1′ )
)
and (tab.owner, tab.table_name) not in
( select qt.owner, qt.queue_table
from dba_queue_tables qt )
and (tab.owner, tab.table_name) not in
( select mv.owner, mv.container_name
from dba_mviews mv )
and (tab.owner, tab.table_name) in
( select syn.table_owner, syn.table_name
from dba_synonyms syn
where syn.owner in
( select oracle_username from system.fnd_oracle_userid
where read_only_flag =’U’ ) )
and not exists
( select ev.owner, ev.view_name
from dba_editioning_views ev
where ev.owner = tab.owner
and ev.table_name = tab.table_name
and ev.view_name = substrb(tab.table_name, 1, 29)||’#’ )
and exists
( select null
from fnd_oracle_userid au, dba_users du
where au.oracle_username = du.username
and au.read_only_flag = ‘U’
and du.editions_enabled = ‘Y’ )
and not exists
( select 1
from system.fnd_oracle_userid fou
, fnd_product_installations fpi
, ad_obsolete_objects aoo
where fpi.application_id = aoo.application_id
and fou.oracle_id = fpi.oracle_id
and fou.oracle_username = tab.owner
and aoo.object_name = tab.table_name
and aoo.object_type = ‘TABLE’ )
order by tab.owner, tab.table_name
/
