Blog

Les différentes méthodes de Reverse Engineering (Partie 1)

Comment retrouver le DDL de sa base de données? Comment vérifier que nos scripts de création d’objets, de schéma, etc sont à jour? Il n’y a pas d’autre solution que de régénérer les scripts de ces objets à partir de la base. Cette opération s’appelle le Reverse Engineering. Nous allons voir les différentes méthodes possibles pour réaliser cette tâche:

  1. Méthode par SQL
  2. Méthode par export : Datapump ou exp
  3. Méthode par extraction d’un dump
  4. Méthode par outil :
    • SQLDeveloper / DataModeler
    • DBVizualiser
    • Autres (Toad,…)


Les informations récupérées seront les suivantes :

  • les tablespaces
  • les profils
  • les users
  • les rôles
  • les privilèges pour les rôles et users
  • les synonymes
  • les droits sur les objets

1 – Méthode par SQL

Les informations sont récupérées directement depuis le dictionnaire de la base de données.

Génération des tablespaces

Les définitions des profils sont récupérées à partir de la vue DBA_DATA_FILES et DBA_TABLESPACES:

 select 'create '
 || decode(t.bigfile,'NO',null, chr(10) || ' bigfile ' )
 || ' tablespace '
 || df.tablespace_name || chr(10)
 || ' datafile ''' || df.file_name || ''' size ' || df.bytes
 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
 || maxbytes)
  || ') ;'
 from dba_data_files df, dba_tablespaces t
 where df.tablespace_name=t.tablespace_name

Génération des profils

Les définitions des profils sont récupérées à partir de la vue DBA_PROFILES:

select distinct 'CREATE PROFILE "'||profile||'" LIMIT CPU_PER_SESSION DEFAULT;'
from dba_profiles
where profile != 'DEFAULT'
Union all
select  'ALTER PROFILE "'||profile||'" LIMIT '||RESOURCE_NAME||' '||LIMIT||';'
from dba_profiles
where profile !='DEFAULT'
order by profile, resource_name;

Génération des users

Les définitions des users sont récupérées à partir de la vue DBA_USERS, le script exclu les schémas oracle :

select 'CREATE USER "'||username||'" IDENTIFIED BY VALUES '''||password||''' DEFAULT TABLESPACE "'||default_tablespace||'" TEMPORARY TABLESPACE
"'||temporary_tablespace||'" ' ||decode(profile,'DEFAULT','','PROFILE "'||profile||'"')
||' ;' from dba_users
where username not in
(
   'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
   'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
   'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY',
   'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
   'WMSYS',  'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
   'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
   'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','ORACLE_OCM','DIP',
   'TSMSYS','TRACESVR','PERFSTAT'
);

Génération des rôles

Les définitions des rôles sont récupérées à partir de la vue DBA_ROLES :

select 'CREATE ROLE "'||role||'";' from dba_roles
where role not in (select distinct role from role_sys_privs)
and role not in (select distinct role from role_tab_privs)
and role != 'GLOBAL_AQ_USER_ROLE';

Génération des privilèges

Les définitions des privilèges sont récupérées à partir de la vue DBA_ROLE_PRIVS, le script exclu les schémas oracle :

select 'GRANT "'||granted_role||'" TO "'||grantee||'";' from dba_role_privs
where grantee not in (select distinct role from role_sys_privs)
and grantee not in (select distinct role from role_tab_privs)
and grantee not in (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
                        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
                        'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY',
                        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
                        'WMSYS',  'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
                        'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
                        'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','ORACLE_OCM','DIP',
                        'TSMSYS','TRACESVR','PERFSTAT');

Génération des synonymes

Les définitions des synonymes sont récupérées à partir de la vue DBA_USERS, le script exclu les schémas oracle :

select 'CREATE PUBLIC SYNONYM "'||synonym_name||'" FOR "'||table_owner||'"."'||table_name||'";'
from dba_synonyms
where owner ='PUBLIC'
and table_owner not in (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
                        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
                        'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY',
                        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
                        'WMSYS',  'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
                        'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
                        'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','ORACLE_OCM','DIP',
                        'TSMSYS','TRACESVR','PERFSTAT')
union all
select 'CREATE SYNONYM "'||owner||'"."'||synonym_name||'" for "'||table_owner||'"."'||table_name||
'";'
from dba_synonyms
where owner not in (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
                        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
                        'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY',
                        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
                        'WMSYS',  'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
                        'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
                        'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','ORACLE_OCM','DIP',
                        'TSMSYS','TRACESVR','PERFSTAT'
)
and table_owner not in (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
                        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
                        'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY',
                        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
                        'WMSYS',  'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
                        'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
                        'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','ORACLE_OCM','DIP',
                        'TSMSYS','TRACESVR','PERFSTAT'
)
and owner !='PUBLIC';

Génération des droits sur les objets

Les définitions des droits sur les objets sont récupérées à partir de la vue DBA_TAB_PRIVS, le script exclu les schémas oracle :

select 'GRANT '||privilege||' ON "'||owner||'"."'||table_name||'" TO "'||grantee||'"'||decode(grantable, 'YES', ' WITH GRANT OPTION')
from dba_tab_privs
where owner not in (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
                        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
                        'ORDPLUGINS',  'OEM_MONITOR', 'WKSYS', 'WKPROXY',
                        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
                        'WMSYS',  'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
                        'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
                        'SI_INFORMTN_SCHEMA', 'XDB', 'ODM','ORACLE_OCM','DIP',
                        'TSMSYS','TRACESVR','PERFSTAT');

2 – Méthode par export : Datapump ou Exp

Le principe est simple: il faut avoir un export full de la base source. Cela s’effectue avec l’instruction suivante:

  • pour EXP
exp "'/ as sysdba'" FILE=INSTANCE1.dmp LOG=INSTANCE1.log FULL=Y CONSISTENT=Y
  • pour Datapump
expdp userid="'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=INSTANCE1.dp logfile=INSTANCE1.log full=y

Ensuite il faut simuler l’import full

  • avec l’option show =y pour IMP. Voici l’instruction:
imp "/a as sysdba" file=expfull.dmp log=imp_show.log SHOW=y full=y rows=n igonre=y
  • avec l’option SQLFILE pour Datapump. Voici l’instruction:
impdp userid="'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=INSTANCE1.dp SQLFILE=imp_show.sql

Vous trouverez dans le fichier log toutes les commandes DDL qui ont été simulées lors de l’import.

3 – Méthode par extraction d’un dump

Pour l’utilisation de cette méthode, il faut avoir un dump full réalisé par la commande EXP. La note metalink suivante parle d’un script perl équivalent au commande suivante: Note 115658.1

Génération des tablespaces

Les définitions des tablespaces sont récupérées en recherchant la commande create tablespace:

cat INSTANCE1.dmp | grep -i 'create tablespace' | awk '{print $0";"}'

Génération des profils

Les définitions des profils sont récupérées en recherchant la commande create profile:

cat INSTANCE1.dmp | grep -i 'create profile' | awk '{print $0";"}'

Génération des users

Les définitions des users sont récupérées en recherchant la commande create user:

cat INSTANCE1.dmp | grep -i 'create user' |
egrep -iv 'SYS|OUTLN|SYSTEM|CTXSYS|DBSNMP|
LOGSTDBY_ADMINISTRATOR|ORDSYS|
ORDPLUGINS|OEM_MONITOR|WKSYS|WKPROXY|
WK_TEST|WKUSER|MDSYS|LBACSYS|DMSYS|
WMSYS|OLAPDBA|OLAPSVR|OLAP_USER|
OLAPSYS|EXFSYS|SYSMAN|MDDATA|
SI_INFORMTN_SCHEMA|XDB|ODM|ORACLE_OCM|DIP|
TSMSYS|TRACESVR|PERFSTAT'|
awk '{print $0";"}'

Génération des rôles

Les définitions des roles sont récupérées en recherchant la commande create role:

cat INSTANCE1.dmp | grep -i 'create role' |
egrep -iv 'SECURITY_|LOGSTDBY_|SCHEDULER|_CATALOG_|AQ_|OEM_|HS_|PLUSTRACE|STATISTICS' |
awk '{print $0";"}'

Génération des privilèges

Les définitions des profils sont récupérées en recherchant la commande create profile:

cat INSTANCE1.dmp | grep -i 'create profile' | awk '{print $0";"}'

Génération des synonymes

Les définitions des synonymes sont récupérées en recherchant la commande create synonym:

cat INSTANCE1.dmp | grep -i 'create public synonym ' |
egrep -iv '"SYS".|"SYSTEM".|"PERFSTAT".|OL$|$LOB|"PRODUCT_PRIVS"' |
awk '{print $0";"}'

Génération des droits sur les objets

Les définitions des profils sont récupérées en recherchant la commande create profile:

cat INSTANCE1.dmp | grep -i 'create profile' | awk '{print $0";"}'
Administrateur
Administrateur
Voir tous ses articles

3 réflexions sur “Les différentes méthodes de Reverse Engineering (Partie 1)”

  1. yc

    pas tout à fait dans la mesure ou l’on peut lui donner le resultat d’une requete sql en parametre par exemple:
    select dbms_metadata.get_ddl(‘TABLESPACE’,tablespace_name) from dba_tablespaces ;

  2. lahouari

    Je crois que le recours aux outils est plus que nécessaire dans ces cas là pour éviter de perdre du temps dans des scripts toujours difficiles à bien paramétrer. Je penses notamment à mon outil préféré TOAD qui fait ceci en deux trois clics
    l’utilisation du package DBMS_METADATA nécéssite la connaissance des objets ! car la fonction GET_DDL à besoin en entrée le nom objet, user et type de l’objet !

  3. yc

    merci mais il existe toute meme une methode beaucoup plus elégante.
    avec l’utilisation de DBMS_METADATA.GET_DDL

Les commentaires sont fermés.

Articles récents

Mentions légales & Politique de confidentialité