jeudi 28 février 2013

ORA-12899 et character set

J'ai obtenu aujourd'hui l'erreur suivante en tentant de mettre à jour une table à partir d'une autre table située sur une base distante (via un dblink):
SQL> insert /*+ append */ into LNS$OWNER.MUT_ERROR_LOG_FILE
 2 select * from MUT_ERROR_LOG_FILE_SAV@LNS_LNSD12;
select * from MUT_ERROR_LOG_FILE_SAV@LNS_LNSD12
 *
ERROR at line 2:
ORA-12899: value too large for column
"LNS$OWNER"."MUT_ERROR_LOG_FILE"."ERR_COMMENT" (actual: 242, maximum: 240)

L'erreur précise que je souhaite insérer une chaine de caractères d'une taille de 242 (on verra après à quoi correspond ce nombre) alors que mon champ est défini par un VARCHAR2(240). Et pourtant la table source et la table cible ont étés crées à partir du même code SQL:
create table MUT_ERROR_LOG_FILE
(
  ERR_ID              NUMBER(10) not null,
  ERR_CLASSNAME       VARCHAR2(150) not null,
  ERR_USUALNAME       VARCHAR2(200) not null,
  ERR_MUTEX_ID        NUMBER(10),
  ERR_USER_ID         NUMBER(10),
  ERR_POST_DATE       DATE,
  ERR_DATE            DATE,
  ERR_OWNER_ERROR_ID  NUMBER(10) not null,
  ERR_OWNER_ENTITY_ID NUMBER(10),
  ERR_ENTITY_ID       NUMBER(10),
  ERR_LEVEL           NUMBER(10),
  ERR_COMMENT         VARCHAR2(240),
  ERR_AUTO_SOLUTION   NUMBER(1),
  ERR_LINKEDEVENT_ID  NUMBER(10),
  ERR_WARNING_DATE    DATE
)

Comment se fait-il alors que mon INSERT plante si les 2 champs sont en VARCHAR2(240)?
La cause est à chercher du côté du jeu de caractères défini pour chacune des 2 bases.
Ma base source utilise un character set single-byte(WE8DEC) alors que ma base cible utilise un character set multi-bytes, en l'occurence AL32UTF8.
Ce qu'il faut savoir c'est qu'en single-byte, 1 caractère correspond toujours à un seul octect. Par contre en mult-byte un caractère peut être défini sur plusieurs octects (1 à 4 bytes pour UTF8).
Illustrons cela avec un exemple.
Dans ma base en single-byte je crée une table avec une colonne de type VARCHAR2(10):
SQL> create table t1 (c1 varchar2(10));
 
Table created
 
SQL> insert into t1 values ('à');
 
1 row inserted
 
SQL> select dump(c1) from t1;
 
DUMP(C1)
--------------------------------------------------------------------------------
Typ=1 Len=1: 224

J'ai inséré dans le champ C1 la valeur 'à' et le résultat de la fonction dump indique bien que la taille stockée pour ce caractère est de 1 octect (Len=1).
Faisons la même chose sur la base définie avec un jeu de caractères multi-bytes:
 SQL> create table t1 (c1 varchar2(10));
 
Table created
 
SQL> insert into t1 values ('à');
 
1 row inserted
 
SQL> select dump(c1) from t1;
 
DUMP(C1)
--------------------------------------------------------------------------------
Typ=1 Len=2: 195,160 

 Cette fois ci le même caractère est stocké sur 2 octects (Len=2).

