This is an article explaining the bug fix for the problem where ddl operations on objects like packages or tables invalidate the dependent synonyms.
this bug is fixed after 10.2.0.5 but in case you are facing this issue on your production database this article might point you to a a quick solution.
Summary: As per below document from metalink it is supposed to be fixed in 10.2.0.5 . We have a patch 6725634 available to fix this problem. I downloaded this patch into my machine. The plan is to implement this on pre-production and the make some tests and also to ask development team to confirm that application functionalities are ok and then to implement this on production
|
Did this article help solve your problem? |
Would you recommend this document to others? |
Bottom of Form
In this Document Applies to: Oracle Server – Enterprise Edition – Version: 10.2.0.3 Symptoms If a DDL occurs against a object dependent on by a synonym and the DDL operation results in the synonym object Test Case: SQL> CREATE TABLE tab1(x NUMBER) Table created. SQL> CREATE OR REPLACE SYNONYM syn1 FOR tab1; Synonym created. SQL> CREATE PACKAGE p Package created. SQL> ALTER TABLE tab1 SPLIT PARTITION p1 AT (5) INTO (PARTITION p2, PARTITION p1 Table altered. SQL> ALTER TABLE tab1 DROP PARTITION p2; Table altered. SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name 2 IN (‘SYN1’, ‘P’); OBJECT_NAME OBJECT_TYPE STATUS SYN1 Cause This issue discussed in unpublished Bug 6725634 . If a DDL occurs against a object dependent on by a synonym and if the DDL operation results in the synonym object Solution As per the Development team, this bug fix will be included in 10.2.0.5 Patchset. Check Metalink for Patch 6725634 availability for your RDBMS version and OS. |
|||||||||||||||||||||
| Bug No. | 5357916 | ||
| Filed | 26-JUN-2006 | Updated | 11-MAY-2009 |
| Product | Enterprise Manager Grid Control | Product Version | 10.2.0.2 |
| Platform | Linux x86 | Platform Version | 3.0 |
| Database Version | 10.2.0.2 | Affects Platforms | Generic |
| Severity | Severe Loss of Service | Status | Development to Q/A |
| Base Bug | N/A | Fixed in Product Version | 11.1 |
Problem statement:
PARTITION MAINTENANCE ROUTINE INCLUDES THE RECOMPILE INVALID OBJECTS CODE
*** 06/26/06 11:47 am ***
TAR
—
.
Problem Description
——————-
– The emd_maintenance.partition_maintenance routine contains the code to do
both the dropping of obsolete partitions, as well as the routine to
recompile
the invalid objects.
– Since the drop of the partitions invalidates the emd_maintenance routine
itself, the routine can get into a ‘hang’ situation.
10.2.0.2 on Windows
.
Fix required:
– Remove the recompilation part from the partition maintenance routine.
– Create an external SQL file, which does the whole maintenance process,
from chaning the init.ora parameters, dropping the partitions, recompiling
the invalid objects, and resetting the init.ora parameter.
DECLARE
PROCEDURE DROP_PARTITIONS (v_table_name IN VARCHAR2,
v_retention_days IN PLS_INTEGER) IS
.
CURSOR old_partition_cursor (c_table_name VARCHAR2, c_keep_date DATE) IS
SELECT PARTITION_NAME
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = UPPER(c_table_name)
AND PARTITION_NAME < TO_CHAR(c_keep_date,’YYYY-MM-DD HH24:MI’)
ORDER BY PARTITION_NAME ;
.
v_partitionDate DATE;
v_keepDate DATE;
v_part_names mgmt_medium_string_table := mgmt_medium_string_table() ;
.
BEGIN
v_keepDate := TRUNC((SYSDATE – v_retention_days), ‘HH24’);
.
— Doing bulk collect up front to avoid snapshot too old
OPEN old_partition_cursor(v_table_name,v_keepDate) ;
FETCH old_partition_cursor BULK COLLECT INTO v_part_names ;
CLOSE old_partition_cursor ;
.
IF v_part_names IS NULL or v_part_names.COUNT = 0
THEN
RETURN ;
END IF ;
.
FOR part_rec IN v_part_names.FIRST..v_part_names.LAST
LOOP
EXECUTE IMMEDIATE ‘ALTER TABLE ‘||v_table_name
||’ DROP PARTITION "’
||v_part_names(part_rec) || ‘"’;
END LOOP;
.
END DROP_PARTITIONS;
PROCEDURE recompile_invalid
IS
BEGIN
FOR crec IN (SELECT object_name, object_type,
DECODE(object_type, ‘FUNCTION’, 1,
‘PROCEDURE’, 2,
‘TRIGGER’, 3,
‘VIEW’, 4,
‘PACKAGE’, 5,
‘PACKAGE BODY’, 6,
9) object_type_seq
FROM user_objects
WHERE status =’INVALID’
AND object_type IN (‘FUNCTION’, ‘PACKAGE’,
‘PACKAGE BODY’, ‘PROCEDURE’,
‘TRIGGER’, ‘VIEW’)
ORDER BY object_type_seq, object_type, created)
LOOP
BEGIN
IF (crec.object_type = ‘PACKAGE BODY’) THEN
.
— If package body is invalid, just compile the body and not
— the specification
EXECUTE IMMEDIATE ‘ALTER PACKAGE ‘ ||
crec.object_name || ‘ compile body’;
ELSE
EXECUTE IMMEDIATE ‘ALTER ‘ || crec.object_type || ‘ ‘ ||
crec.object_name || ‘ compile’;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL; — No action on failure. Continue to next object.
END;
END LOOP;
.
— Recreate the public synonyms for views that start with MGMT$
FOR crec in (SELECT tname from tab where tname like ‘MGMT$%’ and
TABTYPE=’VIEW’)
LOOP
BEGIN
EXECUTE IMMEDIATE ‘CREATE OR REPLACE PUBLIC SYNONYM ‘ ||
crec.tname || ‘ FOR ‘|| crec.tname;
EXCEPTION
WHEN OTHERS THEN
NULL; — No action on failure. Continue to next object.
END;
END LOOP;
.
EXCEPTION
WHEN OTHERS THEN
NULL; — No action on failure
END ;
BEGIN
.
EMD_MAINTENANCE.remove_em_dbms_jobs ;
.
DROP_PARTITIONS(‘MGMT_METRICS_RAW’,7) ;
.
DROP_PARTITIONS(‘MGMT_METRICS_1HOUR’,31) ;
.
DROP_PARTITIONS(‘MGMT_METRICS_1DAY’,365) ;
.
RECOMPILE_INVALID ; RECOMPILE_INVALID ;
END ;
/
.
BEGIN
EMD_MAINTENANCE.submit_em_dbms_jobs ;
END ;
/
