Project:
| View Revisions: Issue #35628 | [ All Revisions ] [ Back to Issue ] | ||
| Summary | 0035628: ORA: sometimes update.database after install.source detects db changes | ||
| Revision | 2017-03-28 10:02 by alostale | ||
| Description | Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes. The problem is caused by Oracle's virtual columns [1] which are included within the CRC used to check local changes, as their names might not be consistent, they can cause this situation. (See below for more details) == Detailed analysis == Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else
'diff values' end
from db_checksums where old_crc = new_crc and obj_type='col'
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;
type name same values
---- --------------------------- -----------
col AD_MODEL_OBJECT.UPDATEDBY same values
-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values'
else 'diff values' end
from db_checksums where old_crc != new_crc and obj_type='col'
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')
order by 2;
type name same values
---- ------------------------------------- -----------
col AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID same values
-- objects with different value to calculate crc
select count(1) from db_checksums where CAST(old_value AS VARCHAR2(100)) != CAST(old_value AS VARCHAR2(100));
0
-- objects present in one check but not in the other
select substr(obj_type,1,5) as ob_type, substr(obj_name,1,40) as ob_name,
case when old_value is null then 'null' else 'not null' end as old,
case when new_value is null then 'null' else 'not null' end as new
from db_checksums where old_value is null or new_value is null
order by 1, 2;
OB_TYPE OB_NAME OLD NEW
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
-------- --------
col AD_MODEL_OBJECT.SYS_STSE58YAH1UUV2MU99DR null not null
col AD_MODEL_OBJECT.SYS_STSZ64L#G7R$IW913WO7 null not null
col AD_ORG.SYS_STSBTFDMLW$XN5GQX0VD5WXNN9 null not null
col C_TAXCATEGORY.SYS_STST77VG34JNVJ$RBGUB46 null not null
col C_UOM.SYS_STST77VG34JNVJ$RBGUB46USK1 null not null
col GL_CATEGORY.SYS_STST77VG34JNVJ$RBGUB46US null not null
col M_LOCATOR.SYS_STST77VG34JNVJ$RBGUB46USK1 null not null
col OBPOS_APP_PAYMENT.SYS_STSV4HY32RGA6QC_6Y null not null
col OBUIAPP_PARAMETER.SYS_STSDD52J8SWA3Y97GL null not null
9 rows selected.
-- SYS_% columns
select table_name, column_name,data_default from user_tab_cols where column_name like 'SYS\_%' escape
'\' order by 1,2
TABLE_NAME COLUMN_NAME DATA_DEFAULT
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
AD_MODEL_OBJECT SYS_STSE58YAH1UUV2MU99DR0UZYX# SYS_OP_COMBINED_HASH("ISACTIVE","OBJECT_TYPE")
AD_MODEL_OBJECT SYS_STSZ64L#G7R$IW913WO7OO2#VE SYS_OP_COMBINED_HASH("ISACTIVE","ACTION","ISDEFAULT","AD_PROCESS_ID")
AD_ORG SYS_STSBTFDMLW$XN5GQX0VD5WXNN9 SYS_OP_COMBINED_HASH("AD_ORG_ID","AD_CLIENT_ID","ISACTIVE")
C_BPARTNER SYS_NC00097$ UPPER("NAME")
C_BPARTNER SYS_NC00098$ UPPER("REFERENCENO")
C_BPARTNER SYS_NC00099$ UPPER("VALUE")
C_ORDER SYS_NC00102$ UPPER("DOCUMENTNO")
C_TAXCATEGORY SYS_STST77VG34JNVJ$RBGUB46USK1 SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")
C_UOM SYS_STST77VG34JNVJ$RBGUB46USK1 SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")
FIN_PAYMENT SYS_NC00043$ "RET_HIGVOL_ORACLE"."OBEQUALS"("GENERATED_CREDIT","USED_CREDIT")
GL_CATEGORY SYS_STST77VG34JNVJ$RBGUB46USK1 SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")
TABLE_NAME COLUMN_NAME DATA_DEFAULT
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
M_INOUT SYS_NC00066$ UPPER("DOCUMENTNO")
M_LOCATOR SYS_STST77VG34JNVJ$RBGUB46USK1 SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")
M_PRODUCT SYS_NC00110$ UPPER("NAME")
M_PRODUCT SYS_NC00111$ UPPER("UPC")
M_PRODUCT SYS_NC00112$ UPPER("VALUE")
OBPOS_APP_PAYMENT SYS_STSV4HY32RGA6QC_6Y4KE76DMR SYS_OP_COMBINED_HASH("VALUE","FIN_FINANCIAL_ACCOUNT_ID","NAME")
OBUIAPP_PARAMETER SYS_STSDD52J8SWA3Y97GLJ1AA8GM5 SYS_OP_COMBINED_HASH("AD_REFERENCE_ID","AD_REFERENCE_VALUE_ID","OBUIAPP_PROCESS_
18 rows selected.
[1] https://blogs.oracle.com/sql/entry/ora_54033_and_the_hidden [^] |
||
| Revision | 2017-03-28 10:00 by alostale | ||
| Description | Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes. Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else
'diff values' end
from db_checksums where old_crc = new_crc and obj_type='col'
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;
type name same values
---- --------------------------- -----------
col AD_MODEL_OBJECT.UPDATEDBY same values
-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values'
else 'diff values' end
from db_checksums where old_crc != new_crc and obj_type='col'
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')
order by 2;
type name same values
---- ------------------------------------- -----------
col AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID same values
-- objects with different value to calculate crc
select count(1) from db_checksums where CAST(old_value AS VARCHAR2(100)) != CAST(old_value AS VARCHAR2(100));
0
-- objects present in one check but not in the other
select substr(obj_type,1,5) as ob_type, substr(obj_name,1,40) as ob_name,
case when old_value is null then 'null' else 'not null' end as old,
case when new_value is null then 'null' else 'not null' end as new
from db_checksums where old_value is null or new_value is null
order by 1, 2;
OB_TYPE OB_NAME OLD NEW
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
-------- --------
col AD_MODEL_OBJECT.SYS_STSE58YAH1UUV2MU99DR null not null
col AD_MODEL_OBJECT.SYS_STSZ64L#G7R$IW913WO7 null not null
col AD_ORG.SYS_STSBTFDMLW$XN5GQX0VD5WXNN9 null not null
col C_TAXCATEGORY.SYS_STST77VG34JNVJ$RBGUB46 null not null
col C_UOM.SYS_STST77VG34JNVJ$RBGUB46USK1 null not null
col GL_CATEGORY.SYS_STST77VG34JNVJ$RBGUB46US null not null
col M_LOCATOR.SYS_STST77VG34JNVJ$RBGUB46USK1 null not null
col OBPOS_APP_PAYMENT.SYS_STSV4HY32RGA6QC_6Y null not null
col OBUIAPP_PARAMETER.SYS_STSDD52J8SWA3Y97GL null not null
9 rows selected.
-- SYS_% columns
select table_name, column_name,data_default from user_tab_cols where column_name like 'SYS\_%' escape
'\' order by 1,2
TABLE_NAME COLUMN_NAME DATA_DEFAULT
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
AD_MODEL_OBJECT SYS_STSE58YAH1UUV2MU99DR0UZYX# SYS_OP_COMBINED_HASH("ISACTIVE","OBJECT_TYPE")
AD_MODEL_OBJECT SYS_STSZ64L#G7R$IW913WO7OO2#VE SYS_OP_COMBINED_HASH("ISACTIVE","ACTION","ISDEFAULT","AD_PROCESS_ID")
AD_ORG SYS_STSBTFDMLW$XN5GQX0VD5WXNN9 SYS_OP_COMBINED_HASH("AD_ORG_ID","AD_CLIENT_ID","ISACTIVE")
C_BPARTNER SYS_NC00097$ UPPER("NAME")
C_BPARTNER SYS_NC00098$ UPPER("REFERENCENO")
C_BPARTNER SYS_NC00099$ UPPER("VALUE")
C_ORDER SYS_NC00102$ UPPER("DOCUMENTNO")
C_TAXCATEGORY SYS_STST77VG34JNVJ$RBGUB46USK1 SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")
C_UOM SYS_STST77VG34JNVJ$RBGUB46USK1 SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")
FIN_PAYMENT SYS_NC00043$ "RET_HIGVOL_ORACLE"."OBEQUALS"("GENERATED_CREDIT","USED_CREDIT")
GL_CATEGORY SYS_STST77VG34JNVJ$RBGUB46USK1 SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")
TABLE_NAME COLUMN_NAME DATA_DEFAULT
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
M_INOUT SYS_NC00066$ UPPER("DOCUMENTNO")
M_LOCATOR SYS_STST77VG34JNVJ$RBGUB46USK1 SYS_OP_COMBINED_HASH("AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","ISDEFAULT")
M_PRODUCT SYS_NC00110$ UPPER("NAME")
M_PRODUCT SYS_NC00111$ UPPER("UPC")
M_PRODUCT SYS_NC00112$ UPPER("VALUE")
OBPOS_APP_PAYMENT SYS_STSV4HY32RGA6QC_6Y4KE76DMR SYS_OP_COMBINED_HASH("VALUE","FIN_FINANCIAL_ACCOUNT_ID","NAME")
OBUIAPP_PARAMETER SYS_STSDD52J8SWA3Y97GLJ1AA8GM5 SYS_OP_COMBINED_HASH("AD_REFERENCE_ID","AD_REFERENCE_VALUE_ID","OBUIAPP_PROCESS_
18 rows selected.
|
||
| Revision | 2017-03-28 09:57 by alostale | ||
| Description | Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes. Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else
'diff values' end
from db_checksums where old_crc = new_crc and obj_type='col'
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;
type name same values
---- --------------------------- -----------
col AD_MODEL_OBJECT.UPDATEDBY same values
-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values'
else 'diff values' end
from db_checksums where old_crc != new_crc and obj_type='col'
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')
order by 2;
type name same values
---- ------------------------------------- -----------
col AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID same values
-- objects with different value to calculate crc
select count(1) from db_checksums where CAST(old_value AS VARCHAR2(100)) != CAST(old_value AS VARCHAR2(100));
0
-- objects present in one check but not in the other
select substr(obj_type,1,5) as ob_type, substr(obj_name,1,40) as ob_name,
case when old_value is null then 'null' else 'not null' end as old,
case when new_value is null then 'null' else 'not null' end as new
from db_checksums where old_value is null or new_value is null
order by 1, 2;
|
||
| Revision | 2017-03-28 09:56 by alostale | ||
| Description | Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes. Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else
'diff values' end
from db_checksums where old_crc = new_crc and obj_type='col'
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;
type name same values
---- --------------------------- -----------
col AD_MODEL_OBJECT.UPDATEDBY same values
-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values'
else 'diff values' end
from db_checksums where old_crc != new_crc and obj_type='col'
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')
order by 2;
type name same values
---- ------------------------------------- -----------
col AD_MODEL_OBJECT_MAPPING.AD_CLIENT_ID same values
|
||
| Revision | 2017-03-28 09:53 by alostale | ||
| Description | Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes. Applying the attached debugging patch, install.source and update.database shows the following results when failing.
-- last CRC OK
select substr(obj_type,1,3), substr(obj_name,1,40),old_value,new_value,
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else
'diff values' end
from db_checksums where old_crc = new_crc and obj_type='col'
and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col')
order by 2;
-- first differenct CRC
select substr(obj_type,1,3), substr(obj_name,1,40),
case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values'
else 'diff values' end
from db_checksums where old_crc != new_crc and obj_type='col'
and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col')
order by 2;
|
||
| Revision | 2017-03-28 09:52 by alostale | ||
| Description | Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes. Applying the attached debugging patch, install.source and update.database shows the following results when failing. <code> -- last CRC OK select substr(obj_type,1,3), substr(obj_name,1,40),old_value,new_value, case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 'diff values' end from db_checksums where old_crc = new_crc and obj_type='col' and obj_name = (select max(obj_name) from db_checksums where old_crc = new_crc and obj_type='col') order by 2; -- first differenct CRC select substr(obj_type,1,3), substr(obj_name,1,40), case when to_char(substr(old_value,1,4000)) = to_char(substr(new_value,1,4000)) then 'same values' else 'diff values' end from db_checksums where old_crc != new_crc and obj_type='col' and obj_name = (select min(obj_name) from db_checksums where old_crc != new_crc and obj_type='col') order by 2; </code> |
||
| Revision | 2017-03-28 09:51 by alostale | ||
| Description | Using Oracle 12 in some occasions, update.database just after install source detects local structural DB changes. |
||
| Copyright © 2000 - 2009 MantisBT Group |
 |