Revenons maintenant au nombre 240 déterminant la taille de ma colonne ERR_COMMENT.
Le type VARCHAR2 peut être défini selon 2 types de taille: BYTES ou CHAR. Avec un jeu de caractères single_byte un caractère vaut forcément un BYTE. Par contre avec un characterset multibytes un caractère peut être stocké sur plusieurs bytes.
Par défaut si on ne précise pas le type de taille c'est celle définie par le paramètre NLS_LENGTH_SEMANTICS qui est utilisée.
Sur mes 2 bases ce paramètre était défini à BYTE.
Donc dans ma table source j'avais des lignes dont la colonne ERR_COMMENT stockait jusqu'à 240 BYTES soit donc 240 caractères puisqu'elle utilise un jeu de caractères single-byte.
Par contre ces 240 caractères vont consommer plus que 240 BYTES sur ma base cible puisqu'elle utilise un jeu de caractères multi-bytes.
Comme ma colonne est définie avec une taille max de 240 BYTES (puisque NLS_LENGTH_SEMANTICS=BYTE) elle ne peut stocker tous les caractères que ma table source contenait d'où mon erreur ORA-12899: value too large for column.
La solution consiste à créer la table en précisant le type de taille CHAR pour indiquer qu'on souhaite stocker 240 caractères maximum et non 240 Bytes:
create table MUT_ERROR_LOG_FILE
(
  ERR_ID              NUMBER(10) not null,
  ERR_CLASSNAME       VARCHAR2(150 CHAR) not null,
  ERR_USUALNAME       VARCHAR2(200 CHAR) not null,
  ERR_MUTEX_ID        NUMBER(10),
  ERR_USER_ID         NUMBER(10),
  ERR_POST_DATE       DATE,
  ERR_DATE            DATE,
  ERR_OWNER_ERROR_ID  NUMBER(10) not null,
  ERR_OWNER_ENTITY_ID NUMBER(10),
  ERR_ENTITY_ID       NUMBER(10),
  ERR_LEVEL           NUMBER(10),
  ERR_COMMENT         VARCHAR2(240 CHAR),
  ERR_AUTO_SOLUTION   NUMBER(1),
  ERR_LINKEDEVENT_ID  NUMBER(10),
  ERR_WARNING_DATE    DATE
)

CONCLUSION:

La morale de cette histoire c'est que lorsque vous utilisez des bases avec un jeu de caractères multi-bytes il vaut mieux préciser (pour les colonnes de type VARCHAR2) une taille de type CHAR plutôt que de type BYTE.
Pour ce faire vous pouvez soit définir le paramètre NLS_LENGTH_SEMANTICS à CHAR ou bien ajouter la clause CHAR lorsque vous définissez vos colonnes de type VARCHAR2.

dimanche 24 février 2013

Astuce: personnaliser son prompt Linux et SQL Plus

Lorsqu'on travaille sur les bases Oracle nous avons généralement plusieurs terminaux Linux ouverts et connectés sur différentes bases. On peut avoir ainsi à la fois des fenêtres connectées sur des bases de PROD et d'autres sur des bases DEV.

Pour éviter la confusion entre ces différentes connexions j'aime modifier mon prompt pour y afficher notamment le nom de l'instance sur laquelle pointe mon $ORACLE_SID. En ouvrant mon terminal je suis sûr de savoir sur quelle base je pointe.

Pour pouvoir modifier l'affichage du prompt il suffit de modifier la variable système $PS1 en ajoutant des variables BASH et/ou des variables d'environnement telles que $ORACLE_SID.

Personnellement je définie ma variable $PS1 de la manière suivante:
PS1='[\u@\W $ORACLE_SID]$ '

J'obtiens ainsi le prompt suivant:
[oracle@oradata orcl1]$ 

La variable BASH \u indique le username Linux, la variable \W indique le répertoire courant. Si je me connecte en SYSDBA je sais d'avance que je pointerai sur l'instance ORCL1.

Bien sûr il est préférable de définir cette variable dans votre fichier .bash_profile pour que votre prompt soit défini automatiquement.

