Oracle Database Commands and Practical Examples: Oracle 12c EXPDP comands and Examples

### check expdp backup status ##

select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;

##check number objects exists on schema ##

select count(*),object_type,status from dba_objects where owner='TEST' group by status,object_type;
select count(*),object_type,status from dba_objects where owner='TEST' group by status,object_type;

## check valid invalid objects of schema ##

select object_type,count(*) from dba_objects where status='INVALID' and owner='TEST' group by object_type;

select object_type,count(*) from dba_objects where status='VALID' and owner='TEST' group by object_type;

select distinct tablespace_name from dba_segments where owner='TEST';
select distinct tablespace_name from dba_segments where owner='TEST';

### check job name during impdp expdp ###
###############################################################
select message, time_remaining/3600 hrs from v$session_longops where sofar <> totalwork;

###############################################################

select t.username,t.START_TIME,t.LAST_UPDATE_TIME,t.TIME_REMAINING,t.ELAPSED_SECONDS,
t.opname,t.target_desc,t.sofar,t.totalwork,t.message from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT';

#########Recompile invalid objects###########

@?/rdbms/admin/utlrp.sql
set time on
set timi on
exec dbms_stats.gather_schema_stats('INFODBA',cascade => TRUE,estimate_percent => 30);
exec dbms_stats.gather_schema_stats('MLIVE',cascade => TRUE,estimate_percent => 10);

EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'INFODBA');
EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'INFODBA2');

########Drop Users##########

Drop Single User
drop user INFODBA cascade;

Drop multiple user
drop user INFODBA1, INFODBA cascade;

######## schema DDL Metadata ################

set heading off
set echo off
set feedback off
set long 999999
spool EISP_PISDATA_PISVIEW_PISLOAD_PERMISSIONS.txt
set long 100000
set pages 200
select dbms_metadata.get_ddl( 'USER','INFODBA' ) from dual;
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'INFODBA' ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'INFODBA' ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'INFODBA' ) from dual;
spool off
set heading on
set feedback on

###################################### expdp -impdp 12c ######################

CREATE OR REPLACE DIRECTORY expdp AS '/u01/app/oracle/oradata/';

GRANT READ, WRITE ON DIRECTORY expdp TO username; (if expdp is done through schema)

---> Full EXPDP full=y

expdp \"sys/infodba@dmenv as sysdba\" directory=expdp dumpfile=expdp:expdp_dmenv%d.dmp logfile=expdp:expdp_full_dmenv%d.log full=y

NOTE : Before full impdp check existing tablespace on particular system and datafile .

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u04/MasterDB/oradata/system01.dbf
/u04/MasterDB/oradata/sysaux01.dbf
/u04/MasterDB/oradata/undotbs01.dbf
/u04/MasterDB/oradata/idata01.dbf
/u04/MasterDB/oradata/ilog01.dbf
/u04/MasterDB/oradata/indx01.dbf
/u04/MasterDB/oradata/idata02.dbf

7 rows selected.

SQL>
select name,open_mode,database_role,db_unique_name from v$database;
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
SQL> @tablespace

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
xxx READ WRITE PRIMARY xxx

Tablespace Allocated Current Used Available %Used (vs)
Name Size(GB) Size(GB) Size(GB) Size(GB) (Allocated)
------------------------------ ------------ ------------ ------------ ------------ ------------
IDATA 64.00 16.10 4.61 59.39 7.20
ILOG 32.00 2.00 .01 31.99 .02
INDX 32.00 .05 .00 32.00 .00
SYSAUX 32.00 4.00 1.15 30.85 3.60
SYSTEM 32.00 4.00 .41 31.59 1.28
UNDOTBS1 32.00 4.00 .05 31.95 .16

6 rows selected.

SQL>

impdp \"sys/infodba@dmenv as sysdba\" directory=expdp dumpfile=expdp:expdp_dmenv%d.dmp logfile=expdp:expdp_full_dmenv%d.log full=y

