Introduction
Un problème agaçant avec Oracle est que les informations recherchées sont parfois difficiles à trouver voir même sont incomplètes. Soit ces infos sont dispersées dans plusieurs vues sans point d'entrée unique, soit des paramètres de niveau base sont écrasés par des paramètres de niveau local soit les infos ne sont pas où le DBA junior pensent qu'elles devraient être.

Oracle est devenu au fil des versions de plus en plus complexe et des règles d'anciennes versions sont obsolètes pour les nouvelles versions, comme par exemple que le DESC permet de voir les colonnes NOT NULL.


 
Points d'attention
N/A.
 



Base de tests
N'importe quelle base Oracle.
 



Exemples
============================================================================================
Une contrainte UNIQUE peut se poser sur la colonne ou sur l'index

============================================================================================

Dans une table il est possible de définir une contrainte UNIQUE au niveau de la colonne pour empêcher la saisie de deux valeurs identiques (à l'exception du NULL). Cette contrainte est visible, non pas en faisant un DESC comme pour les contraintes NOT NULL (du moins certaines :-) ), mais en regardant dans les vues XXX_CONSTRAINTS.
          SQL> create table TEST_UNIQ (ID NUMBER(2) UNIQUE)

          Table created.

          SQL> desc TEST_UNIQ
          Name           Null?    Type
          -----------------------------------------
          ID                            NUMBER(2)

          SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from dba_constraints where table_name = 'TEST_UNIQ' AND OWNER = 'HR';
          CONSTRAINT_NAME      C      STATUS
          -------------------- - ------------------------------
          SYS_C0011856               U      ENABLED

          SQL> insert into TEST_UNIQ values (1);
          1 row created.

          SQL> /
          insert into TEST_UNIQ values (1)
          *
          ERROR at line 1:
          ORA-00001: unique constraint (HR.SYS_C0011856) violated

On crée maintenant une deuxième table, sans contrainte unique et, une fois celle-ci créée, on ajoute un index UNIQUE. Dans les vues XXX_CONSTRAINTS cette table n'a toujours pas de contrainte UNIQUE, néanmoins elle réagit de la même façon que la table précédente car l'index sur cette colonne interdit la saisie de deux valeurs identiques. Vous noterez que le message d'erreur est le même dans les deux cas : "ORA-00001: unique constraint () violated", ce qui montre que la vérification de l'unicité d'une valeur se fait en parcourant l'index.
          SQL> create table TEST_UNIQ_IDX (ID NUMBER(2));
          Table created.

          SQL> create UNIQUE index IDX_UNIQ on TEST_UNIQ_IDX(ID);
          Index created.

          SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, STATUS from dba_constraints where table_name = 'TEST_UNIQ_IDX' and OWNER = 'HR';
          no rows selected

          SQL> select INDEX_NAME, INDEX_TYPE, UNIQUENESS from user_indexes where TABLE_NAME = 'TEST_UNIQ_IDX';
          INDEX_NAME      INDEX_TYPE         UNIQUENES
          ---------- --------------------------- -----------------------------------
          IDX_UNIQ            NORMAL               UNIQUE

          SQL> insert into TEST_UNIQ_IDX values (1);
          1 row created.

          SQL> /
          insert into TEST_UNIQ_IDX values (1)
          *
          ERROR at line 1:
          ORA-00001: unique constraint (HR.IDX_UNIQ) violated

Oracle permet de définir des contraintes d'intégrité UNIQUE de deux façons : au niveau de la colonne ou au niveau de l'index. Et, in fine, le problème pour le DBA junior ou le développeur souhaitant lister les contraintes d'intégrité, est qu'il ne peut plus se limiter à un SELECT dans les vues XXX_CONSTRAINTS mais doit obligatoirement faire un UNION avec les vues XXX_INDEXES pour afficher les contraintes UNIQUES définies au niveau des indexes.
          SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS from user_constraints where table_name = 'TEST_UNIQ_IDX'
          UNION
          select INDEX_NAME, INDEX_TYPE, UNIQUENESS from user_indexes where TABLE_NAME = 'TEST_UNIQ_IDX';
          CONSTRAINT_NAME      CONSTRAINT_TYPE         STATUS
          -------------------- --------------------------- --------- ---------------------------------
          IDX_UNIQ                        NORMAL                         UNIQUE

OK, OK, on est d'accord pour dire que stricto sensu créer un index UNIQUE ne crée pas une contrainte d'intégrité sur la colonne puisque celle-ci n'est pas dans XXX_CONSTRAINTS. Mais, comme le résultat est identique, à savoir impossibilité de créer deux fois la même valeur, on peut considèrer que c'est l'équivalent d'une contrainte d'intégrité.

============================================================================================
Taille d'un bloc de données : paramètre DB_BLOCK_SIZE ou colonne du tablespace

============================================================================================

Le paramètre DB_BLOCK_SIZE est le paramètre donnant la taille par défaut du bloc de données. Depuis plusieurs versions d'Oracle ce paramètre peut être écrasé au niveau du tablespace, exceptions faites des tablespaces SYSTEM et SYSAUX qui eux utilisent DB_BLOCK_SIZE.
          SQL> show parameter block_size
          NAME               TYPE     VALUE
          --------------------------------------------------
          db_block_size    integer     8192

          SQL> select TABLESPACE_NAME, BLOCK_SIZE from dba_tablespaces order by 1;
          TABLESPACE_NAME                    BLOCK_SIZE
          ------------------------------ ---------- -------------------------------
          APEX_1991375173370654            8192
          APEX_1993195660370985            8192
          SYSAUX                             8192
          SYSTEM                             8192
          TEMP                             8192
          UNDOTBS1                         8192
          USERS                             8192
          7 rows selected.

Nous allons créer un tablespace avec une taille de bloc différent du paramètre par défaut. Avant cela, il faut une zone mémoire dimensionnée pour accepter les blocs de taille non standard de ce tablespace sinon le CREATE TABLESPACE échoue.
          SQL> create tablespace "TEST_4K" DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/users_4K.dbf' SIZE 10M blocksize 4k EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
               *
          ERROR at line 1:
          ORA-29339: tablespace block size 4096 does not match configured block sizes

          SQL> show parameter _k_cache
          NAME                              TYPE             VALUE
          ------------------------------------ ----------- ------------------------------
          db_2k_cache_size             big integer         0
          db_4k_cache_size             big integer         0
          db_8k_cache_size             big integer         0
          db_16k_cache_size             big integer       0
          db_32k_cache_size             big integer       0

Cette opération de configuration de la SGA se fait dans le CDB$ROOT si on est en Oracle 12c architecture multi-tenant.
          SQL> alter system set db_4k_cache_size = 16m;
          alter system set db_4k_cache_size = 16m
          *
          ERROR at line 1:
          ORA-65040: operation not allowed from within a pluggable database

          SQL> connect SYS@orcl12c as sysdba
          Enter password:
          Connected.

          SQL> show con_name
          CON_NAME
          ------------------------------
          CDB$ROOT

          SQL> alter system set db_4k_cache_size = 16m;
          System altered.

          SQL> show parameter _k_cache
          NAME                               TYPE              VALUE
          ------------------------------------ ----------- ------------------------------
          db_2k_cache_size             big integer          0
          db_4k_cache_size             big integer     16M
          db_8k_cache_size             big integer          0
          db_16k_cache_size             big integer        0
          db_32k_cache_size             big integer        0

On se reconnecte à la PDB et on crée le tablespace.
          SQL> connect HR/HR@orcl
          Connected.

          SQL> create tablespace "TEST_4K" DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/users_4K.dbf' SIZE 10M blocksize 4k EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
          Tablespace created.

On voit bien que dans DBA_TABLESPACES la taille du bloc du nouveau tablespace a une taille différente de celui des autres tablespaces.
          SQL> select TABLESPACE_NAME, BLOCK_SIZE from dba_tablespaces order by 1;
          TABLESPACE_NAME            BLOCK_SIZE
          ----------------------------------------------------- ----------
          APEX_1991375173370654     8192
          APEX_1993195660370985     8192
          SYSAUX                     8192
          SYSTEM                     8192
          TEMP                     8192
          TEST_4K                  4096
          UNDOTBS1                 8192
          USERS                     8192
          8 rows selected.

Qu'est-ce que cela implique pour le DBA? Hé bien, que si vous utilisez un script calculant la taille de table ou d'index en utilisant le paramètre DB_BLOCK_SIZE, ce script sera faux pour les objets d'un tablespace créé avec une taille de bloc différente de DB_BLOCK_SIZE. On peut même dire que dans les dernières versions d'Oracle, le paramètre DB_BLOCK_SIZE est utilisé lors de la création de la base mais qu'ensuite c'est le champ BLOCK_SIZE de la table DBA_TABLESPACES qui fait foi.

Testons cela avec une table de 999 éléments.

          SQL> insert into TABLE_4K SELECT 'TEST' FROM dual connect by level < 1000;
          999 rows created.

          SQL> commit;
          Commit complete.

La taille de la table (327 680 octets) correspond bien à 80 blocs de 4 096 octets et non pas à 80 blocs de taille DB_BLOCK_SIZE pour notre base.
          SQL> select BYTES, BLOCKS from dba_segments where SEGMENT_NAME = 'TABLE_4K';
          BYTES     BLOCKS
          ---------- ----------
          327680       80

Vérification de la taille avec les valeurs en dur.
          SQL> select 80 * 4096 from dual;
          80*4096
          ----------
          327680

Vérification avec les champs de la table et du tablespace.
          SQL> select (select blocks from dba_segments where SEGMENT_NAME = 'TABLE_4K') * (SELECT BLOCK_SIZE from dba_tablespaces where tablespace_name = 'TEST_4K') AS "TAILLE TABLE"  from dual;
          TAILLE TABLE
          ------------
          327680


============================================================================================
Contrainte d'intégrité NOT NULL invisible avec le DESC et active que pour les nouveaux enregistrements

============================================================================================

Une contrainte d'intégrité NOT NULL ne signifie pas que tous les enregistrements de la table aient une valeur NOT NULL pour cette colonne! Etonnant, non? Et, plus fort, un certain type de contrainte NOT NULL n'apparait pas avec DESC alors que beaucoup de personnes utilisent cette technique pour ne pas faire de SELECT dans les vues XXX_CONSTRAINTS.

Pour rappel, Oracle a introduit les contraintes d'intégrité avec sa version 7 en 1992, soit il y a 25 ans; depuis ce temps, celles-ci se sont enrichies de beaucoup de fonctionnalités, donc certaines assez déroutantes.

La table ZZ02 a deux colonnes, dont une seule est NOT NULL.
          SQL> desc ZZ02;     
          Name                     Null?              Type
          --------------------------------------- ---------------------------------
          TABLE_NAME        NOT NULL     VARCHAR2(128)
          TABLE_TYPE                              VARCHAR2(11)

          SQL> select * from zz02;
          no rows selected

          SQL> insert into ZZ02 values ('TEST01', 'TABLE');
          1 row created.

          SQL> insert into ZZ02 values ('TEST02', 'VIEW');
          1 row created.

          SQL> insert into ZZ02 values ('TEST03', NULL);
          1 row created.

          SQL> commit;
          Commit complete.

          SQL> select * from zz02 order by 1;
          TABLE_NAME           TABLE_TYPE
          ---------------------------------------- -----------
          TEST01                     TABLE
          TEST02                     VIEW
          TEST03

Il est désormais possible avec l'option NOVALIDATE de créer une contrainte d'intégrité active uniquement pour les nouveaux enregistrements. L'alter table passe sans problème alors qu'on a pourtant un enregistrement dans la table avec le champ TABLE_TYPE à NULL.
          SQL> alter table ZZ02 modify TABLE_TYPE NOT NULL NOVALIDATE
          Table altered.

Vous noterez que la nouvelle contrainte NOT NULL avec l'option NOVALIDATE n'apparait pas dans le DESC de la table! Et pourtant, au niveau du dictionnaire de données, les deux contraintes NOT NULL existent bel et bien et ne différent que par la colonne VALIDATED.
          SQL> desc ZZ02
          Name                               Null?                   Type
          -------------------------------- -------- -------------------------------------------------
          TABLE_NAME                  NOT NULL           VARCHAR2(128)
          TABLE_TYPE                                              VARCHAR2(11)

          SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION,  STATUS,  VALIDATED, LAST_CHANGE FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'ZZ02';
          CONSTRAINT_NAME     CONSTRAINT_TYPE        SEARCH_CONDITION                 STATUS          VALIDATED          LAST_CHANGE
          --------------- ---------------     ------------------------    -------     -------------   ------------ ------------------ ----------------------------- ------
          SYS_C0011766              C                                    "TABLE_NAME" IS NOT NULL    ENABLED       VALIDATED          08-SEP-17        
          SYS_C0011855              C                                    "TABLE_TYPE" IS NOT NULL     ENABLED       NOT VALIDATED  14-SEP-17       
 

          SQL> insert into ZZ02 values ('TEST_NOT_NULL', 'IOT');
          1 row created.

La contrainte d'intégrité NOT NULL NOVALIDATE est active pour les nouveaux enregistrements et le code erreur associé est le même que celui de la contrainte NOT NULL classique.
          SQL> insert into ZZ02 values (NULL, 'TABLE');
          insert into ZZ02 values (NULL, 'TABLE')
                                   *
          ERROR at line 1:
          ORA-01400: cannot insert NULL into ("HR"."ZZ02"."TABLE_NAME")

         
SQL> insert into ZZ02 values ('TEST_NOT_NULL', NULL)

          insert into ZZ02 values ('TEST_NOT_NULL', NULL)
                                                    *
          ERROR at line 1:
          ORA-01400: cannot insert NULL into ("HR"."ZZ02"."TABLE_TYPE")

Pourtant Oracle détecte bien que certains enregistrements sont NULL même si la colonne est maintenant considérée comme NOT NULL. Par exemple, avec la fonction COUNT, seuls deux enregistrements sur quatre sont traités pour le champ TABLE_TYPE.
          SQL> select count(*) from zz02;
          COUNT(*)
          ----------
               4

          SQL> select count(table_type) from zz02;
          COUNT(TABLE_TYPE)
          -----------------
                  2

Je vous conseille donc dorénavant de ne plus vous fier au DESC d'une table pour identifier ses colonnes NOT NULL.