Sunday, 6 January 2013

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;



No comments:

Post a Comment