DDL ‘s of Objects in a Schema :- select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME') from dual;

Last DDL Date:-

select to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') from dba_objects where owner='&user' and object_name='&table_name';

Generating DDL of table:-

set long 1000
select dbms_metadata.get_ddl('TABLE','EMP','SCHEMA')||'/' from dual;
set lines 1000 long 2000 pages 9999
select dbms_metadata.get_ddl('TABLE', table_name) from user_tables;

Generate DDL of DB_LINK:-

SET LONG 1000
SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;
SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 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#;

Generate Sequence DDL:-

select 'CREATE SEQUENCE '||SEQUENCE_NAME||chr(10)||
' INCREMENT BY '||INCREMENT_BY||chr(10)||
' START WITH '||LAST_NUMBER||chr(10)||
' MINVALUE '||MIN_VALUE||chr(10)||
' MAXVALUE '||MAX_VALUE||chr(10)||
decode(CYCLE_FLAG,'N',' NOCYCLE','CICLE')||chr(10)||
decode(ORDER_FLAG,'N',' NOORDER','ORDER')||chr(10)||
' CACHE '||CACHE_SIZE|| ';'
from DBA_SEQUENCES where SEQUENCE_OWNER='&OWNER_NAME';

Tablespace DDL:-

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''|| tablespace_name || ''') from dual;' from dba_tablespaces;
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') from dual;
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
/

Users DDL:-

set long 2000
select (case
when ((select count(*)
from dba_users
where username = 'ODB') > 0)
then dbms_metadata.get_ddl ('USER', 'ODB')
else to_clob (' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = 'ODB') > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'ODB')
else to_clob (' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', 'ODB')
else to_clob (' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', 'ODB')
else to_clob (' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', 'ODB')
else to_clob (' -- Note: No Object Privileges found!')
end ) from dual
/ spool Riskusercreate.sql
set pagesize 0
set escape on
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 and LOCKED', ' account lock password expire',
null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system and 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',
'PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
SELECT 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
ORDER BY USERNAME;
select 'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where grantee like upper('%&&uname%')
UNION
select 'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where grantee like upper('%&&uname%')
UNION
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';'
from dba_tab_privs
where grantee like upper('%&&uname%');
set pagesize 100
set escape off
spool off set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/ variable v_username VARCHAR2(30);
exec:v_username := upper('&1');
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
and rownum = 1
/ set linesize 80 pagesize 14 feedback on trimspool on verify on
select dbms_metadata.get_ddl('USER', 'KPHU000') || '/' usercreate from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','KPHU000') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','KPHU000') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KPHU000') || '/' FROM DUAL;
select DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', 'KPHU000') '/' from dual;

Roles,system privs,other privs granted to user:-

select dbms_metadata.get_ddl('USER', '&USER') || '/' usercreate from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&USER') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&USER') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&USER') || '/' FROM DUAL;
select DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', '&USER') '/' from dual;

Single Objects Grants any type of Object :-

select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';' from dba_tab_privs where table_name='&Any_type_of_object_name' ;

Password DDL:-

select 'alter user ' || NAME ||' identified by values ''' || password || ''';' from user$;

Cannot reuse the password:-

declare
userNm varchar2(100);
userpswd varchar2(100);
begin
userNm := upper('&TypeUserNameHere');
select password into userpswd from sys.user$ where name = userNm;
execute immediate ('ALTER PROFILE "FUNCTIONAL_USER" LIMIT
PASSWORD_VERIFY_FUNCTION null
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED');
execute immediate ('alter user '||userNm||' identified by oct152014oct');
execute immediate ('alter user '||userNm||' identified by values '''||userpswd||'''');
execute immediate ('ALTER PROFILE "FUNCTIONAL_USER" LIMIT
PASSWORD_VERIFY_FUNCTION PASSWDCOMPLEXVERIFICATION');
end;
/ ORA-28003: password verification for the specified password failed ORA-20009: Error: You cannot change password SQL> ALTER PROFILE FUNCTIONAL_USER LIMIT PASSWORD_VERIFY_FUNCTION NULL;
Profile altered.
SQL> alter user trial identified by test;
User altered.
SQL> conn trial/test;
ALTER PROFILE "FUNCTIONAL_USER" LIMIT PASSWORD_VERIFY_FUNCTION PASSWDCOMPLEXVERIFICATION;

Profile DDL:-

select ' alter user '||username||' profile '||PROFILE||';' from dba_users;
select 'grant ' || GRANTED_ROLE || ' to ' || ROLE || ';' from role_role_privs where role='&ROLE'
union
select 'grant ' || PRIVILEGE || ' to ' || ROLE || ';' from role_sys_privs where role='&&ROLE'
union
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';' from dba_tab_privs where GRANTEE='&&ROLE';

Roles DDL:-

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/ variable v_role VARCHAR2(30);
exec :v_role := upper('&1');
select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from dba_roles r
where r.role = :v_role
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_role
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_role
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_role
and rownum = 1
/ set long 100000
set longchunksize 200
set heading off
set feedback off
set echo off
set verify off
undefine role
select (case when ((select count(*) from dba_roles
where role = '&&role') > 0)
then dbms_metadata.get_ddl ('ROLE', '&&role')
else to_clob ('Role does not exist')
end ) Extracted_DDL from dual UNION ALL select (case when ((select count(*)
from dba_role_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_role_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_sys_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_tab_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&role')
end ) from dual;

create a role and assign all privileges to the role:-

create role L1_SUPPORT;
create role L2_SUPPORT;
create role L3_SUPPORT;
set pagesize 0
set echo off
set trimspool on
set linesize 120
set feedback off
spool grant.sql
select 'grant select,insert,update,delete on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L2_SUPPORT;' || CHR(10) ||
'grant select on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L1_SUPPORT;' || CHR(10) ||
'grant select on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L3_SUPPORT;'
from dba_tables a
where owner in ('SCHEMA_NAME')
order by owner, table_name;
spool off
set feedback on

Roles comparison between databases:-

DB link : dev to prod CREATE DATABASE LINK "COMPARE" CONNECT TO DBSNMP IDENTIFIED BY mypwd USING 'destonation-db-name';
select * from DBA_TAB_PRIVS@compare T1 where not exists (select 1 from dba_tab_privs T2 where t1.TABLE_NAME=t2.TABLE_NAME and t1.PRIVILEGE=t2.PRIVILEGE and t1.GRANTEE=t2.GRANTEE ) and t1.grantee='&ROLE_NAME';

No of users have a particular role:-

select GRANTEE as users from dba_role_privs where GRANTED_ROLE=UPPER('&GRANTED_ROLE');
select * from DBA_TAB_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_ROLE_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_SYS_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_COL_PRIVS where grantee like upper('%&enter_username%');

ALL Privileges for a SINGLE user:-

select 'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where grantee like upper('%&&uname%')
UNION
select 'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where grantee like upper('%&&uname%')
UNION
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';'
from dba_tab_privs
where grantee like upper('%&&uname%');

List of Users/Roles having privilege on TABLE:-

SELECT grantee || ' Through role ' || granted_role ge, 'SELECT' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'SELECT')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'UPDATE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'UPDATE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'INSERT' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'INSERT')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'DELETE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'DELETE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'INDEX' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'INDEX')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'ALTER' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'ALTER')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'REFERENCES' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'REFERENCES')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'EXECUTE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'EXECUTE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Direct' ge, PRIVILEGE priv
FROM SYS.dba_tab_privs
WHERE table_name = UPPER ('&TABLE_NAME')
ORDER BY 1, 2; select
case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
end from (
/* THE OBJECTS */
select null p1,null p2,object_name obj,owner own,object_type typ from
dba_objects where owner like upper('%&enter_username%')
/* THE OBJECT TO PRIVILEGE RELATIONS */
union select table_name p1, owner p2, grantee, grantee, privilege
from dba_tab_privs
/* THE ROLES TO ROLES/USERS RELATIONS */
union select granted_role p1, granted_role p2, grantee,grantee,null
from dba_role_privs ) start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;

Privileges granted by you to others:-

LISTING INFORMATION ABOUT PRIVILEGES —————————————————————————— To see which table privileges are granted by you to other users. SELECT * FROM USER_TAB_PRIVS_MADE To see which table privileges are granted to you by other users SELECT * FROM USER_TAB_PRIVS_RECD; To see which column level privileges are granted by you to other users. SELECT * FROM USER_COL_PRIVS_MADE To see which column level privileges are granted to you by other users SELECT * FROM USER_COL_PRIVS_RECD; To see which privileges are granted to roles SELECT * FROM USER_ROLE_PRIVS;

System privileges to roles and users:-

select lpad(' ', 2*level) || c "Privilege, Roles and Users" from (
/* THE PRIVILEGES */
select null p,name c from system_privilege_map
where name like upper('%&enter_privliege%')
/* THE ROLES TO ROLES RELATIONS */
union select granted_role p,grantee c from dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union select privilege p,grantee c from dba_sys_privs) start with p is null
connect by p = prior c;

Granted role permissions:-

select * from ROLE_ROLE_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME USER);
select * from ROLE_TAB_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME=USER);
select * from ROLE_SYS_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME=USER);

Roles & privs for a user:-

Granted Roles: SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER';
Privileges Granted Directly To User:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER';
Privileges Granted to Role Granted to User:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT granted_role
FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER');
Granted System Privileges:
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER';

when was the password was changed for a user?

SELECT NAME, TO_CHAR (ptime, ‘DD-MON-YYYY HH24:MI:SS’) AS “LAST TIME CHANGED”, ctime “CREATION TIME”, ltime “LOCKED” FROM USER$ WHERE ptime IS NOT NULL ORDER BY ptime DESC;

NON Default database users:-

select username from dba_users where username not in ( 'ANONYMOUS','AURORA','$JIS','$UTILITY','$AURORA','$ORB','$UNAUTHENTICATED','CTXSYS',
'DBSNMP','DMSYS','DSSYS','EXFSYS','MDSYS','ODM','ODM_MTR','OLAPSYS','ORDPLUGINS',
'ORDSYS','OSE$HTTP$ADMIN','OUTLN','PERFSTAT','PUBLIC','REPADMIN','SYS','SYSMAN',
'SYSTEM','TRACESVR','WKPROXY','WKSYS','WMSYS','XDB','APEX_PUBLIC_USER' ,'APEX_030200',
'APPQOSSYS','BI' ,'DIP' ,'FLOWS_XXXXXX' ,'HR','IX' ,'LBACSYS' ,'MDDATA' ,'MGMT_VIEW' ,
'OE' ,'ORACLE_OCM' ,'ORDDATA' ,'PM' ,'SCOTT' ,'SH' ,'SI_INFORMTN_SCHEMA' ,
'SPATIAL_CSW_ADMIN_USR' ,'SPATIAL_WFS_ADMIN_USR','MTSSYS','OASPUBLIC','OLAPSYS',
'OWBSYS','OWBSYS_AUDIT','WEBSYS','WK_PROXY','WK_TEST','AURORA$JIS$UTILITY$',
'SECURE','AURORA$ORB$UNAUTHENTICATED','XS$NULL','FLOWS_FILES');

DDL of V$ Tables:-

set long 10000 select  VIEW_DEFINITION  from V$FIXED_VIEW_DEFINITION where view_name=’GV$SQL_MONITOR’;

SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || index_name|| ”’ ) from dual;’  from user_indexes; SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’INDEX’; SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’INDEX’ and owner not in (‘SYS’,’SYSTEM’); Script for DDL’s of all Tables of database:- SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || table_name|| ”’ ) from dual;’  from user_tables; SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TABLE’; SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TABLE’ and owner not in (‘SYS’,’SYSTEM’); Script for DDL’s of All Procedures of database:- SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’PROCEDURE’; SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’PROCEDURE’ and owner not in (‘SYS’,’SYSTEM’); SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’PROCEDURE’ and owner=’OWNER_NAME’; Script for DDL’s of All Functions of database :- SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’FUNCTION’; SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’FUNCTION’ and owner not in (‘SYS’,’SYSTEM’); SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’FUNCTION’ and owner=’OWNER_NAME’; Script for DDL’s of All Triggers of database:- SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TRIGGER’; SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TRIGGER’ and owner not in (‘SYS’,’SYSTEM’); SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TRIGGER’ and owner=’OWNER_NAME’; Script for DDL’s of All Views of database:- SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’VIEW’; SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’VIEW’ and owner not in (‘SYS’,’SYSTEM’); SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’VIEW’ and owner=’OWNER_NAME’; Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8