DBMS METADATA

From Oracle FAQ
Jump to: navigation, search

DBMS_METADATA can be used to extract DDL definitions from a database:

[edit] Examples

Create sample objects (needed to show how we can reverse engineer the DDL)

CREATE TYPE vcarray_typ AS VARRAY(10) OF VARCHAR2(256);
/

CREATE TABLE vcarray_tab (
  id  NUMBER,
  arr vcarray_typ)
/

Extract the table definition:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'VCARRAY_TAB') FROM dual
/

Extract the type definition:

SELECT DBMS_METADATA.GET_DDL('TYPE', 'VCARRAY_TYP') FROM dual
/

Extract role definitions:

SELECT dbms_metadata.get_ddl('ROLE', role) FROM dba_roles;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT',  '&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&your_role_name') FROM dual;

[edit] External links