Export and Import Parameters

Export and Import using all parameters:

1.SOURCE_EDITION Parameter

SQL> show user
USER is “SYS”
SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to remo;
Grant succeeded.
SQL> alter user remo enable editions;
User altered.
SQL> grant create any view to remo;
Grant succeeded.
SQL> select USERNAME,EDITIONS_ENABLED from dba_users where USERNAME=’REMO’;

USERNAME

E
-REMO
Y
SQL> col USERNAME for a10
SQL> /

USERNAME E

REMO Y
SQL> conn remo/remo
Connected.
SQL> create edition e2;

Edition created.
SQL> SELECT SYS_CONTEXT(‘userenv’, ‘current_edition_name’) FROM DUAL;

SYS_CONTEXT(‘USERENV’,’CURRENT_EDITION_NAME’)

ORA$BASE
SQL> create table emp(EMPNO int,ENAME varchar(10));

Table created.
SQL> create editioning view ed_emp_view_ORABASE as select EMPNO,ENAME from emp;

View created.
SQL> desc ed_emp_view_ORABASE
Name Null? Type

EMPNO NUMBER(38)
ENAME VARCHAR2(10)

SQL> alter session set edition=e2;

Session altered.

SQL> SELECT SYS_CONTEXT(‘userenv’, ‘current_edition_name’) FROM DUAL;

SYS_CONTEXT(‘USERENV’,’CURRENT_EDITION_NAME’)

SQL> host
[oracle@trichydoyen ~]$ expdp dumpfile=Edition.dmp schemas=remo include=view directory=dir source_edition=e2

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 14:27:18 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_01″: remo/ dumpfile=Edition.dmp schemas=remo include=view directory=dir source_edition=e2
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Master table “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/Edition.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Mon Jan 20 14:27:39 2020 elapsed 0 00:00:13

[oracle@trichydoyen ~]$ sqlplus remo/remo

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 20 14:30:55 2020

Last Successful login time: Mon Jan 20 2020 14:27:25 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> alter session set edition=ORA$BASE;

Session altered.

SQL> drop view emp;
drop view emp
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop view ed_emp_view_ORABASE;

View dropped.

Target_EDITION Parameter

SQL> host
[oracle@trichydoyen ~]$ impdp dumpfile=Edition.dmp directory=dir target_edition=ORA\$BASE

Import: Release 12.2.0.1.0 – Production on Mon Jan 20 14:36:26 2020

Username: remo/remo
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_FULL_01″: remo/ dumpfile=Edition.dmp directory=dir target_edition=ORA$BASE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job “REMO”.”SYS_IMPORT_FULL_01″ successfully completed at Mon Jan 20 14:36:38 2020 elapsed 0 00:00:03

[oracle@trichydoyen ~]$ sqlplus remo/remo

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 20 14:37:16 2020

Last Successful login time: Mon Jan 20 2020 14:36:33 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> ALTER SESSION SET EDITION =ORA$BASE;

Session altered.

SQL> desc emp;
Name Null? Type

EMPNO NUMBER(38)
ENAME VARCHAR2(10)

SQL> desc ed_emp_view_ORABASE;
Name Null? Type

EMPNO NUMBER(38)
ENAME VARCHAR2(10)

Ref Link:http://dbinourhand.blogspot.com/2013/09/datapumps-source-edition-and-target.html

REMAP_DATA Parameter
SQL> conn remo/remo
Connected.
SQL> create
table tbl_test (id number, credit_card number); 2

Table created.

SQL> insert into tbl_test values(1,3432654);

1 row created.

SQL> insert into tbl_test values(2,6345324);

1 row created.

SQL> insert into tbl_test values(3,7998347);

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace package pkg_test as function f_test (credit_card in number) return number;
end;
/ 2 3

Package created.

SQL> create or replace package body pkg_test as function f_test (credit_card in number) return number as changed_value number;
begin
changed_value:=round(credit_card*2/3);
return changed_value;
end;
end;
/ 2 3 4 5 6 7

Package body created.
SQL> host
[oracle@trichydoyen ~]$ expdp remo/remo directory=dir dumpfile=remo_test.dmp logfile=remo_test.log tables=remmo.tbl_test remap_data=remo.tbl_test.credit_card:remo.pkg_test.f_test

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 15:54:48 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
ORA-39001: invalid argument value
ORA-39195: At least one schema in the TABLE_FILTER does not exist.

[oracle@trichydoyen ~]$expdp remo/remo directory=dir dumpfile=remo_test.dmp logfile=remo_test.log tables=remo.tbl_test remap_data=remo.tbl_test.credit_card:remo.pkg_test.f_test

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 15:55:33 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ directory=dir dumpfile=remo_test.dmp logfile=remo_test.log tables=remo.tbl_test remap_data=remo.tbl_test.credit_card:remo.pkg_test.f_test
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”TBL_TEST” 5.507 KB 3 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/remo_test.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jan 20 15:55:55 2020 elapsed 0 00:00:21

[oracle@trichydoyen ~]$ impdp tom/tom directory=dir dumpfile=remo_test.dmp logfile=remo_test.log remap_schema=remo:tom

Import: Release 12.2.0.1.0 – Production on Mon Jan 20 15:57:54 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “TOM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “TOM”.”SYS_IMPORT_FULL_01″: tom/ directory=dir dumpfile=remo_test.dmp logfile=remo_test.log remap_schema=remo:tom
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TOM”.”TBL_TEST” 5.507 KB 3 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “TOM”.”SYS_IMPORT_FULL_01″ successfully completed at Mon Jan 20 15:58:12 2020 elapsed 0 00:00:16
[oracle@trichydoyen ~]$ sqlplus tom/tom
SQL> conn sys/dba as sysdba
Connected.
SQL> select * from remo.tbl_test;

ID CREDIT_CARD

 1     3432654
 2     6345324
 3     7998347

SQL> select * from tom.tbl_test;

ID CREDIT_CARD

 1     2288436
 2     4230216
 3     5332231

PARTITION_OPTIONS Parameter
SQL> show user
USER is “REMO”
SQL> create table emp (emp_id number,sal INT) PARTITION BY RANGE (sal)( PARTITION employee_e1 VALUES LESS THAN (001),PARTITION employee_e2 VALUES LESS THAN (002));

Table created.

SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = ‘EMP’;

PARTITION_NAME

EMPLOYEE_E1
EMPLOYEE_E2

SQL> insert into emp values (101,00);

1 row created.

SQL> insert into emp values (102,01);

1 row created.

SQL> select * from EMP;

EMP_ID      SAL

   101        0
   102        1

SQL> commit;

Commit complete.

SQL> host
[oracle@trichydoyen ~]$ expdp remo/remo dumpfile=emp010.dmp directory=dir tables=emp

Export: Release 12.2.0.1.0 – Production on Fri Jan 24 11:40:56 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ dumpfile=emp010.dmp directory=dir tables=emp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”EMP”:”EMPLOYEE_E1″ 5.492 KB 1 rows
. . exported “REMO”.”EMP”:”EMPLOYEE_E2″ 5.492 KB 1 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/emp010.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Fri Jan 24 11:42:42 2020 elapsed 0 00:01:29

[oracle@trichydoyen ~]$ conn remo/remo
bash: conn: command not found
[oracle@trichydoyen ~]$ sqlplus remo/remo

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 24 11:43:24 2020

Last Successful login time: Fri Jan 24 2020 11:40:59 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> drop table emp purge;

Table dropped.

SQL> host
[oracle@trichydoyen ~]$ impdp remo/remo dumpfile=emp010.dmp directory=dir partition_options=DEPARTITION

Import: Release 12.2.0.1.0 – Production on Fri Jan 24 11:44:21 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_FULL_01″: remo/ dumpfile=emp010.dmp directory=dir partition_options=DEPARTITION
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”EMP_EMPLOYEE_E1″ 5.492 KB 1 rows
. . imported “REMO”.”EMP_EMPLOYEE_E2″ 5.492 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “REMO”.”SYS_IMPORT_FULL_01″ successfully completed at Fri Jan 24 11:44:37 2020 elapsed 0 00:00:13

[oracle@trichydoyen ~]$ sqlplus remo/remo

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 24 11:45:14 2020

Last Successful login time: Fri Jan 24 2020 11:44:22 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select * from EMP_EMPLOYEE_E1;

EMP_ID      SAL

   101        0

SQL> host
[oracle@trichydoyen ~]$ impdp remo/remo dumpfile=emp010.dmp directory=dir partition_options=MERGE

Import: Release 12.2.0.1.0 – Production on Fri Jan 24 11:47:00 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_FULL_01″: remo/ dumpfile=emp010.dmp directory=dir partition_options=MERGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”EMP”:”EMPLOYEE_E1″ 5.492 KB 1 rows
. . imported “REMO”.”EMP”:”EMPLOYEE_E2″ 5.492 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “REMO”.”SYS_IMPORT_FULL_01″ successfully completed at Fri Jan 24 11:47:07 2020 elapsed 0 00:00:05

[oracle@trichydoyen ~]$ sqlplus remo/remo

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 24 11:47:22 2020

Last Successful login time: Fri Jan 24 2020 11:47:00 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = ‘EMP’;

no rows selected

SQL> select * from EMP;

EMP_ID      SAL

   101        0
   102        1

KEEP_MASTER Parameter

Indicates whether the master table should be deleted or retained at the end of a Data Pump job that completes successfully. The master table is automatically retained for jobs that do not complete successfully.
[oracle@doyensys export]$ expdp REMO/REMO directory=dir dumpile=keep.dmp logfile=keep.log KEEP_MASTER=YES

Export: Release 12.2.0.1.0 – Production on Sat Jan 18 23:32:19 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_03″: REMO/ directory=tablez dumpfile=keep.dmp logfile=keep.log KEEP_MASTER=YES
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “REMO”.”SYS_EXPORT_SCHEMA_02″ 268.3 KB 1455 rows
. . exported “REMO”.”SYS_EXPORT_SCHEMA_01″ 310.9 KB 1464 rows
. . exported “REMO”.”TABLE100″ 0 KB 0 rows
. . exported “REMO”.”T1″ 5.984 KB 5 rows
. . exported “REMO”.”TABLE103″ 0 KB 0 rows
. . exported “REMO”.”KU$VAT_80350″ 0 KB 0 rows
. . exported “REMO”.”T2″ 6.351 KB 27 rows
Master table “REMO”.”SYS_EXPORT_SCHEMA_03″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_SCHEMA_03 is:
/u01/export/keep.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_03″ successfully completed at Sat Jan 18 23:33:03 2020 elapsed 0 00:00:43

METRICS Parameter
SQL> host
[oracle@trichydoyen ~]$ expdp directory=dir dumpfile=metrics.dmp schemas=remo METRICS=YES

Export: Release 12.2.0.1.0 – Production on Fri Jan 24 11:58:06 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_01″: remo/ directory=dir dumpfile=metrics.dmp schemas=remo METRICS=YES
W-1 Startup took 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
W-1 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
W-1 Completed 1 PACKAGE_BODY objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
W-1 Completed 6 TABLE_STATISTICS objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
W-1 Completed 1 MARKER objects in 6 seconds
W-1 Processing object type SCHEMA_EXPORT/USER
W-1 Completed 1 USER objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
W-1 Completed 5 SYSTEM_GRANT objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
W-1 Completed 3 ROLE_GRANT objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
W-1 Completed 1 DEFAULT_ROLE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
W-1 Completed 1 PROCACT_SCHEMA objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/DB_LINK
W-1 Completed 1 DB_LINK objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
W-1 Completed 6 TABLE objects in 19 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/COMMENT
W-1 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
W-1 Completed 1 PACKAGE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
W-1 Completed 1 ALTER_PACKAGE_SPEC objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/VIEW/VIEW
W-1 Completed 1 VIEW objects in 4 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
W-1 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
W-1 . . exported “REMO”.”JK” 5.273 KB 32 rows in 1 seconds using direct_path
W-1 . . exported “REMO”.”BB” 0 KB 0 rows in 0 seconds using direct_path
W-1 . . exported “REMO”.”DS” 5.164 KB 16 rows in 0 seconds using direct_path
W-1 . . exported “REMO”.”EMP” 5.5 KB 2 rows in 0 seconds using direct_path
W-1 . . exported “REMO”.”EMP_EMPLOYEE_E1″ 5.5 KB 1 rows in 0 seconds using direct_path
W-1 . . exported “REMO”.”EMP_EMPLOYEE_E2″ 5.5 KB 1 rows in 0 seconds using direct_path
W-1 Completed 6 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 1 seconds
W-1 Master table “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/metrics.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Fri Jan 24 11:59:23 2020 elapsed 0 00:01:07

