DB Migration Query

 -- User and Grant DDL

SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username';

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;

SELECT DBMS_METADATA.GET_DDL('USERNAME','default_tablespace') as script from DBA_USERS where username='&username';

-- Role and Grant DDL

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

/

-- Tablepace DDL

select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name)||';' as table_space from dba_tablespaces tb

where tb.tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1');

-- Here we are just considering the tablespace names and creating them on target RDS with below query.

select tablespace_name from dba_tablespaces where tablespace not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1');

create tablespace tablespace_name;

-- Profile DDL

select dbms_metadata.get_ddl('PROFILE', profile) as profile_ddl

from (select distinct profile from dba_profiles);

--Generate Foreign Key Constraints DDL

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','TABLE_NAME','OWNER') from dual;

--DB Links DDL

Set long 1000

SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;

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

 SET SERVEROUTPUT ON;

DECLARE

val NUMBER;

BEGIN

FOR R IN (SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER='MANOJ'  ORDER BY TABLE_NAME) LOOP

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || R.OWNER || '.' || R.TABLE_NAME INTO val;

DBMS_OUTPUT.PUT_LINE(R.TABLE_NAME || ',' || val );

END LOOP;

END;

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

10:45

LOB Details:
 ========
select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024/1024 size_GB
from dba_lobs a, dba_segments b
where a.segment_name = b.segment_name
and a.owner = b.owner and a.owner='&owner'
order by size_GB;First find the list of tables which have LOB columns:
select col.owner as schema_name,
col.table_name,
count(*) as column_count
from sys.dba_tab_columns col
inner join sys.dba_tables t on col.owner = t.owner
and col.table_name = t.table_name
where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE')
-- excluding some Oracle maintained schemas
and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
group by col.owner,
col.table_name
order by col.owner,
col.table_name;>> Once you get the list of tables, find the length of each LOB column and get the max.
Check actual space used by LOB
SELECT nvl((sum(dbms_lob.getlength( &lob_column ))),0) AS bytes FROM &table_name;

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

 dbms_metadata.get_ddl package, How to get ddl's of object's in the database

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;

SQL> set lin 1000

SQL> set pagesize 9999

SQL> set long 9999

SQL> select dbms_metadata.get_ddl('TABLE','MANOJ') from dual;

DDL 's of Objects in a Any Schema :-

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','USERNAME') from dual;

select dbms_metadata.get_ddl('INDEX','INDEX_NAME','USERNAME') from dual;

select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','USERNAME') from dual;

SQL> set lin 1000

SQL> set pagesize 9999

SQL> set long 9999

SQL> select dbms_metadata.get_ddl('TABLE','MANOJ,'OWNER') from dual;

Script to Generate DDL 's of Various Objects of database :-

Script for DDL 's of All Indexes of database:-

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';

select 'select dbms_metadata.get_ddl(''CONSTRAINT'',''' || CONSTRAINT_NAME || ''',''' || owner|| ''') from dual;' from dba_constraints where OWNER='AMIT';