LLI Wiki — My Digital Knowledge Base

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;
Mastodon LLIMastodon SocialFosstodon