Forcer un plan d'exécution via un SQL profile

Avec la 10g est arrivée la notion de SQL profile, un objet contenant des informations par rapport à une requête donnée (définie par son SQL_ID) et qui permet au CBO de choisir un plan optimal lors du parsing. Ce SQL profile est en générale défini lorsqu'on fait appel au Tuning advisor pour analyser une requête non performante (un article plus détaillé est à venir sur ce sujet).

Le SQL profile, à l'inverse des OUTLINES apparus avec la 8i ou du SQL Plan Management apparu en 11g, n'a pas pour but de forcer un plan d'exécution mais plutôt d'augmenter la flexibilité de l'optimiseur.

Toutefois, une astuce géniale découverte sur le blog de Randolf GEIST permet de forcer pour une requête donnée un plan existant dans le référentiel AWR ou dans la shared pool en utilisant un SQL profile. Imaginons que vous ayez une requête sensible exécutée tous les soirs pendant un process batch et que cette requête met en général 10 minutes pour s'exécuter. Puis un jour vous vous rendez compte que le plan de cette requête a changé et qu'elle met désormais 1 heure. Vous êtes face à un problème d'instabilité de plans d'exécution. S'il s'agit d'un process lancé par un progiciel vous n'aurez pas la main pour modifier la requête. Par contre en regardant l'historique d'exécution pour cette requête dans l'AWR vous savez qu'un bon plan existe et vous souhaiteriez qu'Oracle utilise ce plan.

La technique de Randolf Geist que je vais illustrer ici vous permet d'atteindre cet objectif. Son article sur ce sujet est accessible ici.

TEST CASE:

Je crée d'abord une table t contenant un million de lignes avec une PK sur la colonne ID:

SQL> DROP TABLE t;

Table supprimée.

SQL> CREATE TABLE t
  2  AS
  3  SELECT rownum AS id, rpad('*',100,'*') AS pad
  4  FROM dual
  5  CONNECT BY level <= 1000000;

Table créée.

SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

Table modifiée.

SQL> BEGIN
  2    dbms_stats.gather_table_stats(
  3      ownname          => user,
  4      tabname          => 't',
  5      method_opt       => 'for all columns size 1'
  6    );
  7  END;
  8  /

Procédure PL/SQL terminée avec succès.

J'exécute ensuite une requête me retournant 9 lignes, donc un plan utilisant un INDEX RANGE SCAN de l'index T_PK est utilisé

SQL> VARIABLE id NUMBER
SQL> EXECUTE :id := 10;

Procédure PL/SQL terminée avec succès.

Ecoulé : 00 :00 :00.03
SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
         9

Ecoulé : 00 :00 :00.04
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 0
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 4270555908

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   106 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |   954 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"<:ID)

La partie OUTLINE DATA du plan contient l'ensemble des hints qui détermine le plan d'exécution de cette requête. C'est cet ensemble d'information qu'on souhaite associer définitivement à une requête donnée via un SQL profile.

Maintenant je modifie légèrement un paramètre du CBO juste pour forcer le HARD PARSE sur cette requête, sinon le plan précédent sera utilisé. Je simule ainsi une autre requête identique s'exécutant sous un autre environnement d'exécution.

SQL> alter session set optimizer_index_cost_adj=95;

Session modifiée.

SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
    999989

Ecoulé : 00 :00 :00.09
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 1
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4228 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   106 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   999K|   101M|  4228   (1)| 00:00:51 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 95)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"<:ID)

Cette fois si, comme je récupère quasiment toutes les lignes de ma table, le CBO a estimé qu'un FTS était le plus approprié, et il a raison. Maintenant je reste sur le même environnement d'exécution et je réexécute la première requête censée me retourner 9 lignes.

SQL> EXECUTE :id := 10;

Procédure PL/SQL terminée avec succès.

Ecoulé : 00 :00 :00.00
SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
         9

Ecoulé : 00 :00 :00.07
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 1
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4228 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   106 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   999K|   101M|  4228   (1)| 00:00:51 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 95)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"<:ID)

Et là qu'est-ce que je vois? Un Full Table Scan.
Imaginons que la table soit beaucoup plus volumineuse et la requête beaucoup plus compliquée, le plan ici peut s'avérer désastreux. Face à cette situation je peux vérifier dans la shared pool si un autre plan existe pour cette requête.

