Skip to main content

Search

Welcome to Tibero GTS!

Query to Extract Objects in Invalid State and Automatically Generate Compile Statements After Patch or Migration Work

Document Type | Troubleshooting

Category | Monitoring/Inspection

Applicable Product Version | 6FS02

Document Number | TMOTS023

 

Issue

After patch or migration work, if some objects have changed to an INVALID status, it became necessary to selectively identify only those objects that were newly changed to INVALID (not those that were already INVALID before) and compile them.
 
 

Cause

During patch or migration, related objects (dependent objects) may be changed or deleted. Changes in the structure of objects referenced by PL/SQL objects such as packages/procedures/views, issues with permissions and synonyms, differences in binary or DB settings, and compilation errors can cause objects to become INVALID.
 
 

Solutions

1. Create a table to store invalid items before patch or migration

The process to create a table extracting objects in invalid status from the as-is binary is as follows.
 
1-1) tbsql tibero/tmax
1-2) Execute the table creation statement
CREATE TABLE TIBERO.BEFORE_PATCH AS
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status='INVALID';

 

2. Create a table to store invalid items after patch or migration

The process to create a table extracting objects in invalid status from the to-be binary is as follows.
 
2-1) tbsql tibero/tmax
2-2) Execute the table creation statement
CREATE TABLE TIBERO.AFTER_PATCH AS
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status='INVALID';

 

3. Query to generate compile statements for objects newly invalidated after work (patch, migration)

  • Logic Explanation: This logic filters only those INVALID objects that exist in the AFTER table but not in the BEFORE table. In other words, it extracts newly INVALID objects by performing AFTER table MINUS BEFORE table.
  • Query to generate compile statements for objects invalidated after work:
SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
FROM (SELECT * FROM TIBERO.AFTER_PATCH MINUS SELECT * FROM TIBERO.BEFORE_PATCH)
WHERE OBJECT_TYPE IN ('TRIGGER', 'FUNCTION', 'PROCEDURE', 'PACKAGE', 'TYPE','VIEW')
UNION ALL
SELECT 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;'
FROM (SELECT * FROM TIBERO.AFTER_PATCH MINUS SELECT * FROM TIBERO.BEFORE_PATCH)
WHERE OBJECT_TYPE LIKE '%BODY%';

Note

Interpretation of the logic to compile invalid objects

1) Selecting new INVALID objects by difference (MINUS)
  • The MINUS operator returns rows that exist in the first result set but not in the second.
  • Therefore, among all INVALID objects in the AFTER_PATCH table, objects that were already INVALID before the patch are excluded, leaving only those newly invalidated.
 
2) Interpretation of the part that creates ALTER statements for objects selected and changed to INVALID
  • Interpretation of general object compilation part
    If OBJECT_TYPE is trigger, function, procedure, package specification, etc., a standard ALTER โ€ฆ COMPILE; statement is generated.
SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
FROM (
 ...
)
WHERE OBJECT_TYPE IN ('TRIGGER','FUNCTION','PROCEDURE','PACKAGE','TYPE','VIEW');
  • Interpretation of package body exclusive compilation part
    For PACKAGE BODY, unlike other objects, the ALTER statement requires COMPILE BODY at the end, so this command is added separately.
SELECT 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' 
FROM ( ... ) 
WHERE OBJECT_TYPE LIKE '%BODY%' PACKAGE BODY;

 

Example

  • Before work (patch, migration): select * from tibero.before_patch; โ†’ 10 invalid objects found

 

  • After work (patch, migration): select * from tibero.after_patch; โ†’ 11 invalid objects found

 
  • It was confirmed that the VW_EMP table owned by SYS user became invalid after the work (patch, migration). After extracting the compile statements for the invalidated objects, the objects changed to invalid are compiled to valid.

 

 

 

Related to