Vous pouvez personnaliser votre prompt à votre guise en choisissant parmi les variables BASH dont la liste se trouve dans le manuel BASH (man bash):
PROMPTING
       When executing interactively, bash displays the primary prompt PS1 when it
       is ready to read a command, and the secondary prompt  PS2  when  it  needs
       more  input to complete a command.  Bash allows these prompt strings to be
       customized by inserting a number of backslash-escaped  special  characters
       that are decoded as follows:
              \a     an ASCII bell character (07)
              \d     the date in "Weekday Month Date" format (e.g., "Tue May 26")
              \D{format}
                     the format is  passed  to  strftime(3)  and  the  result  is
                     inserted  into the prompt string; an empty format results in
                     a  locale-specific  time  representation.   The  braces  are
                     required
              \e     an ASCII escape character (033)
              \h     the hostname up to the first ‘.’
              \H     the hostname
              \j     the number of jobs currently managed by the shell
              \l     the basename of the shell’s terminal device name
              \n     newline
              \r     carriage return
              \s     the  name of the shell, the basename of $0 (the portion fol-
                     lowing the final slash)
              \t     the current time in 24-hour HH:MM:SS format
              \T     the current time in 12-hour HH:MM:SS format
              \@     the current time in 12-hour am/pm format
              \A     the current time in 24-hour HH:MM format
              \u     the username of the current user
              \v     the version of bash (e.g., 2.00)
              \V     the release of bash, version + patch level (e.g., 2.00.0)
              \w     the current working directory, with $HOME abbreviated with a
                     tilde (uses the value of the PROMPT_DIRTRIM variable)
              \W     the  basename  of  the current working directory, with $HOME
                     abbreviated with a tilde
              \!     the history number of this command
              \#     the command number of this command
              \$     if the effective UID is 0, a #, otherwise a $
          \nnn   the character corresponding to the octal number nnn
              \\     a backslash
              \[     begin a sequence of non-printing characters, which could  be
                     used to embed a terminal control sequence into the prompt
              \]     end a sequence of non-printing characters
En plus de personnaliser le prompt du terminal linux vous pouvez modifier le prompt SQL Plus en affichant par exemple le nom de l'instance.
Pour ce faire il suffit de modifier la variable SQL Plus sqlprompt de la manière suivante:
SQL> SET sqlprompt &_CONNECT_IDENTIFIER>
orcl1>
En ajoutant cette commande dans le fichier glogin.sql (qui se trouve dans $ORACLE_HOME/sqlplus/admin) la personnalisation du prompt se fera automatiquement à chaque connexion SQL Plus.

jeudi 21 février 2013

NO_INVALIDATE

Cet article est une réponse à Yahya Mezroui avec lequel j'ai fait un pari aujourd'hui. Je discutait avec lui sur le calcul de stats de tables et la possibilité d'invalider les curseurs liés à cette table présents dans la shared pool en jouant sur le paramètre NO_INVALIDATE de la procédure DBMS_STATS.GATHER_TABLE_STATS. Pour moi, le fait de mettre NO_INVALIDATE=FALSE invalidait les curseurs correspondant aux requêtes référençant la table pour laquelle on effectue un calcul de stats. Pour lui, et ce depuis la 11g, Oracle n'invaliderait plus les curseurs même si on précisait NO_INVALIDATE=FALSE.

Voici donc un petit test case (effectué sur une base 11.2.0.1) prouvant que j'avais bien raison.
J'ouvre une première session et je crée une table T1
-- Session 1
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table T1 as select * from dba_objects;

Table created.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SQL> select sid from v$mystat where rownum=1;

       SID
----------
    33
Ma session correspond au SID 33.

Dans une 2ème session je regarde les statistiques « parse count (hard) » et « parse count (total) » pour ma première session (SID 33) :
 SELECT sn.name, ss.value FROM v$statname sn, v$sesstat ss 
WHERE sn.statistic# = ss.statistic#
AND sn.name IN ('parse count (hard)','parse count (total)')
AND ss.sid =33;

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1731
parse count (hard)                            377
Je retourne ensuite sur ma première session et j'effectue une requête toute simple sur T1:
 -- Session 1
SQL> select object_id from t1 where object_id= 2;

 OBJECT_ID
----------
     2
Je regarde maintenant les stats sur mon autre session et je constate qu’un Hard Parse a bien été effectué (la statistique « parse count (hard) » a été incrémenté de 1) :
 -- Session 2
SQL> /

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1736
parse count (hard)                            378
J'exécute de nouveau la requête dans ma session 1 puis dans ma session 2 je check de nouveau les stats de parsing:
-- Session 1
SQL> select object_id from t1 where object_id= 2;

 OBJECT_ID
----------
     2

-- Session 2
SQL> /

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1737
parse count (hard)                            378
Cette fois la statistique « parse count (total) » a été incrémentée mais pas la stats « parse count (hard) », il s’agit donc d’un soft parse. C’est normal puisque un curseur partageable existait déjà dans la library cache depuis que la requête a été exécutée la 1ère fois.

