Effet caché de la database 11G – Deferred Segment Creation

Dans la série des petites modifications qui peuvent devenir de vrais soucis quand on ne les connaît pas, voici la fonctionnalité de création des segments en différé.
En œuvre depuis la version 11.2 seulement et uniquement sur la version « Enterprise », le principe est très simple : lors de la création d’une table le segment associé n’est créé que lors de l’insertion de la (ou des) première(s) ligne(s).
On bénéficie des avantages suivant  pendant l’initialisation d’une application :

  • Gain d’espace dans les tablespaces, particulièrement si l’application génère un grand nombre de tables qui ne seront au final pas utilisées
  • Rapidité du déploiement de l’application, l’opération de création consiste seulement en une nouvelle entrée dans le dictionnaire des objets.

On peut citer quelques inconvénients :

  • Invalidation des curseurs associé à la table lors de la première insertion
  • Difficulté pour prévoir l’évolution de l’espace pris par les objets (capacity planning)

La fonctionnalité est activée par défaut et ceci est très simple à constater :

Par défaut ?

Base de données « Enterprise Edition »  en version 11.2.0.1, depuis le schéma d’exemple HR fourni:

SQL> connect HR/hr
SQL> CREATE TABLE seg_test(c number, d varchar2(500));
Table created.
SQL> SELECT segment_name FROM user_segments where segment_name='SEG_TEST' ;
no rows selected

Insertion d’une ligne (il n’y a même pas besoin de réaliser  un  commit)

SQL> insert into SEG_TEST values (10, 'aaaaaaaaaaaaaaaaaaaa') ;
1 row created.
SQL> col segment_name format a20
SQL> col bytes format 999999
SQL> SELECT segment_name,bytes FROM user_segments where segment_name='SEG_TEST' ;
SEGMENT_NAME           BYTES
-------------------- -------
SEG_TEST               65536

Allocation de 8 blocs pour le segment associé à la table (ma taille de bloc étant de 8Ko) .
Le rollback de l’instruction n’annule d’ailleurs pas l’allocation de l’espace :

SQL> rollback
SQL> SELECT segment_name,bytes FROM user_segments where segment_name='SEG_TEST' ;
SEGMENT_NAME           BYTES
-------------------- -------
SEG_TEST               65536

Conséquences imprévues …

Cette activation par défaut de la fonctionnalité peut créer quelques effets de bord et surprendre le DBA lors de l’utilisation de ses outils habituels, voici comment et pourquoi :

  • Dans le schéma HR d’exemple de ma base de données, créons maintenant la table SEG_TEST2 vide.
SQL> CREATE TABLE seg_test2(c number, d varchar2(500));
Table created.
  • Vérification de l’absence de segment.
SQL> SELECT segment_name,bytes FROM user_segments where segment_name='SEG_TEST2' ;
 no rows selected
  • Autre moyen de vérification, grâce à la nouvelle colonne « segment_created » des vues _tables (colonne présente aussi dans les vues *_indexes):
SQL> SELECT TABLE_NAME, SEGMENT_CREATED FROM USER_TABLES WHERE TABLE_NAME='SEG_TEST2' ;
TABLE_NAME                     SEGMENT_CREATED
------------------------------ ---------------
SEG_TEST2                           NO
  • Regardons maintenant le résultat d’un export « à l’ancienne » du schéma HR.
[oracle@localhost ~]$ exp system/oracle file=seg1.dmp log=seg1.log owner=hr
Export: Release 11.2.0.1.0 - Production on Wed Mar 9 00:49:46 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table                      COUNTRIES         25 rows exported
. . exporting table                    DEPARTMENTS         27 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      EMPLOYEES        107 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                           JOBS         19 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                    JOB_HISTORY         10 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      LOCATIONS         23 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                        REGIONS          4 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                       SEG_TEST          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

On voit clairement dans le log l’absence d’information de ma table SEG_TEST2, seul la table SEG_TEST sur laquelle j’ai fait mon insertion est présente :

[oracle@localhost ~]$ grep SEG_TEST seg1.log
. . exporting table                       SEG_TEST          1 rows exported

Si l’on génère les ordres SQL contenu dans le fichier du dump, on ne trouve pas non plus d’informations concernant ma table sans segment :

[oracle@localhost ~]$ imp system/oracle file=seg1.dmp indexfile=seg1.sql fromuser=HR touser=hr
Import: Release 11.2.0.1.0 - Production on Wed Mar 9 00:59:51 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. . skipping table "COUNTRIES"
. . skipping table "DEPARTMENTS"
. . skipping table "EMPLOYEES"
. . skipping table "JOBS"
. . skipping table "JOB_HISTORY"
. . skipping table "LOCATIONS"
. . skipping table "REGIONS"
. . skipping table "SEG_TEST"
Import terminated successfully without warnings.
[oracle@localhost ~]$ grep SEG_TEST seg1.sql
REM  CREATE TABLE "HR"."SEG_TEST" ("C" NUMBER, "D" VARCHAR2(500)) PCTFREE