ESTIMATE_ONLY Parameter
[oracle@doyensys ~]$ expdp ESTIMATE_ONLY=YES NOLOGFILE=YES schemas=REMO

Export: Release 12.2.0.1.0 – Production on Sun Jan 19 22:28:00 2020

Username: REMO/REMO

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_04″: REMO/ ESTIMATE_ONLY=YES NOLOGFILE=YES schemas=REMO
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated “REMO”.”SYS_EXPORT_SCHEMA_01″ 1.75 MB
. estimated “REMO”.”SYS_EXPORT_SCHEMA_03″ 1.75 MB
. estimated “REMO”.”SYS_IMPORT_SCHEMA_01″ 1.75 MB
. estimated “REMO”.”SYS_EXPORT_SCHEMA_02″ 704 KB
. estimated “REMO”.”T1″ 64 KB
. estimated “REMO”.”EMP” 64 KB
. estimated “REMO”.”TABLE100″ 64 KB
. estimated “REMO”.”TABLE103″ 64 KB
. estimated “REMO”.”KU$VAT_80350″ 0 KB
Total estimation using BLOCKS method: 6.187 MB
Job “REMO”.”SYS_EXPORT_SCHEMA_04″ successfully completed at Sun Jan 19 22:28:08 2020 elapsed 0 00:00:03

Estimate Parameter
[oracle@doyensys ~]$ expdp TABLES=EMP ESTIMATE=STATISTICS DIRECTORY=DIR DUMPFILE=est.dmp
Export: Release 12.2.0.1.0 – Production on Sun Jan 19 22:30:47 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: REMO/REMO
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “EXP_TEST”.”SYS_EXPORT_TABLE_01″: REMO/ TABLES=EMP ESTIMATE=STATISTICS DIRECTORY=tablez DUMPFILE=est.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated “REMO”.”EMP” 4.683 KB
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”EMP” 6.351 KB 27 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/export/est.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Sun Jan 19 22:31:07 2020 elapsed 0 00:00:15
ESTIMATE=BLOCKS
[oracle@doyensys ~]$ expdp TABLES=EMP ESTIMATE=BLOCKS DIRECTORY=DIR DUMPFILE=est1.dmp
Export: Release 12.2.0.1.0 – Production on Sun Jan 19 22:31:40 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: REMO/REMO
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: REMO/ TABLES=EMP ESTIMATE=BLOCKS DIRECTORY=tablez DUMPFILE=est1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated “REMO”.”EMP” 4.683 KB
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”EMP” 6.351 KB 27 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/export/est1.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Sun Jan 19 22:31:55 2020 elapsed 0 00:00:10

SKIP_UNUSABLE_INDEXES Parameter
[oracle@doyensys ~]$ expdp REMO/REMO TABLES=EMP DIRECTORY=DIR DUMPFILE=EMP.dmp LOGFILE=EMP.log

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 17:43:04 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “EXP_TEST”.”SYS_EXPORT_TABLE_01″: REMO/ TABLES=EMP DIRECTORY=tablez DUMPFILE=T2.dmp LOGFILE=T2.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”EMP” 6.351 KB 27 rows
Master table “EXP_TEST”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for EXP_TEST.SYS_EXPORT_TABLE_01 is:
/u01/export/T2.dmp
Job “EXP_TEST”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Jan 21 17:43:24 2020 elapsed 0 00:00:20

IMPORTING IN ANOTHER DATABASE

[oracle@doyensys ~]$ impdp DIRECTORY=DIR DUMPFILE=EMP.dmp LOGFILE=EMP.log SKIP_UNUSABLE_INDEXES=YES table_exists_action=append

Import: Release 12.2.0.1.0 – Production on Tue Jan 21 17:45:06 2020

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYS”.”SYS_IMPORT_FULL_02″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_02″: / AS SYSDBA DIRECTORY=tablez DUMPFILE=T2.dmp LOGFILE=T2.log SKIP_UNUSABLE_INDEXES=YES table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “EXP_TEST”.”T2″ exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”EMP” 6.351 KB 27 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “SYS”.”SYS_IMPORT_FULL_02″ successfully completed at Tue Jan 21 17:45:18 2020 elapsed 0 00:00:08

SQL> select * from REMO.EMP;

NAME DEPT YEAR
———- ———- ———-
Biju CSE 2015
SHA MECH 2016
INB IT 2017
SAGA EEE 2019
SIVA ECE 2020
RAHU CIVIL 2021
RAM EIE 1999
SUNDAR AERO 1998
RAVI EIE 1999
RAVI EIE 1999
RAVI EIE 1999

REMAP_DIRECTORY Parameter
The REMAP_DIRECTORY parameter is a variant of the REMAP_DATAFILE parameter, which allows you to remap the paths of multiple datafile references in a single shot. The REMAP_DIRECTORY and REMAP_DATAFILE parameters are mutually exclusive.

[oracle@doyensys ~]$ impdp DIRECTORY=dir DUMPFILE=remapsch1.dmp REMAP_DIRECTORY=’/u01/app/oracle/oradata/orcl/datafile/EMP.dbf’:’/u01/app/oracle/oradata/orcl/datafile/EMP01.dbf’ table_exists_action=append

Import: Release 12.2.0.1.0 – Production on Tue Jan 21 19:36:40 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: remo/remo
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_FULL_01″: REMO/ DIRECTORY=tablez DUMPFILE=remapsch1.dmp REMAP_DIRECTORY=/u01/app/oracle/oradata/DOYENDB/datafile/EMP.dbf:/u01/app/oracle/oradata/DOYENDB/datafile/ts007.dbf table_exists_action=append
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table “SYSTEM”.”T1″ exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “SYSTEM”.”T1″ 5.523 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “REMO”.”SYS_IMPORT_FULL_01″ successfully completed at Tue Jan 21 19:36:55 2020 elapsed 0 00:00:10

REMAP_DATAFILE Parameter

[oracle@doyensys ~]$ impdp DIRECTORY=tablez DUMPFILE=inclu.dmp REMAP_DATAFILE=’/u01/app/oracle/oradata/orcl/datafile/EMP.dbf’:’/u01/app/oracle/oradata/orcl/datafile/EMP01.dbf’ sqlfile=tablez:remapdata.sql

Import: Release 12.2.0.1.0 – Production on Tue Jan 21 20:17:03 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYS”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_SQL_FILE_FULL_01″: / AS SYSDBA DIRECTORY=tablez DUMPFILE=inclu.dmp REMAP_DATAFILE=/u01/app/oracle/oradata/DOYENDB/datafile/EMP.dbf:/u01/app/oracle/oradata/DOYENDB/datafile/ts007.dbf sqlfile=tablez:remapdata.sql
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_SQL_FILE_FULL_01″ successfully completed at Tue Jan 21 20:17:07 2020 elapsed 0 00:00:01

TRANSPORT_TABLESPACES Parameter
EXIT_CLIENT Parameter
[oracle@doyensys ~]$ expdp SCHEMAS=REMO DIRECTORY=dir DUMPFILE=EXIT.dmp logfile=EXIT.log

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 18:25:49 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_06″: REMO/ SCHEMAS=REMO DIRECTORY=tablez DUMPFILE=EXIT.dmp logfile=EXIT.log
^C
Export> EXIT_CLIENT

[oracle@doyensys ~]$

SQLFILE Parameter
[oracle@trichydoyen ~]$ impdp directory=dir dumpfile=kill.dmp logfile=lks.log tables=remo.jk sqlfile=tablesql.sql

Import: Release 12.2.0.1.0 – Production on Tue Jan 21 16:48:21 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_SQL_FILE_TABLE_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_SQL_FILE_TABLE_01″: remo/ directory=dir dumpfile=kill.dmp logfile=lks.log tables=remo.jk sqlfile=tablesql.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “REMO”.”SYS_SQL_FILE_TABLE_01″ successfully completed at Tue Jan 21 16:48:30 2020 elapsed 0 00:00:03

DISABLE_ARCHIVE_LOGGING Parameter
[oracle@doyensys ~]$ impdp directory=DIR dumpfile=SCHEMS.dmp logfile=impschem.log remap_schema=REMO:REMO transform=disable_archive_logging:y:TABLE

Import: Release 12.2.0.1.0 – Production on Mon Jan 20 20:04:56 2020

Username: REMO/REMO

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_FULL_01″: REMO/ directory=tablez dumpfile=SCHEMS.dmp logfile=impschem.log remap_schema=REMO:SCOTT transform=disable_archive_logging:y:TABLE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”SCOTT” 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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”T1″ 5.945 KB 2 rows
. . imported “REMO”.”T2″ 6.351 KB 27 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “REMO”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at Mon Jan 20 20:05:14 2020 elapsed 0 00:00:12

VIEWS_AS_TABLES Parameter
SQL> conn remo/remo
Connected.
SQL> select * from t1; ID NAME LOCATION

 1 AAA        NCJ
 2 BBB        MAA
 3 CCC        MS
 4 DDD        TPJ
 5 EEE        MDU

SQL> create view LOCATION_DP as select * from t1;

View created.

SQL> select * from LOCATION_DP;

ID NAME       LOCATION

 1 AAA        NCJ
 2 BBB        MAA
 3 CCC        MS
 4 DDD        TPJ
 5 EEE        MDU

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

[oracle@doyensys export]$ expdp remo/remo views_as_tables=LOCATION_DP directory=dir dumpfile=view1.dmp logfile=view1.log

Export: Release 12.2.0.1.0 – Production on Sat Jan 18 22:26:32 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: REMO/ views_as_tables=LOCATION_DP directory=tablez dumpfile=view1.dmp logfile=view1.log
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported “REMO”.”LOCATION_DP” 5.992 KB 5 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/export/view1.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Sat Jan 18 22:26:38 2020 elapsed 0 00:00:05

ENCRYPTION_ALGORITHM Parameter

[oracle@doyensys ~]$ expdp REMO directory=dir dumpfile=ENCRYPMODE.dmp logfile=ENCRYPMODE.log ENCRYPTION_PASSWORD=123456 ENCRYPTION_ALGORITHM=AES128

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 20:14:15 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_01″: REMO/ directory=tablez dumpfile=ENCRYPMODE.dmp logfile=ENCRYPMODE.log ENCRYPTION_PASSWORD= ENCRYPTION_ALGORITHM=AES128
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “REMO”.”T1″ 5.953 KB 2 rows
. . exported “REMO”.”T2″ 6.359 KB 27 rows
Master table “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_SCHEMA_01 is:
/u01/export/ENCRYPMODE.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Mon Jan 20 20:14:54 2020 elapsed 0 00:00:36

ENCRYPTION_MODE Parameter

[oracle@doyensys ~]$ expdp REMO directory=dir dumpfile=ENCRYPMODE1.dmp logfile=ENCRYPMODE1.log ENCRYPTION=all ENCRYPTION_PASSWORD=123456 ENCRYPTION_ALGORITHM=AES128

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 20:35:43 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_01″: REMO/ directory=tablez dumpfile=ENCRYPMODE1.dmp logfile=ENCRYPMODE1.log ENCRYPTION=all ENCRYPTION_PASSWORD= ENCRYPTION_ALGORITHM=AES128
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “REMO”.”T1″ 5.953 KB 2 rows
. . exported “REMO”.”T2″ 6.359 KB 27 rows
Master table “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_SCHEMA_01 is:
/u01/export/ENCRYPMODE1.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Mon Jan 20 20:36:27 2020 elapsed 0 00:00:39

[oracle@doyensys ~]$ expdp REMO directory=tablez dumpfile=ENCRYPMODE1.dmp logfile=ENCRYPMODE1.log ENCRYPTION=mode ENCRYPTION_PASSWORD=123456 ENCRYPTION_ALGORITHM=AES128
LRM-00121: ‘mode’ is not an allowable value for ‘encryption’

[oracle@doyensys ~]$ expdp REMO directory=tablez dumpfile=ENCRYPMODE1.dmp logfile=ENCRYPMODE1.log ENCRYPTION=TRANSPARENT ENCRYPTION_PASSWORD=123456 ENCRYPTION_ALGORITHM=AES128
LRM-00121: ‘TRANSPARENT’ is not an allowable value for ‘encryption’

ENCRYPTION_PWD_PROMPT Parameter

encryption_pwd_prompt=no

[oracle@doyensys ~]$ expdp remo/remo tables=T1 directory=dir dumpfile=encrypt.dmp logfile=encrypt.log encryption_pwd_prompt=no
Export: Release 12.2.0.1.0 – Production on Sat Jan 18 22:17:19 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: REMO/ tables=T1 directory=tablez dumpfile=encrypt1.dmp logfile=encrypt1.log encryption_pwd_prompt=no
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”T1″ 5.984 KB 5 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/export/encrypt1.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Sat Jan 18 22:19:54 2020 elapsed 0 00:00:14

