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”
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)?
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 »
Je me réponds à moi-même, la solution est décrite dans le dernier commentaire ici :
https://forums.oracle.com/forums/thread.jspa?threadID=1059534&tstart=0&messageID=4456562#4456562
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é
Merci pour cette aide claire et détaillée.
A noter aussi que les HEAP tables créées dans les schémas SYS et SYSTEM ne tiennent pas compte de cette fonctionnalité et créent un extent directement.
Ping : Effet caché de la database 11G – Augmentation du volume des traces « EASYTEAM LE BLOG
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.