get ddl of dblinks ,tablespace,user creation, role granted

Get ddl of database links in Oracle Database :-

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

==================================================================

Get ddl of tablespace creation in Oracle Database :- 

 select 'create tablespace ' || df.tablespace_name || chr(10)

 || ' datafile ''' || df.file_name || ''' size ' || df.bytes 

 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize ' 

 || maxbytes) 

 || chr(10) 

 || 'default storage ( initial ' || initial_extent 

 || decode (next_extent, null, null, ' next ' || next_extent )

 || ' minextents ' || min_extents

 || ' maxextents ' ||  decode(max_extents,'2147483645','unlimited',max_extents) 

 || ') ;'

 from dba_data_files df, dba_tablespaces t

 where df.tablespace_name=t.tablespace_name 

/

=====================================================================

Get ddl of table and index :-

select DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','OWNER') from dual; 

select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;

==================================================================

Get ddl of User creation in Oracle :-

select 'create user ' || U.username || ' identified ' || 

DECODE(password, 

      NULL, 'EXTERNALLY', 

      ' by values ' || '''' || password || ''''

      ) 

|| chr(10) ||

'default tablespace ' || default_tablespace || chr(10) ||

'temporary tablespace ' || temporary_Tablespace || chr(10) ||

' profile ' || profile || chr(10) ||

'quota ' ||

decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||

' on ' || default_tablespace || 

decode (account_status,'LOCKED', ' account lock',

      'EXPIRED', ' password expire',

      'EXPIRED \& LOCKED', ' account lock password expire',

      null)

||

';'

from dba_users U, dba_ts_quotas Q

-- Comment this clause out to include system & default users

where U.username not in ('SYS','SYSTEM',

'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',

'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',

'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',

'QS_CB','QS_CS','PERFSTAT')

and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)

;

or use :-

SELECT dbms_metadata.get_ddl('USER','<username>') from dual;

==============================================================

Get ddl of dba_directories: -

select dbms_metadata.get_ddl('DIRECTORY',directory_name) from  dba_directories;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<username>') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<username>') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<username>') from dual;

SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;

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