### expdp schema ###

remap_schema=Source_schema:Destination_schema

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp00.dmp logfile=expdptesr.log schemas=infodba1 parallel=2 content=all exclude=statistics parallel=2

impdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp.dmp logfile=impdp_remap.log schemas=infodba:infodba1 parallel=2 transform=oid:n

Different schema

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp00.dmp logfile=expdptesr.log schemas=infodba1 parallel=2 content=all exclude=statistics parallel=2

impdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp.dmp logfile=impdp_remap.log remap_schema=infodba:infodba1 parallel=2 transform=oid:n

#### expdp using different directories ######

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01.dmp,expdp1:expdp02.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics

impdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01.dmp,expdp1:expdp02.dmp logfile=expdp1:impdpdp.log remap_schema=infodba1:infodba parallel=2

#### EXPDP FILESIZE #####

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01.dmp,expdp1:expdp02.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics filesize=200M

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics

SENARIO :

1. schema size is 1058.875 MB ,Total estimation using BLOCKS method: 495.1 MB , we have given filesize=100 MB but total estimation size of schema is 495 MB .

We have run expdp ang got error below:

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01.dmp,expdp1:expdp02.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics filesize=100M

ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes

2. If dumpfile name already exists and with same name we are doing expdp on it we will get error.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-31641: unable to create dump file "C:\varun\expdp\expdp01.dmp"

ORA-27038: created file already exists

OSD-04010: <create> option specified, file already exists

########## expdp filesize using %U ###############

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics filesize=50M

Dump file set for SYS.SYS_EXPORT_S

C:\VARUN\EXPDP\EXPDP0001.DMP

C:\VARUN\EXPDP1\EXPDP0101.DMP

C:\VARUN\EXPDP2\EXPDP0201.DMP

C:\VARUN\EXPDP\EXPDP0002.DMP

C:\VARUN\EXPDP1\EXPDP0102.DMP

C:\VARUN\EXPDP2\EXPDP0202.DMP

C:\VARUN\EXPDP\EXPDP0003.DMP

C:\VARUN\EXPDP1\EXPDP0103.DMP

#################### Senerio ################################

Accidently infodba already exists and we are trying to remap_schema=infodba:infodba1,

NOTE: But by mistakenly we have done remap_schema=infodba1:infodba, so infodba already exists , so we get the error ORA-31684: Object type USER:"INFODBA" already exists

C:\Users\varunyadav>impdp \"sys/system@prod12c as sysdba\"

directory=expdp

dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp

logfile=expdp:expdp.log

remap_schema=infodba1:infodba

