Extraction des commandes DDL d'un schéma Oracle

Dernièrement, j'ai eu à déplacer un système sur différentes bases de
données. J'ai alors conçu un petit script pour me simplifier la vie pour la
création de schéma. Ce script génère un script contenant les commandes DDL
pour créer un schéma, ses droits et ses synonymes :

REM *********************************************************
REM * Script: extr_schm.sql
REM * Titre : Extraire les schemas
REM * Auteur: Eric Cloutier
REM * Date modif. : 06-08-2009
REM * Exemple : @extr_schm MDSYS
REM *********************************************************
set pagesize 0 feed off linesize 200 trimspoo on verify off
spool extr_schm_&1..log
DECLARE
i number;
ValResult varchar2(1000);
BEGIN
FOR I in (Select du.username from dba_users du
where du.username like '&1%'
order by username)
LOOP
-- Schema
dbms_output.put_line('-- Schema');
Select 'CREATE USER 'usernamechr(10)
'IDENTIFIED BY VALUES '''password''''chr(10)
'DEFAULT TABLESPACE 'default_tablespacechr(10)
'TEMPORARY TABLESPACE 'temporary_tablespacechr(10)
'PROFILE 'profilechr(10)
'ACCOUNT UNLOCK;'chr(10)
into ValResult
FROM DBA_USERS
WHERE username = i.username;
dbms_output.put_line(ValResult);
-- Privileges sur objets
dbms_output.put_line('-- Privileges sur objets');
For J in (SELECT 'GRANT ' m$.name
decode(m$.name,
'READ', ' ON DIRECTORY ',
'WRITE',' ON DIRECTORY ',
' ON ')
lower(uo$.name) '.' lower(o$.name)
' TO ' lower(ue$.name)
decode(NVL(t$.option$,0), 1, ' WITH GRANT
OPTION;',';') cmd_sql
FROM sys.objauth$ t$,
sys.obj$ o$,
sys.user$ ur$,
sys.table_privilege_map m$,
sys.user$ ue$,
sys.user$ uo$
WHERE o$.obj# = t$.obj#
AND t$.privilege# = m$.privilege
AND t$.col# IS NULL
AND t$.grantor# = ur$.user#
AND t$.grantee# = ue$.user#
AND o$.owner# = uo$.user#
-- Grant de SYS
AND ue$.name = i.username
order by ue$.name, uo$.name, o$.name, sequence#)
LOOP
dbms_output.put_line(j.cmd_sql);
END LOOP;
-- Roles
dbms_output.put_line('-- Roles');
For J in (select 'GRANT 'drp.granted_role' to '
drp.grantee';' cmd_sql
from dba_role_privs DRP
where grantee = i.username)
LOOP
dbms_output.put_line(j.cmd_sql);
END LOOP;
-- PrivilÞges SystÞme
dbms_output.put_line('-- Privileges Systeme');
For J in (select 'GRANT 'privilege' to '
dsp.grantee';' cmd_sql
from dba_sys_privs DSP
where grantee = i.username)
LOOP
dbms_output.put_line(j.cmd_sql);
END LOOP;
-- Default Role
dbms_output.put_line('-- Default Role');
dbms_output.put_line('Alter user 'i.username
' default role all;');
-- Synonymes
dbms_output.put_line('-- Synonymes');
For J in (Select 'Create or Replace synonym '
owner'.'synonym_name
' for 'table_owner'.'
table_name
decode(DB_LINK,null,';','@'
DB_LINK';') cmd_sql
from dba_synonyms
where owner = i.username
order by synonym_name)
LOOP
dbms_output.put_line(j.cmd_sql);
END LOOP;
dbms_output.put_line('--
===========================================================');
END LOOP;
END;
/
spool off
set pagesize 40 feed on linesize 5000
Prompt
Prompt -- Executer le script : @extr_schm_&1..log
Prompt