SQL> select sql_id, child_number, executions,  PARSING_SCHEMA_NAME, round(elapsed_time/1000000,2) "elapsed_sec",
  2  round((elapsed_time/1000000)/executions,2) "elapsed_per_exec",plan_hash_value, buffer_gets
  3  from v$sql where sql_id='asth1mx10aygn';

SQL_ID        CHILD_NUMBER EXECUTIONS PARSING_SCHEMA_NAME            elapsed_sec elapsed_per_exec PLAN_HASH_VALUE BUFFER_GETS
------------- ------------ ---------- ------------------------------ ----------- ---------------- --------------- -----------
asth1mx10aygn            0          1 UBXADMIN                               ,04              ,04      4270555908         958
asth1mx10aygn            1          2 UBXADMIN                               ,16              ,08      2966233522       30974

Je m'aperçois que la requête a déjà fait l'objet d'un autre plan par le passé(PLAN_HASH_VALUE=4270555908) et qu'il donne de meilleures stats d'exécution (elapsed_time, buffer_gets). Je souhaiterais forcer l'utilisation de ce plan pour cette requête via un SQL profile. Pour cela j'exécute le script suivant:

DECLARE 
   ar_profile_hints   sys.sqlprof_attr; 
   cl_sql_text        CLOB; 
BEGIN 
   SELECT   EXTRACTVALUE (VALUE (d), '/hint') AS outline_hints 
     BULK   COLLECT 
     INTO   ar_profile_hints 
     FROM   XMLTABLE ('/*/outline_data/hint' PASSING (SELECT   xmltype ( 
                      other_xml) 
                         AS xmlval 
                                                        FROM 
                      v$sql_plan
                                                       WHERE       sql_id = 'asth1mx10aygn' 
                      AND plan_hash_value = 4270555908 
                      AND other_xml IS NOT NULL)) d; 

   SELECT   sql_fulltext 
     INTO   cl_sql_text 
     FROM   v$sql 
    WHERE   sql_id = 'asth1mx10aygn'
    and rownum=1; 

   DBMS_SQLTUNE.import_sql_profile (sql_text      => cl_sql_text, 
                                    profile       => ar_profile_hints, 
                                    category      => 'DEFAULT', 
                                    name          => 'PROFILE_asth1mx10aygn', 
                                    force_match   => TRUE); 
END; 
/

La première requête du script permet de récupérer dans la variable ar_profile_hints l'outline data correspondant au bon plan que je souhaite forcer. Ce qui diffèrera lorsque vous utiliserez ce script est la valeur du SQL_ID et du PLAN_HASH_VALUE.
La deuxième requête permet de récupérer le texte exact de la requête dans la variable cl_sql_text. Une fois ces 2 informations récupérées la 3ème partie du script permet d'attacher un SQL profile nommé PROFILE_asth1mx10aygn au texte de la requête. Le paramètre FORCE_MATCH à TRUE permet de forcer le plan également aux requêtes qui diffèrent sur la valeur littéral utilisée dans la clause WHERE. Par exemple, le SQL profile défini dans mon test précédent s'appliquera à toutes les requêtes suivantes:

SELECT count(pad) FROM t WHERE id < :id;
SELECT count(pad) FROM t WHERE id < 10;
SELECT count(pad) FROM t WHERE id < 12;
SELECT count(pad) FROM t WHERE id < 100000;
etc.

Maintenant que le SQL profile a été attaché à la requête on peut vérifier s'il est bien pris en compte lorsque j'exécute ma requête:

SQL> EXECUTE :id := 10;

Procédure PL/SQL terminée avec succès.

Ecoulé : 00 :00 :00.00
SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
         9

Ecoulé : 00 :00 :00.01
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 1
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 4270555908

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   106 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |   954 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 95)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"<:ID)

Note
-----
   - SQL profile PROFILE_asth1mx10aygn used for this statement

On voit que le bon plan est bien pris en compte ici et que la partie NOTE tout en bas du plan indique bien qu'un SQL profile a été utilisé pour cette requête.

En remplaçant dans le script les vues V$SQL et v$SQL_PLAN par les vues DBA_HIST_SQLTEXT et DBA_HIST_SQL_PLAN, il aurait été possible de forcer un plan se trouvant dans le référentiel AWR et non pas dans la shared pool.

