DBMS_METADATA.Get.DDL – Technology Geek

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

TableSpace

select dbms_metadata.get_ddl(‘TABLESPACE’,tablespace_name) from dba_tablespaces;

Get All Tablespace Script :

set head off echo off
select ‘select dbms_metadata.get_ddl(”TABLESPACE”,”’
|| tablespace_name || ”’) from dual;’ from dba_tablespaces

Table

select dbms_metadata.get_ddl(‘TABLE’,’DEPT’,’SCOTT’) from dual;

User

SELECT dbms_metadata.get_ddl(‘USER’,’SCOTT’) FROM dual;

Role

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SCOTT’) from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’SCOTT’) from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’SCOTT’) from dual;
SELECT dbms_metadata.get_ddl(‘ROLE’,’RESOURCE’) from dual;

This is the Most common Usage for this package more information read Oracle Documentation

Thank you
Osama mustafa