vendredi 27 mai 2011

Happy Birthday my blog

Au mois de mai de l'année dernière je décidai de me lancer dans la prose Oracle via ce blog.

A l'époque Grégory Guillou (auteur du blog ARKZOYD, le meilleur blog français à mon goût) m'avait donné comme conseil pour durer de d'abord faire ce blog pour moi même, et il avait raison. Ce blog je l'espère a eu un intérêt pour certains d'entre vous mais le premier à en bénéficier ça été moi. En effet, le fait d'écrire sur certains concepts ou sur des problématiques Oracle rencontrées me force à conceptualiser mes connaissances, à les structurer et surtout à les approfondir.

J'avoue éprouver un certain plaisir à écrire mes articles et j'espère pouvoir continuer sur cette voie car même si ça n'en a pas l'air comme ça, ça demande un certain investissement en termes de temps.

En tout cas l'heure est venu de faire un premier bilan statistique de ces 12 derniers mois:
- 26 articles publiés, ce qui fait plus de 2 par mois (c'est mieux que je ne le prévoyais au début)
- 2841 pages vues. Mais le plus important c'est que le nombre de pages lues ne cesse d'augmenter. Par exemple 800 pages ont été visitées sur le mois de mai contre 300 sur le mois de février.
- 29 personnes abonnées par mail (sans compter ceux abonnés par flux RSS)

Les articles les plus lues sont:

Forcer un plan d'exécution via un SQL profile => vu 284 fois
11g: ORA-28002 et problème d'expiration de mot de passe => vu 233 fois
NLS_LANG et problèmes de conversions de données => vu 189 fois
Bind Variable Peeking => vu 131 fois
Trace 10046 et TKPROF => vu 77 fois


A l'année prochaine je l'espère pour un nouveau bilan, et surtout n'hésitez pas à poster des commentaires pour enrichir ce blog.

lundi 16 mai 2011

Nouveauté 11g R2: Segment Deferred Creation

Si vous travaillez avec des bases 11g R2 il est possible que vous tombiez sur l’erreur suivante lors d’un export:
EXP-00003: no storage definition found for segment(0, 0)
. . exporting table                  T1

Le descriptif de l’erreur est plutôt clair : il indique qu’il n’existe pas de segment associé à la table T1.
En effet, si on requête la vue USER_SEGMENTS pour récupérer le segment associé à la table T1 on constate que ce segment n’existe pas :
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         0

SQL> select count(*) from user_tables where table_name='T1';
 
  COUNT(*)
----------
         1

Ceci est en fait dû à une nouvelle fonctionnalité de la 11g R2 appelée « Segment Deferred Creation ». En effet, jusqu’à la 11g R1 inclus lorsque qu’on créait une table vide, Oracle allouait automatiquement de l’espace (un extent). On avait donc un segment associé à cette table. Avec la 11g R2, Oracle par défaut ne crée plus de segment pour une table tant qu’on n’a pas inséré de lignes dedans ou qu’on n’ait pas demandé explicitement à Oracle de la faire via la commande ALLOCATE EXTENT :
SQL> alter table t1 allocate extent;
 
Table altered
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         1

Ce comportement par défaut d’Oracle en 11g R2 peut-être changé en touchant au paramètre d’instance DEFERRED_SEGMENT_CREATION. Par défaut celui-ci est à TRUE ce qui veut dire que la création du segment lors du CREATE TABLE n’est pas immédiate. Ce paramètre peut être modifiable au niveau de la session :
SQL> drop table t1 purge;
 
Table dropped
 
SQL> create table t1 (c1 NUMBER, C2 varchar2(50));
 
Table created
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         0
 
SQL> drop table t1 purge;
 
Table dropped
 
SQL> alter session set deferred_segment_creation=false;
 
Session altered
 
SQL> create table t1 (c1 NUMBER, C2 varchar2(50));
 
Table created
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         1

Dans cet exemple on voit que lorsque le paramètre DEFERRED_SEGMENT_CREATION est à TRUE le segment n’est pas crée automatiquement lors de la commande DDL. Par contre lorsque le paramètre est à FALSE on retrouve le comportement d’Oracle des versions précédentes c’est à dire que le segment est crée immédiatement.

Il est également possible de spécifier cette option directement au niveau de la commande DDL via la clause SEGMENT CREATION.
Par défaut la clause est SEGMENT CREATION DEFERRED mais on peut spécifier à la place SEGMENT CREATION IMMEDIATE :

SQL> drop table t1 purge;
 
Table dropped
 
SQL> alter session set deferred_segment_creation=TRUE;
 
Session altered
 
SQL> create table t1 (c1 NUMBER, C2 varchar2(50)) SEGMENT CREATION DEFERRED;
 
Table created
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         0
 
SQL> drop table t1 purge;
 
Table dropped
 
SQL> create table t1 (c1 NUMBER, C2 varchar2(50)) SEGMENT CREATION IMMEDIATE;
 
Table created
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         1

Si vous effectuez ces tests sur votre base et que vous obtenez l’erreur « ORA-14223: Deferred segment creation is not supported for this table », c’est surement que vous utilisez le user SYS ou SYSTEM. Or cette fonctionnalité ne s'applique pas sur ces schémas là.

Dans la vue USER_TABLES existe en 11g R2 une nouvelle colonne SEGMENT_CREATED qui indique si oui ou non le segment associé à la table est crée :
SQL> drop table t1 purge;
 
Table dropped

SQL> create table t1 (c1 NUMBER, C2 varchar2(50)) ;

Table created.

SQL> select count(*) from user_segments where segment_name='T1';

  COUNT(*)
----------
         0

SQL> select SEGMENT_CREATED  from user_tables where table_name='T1';

SEG
---
NO

SQL> alter table T1 allocate extent;

Table altered.

SQL> select SEGMENT_CREATED  from user_tables where table_name='T1';

SEG
---
YES


Sachez aussi qu’en 11.2.0.1 la fonctionnalite Segment Deferred Creation ne peut pas être appliqué aux tables partitionnées, mais qu’en 11.2.0.2 c’est désormais possible.

Si un jour en analysant les statistiques d’exécution d’un plan vous constatez que le nombre de lectures logiques pour un Full Table Scan est de 0 c’est surement que la table n’a pas de segment associé :
SQL> create table t1 (c1 NUMBER, C2 varchar2(50)) ;

Table created.

SQL> select * from t1;

no rows selected

SQL> set autotrace traceonly statistics
SQL> select * from t1;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        338  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> set autot off

Aucune lecture logique ni lecture physique n’a été effectué pour cette requête.
Maintenant si j’alloue de l’espace à la table T1 des lectures logiques seront effectuées (même si la table ne contient pas de lignes) :

SQL> alter table t1 allocate extent;

Table altered.

SQL> select * from t1;

no rows selected

SQL> set autotrace traceonly statistics
SQL> select * from t1;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        338  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> set autot off

Pour ceux qui auront remarqué que j’ai exécuté 2 fois la requête pour les 2 cas, c’est en fait juste pour ne prendre en compte que les I/O liés à l’exécution de la requête elle-même et non pas les éventuels I/O effectués par Oracle lors du parsing de la requête.

samedi 14 mai 2011

Récuperer le DDL d’une table avec DBMS_METADATA

Lorsque vous devez récupérer le script SQL de définition d’une table je suppose que la majorité d’entre vous utilisent des outils graphiques tels que TOAD ou SQL Developper.

Sachez toutefois que depuis la 9i il est possible de récupérer facilement ces scripts DDL et juste avec une fenêtre SQLPLUS grâce à la fonction GET_DDL du package DBMS_METADATA.

Voyons par exemple comment récupérer le DDL de la table CUSTOMERS du schéma SH d’Oracle :

SQL> SET LONG 50000
SQL> SET PAGES 500
SQL> SET LINES 200
SQL>  SELECT dbms_metadata.get_ddl('TABLE', table_name)
  2  from user_tables
  3  WHERE table_name='CUSTOMERS';
 
DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME)
--------------------------------------------------------------------------------
  CREATE TABLE "SH"."CUSTOMERS"
   (    "CUST_ID" NUMBER NOT NULL ENABLE,
        "CUST_FIRST_NAME" VARCHAR2(20) NOT NULL ENABLE,
        "CUST_LAST_NAME" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_GENDER" CHAR(1) NOT NULL ENABLE,
        "CUST_YEAR_OF_BIRTH" NUMBER(4,0) NOT NULL ENABLE,
        "CUST_MARITAL_STATUS" VARCHAR2(20),
        "CUST_STREET_ADDRESS" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_POSTAL_CODE" VARCHAR2(10) NOT NULL ENABLE,
        "CUST_CITY" VARCHAR2(30) NOT NULL ENABLE,
        "CUST_CITY_ID" NUMBER NOT NULL ENABLE,
        "CUST_STATE_PROVINCE" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_STATE_PROVINCE_ID" NUMBER NOT NULL ENABLE,
        "COUNTRY_ID" NUMBER NOT NULL ENABLE,
        "CUST_MAIN_PHONE_NUMBER" VARCHAR2(25) NOT NULL ENABLE,
        "CUST_INCOME_LEVEL" VARCHAR2(30),
        "CUST_CREDIT_LIMIT" NUMBER,
        "CUST_EMAIL" VARCHAR2(30),
        "CUST_TOTAL" VARCHAR2(14) NOT NULL ENABLE,
        "CUST_TOTAL_ID" NUMBER NOT NULL ENABLE,
        "CUST_SRC_ID" NUMBER,
        "CUST_EFF_FROM" DATE,
        "CUST_EFF_TO" DATE,
        "CUST_VALID" VARCHAR2(1),
         CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE,
         CONSTRAINT "CUSTOMERS_COUNTRY_FK" FOREIGN KEY ("COUNTRY_ID")
          REFERENCES "SH"."COUNTRIES" ("COUNTRY_ID") ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"

Pour récupérer le script de création de l’index CUSTOMERS_GENDER_BIX on utilise la même logique :
SQL> SELECT dbms_metadata.get_ddl('INDEX', index_name)
  2  from user_indexes
  3  WHERE index_name='CUSTOMERS_GENDER_BIX';

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
-------------------------------------------------------------------------------
 CREATE BITMAP INDEX "SH"."CUSTOMERS_GENDER_BIX" ON "SH"."CUSTOMERS" ("CUST_GEN

DER")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"

En utilisant un sympathique petit SPOOL il vous est possible de créer un script qui va générer par exemple l’ensemble des DDL des objets d’un schéma donné.

Le package DBMS_METADATA fournit bien d’autres procédures et fonctions que je vous invite à découvrir dans la doc Oracle Database Database PL/SQL Packages and Types Reference

mardi 10 mai 2011

FOREIGN KEY et ORA-00054

Un développeur m’a demandé de l’aide aujourd’hui car il ne comprenait pas pourquoi il obtenait l’erreur ORA-00054 alors qu’il souhaitait simplement créer une nouvelle table:
SQL> CREATE TABLE ETF_TRACKING_ERROR_POINT
  2  (
  3    PTR_ID                   NUMBER                NOT NULL,
  4    PTR_FUNDSHARE            NUMBER(10)            NOT NULL,
  5    PTR_DATE                 DATE                  NOT NULL,
  6    PTR_INDEXID              NUMBER(10)            NOT NULL,
  7    PTR_FUNDSHARENAV         FLOAT(126)            NOT NULL,
  8    PTR_INDEXVALUE           FLOAT(126)            NOT NULL,
  9    PTR_BENCHMARKVALUE       FLOAT(126)            NOT NULL,
 10    PTR_INDEXDIV             FLOAT(126)            NOT NULL,
 11    PTR_SPOT                 FLOAT(126)            NOT NULL,
 12    PTR_CALCVALUE            FLOAT(126)            NOT NULL,
 13    PTR_USEDVALUE            FLOAT(126)            NOT NULL,
 14    PTR_TOBEUSEDFLAG         NUMBER(1)   DEFAULT 1 NOT NULL,
 15    PTR_USER                 VARCHAR2(200),
 16    PTR_COMMENT              VARCHAR2(250),
 17    CONSTRAINT PK_ETFTERRORPOINT PRIMARY KEY (PTR_ID),
 18    CONSTRAINT UK_ETFTERRORPOINT UNIQUE (PTR_FUNDSHARE, PTR_DATE) using index tablespace INDXLNS_M01,
 19    CONSTRAINT FK_ETFTERRORPOINT_OBJECT Foreign KEY (PTR_ID) References MUT_OBJECTS (OBJ_ID)
 20  );
 
CREATE TABLE ETF_TRACKING_ERROR_POINT
(
  PTR_ID                   NUMBER                NOT NULL,
  PTR_FUNDSHARE            NUMBER(10)            NOT NULL,
  PTR_DATE                 DATE                  NOT NULL,
  PTR_INDEXID              NUMBER(10)            NOT NULL,
  PTR_FUNDSHARENAV         FLOAT(126)            NOT NULL,
  PTR_INDEXVALUE           FLOAT(126)            NOT NULL,
  PTR_BENCHMARKVALUE       FLOAT(126)            NOT NULL,
  PTR_INDEXDIV             FLOAT(126)            NOT NULL,
  PTR_SPOT                 FLOAT(126)            NOT NULL,
  PTR_CALCVALUE            FLOAT(126)            NOT NULL,
  PTR_USEDVALUE            FLOAT(126)            NOT NULL,
  PTR_TOBEUSEDFLAG         NUMBER(1)   DEFAULT 1 NOT NULL,
  PTR_USER                 VARCHAR2(200),
  PTR_COMMENT              VARCHAR2(250),
  CONSTRAINT PK_ETFTERRORPOINT PRIMARY KEY (PTR_ID),
  CONSTRAINT UK_ETFTERRORPOINT UNIQUE (PTR_FUNDSHARE, PTR_DATE) using index tablespace INDXLNS_M01,
  CONSTRAINT FK_ETFTERRORPOINT_OBJECT Foreign KEY (PTR_ID) References MUT_OBJECTS (OBJ_ID)
)
 
ORA-00054: resource busy and acquire with NOWAIT specified


Le problème ici était forcément dû à une des 2 FK que le développeur souhaitait créer.
En testant séparément la création des 2 FK, j’ai pu conclure que le problème était lié à la table MUT_OBJECTS et que celle-ci devait surement être lockée par une autre session.

Voyons comment résoudre ce problème avec un exemple.

Test Case:

Session 1 :
SQL> create table t1 as select * from user_objects;

Table created.

SQL> alter table t1 add constraint pk_t1 primary key (object_id);

Table altered.

SQL> update t1 set STATUS='OK';

30755 rows updated.

La table T1 est donc lockée par la session 1.


Session 2:
SQL> create table t2 as select * from user_objects where rownum<=10;

Table created.

SQL> alter table t2 add constraint fk_t1 foreign key (object_id) references t1;
alter table t2 add constraint fk_t1 foreign key (object_id) references t1
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Je reproduis donc le même problème que mon développeur aujourd’hui.
Dans mon exemple je sais qu’on a un lock sur T1 dans la session 1 puisque c’est moi qui l’ait généré, mais comment peut-on trouver « Oraclement parlant » la session qui lock la table ?

La solution que j’ai utilisé consiste à requêter la vue v$LOCKED_OBJECT grâce à l’OBEJCT_ID que j’ai récupéré dans USER_OBJECTS. La vue v$LOCKED_OBJECT me donne la liste des locks de type DML en cours et notamment la session bloquante :

SQL> select object_id from user_objects where object_name='T1';

 OBJECT_ID
----------
     70929

SQL> select SESSION_ID from v$locked_object where object_id=70929;

SESSION_ID
----------
        17

Le coupable est donc la session 17.
On peut ensuite requêter la vue V$SESSION pour avoir plus d’informations sur celui ou celle qui lock la table :
SQL> select OSUSER,machine,PROGRAM from v$session where sid=17;

OSUSER                         MACHINE                                                          PROGRAM
------------------------------ ---------------------------------------------------------------- --------------------------------------
EUR\aaangour012711             EUR\FR9124662D                                                   sqlplus.exe

J’ai ainsi le nom du coupable (moi), le nom de la machine cliente et le programme utilisé.
Je peux ensuite prendre mon téléphone et appeler le LOCKEUR pour lui dire qu’il a intérêt à committer ou rollbacker sa transaction sur le champ sinon je lui kill sa session. Bien sûr je ne lui dis pas ça comme ça, mais ça traduit en tout cas ma pensée ;-).

Et effectivement en committant la transaction de la session 1 je peux créer la FK sur la table T2 :

Session 1 :
SQL> commit;

Commit complete.

Session 2 :
SQL> alter table t2 add constraint fk_t1 foreign key (object_id) references t1;

Table altered.


Voilà comment en utilisant les vues dynamiques de performance qu’Oracle met à notre disposition vous pouvez régler des problèmes Oracle de tous les jours.
N’hésitez pas à faire des DESCRIBE sur ces vues pour voir les infos disponibles.
La doc Oracle Database Reference est là pour vous aider à y voir plus claire.