encryption_pwd_prompt=yes
[oracle@doyensys ~]$ expdp REMO/abc tables=T1 directory=tablez dumpfile=encrypt.dmp logfile=encrypt.log encryption_pwd_prompt=yes
Export: Release 12.2.0.1.0 – Production on Sat Jan 18 22:17:19 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Encryption Password:
Starting “REMO”.”SYS_EXPORT_TABLE_01″: REMO/ tables=T1 directory=tablez dumpfile=encrypt.dmp logfile=encrypt.log encryption_pwd_prompt=yes
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”T1″ 5.992 KB 5 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/export/encrypt.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Sat Jan 18 22:17:48 2020 elapsed 0 00:00:28

Encryption Parameter

[oracle@doyensys ~]$ expdp TABLES=T2 ESTIMATE=STATISTICS DIRECTORY=dir DUMPFILE=est.dmp

Export: Release 12.2.0.1.0 – Production on Sun Jan 19 22:30:47 2020

Username: remo/remo
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: REMO/ TABLES=T2 ESTIMATE=STATISTICS DIRECTORY=tablez DUMPFILE=est.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated “REMO”.”T2″ 4.683 KB
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”T2″ 6.351 KB 27 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/export/est.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Sun Jan 19 22:31:07 2020 elapsed 0 00:00:15

ESTIMATE=BLOCKS

[oracle@doyensys ~]$ expdp TABLES=T2 ESTIMATE=BLOCKS DIRECTORY=dir DUMPFILE=est1.dmp

Export: Release 12.2.0.1.0 – Production on Sun Jan 19 22:31:40 2020

Username: remo/remo
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: REMO/ TABLES=T2 ESTIMATE=BLOCKS DIRECTORY=tablez DUMPFILE=est1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated “REMO”.”T2″ 4.683 KB
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”T2″ 6.351 KB 27 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/export/est1.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Sun Jan 19 22:31:55 2020 elapsed 0 00:00:10

ENCRYPTION_PASSWORD Parameter

[oracle@doyensys ~]$ expdp REMO directory=tablez dumpfile=ENCRYPMODE.dmp logfile=ENCRYPMODE.log ENCRYPTION_PASSWORD=123456 ENCRYPTION_ALGORITHM=AES128

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 20:14:15 2020

Dump file set for REMO.SYS_EXPORT_SCHEMA_01 is:
/u01/export/ENCRYPMODE.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Mon Jan 20 20:14:54 2020 elapsed 0 00:00:36

REMAP_TABLESPACE Parameter
SQL> conn remo/remo
Connected.
SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME =’REMO’;

DEFAULT_TABLESPACE

INBA
SQL> select table_name, tablespace_name from dba_tables where table_name=’JK’ and owner=’REMO’;

TABLE_NAME

TABLESPACE_NAME

JK
INBA
SQL> host
[oracle@trichydoyen ~]$ expdp remo/remo directory=dir dumpfile=exptable.dmp tables=jk
Export: Release 12.2.0.1.0 – Production on Mon Jan 20 09:01:55 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ directory=dir dumpfile=exptable.dmp tables=jk
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.273 KB 32 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/exptable.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jan 20 09:02:23 2020 elapsed 0 00:00:23
[oracle@trichydoyen ~]$ impdp remo/remo directory=dir dumpfile=exptable.dmp table_exists_action=replace remap_tablespace=inba:system
Import: Release 12.2.0.1.0 – Production on Mon Jan 20 09:04:02 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_FULL_01″: remo/ directory=dir dumpfile=exptable.dmp table_exists_action=replace remap_tablespace=inba:system
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”JK” 5.273 KB 32 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “REMO”.”SYS_IMPORT_FULL_01″ successfully completed at Mon Jan 20 09:04:24 2020 elapsed 0 00:00:20
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 20 09:05:38 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select tablespace_name, table_name from dba_tables where table_name=’JK’ and owner=’REMO’;

TABLESPACE_NAME TABLE_NAME

SYSTEM JK
Ref Link:http://www.dba-oracle.com/t_rman_175_impdp_remap_tablespace.htm

TABLE_EXISTS_ACTION Parameter

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
Tells Import what to do if the table it is trying to create already exists.
Syntax and Description
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
The possible values have the following effects:
SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
APPEND loads rows from the source and leaves existing rows unchanged.
TRUNCATE deletes existing rows and then loads rows from the source.
REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
[oracle@doyensys export]$ impdp REMAP_TABLESPACE=inba:jhon DIRECTORY=dir dumpfile=table.dmp TABLE_EXISTS_ACTION=APPEND
Import: Release 12.2.0.1.0 – Production on Sun Jan 19 18:26:10 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: data/pump
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “DATA”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “DATA”.”SYS_IMPORT_FULL_01″: data/ REMAP_TABLESPACE=tabs1:tabs2 DIRECTORY=tablez dumpfile=tabl1020.dmp TABLE_EXISTS_ACTION=APPEND
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “DATA”.”TABL1020″ exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “DATA”.”TABL1020″ 5.085 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “DATA”.”SYS_IMPORT_FULL_01″ successfully completed at Sun Jan 19 18:26:18 2020 elapsed 0 00:00:03

REMAP_TABLE Parameter
SQL> conn remo/remo
Connected.
SQL> select count(*) from jk;

**COUNT(*)**

SQL> host
[oracle@trichydoyen ~]$ expdp remo/remo dumpfile=remo_jk.dmp logfile=remo_jk.log tables=jk
Export: Release 12.2.0.1.0 – Production on Mon Jan 20 09:25:03 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ dumpfile=remo_jk.dmp logfile=remo_jk.log tables=jk
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.273 KB 32 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/remo_jk.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jan 20 09:25:25 2020 elapsed 0 00:00:20
[oracle@trichydoyen ~]$ impdp remo/remo dumpfile=remo_jk.dmp logfile=remo_jk.log remap_table=remo.jk:lks
Import: Release 12.2.0.1.0 – Production on Mon Jan 20 09:30:19 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_FULL_01″: remo/ dumpfile=remo_jk.dmp logfile=remo_jk.log remap_table=remo.jk:lks
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”LKS” 5.273 KB 32 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “REMO”.”SYS_IMPORT_FULL_01″ successfully completed at Mon Jan 20 09:30:36 2020 elapsed 0 00:00:15
[oracle@trichydoyen ~]$ sqlplus remo/remo
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 20 09:30:58 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jan 20 2020 09:30:20 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> select tablespace_name, table_name from dba_tables where table_name=’LKS’ and owner=’REMO’;

TABLESPACE_NAME TABLE_NAME

SYSTEM LKS
Ref Link:http://neeraj-dba.blogspot.com/2011/05/remaptable-parameter-of-data-pump-in.html

SAMPLE Parameter
Let’s say we have a very big table, and for testing purpose, we need only a subset of data i.e to let’s say 10 percent of data from that table, then SAMPLE parameter can be used in EXPDP. We will explain its use in below demo.
SQL> conn remo/remo
Connected.
SQL>
SQL> select count(*) from jk;

**COUNT(*)**

[oracle@trichydoyen ~]$ cat exp_sample.par
dumpfile=remo_jk.dmp
logfile=remo_jk.log
directory=DIR
tables=remo.jk
sample=10

SQL> host
[oracle@trichydoyen ~]$ expdp parfile=exp_sample.par

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 11:07:06 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ parfile=exp_sample.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.062 KB 1 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/remo_jk.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jan 20 11:07:35 2020 elapsed 0 00:00:20

Ref Link:https://dbaclass.com/article/sample-parameter-expdp-export-subset-data/

TRANSPORT_DATAFILES Parameter
SOURCE(DB):
Step-1:SQL> select name from v$datafile;

NAME

/u01/app/oracle/oradata/ORA12C/datafile/o1_mf_system_h1rp08jl_.dbf
/u01/app/oracle/oradata/ORA12C/datafile/o1_mf_sysaux_h1rp1p6l_.dbf
/u01/app/oracle/oradata/ORA12C/datafile/o1_mf_undotbs1_h1rp2hhk_.dbf
/u01/app/oracle/oradata/ORA12C/datafile/jhon0.dbf
/u01/app/oracle/oradata/ORA12C/datafile/o1_mf_users_h1rp2jph_.dbf

SQL> alter tablespace jhon read only;

Tablespace altered.

SQL> host
[oracle@trichydoyen ~]$ cd /u01/app/oracle/oradata/ORA12C/datafile/
[oracle@trichydoyen datafile]$ ls
jhon0.dbf o1_mf_system_h1rp08jl_.dbf o1_mf_undotbs1_h1rp2hhk_.dbf
o1_mf_sysaux_h1rp1p6l_.dbf o1_mf_temp_h1rp4ywq_.tmp o1_mf_users_h1rp2jph_.dbf
[oracle@trichydoyen datafile]$ scp jhon0.dbf oracle@192.168.1.40://u01/app/oracle/oradata/ORCL/datafile/
oracle@192.168.1.40’s password:
jhon0.dbf . 100% 10MB 10.0MB/s 00:00
[oracle@trichydoyen datafile]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 20 17:31:14 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@trichydoyen datafile]$ expdp DUMPFILE=jhon_tab.dmp LOGFILE=jhon_tab.log DIRECTORY=dir TRANSPORT_TABLESPACES=JHON

Export: Release 11.2.0.4.0 – Production on Mon Jan 20 17:33:12 2020

Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″: / AS SYSDBA DUMPFILE=jhon_tab.dmp LOGFILE=jhon_tab.log DIRECTORY=dir TRANSPORT_TABLESPACES=JHON
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/exportfile/jhon_tab.dmp

Datafiles required for transportable tablespace JHON:
/u01/app/oracle/oradata/ORA12C/datafile/jhon0.dbf
Job “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at Mon Jan 20 17:34:09 2020
Step-2:
[oracle@trichydoyen exportfile]$ ls
jhon_tab.dmp jhon_tab.log
[oracle@trichydoyen exportfile]$ scp jhon_tab.dmp oracle@192.168.1.40:/u01/exportfile/
oracle@192.168.1.40’s password:
jhon_tab.dmp 100% 136KB 136.0KB/s 00:00
[oracle@trichydoyen exportfile]$ scp jhon_tab.log oracle@192.168.1.40:/u01/exportfile/
oracle@192.168.1.40’s password:
jhon_tab.log 100% 1061 1.0KB/s 00:00
[oracle@trichydoyen exportfile]$
TARGET (DB):
SQL> host
[oracle@trichydoyen datafile]$ impdp directory=DIR DUMPFILE=jhon_tab.dmp transport_datafiles=’/u01/app/oracle/oradata/ORCL/datafile/jhon0.dbf’ logfile=jhon_tab.log

Import: Release 12.2.0.1.0 – Production on Mon Jan 20 12:23:31 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″: / AS SYSDBA directory=DIR DUMPFILE=jhon_tab.dmp transport_datafiles=/u01/app/oracle/oradata/ORCL/datafile/jhon0.dbf logfile=jhon_tab.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at Mon Jan 20 12:23:41 2020 elapsed 0 00:00:03
[oracle@trichydoyen datafile]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 20 12:24:16 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> select name from v$datafile;

NAME

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_h1lpjm44_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_h1lpl0nb_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_h1lplsxv_.dbf
/u01/app/oracle/oradata/ORCL/datafile/jhon0.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_h1lplv5f_.dbf

Ref Link:https://oracledbwr.com/oracle-18c-datapump-transport_datafiles-parameter/

DUMPFILE Parameter
REMAP_SCHEMA Parameter
SQL> show user
USER is “REMO”
SQL> host
[oracle@trichydoyen ~]$ expdp schemas=remo directory=dir dumpfile=remo_remap.dmp logfile=remo_remap.log
Export: Release 12.2.0.1.0 – Production on Mon Jan 20 10:17:03 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: / AS SYSDBA schemas=remo directory=dir dumpfile=remo_remap.dmp logfile=remo_remap.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
. . exported “REMO”.”JK” 5.273 KB 32 rows
. . exported “REMO”.”BB” 0 KB 0 rows
. . exported “REMO”.”EMP” 0 KB 0 rows
. . exported “REMO”.”JKS” 5.085 KB 4 rows
. . exported “REMO”.”LKS” 5.273 KB 32 rows
. . exported “REMO”.”TBL_TEST” 5.507 KB 3 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/remo_remap.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Mon Jan 20 10:17:53 2020 elapsed 0 00:00:39
[oracle@trichydoyen ~]$ impdp directory=dir dumpfile=remo_remap.dmp logfile=remo_remap.log remap_schema=remo:ton
Import: Release 12.2.0.1.0 – Production on Mon Jan 20 10:19:51 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: / AS SYSDBA directory=dir dumpfile=remo_remap.dmp logfile=remo_remap.log remap_schema=remo:ton
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “TON”.”JK” 5.273 KB 32 rows
. . imported “TON”.”BB” 0 KB 0 rows
. . imported “TON”.”EMP” 0 KB 0 rows
. . imported “TON”.”JKS” 5.085 KB 4 rows
. . imported “TON”.”LKS” 5.273 KB 32 rows
. . imported “TON”.”TBL_TEST” 5.507 KB 3 rows
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYS”.”SYS_IMPORT_FULL_01″ successfully completed at Mon Jan 20 10:20:19 2020 elapsed 0 00:00:19

