About

Martin Klier

usn-it.de

Get Oracle User DDL with dbms_metadata

Sometimes you have to recreate a user in a DB, and you like it to be the same as before again. To have its DDL at hand is usually rather convenient. DBMS_METADATA is a great toolbox to get it, my example is just a short but hopefully useful excerpt of its possibilities:

set long 200000 pages 0 lines 131
column meta format a121 word_wrapped
select dbms_metadata.get_ddl('USER', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&username') meta from dual;

Take care
Usn

EDIT: Thanks to Ask Tom for the settings to use in SQL*PLUS.

EDIT2: Changed Username to input variable

Oracle 11g JDBC driver hangs blocked by /dev/random – entropy pool empty
Oracle Explain Plans or Execution Plans: Guess or Reality?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.