La liste des SQL profiles peut être trouvée dans la vue DBA_SQL_PROFILES.

CONCLUSION:
Contrairement à son rôle premier qui n'est pas de forcer un plan d'exécution, le SQL profile via la procédure DBMS_SQLTUNE.import_sql_profile peut finalement être utilisé à cet effet. Toutefois, il faut bien avoir à l'esprit qu'en utilisant cette astuce le plan importé sera toujours utilisé. Le risque est que ce même plan peut ne pas être du tout approprié pour d'autres valeurs des "FILTER predicates" ou bien selon la volumétrie des tables impliquées.
Vous pouvez utiliser à tout moment les procédures du package DBMS_SQLTUNE pour dropper ou désactiver un SQL profile donné.

Flusher un curseur de la shared pool avec DBMS_SHARED_POOL

Tout le monde connait la commande qui permet de vider la shared pool:
ALTER SYSTEM FLUSH SHARED_POOL;

Mais comment faire lorsqu'on veut que le CBO recalcule un nouveau plan et donc qu'il ne prenne pas en compte la plan déjà en cache? La solution consiste à vider le curseur de la shared pool en utilisant la nouvelle procédure PURGE du package DBMS_SHARED_POOL apparu avec la 10.2.0.4.

TEST CASE:

Créons une table contenant 1000 lignes avec une PK sur la colonne ID:

ALTER SYSTEM FLUSH SHARED_POOL;

DROP TABLE t;

CREATE TABLE t 
AS 
SELECT rownum AS id, rpad('*',100,'*') AS pad 
FROM dual
CONNECT BY level <= 1000;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

BEGIN
  dbms_stats.gather_table_stats(
    ownname          => user, 
    tabname          => 't', 
    estimate_percent => 100, 
    method_opt       => 'for all columns size 1'
  );
END;
/

Le plan de la requête suivante correspond à un FULL TABLE SCAN (FTS). Logique car on retourne 99% de la table:

VARIABLE id NUMBER

EXECUTE :id := 990;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Par contre le plan de la requête suivante va également me retourner un FTS même si on ne veut ici que 1% de la table. Un hard parse ayant déjà été effectué pour un même SQL_ID, le plan en cache est réutilisé pour cette requête même s'il n'est pas approprié:

EXECUTE :id := 10;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Pour flusher ce curseur de la shared pool sans avoir à flusher toute la shared pool on peut utiliser le package DBMS_SHARED_POOL:

SQL> @?/rdbms/admin/dbmspool

Package créé.

Autorisation de privilèges (GRANT) acceptée.

SQL> select address, hash_value from v$sql where sql_text = 'SELECT count(pad) FROM t WHERE id < :id';

ADDRESS  HASH_VALUE
-------- ----------
3C639C80 1107655156

SQL> exec sys.dbms_shared_pool.purge('3C639C80,1107655156','c');

Procédure PL/SQL terminée avec succès.

Le package DBMS_SHARED_POOL n'étant pas installé par défaut vous devez le faire en appelant le script DBMSPOOL qui se trouve dans %ORACLE_HOME%\RDBMS\ADMIN. Je vérifie maintenant que le curseur n'existe plus en mémoire:

SQL> select address, hash_value from v$sql where sql_text = 'SELECT count(pad) FROM t WHERE id < :id';

aucune ligne sélectionnée

Si je réexecute ma requête, le HARD PARSE a cette fois bien lieu et un plan prenant en compte mon index est utilisé:

SELECT count(pad) FROM t WHERE id < :id;

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | T_PK |
---------------------------------------------

Les index virtuels

Lorsque vous vous demandez si le fait de créer un index peut améliorer votre requête, ce qui vous freine souvent c’est le fait d’avoir à créer cet index pour effectuer votre test.
Le fait de créer un index sur une table volumineuse peut prendre énormément de temps (CPU+IO) et va consommer de la place sur votre disque.

Oracle offre la possibilité de créer un index sans lui associer de segment. Cela revient à dire qu’on a la possibilité de créer un index virtuel et ainsi savoir si l’optimiseur prendrait en compte l’index s’il existait réellement.

