Rendre dbms_metadata lisible

DBMS_METADATA lisible, comment faire ?

Le package dbms_metadata existe depuis la version 9i et permet de regénerer les commandes DDL relatives aux objets ORACLE … Maintenant, pour l’utilisation et la liste des focntion au choix : RTFM ou RTFDB (DB pour Donald B le bien connu copiste en chef de la documentation Oracle) ce qui, au final revient à peu près au même. Cependant, souvent sa sortie contient des informations dont on se passerait bien, comme la gestion des extents ou autres alors qu’on utilise la plupart du temps les paramètres par défaut du tablespace.

Deux trois petites astuces bien sympa à retenir

• Ne jamais faire un dbms_metadata sur une liste d’objets (si un objet plante, les suivants ne sont pas traités)
• Utiliser long 9999999 et longc 9999999
• Utiliser des lignes larges (300 à 400 caractères, on peut aller jusqu’à 32768 soit 32k) et un “trimspool on” s’il on choisit de rediriger la sortie dans un fichier.
• Demander la mise en forme avec les terminaisons SQL ( / ou ; )

  exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE ); 

• Eviter les paramètres de stockage afin d’utiliser ceux du tablespace :

  exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false); 

L’exemple suivant donne le DDL de la table WRH$_SQL_PLAN dans ses deux versions

Comportement par défaut de DBMS_METADATA

select dbms_metadata.get_ddl('TABLE','WRH$_SQL_PLAN',USER) from sys.dual ;
DBMS_METADATA.GET_DDL('TABLE','WRH$_SQL_PLAN',USER)
--------------------------------------------------------------------------

  CREATE TABLE "SYS"."WRH$_SQL_PLAN"
   (    "SNAP_ID" NUMBER,
        "DBID" NUMBER NOT NULL ENABLE,

        [...]
        
        "OTHER_XML" CLOB,
         CONSTRAINT "WRH$_SQL_PLAN_PK" PRIMARY KEY ("DBID", "SQL_ID", "PLAN_HASH_VALUE", "ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  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 "SYSAUX"  ENABLE
   ) 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 "SYSAUX"
 LOB ("OTHER_XML") STORE AS BASICFILE (
  TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE 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))

DBMS_METADATA lisible, ou du moins beaucoup moins inutilement chargé

Paramétrage

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

PL/SQL procedure successfully completed.

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);

PL/SQL procedure successfully completed.

sortie DBMS_METADATA lisible

select dbms_metadata.get_ddl('TABLE','WRH$_SQL_PLAN',USER) from sys.dual ;
DBMS_METADATA.GET_DDL('TABLE','WRH$_SQL_PLAN',USER)
--------------------------------------------------------------------------

  CREATE TABLE "SYS"."WRH$_SQL_PLAN"
   (    "SNAP_ID" NUMBER,
        "DBID" NUMBER NOT NULL ENABLE,

        [...]

        "OTHER_XML" CLOB,
         CONSTRAINT "WRH$_SQL_PLAN_PK" PRIMARY KEY ("DBID", "SQL_ID", "PLAN_HASH_VALUE", "ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "SYSAUX"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "SYSAUX"
 LOB ("OTHER_XML") STORE AS BASICFILE (
  TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING ) ;