Extraire le DDL des objets Oracle avec DBMS_METADATA
Exemples de DBMS_METADATA.GET_DDL et GET_GRANTED_DDL pour tables, index, procédures, privilèges et profils.
La fonction GET_DDL du package DBMS_METADATA extrait le DDL des objets d'une base Oracle.
Syntaxe
DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','SCHEMA')
Toutes les valeurs sont des VARCHAR2 : les saisir entre apostrophes et en majuscules. Si le schéma est omis, le schéma courant est utilisé.
a) DDL de toutes les tables d'un schéma
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, u.owner)
FROM DBA_TABLES u WHERE u.owner = '&owner';
SQL*Plus demande le nom du schéma, par exemple Enter value for owner: SCOTT.
b) DDL de tous les index d'un schéma
SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, u.owner)
FROM DBA_INDEXES u WHERE u.owner = '&owner';
c) DDL d'une table précise
SELECT DBMS_METADATA.GET_DDL('TABLE', '&table_name', '&owner') FROM dual;
Exemple de saisie : Enter value for owner: SCOTT, Enter value for table_name: EMP.
d) DDL de toutes les tables du schéma courant
SELECT DBMS_METADATA.GET_DDL('TABLE', a.table_name)
FROM USER_ALL_TABLES a
WHERE a.nested = 'NO' AND (a.iot_type IS NULL OR a.iot_type = 'IOT');
Les tables imbriquées et les segments de débordement sont exclus.
e) DDL des privilèges système accordés à un utilisateur
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'SCOTT') FROM dual;
Sortie :
GRANT CREATE ANY DIRECTORY TO "SCOTT"
GRANT CREATE SYNONYM TO "SCOTT"
f) DDL de toutes les procédures d'un schéma
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name, u.owner)
FROM dba_objects u
WHERE u.object_type = 'PROCEDURE' AND u.owner = '&owner';
g) DDL de toutes les fonctions d'un schéma
SELECT DBMS_METADATA.GET_DDL('FUNCTION', u.object_name, u.owner)
FROM DBA_OBJECTS u
WHERE u.object_type = 'FUNCTION' AND u.owner = '&owner';
h) DDL de tous les synonymes d'un schéma
SELECT DBMS_METADATA.GET_DDL('SYNONYM', u.synonym_name, u.owner)
FROM DBA_SYNONYMS u WHERE u.owner = '&owner';
i) DDL de toutes les séquences d'un schéma
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name, u.sequence_owner)
FROM DBA_SEQUENCES u WHERE u.sequence_owner = '&owner';
j) Autres privilèges accordés
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'APP_USER') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'APP_USER') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'APP_USER') FROM dual;
Rôle, vue, synonyme
SELECT DBMS_METADATA.GET_DDL('ROLE', 'ROLE_NAME') FROM dual;
SELECT DBMS_METADATA.GET_DDL('VIEW', 'VIEW_NAME', 'SCHEMA') FROM dual;
SELECT DBMS_METADATA.GET_DDL('SYNONYM', 'NAME', 'SCHEMA') FROM dual;
Autres types d'objets utilisables
| AUDIT | AUDIT_OBJ | CLUSTER |
| COMMENT | CONSTRAINT | DB_LINK |
| DEFAULT_ROLE | DIRECTORY | FUNCTION |
| INDEX_STATISTICS | INDEX_TYPE | OBJECT_GRANT |
| PACKAGE | PROCEDURE | PROFILE |
| ROLE | ROLE_GRANT | ROLLBACK_SEGMENT |
| SEQUENCE | SYNONYM | SYSTEM_GRANT |
| TABLE | TABLE_DATA | TABLE_STATISTICS |
| TABLESPACE | TABLESPACE_QUOTA | TRIGGER |
| USER | VIEW |
Générer le DDL des profils
Pour migrer les profils d'une base source vers une base cible, on peut générer un appel par profil :
SELECT UNIQUE 'SELECT DBMS_METADATA.GET_DDL(''PROFILE'',' || '''' || PROFILE || ''') FROM DUAL;'
FROM DBA_PROFILES;
Utiliser ensuite les lignes obtenues dans le script suivant (ici, trois profils d'exemple) :
SET HEADING OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE);
-- vos appels à dbms_metadata ici
SELECT DBMS_METADATA.GET_DDL('PROFILE','PROF') FROM dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') FROM dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','APP_PROFILE') FROM dual;