How to include roles and grants into datapump export

For sure one way to do it, would be this one:

Prepare the parameters file first:

FULL=YES
INCLUDE=SCHEMA:"IN (SELECT 'PUBLIC' FROM DUAL UNION SELECT username FROM dba_users WHERE username in ('DB_USER1','DB_USER2'))"
INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner in ('DB_USER1','DB_USER2'))"
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=orcl_expdp.dmp
LOGFILE=orcl_expdp.log
INCLUDE=ROLE:"IN (select role from dba_roles where role in (select grantee from dba_tab_privs where grantor in ('DB_USER1','DB_USER2')))" 

And the command to run would be this one:

expdp  \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR parfile= orcl_full.par

In how much time will RMAN or Datapump finish the job?

This is how you can see it:

RMAN

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar,(sofar/totalwork) * 100 per_done,start_time, sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and lower(opname) not like ‘%aggregate%’
and lower(opname) like ‘rman%’;
SID START_TIME        TOTALWORK SOFAR   PER_DONE END_AT
—– —————– ———- ———- ———- —————– —————– 
491 18/08/10 20:13:45 1213440 1165439   96.04 18/08/10 20:18:06 
480 18/08/10 20:14:42 486816  172329    35.39 18/08/10 20:23:50

Export

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar, round ((sofar/totalwork) * 100) done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like '%EXPORT%';
SID START_TIME TOTALWORK SOFA PER_DONE END_AT
———- —————– ———- ———- ———- —————– —————–
113 18/08/10 21:03:48 3864 892 23.08 18/08/10 21:33:21

Import

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar, round ((sofar/totalwork) * 100) per_done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like '%IMPORT%';
SID START_TIME TOTALWORK SOFAR PER_DONE END_AT
———- —————– ———- ———- ———- —————– —————–
121 19/08/10 11:02:54 3004
532 17.70 19/08/10 11:33:05

How to clone a schema using different name at destination

Recently I had to make a clone of a schema and to move it from production to development using a different name at destination.

First grab all the information you need:

select username,default_tablespace from dba_users where username like '&USERNAME';

# Now get the tablespace name from the previous statement and get the datafiles below:

select file_name from dba_data_files where tablespace_name='&TBS_NAME';

# at destination create the tablespaces (ENCRYPTED if needed) (different name)

create tablespace NEWUSER_DATA datafile '/oradata/DB_NAME/newuser_data01.dbf'  size 900M autoextend on next 100M maxsize unlimited;
create tablespace NEWUSER_INDEX datafile '/oradata/DB_NAME/newuser_index01.dbf'  size 100M autoextend on next 100M maxsize unlimited;

# create the user at destination

create user NEWUSER identified by 'password' default tablespace NEWUSER_DATA;
 grant unlimited tablespace to NEWUSER;
 grant connect,resource to NEWUSER;
# Export the schema:
 set lin 300
 col owner format a30
 col DIRECTORY_NAME format a50
 col DIRECTORY_PATH format a50
 select owner,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
 expdp  \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=NEWUSER.20151028.dmpdp logfile=NEWUSER.20151028.logdp schemas=PROD_USER

# Import at destination

impdp \'/ as sysdba\'  remap_schema=PROD_USER:NEWUSER remap_tablespace=PROD_USER_DATA:NEWUSER_DATA,PROD_USER_INDEX:NEWUSER_INDEX directory=DATA_PUMP_DIR dumpfile=NEWUSER.20151028.dmpdp logfile=NEWUSER.20151028.dmpdp_imp.logdp
# Compile it:
 exec dbms_utility.compile_schema('NEWUSER');