Est -il possible alors d’exporter seulement la table SEG_TEST2 qui ne possède pas de segment ?
Et bien non ! Pour l’outil EXP, la table n’existe tout simplement pas, en voici la preuve :

[oracle@localhost ~]$ exp system/oracle file=seg_test2.dmp log=seg_test2.log tables=HR.SEG_TEST2
Export: Release 11.2.0.1.0 - Production on Wed Mar 9 01:03:49 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Current user changed to HR
EXP-00011: HR.SEG_TEST2 does not exist
Export terminated successfully with warnings.

On peut facilement en déduire que l’outil s’appuie sur la vue DBA_SEGMENTS et que l’absence de segment alloué lui pose un problème.
Voyons maintenant ce qui çe passe si on utilise quelque chose de plus moderne comme expdp : j’utilise l’objet de répertoire DATA_PUMP_DIR créé par défaut comme destination de mes fichiers dump et log

[oracle@localhost ~]$ expdp system/oracle DUMPFILE=HR.dmp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR LOGFILE=HR.log 
Export: Release 11.2.0.1.0 - Production on Wed Mar 9 01:12:07 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DUMPFILE=HR.dmp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR LOGFILE=HR.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . exported "HR"."JOBS"                                 6.984 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.476 KB       4 rows
. . exported "HR"."SEG_TEST"                             5.421 KB       1 rows
. . exported "HR"."SEG_TEST2"                                0 KB       0 rows
. . exported "HR"."T1"                                       0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/oracle/admin/orcl/dpdump/HR.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 01:14:09

La, il n’y a pas problème, la table SEG_TEST2 est bien traitée :

[oracle@localhost dpdump]$ grep SEG_TEST HR.log
. . exported "HR"."SEG_TEST"                             5.421 KB       1 rows
. . exported "HR"."SEG_TEST2"                                0 KB       0 rows

Ce que nous confirme la présence de l’ordre de création de la table dans le fichier contenant les DDL que nous générons ainsi:

[oracle@localhost dpdump]$ impdp system/oracle DUMPFILE=HR.dmp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR SQLFILE=HR.sql
Import: Release 11.2.0.1.0 - Production on Wed Mar 9 01:22:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/******** DUMPFILE=HR.dmp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR SQLFILE=HR.sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 01:22:53

Dans le fichier HR.sql l’ordre est bien présent :

CREATE TABLE "HR"."SEG_TEST2"
(    "C" NUMBER,
"D" VARCHAR2(500 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

Remarquez les nouveaux mots clés « SEGMENT CREATION DEFERRED » de l’ordre CREATE TABLE,  l’absence de la clause « STORAGE » et la clause « SEGMENT CREATION IMMEDIATE » pour les tables contenant déjà des lignes :

CREATE TABLE "HR"."SEG_TEST"
(    "C" NUMBER,
"D" VARCHAR2(500 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

Cette fois on peut réaliser l’export de cette seule table :

[oracle@localhost dpdump]$ expdp system/oracle DUMPFILE=HR.SEG_TEST2.dmp DIRECTORY=DATA_PUMP_DIR TABLES=HR.SEG_TEST2 LOGFILE=HR.SEG_TEST2.log
Export: Release 11.2.0.1.0 - Production on Wed Mar 9 01:30:29 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** DUMPFILE=HR.SEG_TEST2.dmp DIRECTORY=DATA_PUMP_DIR TABLES=HR.SEG_TEST2 LOGFILE=HR.SEG_TEST2.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HR"."SEG_TEST2"                                0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/app/oracle/admin/orcl/dpdump/HR.SEG_TEST2.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 01:31:02

Pas d’erreur, la définition de la table est bien exportée dans le fichier dump.

Quelques détails sur ce paramètre

La gestion de cette fonctionnalité passe par la définition du nouveau paramètre DEFERRED SEGMENT CREATION, celui ci est discrètement positionné sur TRUE par défaut :

SQL> show parameter DEF
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

il est modifiable et modulable au niveau de la session, de la base de données ou même d’une instance (dans le cadre d’une base de données RAC). Pour ne pas mettre en œuvre la fonctionnalité, il vous suffit de faire en tant que SYSDBA :

SQL> alter system set deferred_segment_creation=FALSE  ;

A vous de choisir le scope qui vous convient et le moment le plus opportun pour le positionner (qui serait pour moi tout de suite après la création de la base). On peut malgré le positionnement sur FALSE du paramètre utiliser ponctuellement la fonctionnalité avec la clause SEGMENT CREATION DEFERRED de l’ordre de création de la table.
Quelques précisions en ce qui concerne cette fonctionnalité de création différée des segments :

  • Seules sont concernées les tables de type « Heap » (autres que IOT ou cluster), les tables non partitionnés et les index non partitionnés
  • Ne s’applique pas aux tables figurant dans des tablespaces gérés par le dictionnaire
  • Les index héritent des caractéristiques de la table associée

Notez une petite modification depuis la version 11.2.0.2 (et seulement depuis cette version), la commande TRUNCATE a été modifié pour désallouer complétement tous les segments d’une table après le truncate , et se retrouver ainsi exactement dans les mêmes conditions que lors de la création de la table, avant les insertions, la clause de la commande est :

TRUNCATE TABLE nom_de _la_table DROP ALL STORAGE ;

Si malheureusement vous ne  possédez que la version « Standard Edition » ,  bien que le paramètre soit lui aussi positionné sur « TRUE » ,   un ordre select sur la vue v$option montre bien que la fonctionnalité n’est pas active :

SQL> select * from v$version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - Production
SQL> select parameter,value from v$option where parameter='Deferred Segment Creation'  ;
PARAMETER                                 VALUE
-------------------------------------------------
Deferred Segment Creation                 FALSE

Si vous tentez de créer une table avec la nouvelle clause, vous obtenez une erreur assez explicite :

SQL>create table T1 (c1 number) SEGMENT CREATION deferred ;
Erreur commençant à la ligne 1 de la commande :
SQL>create table T1 (c1 number) SEGMENT CREATION deferred ;
Erreur à la ligne de commande : 2, colonne : 0
Rapport d'erreur :
Erreur SQL : ORA-00439: fonction non activée : Deferred Segment Creation
00439. 00000 -  "feature not enabled: %s"
*Cause:    The specified feature is not enabled.
*Action:   Do not attempt to use this feature.

Du coup,  attention si vous mixez vos opérations datapump entre versions : un export expdp depuis une version « Enterprise » générera potentiellement l’erreur ORA-000439 lors d’un import impdp vers une version « Standard Edition ».
Enfin si vous souhaitez déclencher au moment le plus opportun l’allocation des segments (période creuse avant une insertion massive par exemple), vous avez à votre disposition la clause « ALLOCATE EXTENT » de la commande ALTER TABLE :

SQL> CREATE TABLE T10 (C1 number) storage (initial 256K next 256K minextents 2) ;
Succès de l'élément CREATE TABLE.
SQL>select table_name,segment_created from user_tables where table_name='T10' ;
TABLE_NAME                     SEGMENT_CREATED
------------------------------ ---------------
T10                            NO
SQL> ALTER TABLE T10 allocate extent ;
Succès de l'élément  ALTER TABLE T10.
SQL> select table_name,segment_created from user_tables where table_name='T10' ;
TABLE_NAME                     SEGMENT_CREATED
------------------------------ ---------------
T10                            YES

Les effets de cette petite modification restent limités, mais peuvent vous surprendre si vous n’y pensez plus, ils se conjuguent avec les possibilités d’avoir des indexes dans un état « unusable » (pas de segments d’alloués) voir invisible (ce qui peut être l’objet d’une nouvelle entrée). N’hésitez pas à me remonter d’autres effets de bord de ce paramètre de la 11GR2, qui sans être révolutionnaire apporte son lot de surprises.

8 réflexions sur “Effet caché de la database 11G – Deferred Segment Creation”

  1. D’abord merci pour cette aide qui tombe à pic pour moi. Je teste la migration d’un système 10g vers 11g et je me demandais pourquoi mes scripts n’exportaient plus les tables vides…
    Par contre, ma data base 11g a été créée avec la valeur par défaut (TRUE), elle n’a pas été altérée pour passer le paramètre à FALSE, des tables y ont déjà été créées sans la nouvelle clause et certaines ont déjà été populées, les tables sont donc vides ou non.
    Une commande export ne m’exporte donc que les tables ayant été populées, ce qui ne m’intéresse pas car je veux être capable de créer un schéma à l’identique.
    Je vais donc passer ce paramètre de TRUE à FALSE via ALTER DATABASE pour les nouvelles tables à venir..
    Et maintenant arrivent les questions concernant les tables vides déjà « créées »:
    -En faisant ça, est-ce que le statut de mes tables vides déjà existantes change aussitôt? deviennent-elles réellement créées et donc exportables?
    -Ou faut-il que je bidouille chaque table vide pour qu’elle soit effectivement créée (genre ajouter un row et faire rollback dessus, ce qui, d’après votre explication, suffit)?
    -Y a t-il une autre façon de rendre une table vide existante effectivement créée et exportable (ALTER TABLE avec clause SEGMENT CREATION IMMEDIATE)?

    1. Bonjour,
      merci de vos commentaires.
      Pour compléter et répondre, une fois le paramétrage deferred_segment_creation passé de TRUE à FALSE (ce qui est complètement dynamique) , il n’y a pas de changement sur vos tables créées sans segment. Pour lui alloué un segment et permettre ainsi à la commande export de les voir, le plus simple est de faire la commande « ALTER TABLE …. ALLOCATE EXTENT »

  2. Merci pour ces informations qui appellent une nouvelle question : si on veut faire un export depuis une version entreprise vers une version standard, y a-t-il tout de même un moyen de dire à expdp/impdp de ne pas exporter/importer en utilisant cette fonctionnalité

  3. Ping : Effet caché de la database 11G – Augmentation du volume des traces « EASYTEAM LE BLOG

  4. Bien vu. Cela ne m’étonne pas du tout, depuis le temps qu’oracle conseille de ne plus utiliser IMP et EXP depuis la 10 G.

Les commentaires sont fermés.