Reverse Engineering with DBMS_METADATA

Posted by Dirk Nachbar on Monday, July 13, 2009 with 8 comments
Today I'm coming with PL/SQL stuff, nothing with Oracle Application Server :-)

At the moment I'm developing for a customer a small reverse engineering routine to capture an Oracle Database (CreateDB.sql, Tablespaces, Users, Grants aso). For this I'm using the package dbms_metadata. If you are using this package to capture e.g. all objects grants to all users, you may receive errors like this "SQL Error: ORA-31608: specified object of type OBJECT_GRANT not found". This comes up, if the user don't hold any object grants.
To avoid this simply run a query like this, which will generate you a list of DBMS_METADATA.GET_GRANTED_DDL statements for every user which holds objects grants:

select 'select DBMS_METADATA.GET_GRANTED_DDL(''OBJECT_GRANT'', '||a.username||') from dual;'
from dba_users a, (select distinct(grantee) from dba_tab_privs) b
where a.username = b.grantee;

You can adapt this construct easily on SYSTEM_GRANT and ROLE_GRANT.
Categories: ,