Saturday, March 2, 2013

DBMS_METADATA

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

No comments:

Post a Comment