Maintenant je lance un calcul de stats sur T1 (depuis une 3ème session pour éviter d'impacter les stats de parsing de ma session 1) mais en indiquant à Oracle que je ne souhaite pas invalider les curseurs (NO_INVALIDATE=TRUE):
-- Session 3
-- Recalcul de stats avec non invalidation du curseur (no_invalidate=TRUE)
SQL> exec dbms_stats.gather_table_stats(user,'T1',no_invalidate=>TRUE);

PL/SQL procedure successfully completed.
Si j'exécute de nouveau ma requête je constate que le « parse count (hard) » n'a pas été incrémenté:
 -- Session 1
SQL> select object_id from t1 where object_id= 2;

 OBJECT_ID
----------
     2

-- Session 2
SQL> /

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1738
parse count (hard)                            378
C'est donc bien que le NO_INVALIDATE=TRUE n'a pas invalidé le curseur.

Calculons maintenant les stats en forçant l'invalidation du curseur c'est à dire en mettant le paramètre NO_INVALIDATE à FALSE:
-- Session 3
-- Recalcul de stats avec  invalidation du curseur (no_invalidate=FALSE)
SQL> exec dbms_stats.gather_table_stats(user,'T1',no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

-- Session 1
SQL> select object_id from t1 where object_id= 2;

 OBJECT_ID
----------
     2

-- Session 2
SQL> /

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse count (total)                               1743
parse count (hard)                            379

Cette fois ci le hard parse a bien eu lieu puisque la statistique « parse count (hard) » a été incrémenté c'est donc bien que le curseur présent dans ma shared pool a bien été invalidé par le calcul de stats effectué dans ma session 3.

Donc Yahya tu me dois bien un resto demain ;-)

Je le tacle gentiment ici mais je viens de passer une semaine avec lui en formation SQL Server (oui oui j'ai bien dit SQL Server) et il m'a appris beaucoup de choses sur Oracle rien qu'en discutant. C'est quelqu'un d'extrêmement compétent avec une connaissance très large des technos Oracle. Nos amis de la BNP ont bien de la chance de l'avoir dans leur équipe de DBAs. Ca n'empêche pas que demain il va me payer le resto...
























































































































































































































dimanche 3 février 2013

Naviguer dans l'historique de SQLplus sous Linux

Si vous utilisez SQL Plus sous DOS vous devez surement apprécier  de pouvoir naviguer dans l'historique des commandes exécutées précédemment  en jouant avec les flèches HAUT et BAS.

Si vous utilisez par contre SQL Plus sous Linux vous serez déçu de voir qu'en tapant sur les flèches HAUT et BAS vous ne pourrez pas faire défiler les commandes précédemment exécutées. Vous aurez en retour des caractères bizarres du genre:
SQL> ^[[A^[[B  

Heureusement sous Linux il existe toujours une solution.
Celle-ci consiste ici à récupérer le package RLWRAP puis à l'installer. Vous pouvez télécharger le package correspondant à votre version de Linux ici.
Voici un exemple d'istallation pour mon Oracle Linux 6:
 [oracle@ahmed-ol6 ~]$ su root
Password: 
[root@ahmed-ol6 oracle]# cd /media/sf_Softs/
[root@ahmed-ol6 sf_Softs]# ls rlwrap*
rlwrap-0.37-1.el6.i686.rpm
[root@ahmed-ol6 sf_Softs]# rpm -ivh rlwrap-0.37-1.el6.i686.rpm
warning: rlwrap-0.37-1.el6.i686.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                ########################################### [100%]
   1:rlwrap                 ########################################### [100%]

Maintenant que le package a été installé je me connecte à ma base via SQL Plus en précédent SQLPLUS par RLWRAP:
 [oracle@ahmed-ol6 ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 3 11:58:21 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> sho parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl1

Si j'appuie sur la flèche du haut je vois apparaitre la dernière commande exécutée:
SQL> sho parameter db_name
Pour que RLWRAP soit automatiquement pris en compte lorsqu'on lance SQL Plus il suffit d'ajouter l'alias suivant dans le fichier .bash_profile:
alias sqlplus='rlwrap sqlplus'