Here is a script that can be used to gather source database details when we are attempting to upgrade from one version of DB to another or migrate Oracle DB from one server to another.
The purpose is to gather as much details so that the target DB can be prepared in such a way we don't have much work post import of the required schemas.
/* -----------------------------------------------------------------------------------------
Script name : export_details.sql
Usage : sqlplus / as sysdba @export_details.sql
Instructions :
Replace all SCHEMA1 and SCHEMA2 with the schemas you are intended to export
and save the file as export_details.sql, use as many users needed
Important Note :
Once details gathered, export NLS_LANG as specified below to set env variable
and start export by using the options mentioned in the end of this file
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 - Check what is the NLS_LANG needed as per app
This script will create 3 o/p files, 1 html and 2 sql files.
------------------------------------------------------------------------------------------ */
set echo on pages 999 long 90000
col OWNER for a30
col USERNAME for a30
col PROFILE for a30
col GRANTED_ROLE for a30
col GRANTEE for a30
col PROPERTY_NAME for a40
col PROPERTY_VALUE for a40
col DISPLAY_VALUE for a50
col DEFAULT_VALUE for a50
set markup html on spool on pre off
spool source_details.html
Prompt Parameter details:
------ ------------------
select name, DISPLAY_VALUE from v$parameter where name in ('service_names', 'compatible','sga_max_size','sga_target','log_buffer','db_cache_size','pga_aggregate_target', 'pga_aggregate_limit','cpu_count','session_cached_cursors','open_cursors','processes') order by name;
Prompt User details:
------ -------------
select count(*) "Total Schemas" from dba_users;
select sum(bytes)/1024/1024/1024 Total_DB_used_Size_In_GB from dba_segments;
select sum(bytes)/1024/1024/1024 Total_DB_allocated_Size_In_GB from dba_data_files;
SELECT owner,sum(bytes)/1024/1024/1024 Size_In_GB from dba_segments WHERE owner IN ('SCHEMA1','SCHEMA2') group by owner order by owner;
Prompt Info about Schemas:
------ -------------------
select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where USERNAME in ('SCHEMA1','SCHEMA2') order by 1;
set markup html on spool on pre on
Prompt Profile details for user:
------ -------------------------
SELECT dbms_metadata.get_ddl('PROFILE', p.profile) || ';' from dba_profiles p where profile not in ('DEFAULT') and resource_name='COMPOSITE_LIMIT';
set markup html on spool on pre off
Prompt Tablespace Size for particular schema:
------ --------------------------------------
select owner, TABLESPACE_NAME, sum(BYTES)/1024/1024/1024 Size_In_GB from dba_segments where OWNER in ('SCHEMA1','SCHEMA2') group by owner,TABLESPACE_NAME order by owner,TABLESPACE_NAME;
set markup html on spool on pre on
Prompt DDL for Schema, Non default Roles and Tablespace:
------ -------------------------------------------------
SELECT dbms_metadata.get_ddl('USER',u.username) || ';' as USERS from dba_users u where username in ('SCHEMA1','SCHEMA2');
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) || ';' as Tablespaces from dba_tablespaces tb where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS','TEMP');
select dbms_metadata.get_ddl('ROLE', r.role) || ';' as Roles from dba_roles r where ORACLE_MAINTAINED<>'Y';
set markup html on spool on pre off
Prompt Temp tablespace size:
------ ---------------------
select tablespace_name, sum(BYTES)/1024/1024/1024 from dba_temp_files group by tablespace_name;
Prompt Objects counts:
------ ---------------
select owner, object_type, status, count(*) from dba_objects where owner in ('SCHEMA1','SCHEMA2') group by object_type,owner, status order by 1,2,3;
Prompt Roles:
------ ------
select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from dba_role_privs where grantee in ('SCHEMA1','SCHEMA2') order by 1,2;
select GRANTEE, PRIVILEGE, ADMIN_OPTION from dba_sys_privs where grantee in ('SCHEMA1','SCHEMA2') order by 1,2;
Prompt System privileges having a property value = 1
Prompt Use specific DBMS packages such as DBMS_RESOURCE_MANAGER_PRIVS to provide grants to these objects.
------ --------------------------------------------------------------------------------------------------
select p.grantee, m.name from system_privilege_map m, dba_sys_privs p where m.name=p.privilege and m.property=1 and p.grantee in ('SCHEMA1','SCHEMA2') order by p.grantee, m.name;
Prompt DB Time Zone Details:
------ ---------------------
select sysdate,systimestamp,current_timestamp,sessiontimezone,dbtimezone from dual;
Prompt DB Characterset:
------ ----------------
select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
spool off
Prompt Generating grants script:
------ -------------------------
set markup html off
set lines 200 pages 0 head off feed off
spool source_all_privs.sql
select 'grant ' || privilege ||' on '|| owner || '.' || table_name ||' to '||grantee||';' from dba_tab_privs where grantee in ('SCHEMA1','SCHEMA2') and table_name NOT like 'BIN%';
spool off
spool source_all_synonyms.sql
SELECT 'CREATE OR REPLACE PUBLIC SYNONYM '||SYNONYM_NAME||' FOR '||TABLE_OWNER||'.'||TABLE_NAME||';' FROM ALL_SYNONYMS WHERE OWNER='PUBLIC' AND TABLE_OWNER IN ('SCHEMA1','SCHEMA2');
spool off
exit
Once the details are gathered, we can use the generated html file to prepare the target DB like creating required tablespaces, roles, etc. Once the import is completed in the target DB, both the sql scripts can be run against target DB to make sure all the grants are provided and public synonyms are created.
Let me know in comments on whether we can further add any details which might be needed for collecting details on source DB.
Also check, Datapump & system privileges with property value = 1 for details on this part.
Happy Datapumping...!!!