[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQLPlus: Release 12.2.0.1.0 Production on Mon Jan 20 10:21:13 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production SQL> conn remo/remo Connected. SQL> conn sys/dba as sysdba Connected. SQL> alter user ton identified by ton; User altered. SQL> conn ton/ton Connected. SQL> select count(),owner from all_objects where owner in (‘REMO’,’TON’) GROUP BY OWNER;

COUNT(*) OWNER

 9 REMO
 9 TON

SQL> select count(*) from jk;

**COUNT(*)**

Ref Link:http://www.acehints.com/2012/05/data-pump-impdp-remapschema-parameter.html

FILESIZE Parameter
SQL> conn remo/remo
Connected.
SQL> create tablespace inba datafile ‘/u01/app/oracle/oradata/ORCL/datafile/inba01.dbf’ size 1m;

Tablespace created.
SQL> alter tablespace inba add datafile ‘/u01/app/oracle/oradata/ORCL/datafile/inba02.dbf’ size 1m;

Tablespace altered.
SQL> alter tablespace inba add datafile ‘/u01/app/oracle/oradata/ORCL/datafile/inba03.dbf’ size 1m;
Sql> alter tablespace inba add datafile ‘/u01/app/oracle/oradata/ORCL/datafile/inba04.dbf’ size 3m;

Tablespace altered.
SQL> select username,default_tablespace from dba_users where oracle_maintained=’N’;

USERNAME DEFAULT_TABLESPACE

TON INBA
REMO INBA
TOM USERS
SQL> conn remo/remo
Connected.
SQL> select count(*) from ds;

**COUNT(*)**

[oracle@trichydoyen ~]$ expdp remo tables=ds directory=dir dumpfile=remo_inba01.dmp,remo_inba02.dmp,remo_inba03.dmp, remo_inba04.dmp filesize=6m

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 13:52:15 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ tables=ds directory=dir dumpfile=remo_inba01.dmp,remo_inba02.dmp,remo_inba03.dmp, remo_inba04.dmp filesize=6m
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”DS” 5.164 KB 16 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/remo_inba01.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jan 20 13:52:45 2020 elapsed 0 00:00:22

[oracle@trichydoyen ~]$ expdp remo tables=ds directory=dir dumpfile=remo_inba01.dmp,remo_inba02.dmp,remo_inba03.dmp, remo_inba04.dmp filesize=4k

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 13:56:46 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31618: FILESIZE is outside valid range of 40KB to 16TB

Ref Link:https://oracledbwr.com/oracle-18c-datapump-filesize-parameter/
http://www.dba-oracle.com/t_rman_168_expdp_filesize.htm

ATTACH Parameter
Occasionally we might want to kill a long running Oracle import jobs ,we do it using kill -p But when this (kill -p) is done for a datapump job, it doesn’t completely kill the job and locks up the underlying db objects. To resolve this, you must attach to a datapump job and use kill_job.

Ref Link:http://gliuoracle.blogspot.com/2017/07/attach-to-impdp-expdp-running-job-and.html
http://www.online-database.eu/administration/182-datapump-feature-attach-to-and-manage-a-running-datapump-job

HELP Parameter
[oracle@trichydoyen ~]$ expdp help=y (or) impdp help=y

Export: Release 11.2.0.4.0 – Production on Sat Jan 25 13:45:00 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the ‘expdp’ command followed
by various parameters. To specify parameters, you use keywords:
Format: expdp KEYWORD=value or KEYWORD=(value1,value2,…,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.

The available keywords and their descriptions follow. Default values are listed within square brackets.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC.
Valid keyword values are: [Y] and N.
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keyword values are: XML_CLOBS.

DIRECTORY
Directory object to be used for dump and log files.

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

ESTIMATE_ONLY
Calculate job estimates without performing the export.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:”=’HR'”.

FILESIZE
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Export entire database [N].

HELP
Display Help messages [N].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of export job to create.

LOGFILE
Specify log file name [export.log].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [N].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:”WHERE department_id > 10″.

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

SAMPLE
Percentage of data to be exported.

SCHEMAS
List of schemas to export [login schema].

SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.

SOURCE_EDITION
Edition to be used for extracting metadata.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to export.

TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.

DIRECTORY Parameter
CONTENT Parameter
The CONTENT parameter allows you to alter the contents of the export. The following command uses the METADATA_ONLY parameter value to export the contents of the schema without the data.
[oracle@trichydoyen ~]$ expdp schemas=REMO directory=dir dumpfile=remo_meta.dmp logfile=remo_meta.log content=METADATA_ONLY

SQL> drop user remo cascade;

[oracle@trichydoyen ~]$ impdp schemas=REMO directory=dir dumpfile=remo_meta.dmp logfile=remo_meta.log content=METADATA_ONLY

SQL> conn remo/remo
Connected.
SQL> select count(*) from jk;

COUNT(*)

NOLOGFILE Parameter
[oracle@trichydoyen ~]$ expdp directory=dir dumpfile=kill.dmp nologfile=yes tables=remo.jk

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 16:33:01 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ directory=dir dumpfile=kill.dmp nologfile=yes tables=remo.jk
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.273 KB 32 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/kill.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Jan 21 16:33:25 2020 elapsed 0 00:00:19

LOGFILE Parameter
[oracle@trichydoyen ~]$ expdp directory=dir dumpfile=kill.dmp logfile=lks.log tables=remo.jk

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 16:36:49 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ directory=dir dumpfile=kill.dmp logfile=lks.log tables=remo.jk
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.273 KB 32 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/kill.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Jan 21 16:37:15 2020 elapsed 0 00:00:19

VERSION Parameter
Step1:Ora12c.
SQL>conn joe/joe
Connected.
SQL> select count(*) from dpt;

**COUNT(*)**

SQL> host
[oracle@trichydoyen ~]$ expdp joe/joe TABLES=joe.dpt VERSION=11.2.0.4.0 DIRECTORY=dir DUMPFILE=joe_dpt.dmp LOGFILE=joe_dpt.log

Export: Release 11.2.0.4.0 – Production on Mon Jan 20 21:41:37 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.
Starting “JOE”.”SYS_EXPORT_TABLE_01″: joe/ TABLES=joe.dpt VERSION=11.2.0.4.0 DIRECTORY=dir DUMPFILE=joe_dpt.dmp LOGFILE=joe_dpt.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “JOE”.”DPT” 5.109 KB 8 rows
Master table “JOE”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for JOE.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/joe_dpt.dmp
Job “JOE”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jan 20 21:42:10 2020 elapsed 0 00:00:26
Step2:ora11g.
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 20 21:36:51 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create user joe identified by joe;
User created.
SQL> grant connect,resource to joe;
Grant succeeded.

SQL> grant dba to joe;
Grant succeeded.

SQL> grant read, write on directory dir to joe;
Grant succeeded.
SQL> host
[oracle@trichydoyen ~]$ impdp joe/joe TABLES=joe.dpt DIRECTORY=dir DUMPFILE=joe_dpt.dmp LOGFILE=joe_dpt.log

Import: Release 11.2.0.4.0 – Production on Mon Jan 20 21:48:13 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “JOE”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “JOE”.”SYS_IMPORT_TABLE_01″: joe/ TABLES=joe.dpt DIRECTORY=dir DUMPFILE=joe_dpt.dmp LOGFILE=joe_dpt.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “JOE”.”DPT” 5.109 KB 8 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “JOE”.”SYS_IMPORT_TABLE_01″ successfully completed at Mon Jan 20 21:48:26 2020 elapsed 0 00:00:09

[oracle@trichydoyen ~]$ sqlplus joe/joe

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 20 21:48:44 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from dpt;

**COUNT(*)**

Ref Link:http://www.dba-oracle.com/t_export_import_different_releases_of_oracle.htm

JOB_NAME Parameter

[oracle@trichydoyen ~]$ impdp JOB_NAME=SYS_EXPORT_TABLE_01 DIRECTORY=dir DUMPFILE=kill.dmp logfile=kill.log tables=remo.lks

Import: Release 12.2.0.1.0 – Production on Tue Jan 21 16:08:35 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ JOB_NAME=SYS_EXPORT_TABLE_01 DIRECTORY=dir DUMPFILE=kill.dmp logfile=kill.log tables=remo.lks
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”LKS” 5.273 KB 32 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Jan 21 16:08:54 2020 elapsed 0 00:00:14

CONTINUE_CLIENT Parameter
[oracle@doyensys ~]$ expdp SCHEMAS=REMO DIRECTORY=dir DUMPFILE=SCHEMASDOYEN1.dmp logfile=CHEMASDOYEN1.log

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 18:13:49 2020

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: / AS SYSDBA SCHEMAS=REMO DIRECTORY=tablez DUMPFILE=SCHEMASDOYEN1.dmp logfile=CHEMASDOYEN1.log
^C
Export> CONTINUE_CLIENT
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “REMO”.”SYS_IMPORT_SCHEMA_01″ 502.7 KB 2948 rows
. . exported “REMO”.”SYS_EXPORT_SCHEMA_03″ 670.8 KB 1502 rows
. . exported “REMO”.”SYS_EXPORT_SCHEMA_02″ 268.3 KB 1455 rows
. . exported “REMO”.”SYS_EXPORT_SCHEMA_01″ 310.9 KB 1464 rows
. . exported “REMO”.”SYS_EXPORT_SCHEMA_04″ 238.3 KB 1279 rows
. . exported “REMO”.”SYS_EXPORT_SCHEMA_05″ 238.3 KB 1279 rows
. . exported “REMO”.”T2″ 7.25 KB 81 rows
. . exported “REMO”.”TABLE100″ 0 KB 0 rows
. . exported “REMO”.”T1″ 6.054 KB 10 rows
. . exported “REMO”.”TABLE103″ 0 KB 0 rows
. . exported “REMO”.”KU$VAT_80350″ 0 KB 0 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/export/SCHEMASDOYEN1.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Jan 21 18:16:33 2020 elapsed 0 00:02:39

KILL_JOB Parameter
[oracle@trichydoyen ~]$ expdp directory=dir dumpfile=kill.dmp logfile=kill.log tables=remo.lks

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 15:56:49 2020

Username: remo/remo

UDE-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 1852
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4794
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 6181
ORA-06512: at line 1

STATUS Parameter
[oracle@doyensys ~]$ impdp DIRECTORY=dir SCHEMAS=remo dumpfile=remo.dmp STATUS=10

Worker 1 Status:
Instance ID: 1
Instance name: orcl
Host name: trichydoyen
Access method: external_table
Object start time: Tuesday, 21 January, 2020 14:48:09
Object status at: Tuesday, 21 January, 2020 14:48:10
Process Name: DW00
State: WORK WAITING
ORA-39082: Object type VIEW:”REMO”.”ED_EMP_VIEW_ORABASE” created with compilation warnings

Job: SYS_IMPORT_SCHEMA_02
Operation: IMPORT
Mode: SCHEMA
State: COMPLETED
Bytes Processed: 16,088
Percent Done: 80
Current Parallelism: 1
Job Error Count: 1
Job heartbeat: 1
Dump File: /u01/exportfile/remo.dmp

Worker 1 Status:
Instance ID: 1
Instance name: orcl
Host name: trichydoyen
Access method: external_table
Object start time: Tuesday, 21 January, 2020 14:48:09
Object status at: Tuesday, 21 January, 2020 14:48:10
Process Name: DW00
State: WORK WAITING
Job “SYS”.”SYS_IMPORT_SCHEMA_02″ completed with 1 error(s) at Tue Jan 21 14:48:10 2020 elapsed 0 00:00:20

PARALLEL Parameter
⦁ PARALLEL parameter is used to improve the speed of the export and import.
⦁ The PARALLEL parameter specifies the maximum number of threads of active execution operating on behalf of the export job.Each Data Pump execution thread writes exclusively to one file at a time.
⦁ This PARALLEL option can be used in conjunction with the “%U” wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read. The same wildcard can be used during the import to allow you to reference multiple files.

[oracle@trichydoyen ~]$ expdp DIRECTORY=dir SCHEMAS=remo DUMPFILE=PARALLEL.dmp PARALLEL=4

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 13:32:28 2020

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: / AS SYSDBA DIRECTORY=dir SCHEMAS=remo DUMPFILE=PARALLEL.dmp PARALLEL=4
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/VIEW/VIEW
. . exported “REMO”.”JK” 5.273 KB 32 rows
. . exported “REMO”.”BB” 0 KB 0 rows
. . exported “REMO”.”DS” 5.164 KB 16 rows
. . exported “REMO”.”LKS” 5.273 KB 32 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/PARALLEL.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Jan 21 13:33:36 2020 elapsed 0 00:00:57
Ref Link:http://www.dba-oracle.com/t_rman_170_expdp_parallel.htm

START_JOB Parameter
It will restart the current job.START_JOB=SKIP_CURRENT will start the job after skipping any action which was in progress when job was stopped.
[oracle@18c ~]$ expdp system/oracle attach=SYS_EXPORT_SCHEMA_02
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 21 14:57:28 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Job: SYS_EXPORT_SCHEMA_02
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 73E179EA47021DBEE0536801A8C01560
Start Time: Monday, 20 August, 2018 22:19:03
Mode: SCHEMA
Instance: prod18c
Max Parallelism: 1
Timezone: +00:00
Timezone version: 31
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/ directory=DATA_PUMP_DIR dumpfile=emp_prod18c.dmp logfile=emp_prod18c.log
TRACE 0State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Job heartbeat: 4
Dump File: /u01/app/oracle/admin/prod18c/dpdump/emp_prod18c.dmp
bytes written: 28,672

Worker 1 Status:
Instance ID: 1

Instance name: prod18c
Host name: 18c.localdomain.com
Object start time: Monday, 20 August, 2018 22:04:09
Object status at: Monday, 20 August, 2018 22:04:13
Process Name: DW00
State: UNDEFINED
The above job was stopped,we need to restart the job.
Export> START_JOB

Export> STATUS

Job: SYS_EXPORT_SCHEMA_02
Operation: EXPORT
Mode: SCHEMAState: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Job heartbeat: 1
Dump File: /u01/app/oracle/admin/prod18c/dpdump/emp_prod18c.dmp
bytes written: 32,768

Worker 1 Status:
Instance ID: 1
Instance name: prod18c
Host name: 18c.localdomain.com
Object start time: Monday, 20 August, 2018 22:04:09
Object status at: Monday, 20 August, 2018 22:21:56
Process Name: DW00
State: EXECUTING
Ref Link:https://oracledbwr.com/oracle-18c-datapump-start_job-parameter/

STOP_JOB Parameter
[oracle@doyensys ~]$ impdp directory=dir dumpfile=remo.dmp status=8 nologfile=YES

Import: Release 12.2.0.1.0 – Production on Sun Jan 19 19:07:55 2020

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Job: SYS_IMPORT_FULL_01
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Job heartbeat: 0

PRESS CTRL+C,

Import> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): YES

MASTER_ONLY Parameter
Indicates whether to import just the master table and then stop the job so that the contents of the master table can be examined.

[oracle@trichydoyen ~]$ expdp SCHEMAS=remo DIRECTORY=dir DUMPFILE=master.dmp

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 13:55:57 2020

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: / AS SYSDBA SCHEMAS=remo DIRECTORY=dir DUMPFILE=master.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
. . exported “REMO”.”JK” 5.273 KB 32 rows
. . exported “REMO”.”BB” 0 KB 0 rows
. . exported “REMO”.”DS” 5.164 KB 16 rows
. . exported “REMO”.”LKS” 5.273 KB 32 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/master.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Jan 21 13:56:39 2020 elapsed 0 00:00:36

[oracle@trichydoyen ~]$ impdp SCHEMAS=remo DIRECTORY=dir DUMPFILE=master.dmp MASTER_ONLY=YES

Import: Release 12.2.0.1.0 – Production on Tue Jan 21 13:57:24 2020

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYS”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Job “SYS”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Tue Jan 21 13:57:33 2020 elapsed 0 00:00:02

FLASHBACK_SCN Parameter

Ref Link:https://dbaclass.com/article/flashback-parameter-datapumpexpdp/

SQL> conn remo/remo
Connected.
SQL> select count(*) from jk;

COUNT(*)

SQL> SELECT dbms_flashback.get_system_change_number FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER

SQL> insert into jk select * from jk;

8 rows created.

SQL> select count(*) from jk;

COUNT(*)

SQL> commit;
Commit complete.
[oracle@trichydoyen ~]$ cat exp_flash.par
dumpfile=flash_jk.dmp
logfile=flash_jk.log
directory=DIR
tables=REMO.JK
flashback_scn=2408735
SQL> host
[oracle@trichydoyen ~]$ expdp parfile=exp_flash.par
Export: Release 12.2.0.1.0 – Production on Mon Jan 20 12:37:39 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_TABLE_01″: / AS SYSDBA parfile=exp_flash.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.109 KB 8 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

LOGTIME Parameter
logtime=none: No timestamp information is displayed. (This is the default.) No timestamps on status or log file messages (same as default).
logtime=status: Timestamp messages on status are displayed. Timestamps on status messages only.
logtime=logfile: Same as STATUS, but only displayed for logfile messages. Timestamps on log file messages only.
logtime=all: A combination of STATUS and LOGFILE. Timestamps on both status and log file m[oracle@trichydoyen ~]$ expdp dumpfile=logdump.dmp logfile=viewlog.log directory=dir schemas=remo logtime=ALL
Export: Release 12.2.0.1.0 – Production on Tue Jan 21 09:16:56 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
21-JAN-20 09:17:34.441: Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: / AS SYSDBA dumpfile=logdump.dmp logfile=viewlog.log directory=dir schemas=remo logtime=ALL
21-JAN-20 09:17:40.430: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-JAN-20 09:17:40.864: Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
21-JAN-20 09:17:41.244: Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
21-JAN-20 09:17:47.226: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
21-JAN-20 09:17:47.570: Processing object type SCHEMA_EXPORT/USER
21-JAN-20 09:17:47.628: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
21-JAN-20 09:17:47.688: Processing object type SCHEMA_EXPORT/ROLE_GRANT
21-JAN-20 09:17:47.805: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
21-JAN-20 09:17:48.199: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
21-JAN-20 09:17:48.313: Processing object type SCHEMA_EXPORT/DB_LINK
21-JAN-20 09:17:54.238: Processing object type SCHEMA_EXPORT/TABLE/TABLE
21-JAN-20 09:17:57.652: Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
21-JAN-20 09:17:58.574: Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
21-JAN-20 09:18:00.766: Processing object type SCHEMA_EXPORT/VIEW/VIEW
21-JAN-20 09:18:06.659: . . exported “REMO”.”JK” 5.273 KB 32 rows
21-JAN-20 09:18:06.668: . . exported “REMO”.”BB” 0 KB 0 rows
21-JAN-20 09:18:06.700: . . exported “REMO”.”DS” 5.164 KB 16 rows
21-JAN-20 09:18:06.729: . . exported “REMO”.”LKS” 5.273 KB 32 rows
21-JAN-20 09:18:08.445: Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
21-JAN-20 09:18:08.469:
21-JAN-20 09:18:08.476: Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
21-JAN-20 09:18:08.480: /u01/exportfile/logdump.dmp
21-JAN-20 09:18:08.536: Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Jan 21 09:18:08 2020 elapsed 0 00:00:35
Ref Link:https://dbaclass.com/article/logtimeall-parameter-in-datapump-of-oracle-12c/
REUSE_DUMPFILES Parameter
f we try to export a dumpfile with the name, which is already present in that directory. then we will get error like ORA-27038: created file already exists
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file “/export/home/oracle/dbaclass_estim.dmp“
ORA-27038: created file already exists
[oracle@trichydoyen ~]$ expdp dumpfile=reuse_dump.dmp logfile=reuse_dump.log directory=DIR tables=remo.lks REUSE_DUMPFILES=Y
Export: Release 12.2.0.1.0 – Production on Tue Jan 21 09:35:04 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_TABLE_01″: / AS SYSDBA dumpfile=reuse_dump.dmp logfile=reuse_dump.log directory=DIR tables=remo.lks REUSE_DUMPFILES=Y
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”LKS” 5.273 KB 32 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/reuse_dump.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Jan 21 09:35:30 2020 elapsed 0 00:00:18
Ref Link:https://dbaclass.com/article/reuse_dumpfiles-parameter-expdp/

FLASHBACK_TIME Parameter
SQL> conn remo/remo
Connected.
SQL> select count(*) from jk;

COUNT(*)

SQL> SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP)

