DBMS METADATA

From Oracle FAQ
Jump to: navigation, search

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

Examples[edit]

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;

External links[edit]

Retrieved from "http://www.orafaq.com/wiki/index.php?title=DBMS_METADATA&oldid=15621"