Oracle schema / user ddl via dbms_metadata

SET LONG 10000  TRIMSPOOL ON  LINES 180 HEADING OFF  FEEDBACK OFF PAGES 0  VERIFY OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SPECIFICATION',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'BODY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);


SPOOL ANUJ_DDL.SQL
CONNECT SCOTT/TIGER;
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) FROM USER_OBJECTS ;

from sys account 

SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, owner) FROM dba_OBJECTS
where owner='SCOTT';



set pages 800 lines 300  long 99999
select DBMS_METADATA.GET_DDL('USER','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&USER') ddl from dual ;


set autoprint on long 100000
variable Y clob
variable x clob

declare
            no_grant exception;
            pragma exception_init( no_grant, -31608 );
begin
   
  
  begin 
            :Y := dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '&&USER' );
         
   exception
           when no_grant then :Y := '-- no system grants';
end ;
 end;
/




SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL( 'SYSTEM_GRANT','RESOURCE') from dual;