11g: ORA-28002 et problème d'expiration de mot de passe

6 mois après avoir migré votre base en 11g il est fort probable que vous rencontriez le message d'erreur suivant après une simple tentative de connexion à votre base:

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Lun. Sept. 13 12:19:01 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-28002: le mot de passe expirera dans 7 jours

Ce problème est dû au fait qu'en 11g le profil DEFAULT impose certaines limites au niveau du password et notamment une durée de vie du mot de passe de 180 jours:

SQL> select * from dba_profiles where resource_name = 'PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

Si vous ne voulez pas avoir à changer de password tous les 6 mois vous pouvez redéfinir le PASSWORD_LIFE_TIME du profil DEFAULT à UNLIMITED:

alter profile default LIMIT PASSWORD_LIFE_TIME  UNLIMITED;

Néanmoins, si le mot de passe est déjà arrivé dans la phase de PASSWORD_GRACE_TIME ou s'il a déjà expiré il faudra redéfinir un password pour votre user:

alter user SCOTT identified by TIGER;

SELECT FOR UPDATE OF et ORA-01733

En 11G R2, je suis tombé récemment sur un bug un peu "sioux" qui pourrait être simplifié par le testcase suivant:

SQL> create table t1 (t1_c1 number);

Table crÚÚe.

SQL> select x.t1_c1 from
2  (select t1.t1_c1 from t1) x
3  for update of x.t1_c1;
for update of x.t1_c1
*
ERREUR à la ligne 3 :
ORA-01733: les colonnes virtuelles ne sont pas autorisées ici


SQL> select b.t1_c1 from
2  (select t1.t1_c1 from t1) b
3  for update of b.t1_c1;

aucune ligne sélectionnée

En gros, lorsque j'alias ma subquery avec la lettre "X" j'obtiens une erreur ORA-01733, par contre si j'utilise une autre lettre ça fonctionne.

Bon, par rapport au progiciel sur lequel je travaille, le workaround est simple il suffit de modifier le nom de l'alias.

J'ai quand même ouvert un case au support Oracle pour en savoir plus, et apparemment cette erreur est dû au fait que j'ai dans ma base un fonction qui porte le nom "X".
En faisant, une petite recherche sur le nom de mes objets je me suis effectivement aperçu qu'il existait un synonyme public (pointant sur une fonction du schéma MDSYS) qui portait ce nom:

SQL> select SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from all_synonyms where SYNONYM_NAME='X';

SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ----------------------------------------- 
X                              MDSYS                          OGC_X

Le schema MDSYS est généralement présent lorsque les options MULTIMEDIA ou SPATIAL ont été installées.
Si mon alias s'appelait TOTO et que j'avais une fonction ou un synonyme portant ce nom, je serais tombé sur la même erreur:

SQL> select toto.t1_c1 from
  2  (select t1.t1_c1 from t1) toto
  3  for update of toto.t1_c1;

aucune ligne sélectionnée

SQL> create or replace function toto return number is
  2  begin
  3     return 1;
  4  end;
  5  /

Fonction créée.

SQL> select toto.t1_c1 from
  2  (select t1.t1_c1 from t1) toto
  3  for update of toto.t1_c1;
for update of toto.t1_c1
              *
ERREUR à la ligne 3 :
ORA-01733: les colonnes virtuelles ne sont pas autorisées ici

 
Donc pour résumer, si un jour vous tombez sur cette erreur lors d'un SELECT FOR UPDATE OF c'est qu'il existe surement un objet dans votre base portant le même nom que l'alias de votre sous-requête.

Nouveauté 11g: Interval partitioning

Un des inconvénients lorsqu'on utilise le partitioning est le fait d'avoir à ajouter manuellement les partitions lorsqu'on insère des données pour lesquelles aucune partition existante ne correspond.
Par exemple, pour une table partitionnée selon la date de mise à jour (une partition = 1 mois) il fallait soit pré-créer à l'avance des partitions pour les futurs mois, soit créer de nouvelles partitions au fur et à mesure qu'on avance dans le temps.

Oracle gomme ce soucis en proposant avec la 11g le partitioning par INTERVAL.
Il s'agit en fait d'une extension du partitioning BY RANGE. Avec ce type de partitioning si une ligne d'une table partitionnée BY RANGE selon une colonne DATE ne correspond pas à une partition existante, oracle créera automatiquement la partition manquante.

EXEMPLE

Tout d'abord je crée une table partitionnée BY RANGE avec l'option INTERVAL:

SQL> CREATE TABLE test_interval
2  (id number,
3  creation_date date default sysdate)
4  partition by range (creation_date)
5  interval (numtoyminterval(1,'MONTH'))
6  ( PARTITION p_jan2010 VALUES
7  LESS THAN (TO_DATE('01-02-2010','DD-MM-RRRR')))
8  /

Table crÚÚe.

La table est partitionnée selon la colonne CREATION_DATE. La première partition correspond aux données ayant une date inférieure au 01/02/2010. La fonction interval (numtoyminterval(1,'MONTH') indique que chaque partition correspond à un mois.

SQL> set lines 500
SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Si j'insère une ligne dans la table avec une date au mois de janvier 2010 la ligne ira dans la partition existante, par contre si j'insère une ligne avec une date au mois de février Oracle va créer la partition manquante et mettre la ligne dans cette nouvelle partition:

SQL> insert into TEST_INTERVAL values (1, '01-01-10');

1 ligne crÚÚe.

SQL> commit;

Validation effectuÚe.

SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> insert into TEST_INTERVAL values (2, '01-02-10');

1 ligne crÚÚe.

SQL> commit;

Validation effectuÚe.

SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P27                        TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

La partition SYS_P27 est la partition crée par Oracle. On perd donc le contrôle sur le nommage des partitions mais on n'a plus le soucis de créer à la main les partitions manquantes.

Si vous venez de migrer de la 10g vers la 11g et que vous avez des tables partitionnées BY RANGE vous pouvez appliquer l'Interval partitioning sur cette table sans avoir à recréer votre table. Il suffit d'utiliser la commande suivante:

SQL> alter table test_interval set interval (NUMTOYMINTERVAL(1, 'MONTH'));

Table modifiÚe.

Pour que ça fonctionne il ne faut pas que vous ayez de partition avec une MAXVALUE sinon vous tomberez sur l'erreur suivante: ORA-14759.

Il existe d'autres nouveautés en matière de partitioning avec la 11g mais ils feront l'objet d'un nouveau post.