SQL> SELECT SCN_TO_TIMESTAMP(2411422) FROM dual;

SCN_TO_TIMESTAMP(2411422)

20-JAN-20 12.52.09.000000000 PM

SQL> insert into jk select * from jk;

16 rows created.
SQL> select count(*) from jk;

COUNT(*)

SQL> commit;
Commit complete.
SQL> host
[oracle@trichydoyen ~]$ expdp parfile=exp_flash.par

Export: Release 12.2.0.1.0 – Production on Mon Jan 20 12:59:13 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYS”.”SYS_EXPORT_TABLE_01″: / AS SYSDBA parfile=exp_flash.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.164 KB 16 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Compression Parameter
Syntax and Description
COMPRESSION=[ALL | DATA_ONLY | METADATA_ONLY | NONE]

ALL enables compression for the entire export operation. The ALL option requires that the Oracle Advanced Compression option be enabled.
DATA_ONLY results in all data being written to the dump file in compressed format. The DATA_ONLY option requires that the Oracle Advanced Compression option be enabled.
METADATA_ONLY results in all metadata being written to the dump file in compressed format. This is the default.
NONE disables compression for the entire export operation
SQL> conn remo/remo
Connected.
SQL> host
[oracle@trichydoyen ~]$ expdp COMPRESSION=METADATA_ONLY DIRECTORY=dir DUMPFILE=compressin_remo.dmp

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 08:37:44 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_01″: remo/ COMPRESSION=METADATA_ONLY DIRECTORY=dir DUMPFILE=compressin_remo.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “REMO”.”JK” 5.273 KB 32 rows
. . exported “REMO”.”BB” 0 KB 0 rows
. . exported “REMO”.”DS” 5.164 KB 16 rows
. . exported “REMO”.”LKS” 5.273 KB 32 rows
Master table “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/compressin_remo.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Jan 21 08:40:13 2020 elapsed 0 00:02:06

[oracle@trichydoyen ~]$ expdp COMPRESSION=ALL DIRECTORY=dir DUMPFILE=compressin_all__remo.dmp

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 08:41:16 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_01″: remo/ COMPRESSION=ALL DIRECTORY=dir DUMPFILE=compressin_all__remo.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “REMO”.”JK” 4.773 KB 32 rows
. . exported “REMO”.”BB” 0 KB 0 rows
. . exported “REMO”.”DS” 4.734 KB 16 rows
. . exported “REMO”.”LKS” 4.742 KB 32 rows
Master table “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/compressin_all__remo.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Jan 21 08:42:12 2020 elapsed 0 00:00:49
[oracle@trichydoyen ~]$ expdp COMPRESSION=NONE DIRECTORY=dir DUMPFILE=compressin_none__remo.dmp

Export: Release 12.2.0.1.0 – Production on Tue Jan 21 08:48:22 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “REMO”.”SYS_EXPORT_SCHEMA_01″: remo/ COMPRESSION=NONE DIRECTORY=dir DUMPFILE=compressin_none__remo.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported “REMO”.”JK” 5.273 KB 32 rows
. . exported “REMO”.”BB” 0 KB 0 rows
. . exported “REMO”.”DS” 5.164 KB 16 rows
. . exported “REMO”.”LKS” 5.273 KB 32 rows
Master table “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/compressin_none__remo.dmp
Job “REMO”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Jan 21 08:49:14 2020 elapsed 0 00:00:44

Content Parameter
The CONTENT parameter allows you to alter the contents of the export. The following command uses the METADATA_ONLY parameter value to export the contents of the schema without the data.
[oracle@trichydoyen ~]$ expdp schemas=REMO directory=dir dumpfile=remo_meta.dmp logfile=remo_meta.log content=METADATA_ONLY

SQL> drop user remo cascade;

[oracle@trichydoyen ~]$ impdp schemas=REMO directory=dir dumpfile=remo_meta.dmp logfile=remo_meta.log content=METADATA_ONLY

SQL> conn remo/remo
Connected.
SQL> select count(*) from jk;

COUNT(*)