Voici un exemple pour bien comprendre comment profiter des index virtuels.

Tout d’abord créons une table volumineuse :

SQL> create table t1 as select * from all_objects;

Table créée.

Lorsque je veux récupérer les données de T1 dont la colonne OBJECT_TYPE équivaut à « WINDOW », je constate que l’optimiseur effectue un Full Table Scan (FTS) sur ma table.

SQL>  explain plan for
  2  select * from t1 where object_type='WINDOW';

Explicité.

SQL> select * FROM TABLE
  2  (DBMS_XPLAN.display (NULL, NULL, 'BASIC +COST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------
Plan hash value: 3617692013

-----------------------------------------------
| Id  | Operation         | Name | Cost (%CPU)|
-----------------------------------------------
|   0 | SELECT STATEMENT  |      |   367   (1)|
|   1 |  TABLE ACCESS FULL| T1   |   367   (1)|
-----------------------------------------------

Maintenant je me demande la chose suivante : si j’avais un index sur la colonne OBJECT_TYPE, est-ce que l’optimiseur l’utiliserait ?

J’aimerais avoir une réponse à cette question mais sans avoir à créer réellement ma structure d'index.
Pour cela je crée un index virtuel :

SQL> create index idx_t1 on t1(object_type) NOSEGMENT;

Index créé.

La clause NOSEGMENT indique que mon index est virtuel.

A ce stade l’index n’est toujours pas visible par le CBO. Pour le rendre visible il faut modifier un paramètre caché :

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session modifiée.

Maintenant, le CBO voit l’index et décide de le prendre en compte dans le plan :

SQL> explain plan for
  2  select * from t1 where object_type='WINDOW';

Explicité.

SQL> select * FROM TABLE
  2  (DBMS_XPLAN.display (NULL, NULL, 'BASIC +COST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 50753647

-----------------------------------------------------------
| Id  | Operation                   | Name   | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     5   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     5   (0)|
|   2 |   INDEX RANGE SCAN          | IDX_T1 |     1   (0)|
-----------------------------------------------------------

Notez que la création de l’index est intéressante car il fait chuter le COST du plan de 367 à 5.

Pour vous prouver que l’index n’existe pas réellement :

SQL> Select * from user_indexes where table_name='T1' ;

aucune ligne sélectionnée

L’index n’est pas référencé en tant qu’un index dans USER_INDEXES mais est bien défini en tant qu’objet:

SQL> select object_name from user_objects where object_name='IDX_T1';

OBJECT_NAME
-----------------
IDX_T1

Maintenant que j’ai validé que mon index est vraiment intéressant à créer je peux dropper mon index virtuel et créer un véritable index à la place.

ORA-01427 lors d'un update-select

Un développeur est venu me voir hier car l'update qu'il essayait d'exécuter lui retournait l'erreur suivante:

Erreur ORA-01427 single-row subquery returns more than one row

L'update en question était le suivant:

update refech e set daech=(
select distinct m.action_date 
from maturities m where m.ubix_contract_code=e.corex 
and m.cmech=e.cmech and m.caech=e.caech 
and m.action in ('LTD','XD')
) 
where e.corig='FOW';

Le développeur avait bien compris que l'erreur était dû au fait que la clause select dans l'update retournait plus d'une ligne pour un match dans la table REFECH mais n'arrivait pas à identifier ces doublons.
Voici la requête qui permet ici d'avoir les lignes dans la table MATURITIES qui retournent plus d'une ligne dans la jointure avec REFECH effectuée dans l'update:

select count(1), m.ubix_contract_code,e.corex, m.cmech,
e.cmech,m.caech,e.caech 
from maturities m, refech e 
where m.ubix_contract_code=e.corex and m.cmech=e.cmech 
and m.caech=e.caech and m.action in ('LTD','XD')
and e.corig='FOW' 
group by  m.ubix_contract_code, e.corex, m.cmech,
e.cmech,m.caech,e.caech 
having count(1)>1

Le principe ici consiste à joindre les 2 tables selon les critères utilisés dans l'update et de regrouper les lignes selon ces critères en n'affichant que les lignes ayant plus d'une occurrence.

Une fois les doublons récupérés il est facile de régler le problème (suppression des doublons, ajout d'une clause de jointure oubliée etc.)

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;