Les informations affichées par Oracle ne sont pas toujours fiables - The information displayed by Oracle is not always reliable
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.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)