Skip to content

bug report-ddl on an object invalidates dependent synonyms until oracle 10.2.0.5





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

 

  TIP:  Click help for a detailed explanation of this page.

 

Bookmark Go to End

 

Subject: DDL Against An Object Dependent On By A Synonym Invalidates The Synonym Object.
  Doc ID: 603093.1 Type: PROBLEM
  Modified Date : 02-JUL-2008 Status: PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References


Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.3
This problem can occur on any platform.

Symptoms

If a DDL occurs against a object dependent on by a synonym and the DDL operation results in the synonym object
(and other objects dependent on the synonym) becoming invalid.

Test Case:

SQL> CREATE TABLE tab1(x NUMBER)
  2     PARTITION BY RANGE (x) (
  3     PARTITION p1 VALUES LESS THAN (10)
  4     , PARTITION pM VALUES LESS THAN (MAXVALUE)
  5     );

Table created.

SQL> CREATE OR REPLACE SYNONYM syn1 FOR tab1;

Synonym created.

SQL> CREATE PACKAGE p
  2     AS
  3     PROCEDURE p (
  4     i_param                        IN syn1.x%TYPE
  5     );
  6     END p;
  7  /

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
——————- ——-
P
PACKAGE             INVALID

SYN1
SYNONYM             INVALID

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
(and other objects dependent on the synonym) becoming invalid, then you have most likely encountered this problem.

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 ;

/

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.