Include Parameter
The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import.
[oracle@trichydoyen ~]$ expdp \’/ as sysdba\’ parfile=expfull.par
[oracle@trichydoyen ~]$ cat expfull.par
directory=dir
DUMPFILE=remo_tables.dmp
Logfile=remo_tables.log
SCHEMAS =REMO
INCLUDE=TABLE:”IN (‘JK’, ‘JKS’)”
Exclude Parameter
When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import.
[oracle@trichydoyen ~]$ expdp \’/ as sysdba\’ parfile=exclude.par
[oracle@trichydoyen ~]$ cat exclude.par
directory=dir
DUMPFILE=remo_tables_bb.dmp
Logfile=remo_tables_bb.log
SCHEMAS =REMO
exclude=TABLE:”= ‘BB'”
Network Link

Step1:To Connect TNS and Listener(Source dbuser name:remo)
SQL> host
[oracle@trichydoyen ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/network/admin
[oracle@trichydoyen admin]$ vi tnsnames.ora
[oracle@trichydoyen admin]$ sqlplus remo/remo@192.168.1.40:1521/orcl

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 25 15:42:45 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> show user
USER is “REMO”

Step2:To Connect Targetdb(sysdba)

[oracle@trichydoyen ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 25 16:07:18 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Step3:Grant permission and Connect Targetdb user Joe and create the database link

SQL> grant read,write on directory dir to joe;

Grant succeeded.

SQL> conn joe/joe
Connected.
SQL> create database link remo_link connect to remo identified by remo using ‘orcl’;

Database link created.

SQL> host
[oracle@trichydoyen ~]$ expdp joe/joe directory=dir dumpfile=testt.dmp logfile=testt.log network_link=remo_link

Export: Release 11.2.0.4.0 – Production on Sat Jan 25 16:14:10 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “JOE”.”SYS_EXPORT_SCHEMA_01″: joe/ directory=dir dumpfile=testt.dmp logfile=testt.log network_link=remo_link
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
. . exported “REMO”.”EMP” 6.203 KB 2 rows
. . exported “REMO”.”EMP_EMPLOYEE_E2″ 6.210 KB 1 rows
. . exported “REMO”.”EMP_EMPLOYEE_E1″ 6.210 KB 1 rows
. . exported “REMO”.”DS” 5.593 KB 16 rows
. . exported “REMO”.”JK” 5.703 KB 32 rows
. . exported “REMO”.”BB” 0 KB 0 rows
. . exported “REMO”.”TEST” 5.484 KB 4 rows
Master table “JOE”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for JOE.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/testt.dmp
Job “JOE”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Sat Jan 25 16:15:24 2020 elapsed 0 00:01:13

Step4:Drop the User in Source db and Recreate the Same user

SQL> conn sys/dba as sysdba
Connected.
SQL> drop user remo cascade;

User dropped.
SQL> create user remo identified by remo;

User created.

SQL> grant connect,resource to remo;

Grant succeeded.

SQL> grant dba to remo;

Grant succeeded.

SQL> conn remo/remo
Connected.
SQL>

Step5:Connect Target db and Impdp

[oracle@trichydoyen ~]$ sqlplus remo/remo@192.168.1.40:1521/orcl

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 25 16:56:29 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> show user
USER is “REMO”
SQL> host
[oracle@trichydoyen ~]$ impdp joe/joe directory=dir logfile=testt.log network_link=remo_link

Import: Release 11.2.0.4.0 – Production on Sat Jan 25 16:57:08 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “JOE”.”SYS_IMPORT_SCHEMA_01″: joe/ directory=dir logfile=testt.log network_link=remo_link
Estimate in progress using BLOCKS method…
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “JOE”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Sat Jan 25 16:57:37 2020 elapsed 0 00:00:29

Ref Link:http://oracle-help.com/oracle-11g/network-link-expdp/

Database Link

Step1:Entry of database info in Listener.ora
[oracle@trichydoyen ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/network/admin
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.129)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora12c)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = ora12c)
)
)

Step2:Entry of database info in tnsnames.ora
ora12c=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.129)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =ora12c)
)
)

[oracle@trichydoyen admin]$lsnrctl start,lsnrctl stop,lsnrctl status

[oracle@trichydoyen ~]$ sqlplus system/oracle@ora12c(Using one database to testing)

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 24 20:24:39 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> create user boss identified by boss;

User created.

SQL> grant create table, unlimited tablespace , create session to boss;

Grant succeeded
SQL> conn boss/boss@ora12c
Connected.
SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID

 1
 1
 1

SQL> conn system/oracle@ora12c
Connected.
SQL> create database link db_linker connect to boss identified by boss using ‘ora12c’;

Database link created.

SQL> select * from test@db_linker;

ID

 1
 1
 1

SQL> host
[oracle@trichydoyen ~]$ expdp system/oracle@ora12c directory=dir dumpfile=dblinker.dmp

Export: Release 11.2.0.4.0 – Production on Fri Jan 24 20:53:28 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/@orcl directory=dir dumpfile=dblinker.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/dblinker.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Fri Jan 24 15:25:04 2020 elapsed 0 00:00:34

[oracle@trichydoyen ~]$ sqlplus system/oracle@orcl

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 24 20:54:47 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> drop database link db_linker;

Database link dropped.

SQL> select * from test@db_linker;
select * from test@db_linker
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> host
[oracle@trichydoyen ~]$ impdp system/oracle@ora12c directory=dir dumpfile=dblinker.dmp

Import: Release 11.2.0.4.0 – Production on Fri Jan 24 20:56:10 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/@orcl directory=dir dumpfile=dblinker.dmp
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at Fri Jan 24 15:27:17 2020 elapsed 0 00:00:04

[oracle@trichydoyen ~]$ sqlplus system/oracle@orcl

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 24 20:56:46 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> select * from test@db_linker;

ID

 1
 1
 1

Parfile Parameter
SQL> conn lks/lks
ERROR:
ORA-00942: table or view does not exist
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> select * from tab;

TNAME

TABTYPE CLUSTERID

SALES
TABLE

PRODS
TABLE

STU
TABLE
SQL> select count(*) from stu;

COUNT(*)

SQL> host
[oracle@trichydoyen ~]$ exp file=/u01/exportfile/lks_stu.dmp logfile=/u01/exportfile/lks_stu.log tables=’LKS.STU’
LRM-00101: unknown parameter name ‘logfile’

EXP-00019: failed to process parameters, type ‘EXP HELP=Y’ for help
EXP-00000: Export terminated unsuccessfully
[oracle@trichydoyen ~]$ exp file=/u01/exportfile/lks_stu.dmp log=/u01/exportfile/lks_stu.log tables=’LKS.STU’
Export: Release 12.2.0.1.0 – Production on Mon Jan 6 09:14:26 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved
Username: lks/lks
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
. . exporting table STU 32 rows exported
Export terminated successfully without warnings.
[oracle@trichydoyen ~]$ exp parfile=expfull.par

Export: Release 12.2.0.1.0 – Production on Mon Jan 6 09:17:28 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: lks/lks
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
. . exporting table STU 32 rows exported
Export terminated successfully without warnings.
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 6 09:18:50 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn lks/lks
ERROR:
ORA-00942: table or view does not exist

Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> select count(*) from stu;

COUNT(*)

SQL> drop table stu;

Table dropped.

SQL> select count() from stu; select count() from stu
*
RROR at line 1:
ORA-00942: table or view does not exist

SQL> host
[oracle@trichydoyen ~]$ imp parfile=expfull.par

Import: Release 12.2.0.1.0 – Production on Mon Jan 6 09:20:01 2020

Username: lks/lks

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Export file created by EXPORT:V12.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
IMP-00403:

Warning: This import generated a separate SQL file “/u01/exportfile/lks_stu_sys.sql” which contains DDL that failed due to a privilege issue.

IMP-00029: cannot qualify table name by owner (LKS.STU), use FROMUSER parameter
IMP-00000: Import terminated unsuccessfully
[oracle@trichydoyen ~]$ imp parfile=expfull.par

Import: Release 12.2.0.1.0 – Production on Mon Jan 6 09:25:38 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: lks/lks
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Export file created by EXPORT:V12.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
IMP-00403:
Warning: This import generated a separate SQL file “/u01/exportfile/lks_stu_sys.sql” which contains DDL that failed due to a privilege issue.

. importing LKS’s objects into LKS
. . importing table “STU” 32 rows imported
Import terminated successfully with warnings.
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 6 09:26:04 2020

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn lks/lks
ERROR:
ORA-00942: table or view does not exist

Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> select count(*) from stu;

COUNT(*)

Screen shot:

Query Parameter
SQL> show user
USER is “REMO”
SQL> select * from bb;

SQL> insert into bb values(22);

1 row created.

SQL> insert into bb values(23);

1 row created.

SQL> insert into bb values(24);

1 row created.

SQL> insert into bb values(25);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bb;

ID

SQL> host
[oracle@trichydoyen ~]$ expdp directory=dir dumpfile=remo_bb_row.dmp log=remo_bb_row.log tables=’REMO.BB’ query=\”where id=25\”
Export: Release 12.2.0.1.0 – Production on Sat Jan 11 14:30:53 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Username: remo/remo
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “log=remo_bb_row.log” Location: Command Line, Replaced with: “logfile=remo_bb_row.log”
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting “REMO”.”SYS_EXPORT_TABLE_01″: remo/ directory=dir dumpfile=remo_bb_row.dmp logfile=remo_bb_row.log tables=REMO.BB query=”where id=25″ reuse_dumpfiles=true
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”BB” 5.062 KB 1 rows
Master table “REMO”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:
/u01/exportfile/remo_bb_row.dmp
Job “REMO”.”SYS_EXPORT_TABLE_01″ successfully completed at Sat Jan 11 14:31:51 2020 elapsed 0 00:00:47
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 11 14:32:00 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn remo/remo
Connected.
SQL> select * from bb;

ID

SQL> delete from bb where id=25;

1 row deleted.

SQL> select * from bb;

ID

22
23
24

SQL> commit;

Commit complete.

SQL> select * from bb;

ID

22
23
24

SQL> host
[oracle@trichydoyen ~]$ impdp directory=dir dumpfile=remo_bb_row.dmp fromuser=’REMO’ touser=’REMO’ tables=’BB’

Import: Release 12.2.0.1.0 – Production on Sat Jan 11 14:35:42 2020

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “fromuser=REMO” Location: Command Line, Replaced with: “remap_schema”
Master table “REMO”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_TABLE_01″: remo/ directory=dir dumpfile=remo_bb_row.dmp remap_schema=REMO:REMO tables=BB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table “REMO”.”BB” exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “REMO”.”SYS_IMPORT_TABLE_01″ completed with 1 error(s) at Sat Jan 11 14:35:54 2020 elapsed 0 00:00:03

[oracle@trichydoyen ~]$ impdp directory=dir dumpfile=remo_bb_row.dmp fromuser=’REMO’ touser=’REMO’ tables=’BB’ table_exists_action=APPEND

Import: Release 12.2.0.1.0 – Production on Sat Jan 11 14:42:46 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: remo/remo
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “fromuser=REMO” Location: Command Line, Replaced with: “remap_schema”
Master table “REMO”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “REMO”.”SYS_IMPORT_TABLE_01″: remo/ directory=dir dumpfile=remo_bb_row.dmp remap_schema=REMO:REMO tables=BB table_exists_action=APPEND
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “REMO”.”BB” exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”BB” 5.062 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “REMO”.”SYS_IMPORT_TABLE_01″ successfully completed at Sat Jan 11 14:43:00 2020 elapsed 0 00:00:07