Import: Release 12.1.0.2.0 - Production on Fri Dec 1 13:32:55 2017

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01": "sys/********@prod12c AS SYSDBA" directory=expdp dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:expdp.log remap_schema=infodba1:infodba

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"INFODBA" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "INFODBA"."EIM_UID_GENERATOR_ROOT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."PM_PROCESS_LIST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."ACLS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_ROOT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_BOOT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_CV_OBJECTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_INDEXES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_M_LOCK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_R_LOCK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_F_LOCK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_B_LOCK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."AUDITLOG" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."USERDEFINEDLOG" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_LOCK_LOGGING" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_RECIPE_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."TIE_CONCURRENCY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."SCRATCH_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."SUBSCRIPTION_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."BOM_ACCT_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."BOM_RECIPE_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."ACCT_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."MMV_SPATIAL_CELL_INDEX" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."MMV_EQUIVALENT_THREADS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."TRANSACTION_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."ROOT_OBJS_IN_TRANS_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."EXPORT_TO_SITE_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."PRINCIPAL_OBJ_IN_TRANS_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

######### IMPDP schema using FILESIZE ############

remap_schema=Source_schema:Destination_schema

impdp \"sys/system@smrprodb as sysdba\" dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4 parallel=1 filesize =200m

####### Impdp remap schema (multiple schema) #####

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_dmenv12dec2017.dmp logfile=impdp_dmenv12dec2017.log remap_schema=infodba:infodbatest parallel=2

###### Remap multiple tablespace ##########

SQL> create tablespace ILOG3 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\ilog3.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace ILOG4 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\ilog4.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace INDX3 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\indx3.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace INDX4 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\indx4.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace IDATA3 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\idata3.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace IDATA4 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\idata4.DBF' size 2g autoextend on;

Tablespace created.

#####################################

REMAP_TABLESPACE=src1:dst1 REMAP_TABLESPACE=src2:dst2

######### EXPDP table flashback #########################

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log full=y FLASHBACK_SCN=2106577

########################################################## EXPDP table Flashback SCN | Flashback Time ###################################################

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log full=y FLASHBACK_SCN=2106577

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba flashback_time="to_timestamp('13-12-2017 14:21:00', 'DD-MM-YYYY HH24:MI:SS')"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="TO_TIMESTAMP('2017/12/13 14:00:00','YYYY/MM/DDHH24:MI:SS')"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME='13-12-2017 2:35:00 PM'

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME='13-12-2017 2:35:00 PM'expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba

FLASHBACK_TIME="2017/12/1315:34:01"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="2017/12/1315:34:01"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn%.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="2017/12/1315:34:01"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn_1.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="2017/12/1315:34:01"

####################################### table_exists_action=append|replace | truncate ########################################

673 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log tables=infodba.PITEM,infodba.PEPMTASK table_exists_action=append

674 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log tables=infodba.PITEM,infodba.PEPMTASK table_exists_action=replace

675 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log remap_schema=infodba:infodbatest tables=PITEM,PEPMTASK

676 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log remap_schema=infodba:infodbatest tables=infodba.PITEM,infodba.PEPMTASK

677 expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=dmenv_table1.log tables=infodba.PITEM,infodba.PFORM

678 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log remap_schema=infodba:infodbatest tables=infodba.PITEM,infodba.PFORM table_exists_action=append

679 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log remap_schema=infodba:infodbatest tables=infodba.PITEM,infodba.PFORM table_exists_action=replace

680 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log remap_schema=infodba:infodbatest tables=infodba.PITEM,infodba.PFORM table_exists_action=truncate

681 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log tables=PITEM:PITEM2,PFORM:PFORM2

682 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log schemas=infodba tables=PITEM:PITEM2,PFORM:PFORM2

683 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log schemas=infodba tables=PITEM:PITEM2,PFORM:PFORM2

684 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log tables=infodba.PITEM:PITEM2,infodba.PFORM:PFORM2

########################### IMPDP TABLES #############################################################

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=dmenv_table.log tables=infodba.PITEM,infodba.PEPMTASK

impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log tables=infodba.PITEM,infodba.PEPMTASK

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp directory=expdpdmenv dumpfile=expdp_table%u.dmplogfile=impdp_table1.log remap_table=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:infodbatest.pitem2,PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=infodbatest.pitem2,PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2

impdp "sys/infodba@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=truncate

impdp "sys/infodba@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=replace

impdp "sys/infodba@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=append

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=append

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=replace

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=truncateimpdp help=y

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM:infodbatest.pitem2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM,infodba.pam_tree remap_schema=infodba:infodbatest parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:pitem2,pam_tree:pam_tree2

Expdp Estimate only

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=expdp_full.dmp ESTIMATE_ONLY=yes logfile=full_log_estimate.log

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=yes logfile=full_log_estimate.log

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=y logfile=full_log_estimate.log

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=y logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=y logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=BLOCKS logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=STATISTICS logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=expdp_statistivcs.dmp ESTIMATE=STATISTICS logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=y logfile=full_log_estimate.log schemas=infodba

Oracle Database Commands and Practical Examples

HOME

Wednesday 9 January 2019

Oracle 12c EXPDP comands and Examples

No comments:

Post a Comment