Récuperer le DDL d’une table avec DBMS_METADATA

Lorsque vous devez récupérer le script SQL de définition d’une table je suppose que la majorité d’entre vous utilisent des outils graphiques tels que TOAD ou SQL Developper.

Sachez toutefois que depuis la 9i il est possible de récupérer facilement ces scripts DDL et juste avec une fenêtre SQLPLUS grâce à la fonction GET_DDL du package DBMS_METADATA.

Voyons par exemple comment récupérer le DDL de la table CUSTOMERS du schéma SH d’Oracle :

SQL> SET LONG 50000
SQL> SET PAGES 500
SQL> SET LINES 200
SQL>  SELECT dbms_metadata.get_ddl('TABLE', table_name)
  2  from user_tables
  3  WHERE table_name='CUSTOMERS';
 
DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME)
--------------------------------------------------------------------------------
  CREATE TABLE "SH"."CUSTOMERS"
   (    "CUST_ID" NUMBER NOT NULL ENABLE,
        "CUST_FIRST_NAME" VARCHAR2(20) NOT NULL ENABLE,
        "CUST_LAST_NAME" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_GENDER" CHAR(1) NOT NULL ENABLE,
        "CUST_YEAR_OF_BIRTH" NUMBER(4,0) NOT NULL ENABLE,
        "CUST_MARITAL_STATUS" VARCHAR2(20),
        "CUST_STREET_ADDRESS" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_POSTAL_CODE" VARCHAR2(10) NOT NULL ENABLE,
        "CUST_CITY" VARCHAR2(30) NOT NULL ENABLE,
        "CUST_CITY_ID" NUMBER NOT NULL ENABLE,
        "CUST_STATE_PROVINCE" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_STATE_PROVINCE_ID" NUMBER NOT NULL ENABLE,
        "COUNTRY_ID" NUMBER NOT NULL ENABLE,
        "CUST_MAIN_PHONE_NUMBER" VARCHAR2(25) NOT NULL ENABLE,
        "CUST_INCOME_LEVEL" VARCHAR2(30),
        "CUST_CREDIT_LIMIT" NUMBER,
        "CUST_EMAIL" VARCHAR2(30),
        "CUST_TOTAL" VARCHAR2(14) NOT NULL ENABLE,
        "CUST_TOTAL_ID" NUMBER NOT NULL ENABLE,
        "CUST_SRC_ID" NUMBER,
        "CUST_EFF_FROM" DATE,
        "CUST_EFF_TO" DATE,
        "CUST_VALID" VARCHAR2(1),
         CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE,
         CONSTRAINT "CUSTOMERS_COUNTRY_FK" FOREIGN KEY ("COUNTRY_ID")
          REFERENCES "SH"."COUNTRIES" ("COUNTRY_ID") ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"

Pour récupérer le script de création de l’index CUSTOMERS_GENDER_BIX on utilise la même logique :

SQL> SELECT dbms_metadata.get_ddl('INDEX', index_name)
  2  from user_indexes
  3  WHERE index_name='CUSTOMERS_GENDER_BIX';

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
-------------------------------------------------------------------------------
 CREATE BITMAP INDEX "SH"."CUSTOMERS_GENDER_BIX" ON "SH"."CUSTOMERS" ("CUST_GEN

DER")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"

En utilisant un sympathique petit SPOOL il vous est possible de créer un script qui va générer par exemple l’ensemble des DDL des objets d’un schéma donné.

Le package DBMS_METADATA fournit bien d’autres procédures et fonctions que je vous invite à découvrir dans la doc Oracle Database Database PL/SQL Packages and Types Reference