[11g] Origine d'une invalidation de procédure PL/SQL

Version imprimable

23/05/2012, 14h18
PpPool

[11g] Origine d'une invalidation de procédure PL/SQL

Bonjour à tous

Sur une release 11g (11.2.0.2) nous rencontrons depuis quelques temps un phénoméne curieux : plusieurs procédures PL/SQL deviennent invalides alors que les objets référants ne font l'objet d'aucunes altérations : interrogation de la vue DBA_OBJECTS et notamment du champ LAST_DDL_TIME ne donne rien

ceci peut arriver plusieurs fois par jour pour certaines procédures.

La seule particularité de ces procédures est qu'elles accédent à certaines vues du dictionnaires (ALL_TABLES,..). Est-il possible que certaines modifications du dictionnaire pourraient expliquer ce phénoméne ?

Avez-vous une solution pour introspecter ce problème ? Mise en trace ? Audit ?

merci pour votre aide

Au prix qu'on paye....

Il y a des vues toutes faites pour ca. :D
Exemple: (DBA/USER)_DEPENDENCIES

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create or replace procedure tmp_proc1 is
begin
   dbms_lock.sleep(1);
end;
/
create or replace procedure tmp_proc2 is
l_num number;
begin
   tmp_proc1;
   tmp_proc1;
   l_num:=dbms_random.value;
end;
/
 
create or replace procedure tmp_proc3 is
begin
   tmp_proc2;
end;
/
 
select name, referenced_owner, referenced_name, referenced_type from user_dependencies where name like 'TMP_PROC_' order by 1;

Et dans cet exemple, j'obtiens le resultat suivant:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
TMP@MINILHC >select name, referenced_owner, referenced_name, referenced_type from user_dependencies where name like 'TMP_PROC_' order by 1;
 
NAME                 REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE
-------------------- ------------------------------ ------------------------------ ------------------
TMP_PROC1            SYS                            SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE
TMP_PROC1            PUBLIC                         DBMS_LOCK                      SYNONYM
TMP_PROC1            SYS                            STANDARD                       PACKAGE
TMP_PROC2            SYS                            SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE
TMP_PROC2            PUBLIC                         DBMS_RANDOM                    SYNONYM
TMP_PROC2            TMP                            TMP_PROC1                      PROCEDURE
TMP_PROC2            SYS                            STANDARD                       PACKAGE
TMP_PROC3            TMP                            TMP_PROC2                      PROCEDURE
TMP_PROC3            SYS                            SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE
 
9 rows selected.

Apres, si vous modifiez des tables, alors oui ca invalide. :D

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop procedure tmp_proc1;
drop procedure tmp_proc2;
drop procedure tmp_proc3;
create table tmp_tab (n number);
create or replace procedure tmp_proc1 is
   l_var tmp_tab%rowtype;
begin
   select * into l_var from tmp_tab;
end;
/
select status from user_objects where object_name='TMP_PROC1';
select name, referenced_owner, referenced_name, referenced_type from user_dependencies where name like 'TMP_PROC_' order by 1;
alter table tmp_tab add (vc varchar2(20));
select status from user_objects where object_name='TMP_PROC1';

Et:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
TMP@MINILHC >select status from user_objects where object_name='TMP_PROC1';
 
STATUS
-------
VALID
 
Elapsed: 00:00:00.00
TMP@MINILHC >select name, referenced_owner, referenced_name, referenced_type from user_dependencies where name like 'TMP_PROC_' order by 1;
 
NAME                 REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE
-------------------- ------------------------------ ------------------------------ ------------------
TMP_PROC1            SYS                            STANDARD                       PACKAGE
TMP_PROC1            TMP                            TMP_TAB                        TABLE
TMP_PROC1            SYS                            SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE
 
Elapsed: 00:00:00.05
TMP@MINILHC >alter table tmp_tab add (vc varchar2(20));
 
Table altered.
 
Elapsed: 00:00:00.00
TMP@MINILHC >select status from user_objects where object_name='TMP_PROC1';
 
STATUS
-------
INVALID

23/05/2012, 14h59
Mohamed.Houri

Citation:

Envoyé par PpPool

Bonjour à tous

Sur une release 11g (11.2.0.2) nous rencontrons depuis quelques temps un phénoméne curieux : plusieurs procédures PL/SQL deviennent invalides alors que les objets référants ne font l'objet d'aucunes altérations : interrogation de la vue DBA_OBJECTS et notamment du champ LAST_DDL_TIME ne donne rien

ceci peut arriver plusieurs fois par jour pour certaines procédures.

La seule particularité de ces procédures est qu'elles accédent à certaines vues du dictionnaires (ALL_TABLES,..). Est-il possible que certaines modifications du dictionnaire pourraient expliquer ce phénoméne ?

Avez-vous une solution pour introspecter ce problème ? Mise en trace ? Audit ?

merci pour votre aide

Est-ce que ces procédures ont été créés toutes seules ou elles sont incluses dans des packages?

Au niveau de l'analyse des dépendances, on utilise cette requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT dd.referenced_owner, dd.referenced_name, dd.referenced_type,
       DO.created, DO.last_ddl_time
  FROM dba_dependencies dd, dba_objects DO
 WHERE dd.referenced_owner = DO.owner
   AND dd.referenced_name = DO.object_name
   AND dd.referenced_type = DO.object_type
   AND dd.NAME = '<PS>'
   AND dd.owner = '<OWNER>'
   AND (   DO.last_ddl_time >= TO_DATE ('23/05/2012', 'DD/MM/YYYY')
        OR DO.created >= TO_DATE ('23/05/2012', 'DD/MM/YYYY')
       )

Aucun objet référencé par la PS n'a été re-créé, ni altéré
pourtant la PS ce matin était en status INVALID
bizarre, j'ai dit bizarre -)

23/05/2012, 15h10
PpPool

Citation:

Envoyé par Mohamed.Houri

Est-ce que ces procédures ont été créés toutes seules ou elles sont incluses dans des packages?

Bonjour Mohamed
Merci pour ta réponse

Non des procédures simples

@Rams7s
Nous interrogeons en effet DBA_DEPENDENCIES
23/05/2012, 15h35
Mohamed.Houri

Citation:

Envoyé par PpPool

Bonjour Mohamed
Merci pour ta réponse

Non des procédures simples

@Rams7s
Nous interrogeons en effet DBA_DEPENDENCIES

Et qu'est ce que vous avez dans dba_errors (ou user_errors)?
23/05/2012, 15h47
PpPool

Citation:

Envoyé par Mohamed.Houri

Et qu'est ce que vous avez dans dba_errors (ou user_errors)?

rien qui concerne cette procédure
Celle-ci tourne actuellement sans problèmes
je vais voir demain

je soupçonne des invalidations de obj_id au niveau du dictionnaire. on a déjà connu ce genre de problème avec des tables
merci en tout cas de votre aide
23/05/2012, 19h09
yong321

Citation:

Envoyé par PpPool

Bonjour à tous
...
plusieurs procédures PL/SQL deviennent invalides alors que les objets référants ne font l'objet d'aucunes altérations : interrogation de la vue DBA_OBJECTS et notamment du champ LAST_DDL_TIME ne donne rien
...
Avez-vous une solution pour introspecter ce problème ? Mise en trace ? Audit ?

Quelqu'un at-il exécuter dbms_utility.invalidate?

Audit DDL on table1 et table2, ...