Script DDL pour la définition des comptes - DBMS_METADATA

Des fois on a besoin de recréer les comptes des utilisateurs lorsqu'on a besoin de déplacer ou migrer nos données d'une base de données vers une autre. 
Dans ce cas, c'est toujours nécessaire de vérifier les comptes existants et de vérifier les privilèges et quotas existants pour pouvoir les reproduire sur la destination.

Bien sûr existe le DBMS_METADATA.GET_DDL, ce qui représente la façon élégant et pratique de le faire :

Par exemple pour le compte "xyz" :

SET LONG 2000

SELECT DBMS_METADATA.GET_DDL('USER','xyz') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','xyz') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','xyz') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','xyz') FROM DUAL;

Sinon, si vous vous sentez plus à l'aise avec les requêtes ou si la nostalgie est plus puissante que vous, vous pouvez toujours aller chercher les données à l'ancienne façon -qui reste toujours efficace- :

SET SERVEROUTPUT ON ;
SET FEEDBACK OFF
DECLARE
CURSOR C_USER IS 

SELECT USERNAME, 'CREATE USER '||USERNAME||' '|| DECODE(PASSWORD, 'EXTERNAL', 'IDENTIFIED EXTERNALLY','IDENTIFIED BY VALUES '''||PASSWORD||''' ')||--CHR(10)||
' DEFAULT TABLESPACE '||DEFAULT_TABLESPACE ||--CHR(10)||
' TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';' LNE
FROM DBA_USERS
ORDER BY USERNAME ;

CURSOR C_QUOTA (V_USER DBA_USERS.USERNAME%TYPE) IS

SELECT 'ALTER USER '||USERNAME||' QUOTA '|| DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K') ||' ON '||TABLESPACE_NAME||';' LN_QUOTA
FROM DBA_TS_QUOTAS
WHERE USERNAME = V_USER ;

CURSOR C_PRIVS (V_USER DBA_USERS.USERNAME%TYPE) IS

SELECT 'GRANT '||GRANTED_ROLE||' TO "'||GRANTEE||'"'||DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';') LN_PRIVS
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = V_USER ;

CURSOR C_PRIVSSYS (V_USER DBA_USERS.USERNAME%TYPE) IS

SELECT 'GRANT '||PRIVILEGE||' TO "'||GRANTEE||'"'|| DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';') LN_PRIVSSYS
FROM DBA_SYS_PRIVS
WHERE GRANTEE = V_USER ;

CURSOR C_PRIVSTAB (V_USER DBA_USERS.USERNAME%TYPE) IS 

SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO "'||GRANTEE||'"'|| DECODE(GRANTABLE, 'YES', ' WITH GRANT OPTION;', ';') LN_PRIVSTAB
FROM DBA_TAB_PRIVS
WHERE GRANTEE = V_USER
ORDER BY OWNER;

BEGIN 

   FOR REG1 IN C_USER LOOP

      DBMS_OUTPUT.PUT_LINE('-----------------------------') ;

      DBMS_OUTPUT.PUT_LINE('--User : '||REG1.USERNAME) ;

      DBMS_OUTPUT.PUT_LINE('-----------------------------') ;

      DBMS_OUTPUT.PUT_LINE(REG1.LNE) ; 

      FOR REG2 IN C_QUOTA(REG1.USERNAME) LOOP

         DBMS_OUTPUT.PUT_LINE(REG2.LN_QUOTA) ; 

      END LOOP; 

      FOR REG3 IN C_PRIVS(REG1.USERNAME) LOOP

         DBMS_OUTPUT.PUT_LINE(REG3.LN_PRIVS) ; 

      END LOOP; 

      FOR REG4 IN C_PRIVSSYS(REG1.USERNAME) LOOP

         DBMS_OUTPUT.PUT_LINE(REG4.LN_PRIVSSYS) ; 

      END LOOP; 

      FOR REG5 IN C_PRIVSTAB(REG1.USERNAME) LOOP

         DBMS_OUTPUT.PUT_LINE(REG5.LN_PRIVSTAB) ; 

      END LOOP; 

   END LOOP ;
END ; 

/