[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn remo/remo
Connected.
SQL> select * from bb;

ID

Screen Shot:

(Error)-Ref Link:https://dbaclass.com/article/table_exists_action-option-impdp/

Database Level
Full Database Level Expdb&Impdp

Step1:Expdp in full database

SQL> conn sys/dba as sysdba
Connected.
SQL> host
[oracle@trichydoyen ~]$ expdp directory=dir dumpfile=full_orcl.dmp log=full_orcl.log full=y

Export: Release 12.2.0.1.0 – Production on Sat Jan 11 15:06:06 2020

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “log=full_orcl.log” Location: Command Line, Replaced with: “logfile=full_orcl.log”
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting “SYS”.”SYS_EXPORT_FULL_01″: / AS SYSDBA directory=dir dumpfile=full_orcl.dmp logfile=full_orcl.log full=y reuse_dumpfiles=true
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported “SYS”.”KU$_USER_MAPPING_VIEW” 6.101 KB 39 rows
. . exported “SYSTEM”.”REDO_DB” 25.59 KB 1 rows
. . exported “ORDDATA”.”ORDDCM_DOCS” 252.9 KB 9 rows
. . exported “WMSYS”.”WM$WORKSPACES_TABLE$” 12.10 KB 1 rows
. . exported “WMSYS”.”WM$HINT_TABLE$” 9.984 KB 97 rows
. . exported “LBACSYS”.”OLS$INSTALLATIONS” 6.960 KB 2 rows
. . exported “WMSYS”.”WM$WORKSPACE_PRIV_TABLE$” 7.078 KB 11 rows
. . exported “SYS”.”DAM_CONFIG_PARAM$” 6.531 KB 14 rows
. . exported “SYS”.”TSDP_SUBPOL$” 6.328 KB 1 rows
. . exported “WMSYS”.”WM$NEXTVER_TABLE$” 6.375 KB 1 rows
. . exported “LBACSYS”.”OLS$PROPS” 6.234 KB 5 rows
. . exported “WMSYS”.”WM$ENV_VARS$” 6.015 KB 3 rows
. . exported “SYS”.”TSDP_PARAMETER$” 5.953 KB 1 rows
. . exported “SYS”.”TSDP_POLICY$” 5.921 KB 1 rows
. . exported “WMSYS”.”WM$VERSION_HIERARCHY_TABLE$” 5.984 KB 1 rows
. . exported “WMSYS”.”WM$EVENTS_INFO$” 5.812 KB 12 rows
. . exported “LBACSYS”.”OLS$AUDIT_ACTIONS” 5.757 KB 8 rows
. . exported “LBACSYS”.”OLS$DIP_EVENTS” 5.539 KB 2 rows
. . exported “LBACSYS”.”OLS$AUDIT” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$COMPARTMENTS” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$DIP_DEBUG” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$GROUPS” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$LAB” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$LEVELS” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$POL” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$POLICY_ADMIN” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$POLS” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$POLT” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$PROFILE” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$PROFILES” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$PROG” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$SESSINFO” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$USER” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$USER_COMPARTMENTS” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$USER_GROUPS” 0 KB 0 rows
. . exported “LBACSYS”.”OLS$USER_LEVELS” 0 KB 0 rows
. . exported “SYS”.”AUD$” 0 KB 0 rows
. . exported “SYS”.”DAM_CLEANUP_EVENTS$” 0 KB 0 rows
. . exported “SYS”.”DAM_CLEANUP_JOBS$” 0 KB 0 rows
. . exported “SYS”.”TSDP_ASSOCIATION$” 0 KB 0 rows
. . exported “SYS”.”TSDP_CONDITION$” 0 KB 0 rows
. . exported “SYS”.”TSDP_FEATURE_POLICY$” 0 KB 0 rows
. . exported “SYS”.”TSDP_PROTECTION$” 0 KB 0 rows
. . exported “SYS”.”TSDP_SENSITIVE_DATA$” 0 KB 0 rows
. . exported “SYS”.”TSDP_SENSITIVE_TYPE$” 0 KB 0 rows
. . exported “SYS”.”TSDP_SOURCE$” 0 KB 0 rows
. . exported “SYSTEM”.”REDO_LOG” 0 KB 0 rows
. . exported “WMSYS”.”WM$BATCH_COMPRESSIBLE_TABLES$” 0 KB 0 rows
. . exported “WMSYS”.”WM$CONSTRAINTS_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$CONS_COLUMNS$” 0 KB 0 rows
. . exported “WMSYS”.”WM$LOCKROWS_INFO$” 0 KB 0 rows
. . exported “WMSYS”.”WM$MODIFIED_TABLES$” 0 KB 0 rows
. . exported “WMSYS”.”WM$MP_GRAPH_WORKSPACES_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$MP_PARENT_WORKSPACES_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$NESTED_COLUMNS_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$RESOLVE_WORKSPACES_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$RIC_LOCKING_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$RIC_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$RIC_TRIGGERS_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$UDTRIG_DISPATCH_PROCS$” 0 KB 0 rows
. . exported “WMSYS”.”WM$UDTRIG_INFO$” 0 KB 0 rows
. . exported “WMSYS”.”WM$VERSION_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$VT_ERRORS_TABLE$” 0 KB 0 rows
. . exported “WMSYS”.”WM$WORKSPACE_SAVEPOINTS_TABLE$” 0 KB 0 rows
. . exported “MDSYS”.”RDF_PARAM$” 6.515 KB 3 rows
. . exported “SYS”.”AUDTAB$TBS$FOR_EXPORT” 5.953 KB 2 rows
. . exported “SYS”.”DBA_SENSITIVE_DATA” 0 KB 0 rows
. . exported “SYS”.”DBA_TSDP_POLICY_PROTECTION” 0 KB 0 rows
. . exported “SYS”.”FGA_LOG$FOR_EXPORT” 0 KB 0 rows
. . exported “SYS”.”NACL$_ACE_EXP” 0 KB 0 rows
. . exported “SYS”.”NACL$_HOST_EXP” 6.976 KB 2 rows
. . exported “SYS”.”NACL$_WALLET_EXP” 0 KB 0 rows
. . exported “SYS”.”SQL$TEXT_DATAPUMP” 0 KB 0 rows
. . exported “SYS”.”SQL$_DATAPUMP” 0 KB 0 rows
. . exported “SYS”.”SQLOBJ$AUXDATA_DATAPUMP” 0 KB 0 rows
. . exported “SYS”.”SQLOBJ$DATA_DATAPUMP” 0 KB 0 rows
. . exported “SYS”.”SQLOBJ$PLAN_DATAPUMP” 0 KB 0 rows
. . exported “SYS”.”SQLOBJ$_DATAPUMP” 0 KB 0 rows
. . exported “SYSTEM”.”SCHEDULER_JOB_ARGS” 8.671 KB 4 rows
. . exported “SYSTEM”.”SCHEDULER_PROGRAM_ARGS” 10.29 KB 23 rows
. . exported “WMSYS”.”WM$EXP_MAP” 7.718 KB 3 rows
. . exported “WMSYS”.”WM$METADATA_MAP” 0 KB 0 rows
. . exported “JOE”.”BB” 5.085 KB 4 rows
. . exported “REMO”.”BB” 5.085 KB 4 rows
. . exported “TOM”.”BB” 5.085 KB 4 rows
Master table “SYS”.”SYS_EXPORT_FULL_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/u01/exportfile/full_orcl.dmp
Job “SYS”.”SYS_EXPORT_FULL_01″ successfully completed at Sat Jan 11 15:08:47 2020 elapsed 0 00:02:31

Step2:Impdp in full database(Impdp in another Database):

Ref Link:https://dbaclass.com/article/full-database-export-import-using-datapump-utility/

[oracle@trichydoyen exportfile]$ scp full_orcl.dmp oracle@192.168.1.1:/u01/exportfile
The authenticity of host ‘192.168.1.1 (192.168.1.1)’ can’t be established.
RSA key fingerprint is ea:64:be:a1:f4:ac:30:cb:0f:94:cb:46:11:fb:80:ab.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.1.1’ (RSA) to the list of known hosts.
oracle@192.168.1.1’s password:
full_orcl.dmp 100% 3568KB 3.5MB/s 00:01

[oracle@trichydoyen exportfile]$ scp full_orcl.log oracle@192.168.1.1:/u01/exportfileoracle
@192.168.1.1’s password:
full_orcl.log 100% 10KB 9.8KB/s 00:00

[oracle@oracle ~]$ export $ORACLE_SID=cdb
[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 11 10:43:58 2020

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 8620080 bytes
Variable Size 436209616 bytes
Database Buffers 754974720 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> grant exp_full_database,imp_full_database to sys,system;

Grant succeeded.

SQL> select DIRECTORY_NAME from dba_directories;

DIRECTORY_NAME

PAREXP
EXPORT_DIR
EXP_DIR
XMLDIR
XSDDIR
ORA_DBMS_FCP_LOGDIR
ORA_DBMS_FCP_ADMINDIR
OPATCH_INST_DIR
ORACLE_OCM_CONFIG_DIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR2

DIRECTORY_NAME

OPATCH_SCRIPT_DIR
OPATCH_LOG_DIR
ORACLE_BASE
ORACLE_HOME

15 rows selected.

SQL> create directory dir as ‘/u01/exportfile’;

Directory created.

SQL> host
[oracle@oracle ~]$ impdp directory=dir full=y dumpfile=full_orcl.dmp log=full_orcl.log
Import: Release 12.2.0.1.0 – Production on Sat Jan 11 10:53:28 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “log=full_orcl.log” Location: Command Line, Replaced with: “logfile=full_orcl.log”
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: / AS SYSDBA directory=dir full=y dumpfile=full_orcl.dmp logfile=full_orcl.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:”UNDOTBS1″ already exists

ORA-31684: Object type TABLESPACE:”TEMP” already exists

ORA-31684: Object type TABLESPACE:”USERS” already exists

Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
ORA-31685: Object type USER:”SYS” failed due to insufficient privileges. Failing sql is:
ALTER USER “SYS” IDENTIFIED BY VALUES ‘S:24A44D99B79411BC0EB028FD6CC1A76006704BC08C4C6FDE97AFEB7DBAE3;T:F03B162DD4553424D82897A36128B646B05EB488CD45A73A41DF50723C2F9E4CBA7122ECD06C41FC8E1016AB47B3150495DC3D6708AB32DD219E25B1980F8FED4D121B65C3D58AB95C9B21597EC4572C’ TEMPORARY TABLESPACE “TEMP”

Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
ORA-31684: Object type DIRECTORY:”DIR” already exists

Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported “SYS”.”KU$_EXPORT_USER_MAP” 6.101 KB 39 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported “SYSTEM”.”REDO_DB_TMP” 25.59 KB 1 rows
. . imported “ORDDATA”.”ORDDCM_DOCS_TRANSIENT” 252.9 KB 9 rows
. . imported “WMSYS”.”E$WORKSPACES_TABLE$” 12.10 KB 1 rows
. . imported “WMSYS”.”E$HINT_TABLE$” 9.984 KB 97 rows
. . imported “WMSYS”.”E$WORKSPACE_PRIV_TABLE$” 7.078 KB 11 rows
. . imported “SYS”.”AMGT$DP$DAM_CONFIG_PARAM$” 6.531 KB 14 rows
. . imported “SYS”.”DP$TSDP_SUBPOL$” 6.328 KB 1 rows
. . imported “WMSYS”.”E$NEXTVER_TABLE$” 6.375 KB 1 rows
. . imported “WMSYS”.”E$ENV_VARS$” 6.015 KB 3 rows
. . imported “SYS”.”DP$TSDP_PARAMETER$” 5.953 KB 1 rows
. . imported “SYS”.”DP$TSDP_POLICY$” 5.921 KB 1 rows
. . imported “WMSYS”.”E$VERSION_HIERARCHY_TABLE$” 5.984 KB 1 rows
. . imported “WMSYS”.”E$EVENTS_INFO$” 5.812 KB 12 rows
. . imported “LBACSYS”.”OLS_DP$OLS$AUDIT” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$COMPARTMENTS” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$GROUPS” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$LAB” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$LEVELS” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$POL” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$POLS” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$POLT” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$PROFILE” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$PROG” 0 KB 0 rows
. . imported “LBACSYS”.”OLS_DP$OLS$USER” 0 KB 0 rows
. . imported “SYS”.”AMGT$DP$AUD$” 0 KB 0 rows
. . imported “SYS”.”AMGT$DP$DAM_CLEANUP_EVENTS$” 0 KB 0 rows
. . imported “SYS”.”AMGT$DP$DAM_CLEANUP_JOBS$” 0 KB 0 rows
. . imported “SYS”.”DP$TSDP_ASSOCIATION$” 0 KB 0 rows
. . imported “SYS”.”DP$TSDP_CONDITION$” 0 KB 0 rows
. . imported “SYS”.”DP$TSDP_FEATURE_POLICY$” 0 KB 0 rows
. . imported “SYS”.”DP$TSDP_PROTECTION$” 0 KB 0 rows
. . imported “SYS”.”DP$TSDP_SENSITIVE_DATA$” 0 KB 0 rows
. . imported “SYS”.”DP$TSDP_SENSITIVE_TYPE$” 0 KB 0 rows
. . imported “SYS”.”DP$TSDP_SOURCE$” 0 KB 0 rows
. . imported “SYSTEM”.”REDO_LOG_TMP” 0 KB 0 rows
. . imported “WMSYS”.”E$BATCH_COMPRESSIBLE_TABLES$” 0 KB 0 rows
. . imported “WMSYS”.”E$CONSTRAINTS_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$CONS_COLUMNS$” 0 KB 0 rows
. . imported “WMSYS”.”E$LOCKROWS_INFO$” 0 KB 0 rows
. . imported “WMSYS”.”E$MODIFIED_TABLES$” 0 KB 0 rows
. . imported “WMSYS”.”E$MP_GRAPH_WORKSPACES_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$MP_PARENT_WORKSPACES_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$NESTED_COLUMNS_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$RESOLVE_WORKSPACES_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$RIC_LOCKING_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$RIC_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$RIC_TRIGGERS_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$UDTRIG_DISPATCH_PROCS$” 0 KB 0 rows
. . imported “WMSYS”.”E$UDTRIG_INFO$” 0 KB 0 rows
. . imported “WMSYS”.”E$VERSION_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$VT_ERRORS_TABLE$” 0 KB 0 rows
. . imported “WMSYS”.”E$WORKSPACE_SAVEPOINTS_TABLE$” 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported “MDSYS”.”RDF_PARAM$TBL” 6.515 KB 3 rows
. . imported “SYS”.”AMGT$DP$AUDTAB$TBS$FOR_EXPORT” 5.953 KB 2 rows
. . imported “SYS”.”DP$DBA_SENSITIVE_DATA” 0 KB 0 rows
. . imported “SYS”.”DP$DBA_TSDP_POLICY_PROTECTION” 0 KB 0 rows
. . imported “SYS”.”AMGT$DP$FGA_LOG$FOR_EXPORT” 0 KB 0 rows
. . imported “SYS”.”NACL$_ACE_IMP” 0 KB 0 rows
. . imported “SYS”.”NACL$_HOST_IMP” 6.976 KB 2 rows
. . imported “SYS”.”NACL$_WALLET_IMP” 0 KB 0 rows
. . imported “SYS”.”DATAPUMP$SQL$TEXT” 0 KB 0 rows
. . imported “SYS”.”DATAPUMP$SQL$” 0 KB 0 rows
. . imported “SYS”.”DATAPUMP$SQLOBJ$AUXDATA” 0 KB 0 rows
. . imported “SYS”.”DATAPUMP$SQLOBJ$DATA” 0 KB 0 rows
. . imported “SYS”.”DATAPUMP$SQLOBJ$PLAN” 0 KB 0 rows
. . imported “SYS”.”DATAPUMP$SQLOBJ$” 0 KB 0 rows
. . imported “SYSTEM”.”SCHEDULER_JOB_ARGS_TMP” 8.671 KB 4 rows
. . imported “SYSTEM”.”SCHEDULER_PROGRAM_ARGS_TMP” 10.29 KB 23 rows
. . imported “WMSYS”.”E$EXP_MAP” 7.718 KB 3 rows
. . imported “WMSYS”.”E$METADATA_MAP” 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported “JOE”.”BB” 5.085 KB 4 rows
. . imported “REMO”.”BB” 5.085 KB 4 rows
. . imported “TOM”.”BB” 5.085 KB 4 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job “SYS”.”SYS_IMPORT_FULL_01″ completed with 5 error(s) at Sat Jan 11 10:56:33 2020 elapsed 0 00:02:56

[oracle@oracle ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 11 10:59:43 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn remo/remo
Connected.
SQL> select * from bb;

ID

SQL> conn sys/dba as sysdba
Connected.
SQL> conn tom/tom
Connected.
SQL> conn sys/dba as sysdba
Connected.
SQL> select name from v$datafile;

NAME

/u01/app/oracle/oradata/CDB/datafile/o1_mf_system_gyqmt56v_.dbf
/u01/app/oracle/product/12.2.0.1/db_1/dbs/sam_data.dbf
/u01/app/oracle/oradata/CDB/datafile/o1_mf_sysaux_gyqmvx9t_.dbf
/u01/app/oracle/oradata/CDB/datafile/o1_mf_undotbs1_gyqmwpl5_.dbf
/u01/app/oracle/product/12.2.0.1/db_1/dbs/tbs1_data.dbf
/u01/app/oracle/oradata/CDB/datafile/o1_mf_users_gyqmwqo7_.dbf
/u01/app/oracle/product/12.2.0.1/db_1/dbs/sample1.dbf

7 rows selected.

Screen shot:

Tablespace Level Tablespace Level(exp/imp)

Step1:Create tablespace and Expdp:
SQL> select table_name,tablespace_name from dba_tables where table_name=’BK’;

TABLE_NAME

TABLESPACE_NAME

BK
INBA
SQL> select name from v$datafile;

NAME

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_h1lpjm44_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_h1lpl0nb_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_h1lplsxv_.dbf
/u01/app/oracle/oradata/ORCL/datafile/inba01.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_h1lplv5f_.dbf
SQL> alter user remo default tablespace inba;
User altered.
SQL> conn remo/remo
Connected.
SQL> select * from bb;

ID

SQL> create table jk(id int);

Table created.

SQL> insert into jk values(33);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> select * from jk;

ID

SQL> commit;

Commit complete.
SQL> host
[oracle@trichydoyen ~]$ expdp tablespaces=inba dumpfile=tablespace_inba.dmp log=tablespace_inba.log (Don ‘t mentioned the file location or directory name in Tablespace Level)
Export: Release 12.2.0.1.0 – Production on Sun Jan 12 13:30:16 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “log=tablespace_inba.log” Location: Command Line, Replaced with: “logfile=tablespace_inba.log”
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting “SYS”.”SYS_EXPORT_TABLESPACE_01″: / AS SYSDBA tablespaces=inba dumpfile=tablespace_inba.dmp logfile=tablespace_inba.log reuse_dumpfiles=true
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “REMO”.”JK” 5.093 KB 5 rows
Master table “SYS”.”SYS_EXPORT_TABLESPACE_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/u01/app/oracle/admin/orcl/dpdump/tablespace_inba.dmp
Job “SYS”.”SYS_EXPORT_TABLESPACE_01″ successfully completed at Sun Jan 12 13:30:46 2020 elapsed 0 00:00:23
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 12 13:31:03 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

Step2:Drop the tablespace and recreate the tablespace then Impdp.

SQL> drop tablespace inba including contents and datafiles;
drop tablespace inba including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> show user
USER is “SYS”
SQL> shut abort
ORACLE instance shut down.
SQL> startup force
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 8626192 bytes
Variable Size 583012336 bytes
Database Buffers 239075328 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
SQL> drop tablespace inba including contents and datafiles;

Tablespace dropped.

SQL> conn remo/remo
Connected.
SQL> select * from jk;
select * from jk *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn sys/dba as sysdba
Connected.
SQL> create tablespace inba datafile ‘/u01/app/oracle/oradata/ORCL/datafile/inba01.dbf’ size 10m;
Tablespace created.
SQL> conn remo/remo
Connected.
SQL> select * from jk;
select * from jk *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn sys/dba as sysdba
Connected.
SQL> host
[oracle@trichydoyen ~]$ impdp tablespaces=inba dumpfile=tablespace_inba.dmp log=tablespace_inba.log (Don ‘t mentioned the file location or directory name in Tablespace Level)
Import: Release 12.2.0.1.0 – Production on Sun Jan 12 13:41:58 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “log=tablespace_inba.log” Location: Command Line, Replaced with: “logfile=tablespace_inba.log”
Master table “SYS”.”SYS_IMPORT_TABLESPACE_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_TABLESPACE_01″: / AS SYSDBA tablespaces=inba dumpfile=tablespace_inba.dmp logfile=tablespace_inba.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”JK” 5.093 KB 5 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “SYS”.”SYS_IMPORT_TABLESPACE_01″ successfully completed at Sun Jan 12 13:43:11 2020 elapsed 0 00:00:59
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 12 13:43:22 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn remo/remo
Connected.
SQL> select * from jk;

ID

Screen shot:

Schema Level
User Level Expdp/Impdp

Step1:Tc create user and table:

SQL> conn remo/remo
Connected.
SQL> select count(*) from bb;

COUNT(*)

SQL> host

Step2:Create Directory and grant permission:

SQL>create directory dir as ‘/u01/exportfile’;

SQL>grant exp_full_database,imp_full_database to sys,system,remo;

SQL> grant read,write on directory dir to sys,system,remo;

Grant succeeded.

Step3:Export the Dumpfile:

[oracle@trichydoyen ~]$ expdp directory=dir dumpfile=bkp_remo.dmp log=bkp_remo.log owner=’REMO’
Export: Release 12.2.0.1.0 – Production on Sat Jan 11 12:01:38 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “log=bkp_remo.log” Location: Command Line, Replaced with: “logfile=bkp_remo.log”
Legacy Mode Parameter: “owner=REMO” Location: Command Line, Replaced with: “schemas=REMO”
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: / AS SYSDBA directory=dir dumpfile=bkp_remo.dmp logfile=bkp_remo.log schemas=REMO reuse_dumpfiles=true
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “REMO”.”BB” 5.085 KB 4 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/exportfile/bkp_remo.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Sat Jan 11 12:03:02 2020 elapsed 0 00:01:18
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 11 12:05:30 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn remo/remo
Connected.
SQL> select count(*) from bb;

COUNT(*)

Step4:To Drop the User:

SQL> conn sys/dba as sysdba
Connected.
SQL> drop user remo cascade;
drop user remo cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL> shut abort
ORACLE instance shut down.
SQL> startup force
ORACLE instance started.

Total System Global Area 834666496 bytes
Fixed Size 8626192 bytes
Variable Size 583012336 bytes
Database Buffers 239075328 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
SQL> drop user remo cascade;
User dropped.
SQL> conn remo/remo
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn sys/dba as sysdba
Connected.
SQL> host
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 11 12:10:05 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> create user remo identified by remo;(Don ‘t Recreate the Same user in Ex/Im dump and only Drop the user then importdp)

User created.
SQL> grant connect,resource to remo;
Grant succeeded.
SQL> grant dba to remo;
Grant succeeded.
SQL> host
[oracle@trichydoyen ~]$ impdp directory=dir dumpfile=bkp_remo.dmp log=bkp_remo.log fromuser=remo touser=remo ignore=y
Import: Release 12.2.0.1.0 – Production on Sat Jan 11 12:12:31 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “fromuser=remo” Location: Command Line, Replaced with: “remap_schema”
Legacy Mode Parameter: “ignore=TRUE” Location: Command Line, Replaced with: “table_exists_action=append”
Legacy Mode Parameter: “log=bkp_remo.log” Location: Command Line, Replaced with: “logfile=bkp_remo.log”
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: / AS SYSDBA directory=dir dumpfile=bkp_remo.dmp logfile=bkp_remo.log remap_schema=remo:remo table_exists_action=append
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”REMO” already exists(Because to create the same user in ex/im dump concept but export/import it will be accepted.)

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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”BB” 5.085 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYS”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at Sat Jan 11 12:13:26 2020 elapsed 0 00:00:44
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 11 12:25:42 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> drop user remo cascade;
User dropped.
Step5:Import the Dumpfile:
SQL> host
[oracle@trichydoyen ~]$ impdp directory=dir dumpfile=bkp_remo.dmp log=bkp_remo.log fromuser=remo touser=remo ignore=y
Import: Release 12.2.0.1.0 – Production on Sat Jan 11 12:26:24 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “fromuser=remo” Location: Command Line, Replaced with: “remap_schema”
Legacy Mode Parameter: “ignore=TRUE” Location: Command Line, Replaced with: “table_exists_action=append”
Legacy Mode Parameter: “log=bkp_remo.log” Location: Command Line, Replaced with: “logfile=bkp_remo.log”
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: / AS SYSDBA directory=dir dumpfile=bkp_remo.dmp logfile=bkp_remo.log remap_schema=remo:remo table_exists_action=append
Processing object type SCHEMA_EXPORT/USER
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “REMO”.”BB” 5.085 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYS”.”SYS_IMPORT_FULL_01″ successfully completed at Sat Jan 11 12:26:47 2020 elapsed 0 00:00:16
[oracle@trichydoyen ~]$ !sq
sqlplus / as sysdba
SQLPlus: Release 12.2.0.1.0 Production on Sat Jan 11 12:26:56 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production SQL> conn remo/remo Connected. SQL> select count() from bb;

Export and Import Parameters

Export and Import Parameters

USERNAME

SYS_CONTEXT(‘USERENV’,’CURRENT_EDITION_NAME’)

SYS_CONTEXT(‘USERENV’,’CURRENT_EDITION_NAME’)

PARTITION_NAME

DEFAULT_TABLESPACE

TABLE_NAME

TABLESPACE_NAME

COUNT(*)

COUNT(*)

NAME

NAME

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

GET_SYSTEM_CHANGE_NUMBER

COUNT(*)

COUNT(*)

TIMESTAMP_TO_SCN(SYSTIMESTAMP)

SCN_TO_TIMESTAMP(2411422)

COUNT(*)

COUNT(*)

ID

ID

ID

TNAME

COUNT(*)

COUNT(*)

COUNT(*)

ID

ID

ID

ID

ID

DIRECTORY_NAME

DIRECTORY_NAME

ID

NAME

TABLE_NAME

TABLESPACE_NAME

NAME

ID

ID

ID

COUNT(*)

COUNT(*)

COUNT(*)

Leave a Reply Cancel reply

**COUNT(*)**

**COUNT(*)**

**COUNT(*)**

**COUNT(*)**

**COUNT(*)**

**COUNT(*)**