Export from 11GR2 Oracle Database to pluggable 19c Database(data-pump)

Hello for all In this article we discuss how to import a particular schema from oracle database 11GR2(rac with two nodes) to a pluggable database version 19c Source : -- export import schema --- -- export BARIDIMOD SCHEMA --create file param in the source exp-schema.par --[kamal.khelifi.poste1] ➤ ssh oracle@10.100.0.55 -- Last login: Wed Jan 31 15:47:56 2024 from 192.168.0.55 -- step 1 : create new direcyory [oracle@rac1 ~]$ mkdir sqlldr [oracle@rac1 ~]$ -- EXP Preparation --- step 1 : Grant Read and Write Privileges on The Directory to schema1 user GRANT read, write ON DIRECTORY exp_schema TO schema1; [oracle@rac1 sqlldr]$ sqlplus / as sysdba SQL> GRANT read, write ON DIRECTORY exp_schema TO schema1; Grant succeeded. --- step 2 : create the parm fie vi exp-schema.par DIRECTORY=EXP_SCHEMA DUMPFILE=exp-sch-schema1-010224.dmp LOGFILE=exp-sch-schema1-010224.log SCHEMAS=schema1 METRICS=YES EXCLUDE=STATISTICS PARALLEL=6 [oracle@rac1 sqlldr]$ pwd /home/oracle/sqlldr [oracle@rac1 sqlldr]$ ls exp-schema.par [oracle@rac1 sqlldr]$ cat exp-schema.par DIRECTORY=EXP_SCHEMA DUMPFILE=exp-sch-schema1-010224.dmp LOGFILE=exp-sch-schema1-010224.log SCHEMAS=schema1 METRICS=YES EXCLUDE=STATISTICS PARALLEL=6 --- step3 : execute the exp : expdp schema1/schema1 parfile=exp-schema.par expdp system/atmanager parfile=exp-schema.par [oracle@rac1 sqlldr]$ expdp system/atmanager parfile=exp-schema.par Export: Release 11.2.0.4.0 - Production on Thu Feb 1 09:48:40 2024 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=exp-schema.par Startup took 1 seconds Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Estimated 8 TABLE_DATA objects in 1 seconds Total estimation using BLOCKS method: 1.083 GB Startup took 2 seconds Processing object type SCHEMA_EXPORT/USER Completed 1 USER objects in 0 seconds Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Completed 1 SYSTEM_GRANT objects in 1 seconds Processing object type SCHEMA_EXPORT/ROLE_GRANT Completed 3 ROLE_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Startup took 2 seconds Completed 1 DEFAULT_ROLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Startup took 356 seconds Startup took 356 seconds Startup took 356 seconds Completed 1 TABLESPACE_QUOTA objects in 0 seconds Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed 1 PROCACT_SCHEMA objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Completed 4 SEQUENCE objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 16 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE Completed 8 TABLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 46 OBJECT_GRANT objects in 1 seconds Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Completed 5 INDEX objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Completed 14 CONSTRAINT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Completed 4 TRIGGER objects in 0 seconds . . exported "schema1"."table_BARIDI_ADSL" 536.2 MB 3708280 rows . . exported "schema1"."table_BARIDI_GLTE" 149.7 MB 1029697 rows . . exported "schema1"."LIST_GLTE" 2.481 MB 198992 rows . . exported "schema1"."LIST_GLTE_BKP1" 762.3 KB 59660 rows . . exported "schema1"."table_BARIDI_ADSL_REGENERE" 12.74 KB 1 rows ORA-31693: Table data object "schema1"."table_BARIDI_FTTH" failed to load/unload and is being skipped due to error: ORA-31617: unable to open dump file "/u01/oracle/exp_schema/exp-sch-schema1-010224.dmp" for write ORA-19505: failed to identify file "/u01/oracle/exp_schema/exp-sch-schema1-010224.dmp" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-31693: Table data object "schema1"."table_BARIDI_FACTURE" failed to load/unload and is being skipped due to error: ORA-31617: unable to open dump file "/u01/oracle/exp_schema/exp-sch-schema1-010224.dmp" for write ORA-19505: failed to identify file "/u01/oracle/exp_schema/exp-sch-schema1-010224.dmp" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-31693: Table data object "schema1"."TRANSACTION" failed to load/unload and is being skipped due to error: ORA-31617: unable to open dump file "/u01/oracle/exp_schema/exp-sch-schema1-010224.dmp" for write ORA-19505: failed to identify file "/u01/oracle/exp_schema/exp-sch-schema1-010224.dmp" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Completed 5 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 5 seconds Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/oracle/exp_schema/exp-sch-schema1-010224.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 3 error(s) at Thu Feb 1 09:49:25 2024 elapsed 0 00:00:44 Impdp Fails ORA-31693: Table Data Object Failed To Load/unload And Is Being Skip expdp Fails ORA-31693: Table Data Object Failed To Load/unload And Is Being skipped ORA-31693: Table data object "schema1"."table_BARIDI_ADSL" failed to load/unload and is being skipped due to error: SQL> show parameter /u01/oracle/exp_schema SQL> SQL> show parameter OPEN_CURSORS; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 300 SQL> alter system set OPEN_CURSORS = 500 scope=both; System altered. SQL> show parameter OPEN_CURSORS; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 500 Datapump Export Fails with ORA-31693, ORA-29913, ORA-31617: "unable to open dump file for write" on RAC (Doc ID 751330.1) CAUSE When the job scheduler starts parallel jobs, one or more processes may be executed on another node in the cluster and may not have access to the target directory. Make sure in a RAC environment the target directory is on SHARED storage (ASM/ACFS is very good for this purpose). Try to change the value of PARALLEL clause. (Experiment with value of parallel and see if issue is resolved by changing parallel value). SUGGESTION: You can try from 2-8. [oracle@rac1 sqlldr]$ cp exp-schema.par exp-schema.par.010224 [oracle@rac1 sqlldr]$ pwd /home/oracle/sqlldr -- comment the #PARALLEL=2 [oracle@rac1 sqlldr]$ vi exp-schema.par [oracle@rac1 sqlldr]$ cat exp-schema.par DIRECTORY=EXP_SCHEMA DUMPFILE=exp-sch-schema1-010224.dmp LOGFILE=exp-sch-schema1-010224.log SCHEMAS=schema1 METRICS=YES EXCLUDE=STATISTICS #PARALLEL=2 --- issue resolved [oracle@rac1 sqlldr]$ expdp schema1/schema1 parfile=exp-schema.par Export: Release 11.2.0.4.0 - Production on Thu Feb 1 11:23:07 2024 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "schema1"."SYS_EXPORT_SCHEMA_01": schema1/******** parfile=exp-schema.par Startup took 1 seconds Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Estimated 8 TABLE_DATA objects in 0 seconds Total estimation using BLOCKS method: 1.083 GB Processing object type SCHEMA_EXPORT/USER Completed 1 USER objects in 0 seconds Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Completed 1 SYSTEM_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/ROLE_GRANT Completed 3 ROLE_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Completed 1 DEFAULT_ROLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Completed 1 TABLESPACE_QUOTA objects in 1 seconds Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed 1 PROCACT_SCHEMA objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Completed 4 SEQUENCE objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 16 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE Completed 8 TABLE objects in 9 seconds Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 46 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/COMMENT Completed OBJECT_GRANT objects in seconds Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Completed 5 INDEX objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Completed 14 CONSTRAINT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Completed 4 TRIGGER objects in 1 seconds . . exported "schema1"."table_BARIDI_ADSL" 536.3 MB 3709022 rows . . exported "schema1"."table_BARIDI_GLTE" 149.8 MB 1029800 rows . . exported "schema1"."table_BARIDI_FACTURE" 115.8 MB 1024028 rows . . exported "schema1"."table_BARIDI_FTTH" 122.1 MB 843112 rows . . exported "schema1"."TRANSACTION" 5.273 KB 16 rows . . exported "schema1"."LIST_GLTE" 2.481 MB 198992 rows . . exported "schema1"."LIST_GLTE_BKP1" 762.3 KB 59660 rows . . exported "schema1"."table_BARIDI_ADSL_REGENERE" 12.74 KB 1 rows Completed 8 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 4 seconds Master table "schema1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for schema1.SYS_EXPORT_SCHEMA_01 is: /u01/oracle/exp_schema/exp-sch-schema1-010224.dmp Job "schema1"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Feb 1 11:23:27 2024 elapsed 0 00:00:18 ------------ 2 solution [oracle@rac1 sqlldr]$ vi exp_schema-by-system.par [oracle@rac1 sqlldr]$ cat exp_schema-by-system.par DIRECTORY=EXP_SCHEMA DUMPFILE=exp-sch-schema1-by-system--010224.dmp LOGFILE=exp-sch-schema1-by-system-010224.log SCHEMAS=schema1 METRICS=YES EXCLUDE=STATISTICS #PARALLEL=2 [oracle@rac1 sqlldr]$ pwd /home/oracle/sqlldr [oracle@rac1 sqlldr]$ pwd /home/oracle/sqlldr [oracle@rac1 sqlldr]$ clear [oracle@rac1 sqlldr]$ ls exp_schema-by-system.par exp-schema.par exp-schema.par.010224 [oracle@rac1 sqlldr]$ expdp system/atmanager parfile=exp_schema-by-system.par Export: Release 11.2.0.4.0 - Production on Thu Feb 1 11:28:39 2024 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** parfile=exp_schema-by-system.par Startup took 1 seconds Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Estimated 8 TABLE_DATA objects in 0 seconds Total estimation using BLOCKS method: 1.083 GB Processing object type SCHEMA_EXPORT/USER Completed 1 USER objects in 0 seconds Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Completed 1 SYSTEM_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/ROLE_GRANT Completed 3 ROLE_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Completed 1 DEFAULT_ROLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Completed 1 TABLESPACE_QUOTA objects in 0 seconds Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed 1 PROCACT_SCHEMA objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Completed 4 SEQUENCE objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 16 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE Completed 8 TABLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 46 OBJECT_GRANT objects in 1 seconds Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Completed 5 INDEX objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Completed 14 CONSTRAINT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Completed 4 TRIGGER objects in 0 seconds . . exported "schema1"."table_BARIDI_ADSL" 536.3 MB 3709065 rows . . exported "schema1"."table_BARIDI_GLTE" 149.8 MB 1029809 rows . . exported "schema1"."table_BARIDI_FACTURE" 115.8 MB 1024043 rows . . exported "schema1"."table_BARIDI_FTTH" 122.1 MB 843126 rows . . exported "schema1"."TRANSACTION" 5.273 KB 16 rows . . exported "schema1"."LIST_GLTE" 2.481 MB 198992 rows . . exported "schema1"."LIST_GLTE_BKP1" 762.3 KB 59660 rows . . exported "schema1"."table_BARIDI_ADSL_REGENERE" 12.74 KB 1 rows Completed 8 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 5 seconds Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is: /u01/oracle/exp_schema/exp-sch-schema1-by-system--010224.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Feb 1 11:28:49 2024 elapsed 0 00:00:10 --------- slotion 3 --- create new directory "EXPORT_DIR" in the shared file system NFS /stage -- PARALLEL=6 SQL > select *from all_directories SYS EXPORT_DIR /stage/export [oracle@rac1 stage]$ mkdir export [oracle@rac1 stage]$ chmod 777 export [oracle@rac1 stage]$ cd export [oracle@rac1 export]$ pwd /stage/export GRANT read, write ON DIRECTORY EXPORT_DIR TO schema1; GRANT read, write ON DIRECTORY EXPORT_DIR TO SYSTEM; [oracle@rac1 ~]$ sqlplus / as sysdba SQL> GRANT read, write ON DIRECTORY EXPORT_DIR TO schema1; Grant succeeded. SQL> GRANT read, write ON DIRECTORY EXPORT_DIR TO SYSTEM; Grant succeeded. [oracle@rac1 sqlldr]$ pwd /home/oracle/sqlldr [oracle@rac1 sqlldr]$ ls exp_schema-by-system.par exp-schema.par exp-schema.par.010224 [oracle@rac1 sqlldr]$ cp exp-schema.par exp-schema_nfs.par [oracle@rac1 sqlldr]$ pwd /home/oracle/sqlldr [oracle@rac1 sqlldr]$ vi exp-schema_nfs.par [oracle@rac1 sqlldr]$ cat exp-schema_nfs.par DIRECTORY=EXPORT_DIR DUMPFILE=exp-sch-schema1-nfs-010224.dmp LOGFILE=exp-sch-schema1-nfs-010224.log SCHEMAS=schema1 METRICS=YES EXCLUDE=STATISTICS PARALLEL=6 expdp schema1/schema1 parfile=exp-schema_nfs.par [oracle@rac1 sqlldr]$ expdp schema1/schema1 parfile=exp-schema_nfs.par Export: Release 11.2.0.4.0 - Production on Thu Feb 1 11:43:28 2024 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "schema1"."SYS_EXPORT_SCHEMA_01": schema1/******** parfile=exp-schema_nfs.par Startup took 1 seconds Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Estimated 8 TABLE_DATA objects in 0 seconds Total estimation using BLOCKS method: 1.083 GB Startup took 2 seconds Processing object type SCHEMA_EXPORT/USER Startup took 2 seconds Startup took 360 seconds Completed 1 USER objects in 0 seconds Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Completed 1 SYSTEM_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/ROLE_GRANT Startup took 360 seconds Completed 3 ROLE_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Completed 1 DEFAULT_ROLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Startup took 360 seconds Completed 1 TABLESPACE_QUOTA objects in 0 seconds Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed 1 PROCACT_SCHEMA objects in 1 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Completed 4 SEQUENCE objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 16 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE Completed 8 TABLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 46 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/COMMENT Completed OBJECT_GRANT objects in seconds Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Completed 5 INDEX objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Completed 14 CONSTRAINT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Completed 4 TRIGGER objects in 1 seconds . . exported "schema1"."table_BARIDI_ADSL" 536.3 MB 3709183 rows . . exported "schema1"."table_BARIDI_GLTE" 149.8 MB 1029823 rows . . exported "schema1"."LIST_GLTE" 2.481 MB 198992 rows . . exported "schema1"."LIST_GLTE_BKP1" 762.3 KB 59660 rows . . exported "schema1"."table_BARIDI_ADSL_REGENERE" 12.74 KB 1 rows . . exported "schema1"."table_BARIDI_FACTURE" 115.8 MB 1024084 rows . . exported "schema1"."TRANSACTION" 5.273 KB 16 rows . . exported "schema1"."table_BARIDI_FTTH" 122.1 MB 843182 rows Completed 8 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 406 seconds Master table "schema1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for schema1.SYS_EXPORT_SCHEMA_01 is: /stage/export/exp-sch-schema1-nfs-010224.dmp Job "schema1"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Feb 1 11:44:35 2024 elapsed 0 00:01:06 /*************** import schema schema1 to target [oracle@rac1 export]$ pwd /stage/export [oracle@rac1 export]$ ls -alrt total 950048 drwxrwxrwx. 8 oracle oinstall 4096 Feb 1 12:37 .. drwxrwxrwx. 2 nobody nobody 4096 Feb 1 12:47 . -rw-r-----. 1 nobody nobody 972832768 Feb 1 12:48 exp-sch-schema1-nfs-010224.dmp -rw-r--r--. 1 nobody nobody 3354 Feb 1 12:48 exp-sch-schema1-nfs-010224.log -- source [oracle@rac1 export]$ scp /stage/export/exp-sch-schema1-nfs-010224.dmp oracle@10.100.114.7:/u01/app/oracle/exp_schema oracle@10.100.114.7's password: oracle exp-sch-schema1-nfs-010224.dmp 100% 928MB 84.3MB/s 00:11 You have new mail in /var/spool/mail/oracle -- target [kamal.khelifi.poste1] ➤ ssh oracle@10.100.114.7 Last login: Thu Feb 1 09:45:28 2024 from 192.168.0.55 [oracle@vs_dev1 ~]$ ls /u01/app/oracle/exp_schema schema1_TABLES_30012024.dmp EXP_SCHM_schema1_31012024.dmp EXP_SCHM_schema1_31012024.log [oracle@vs_dev1 ~]$ rm -f /u01/app/oracle/exp_schema/* [oracle@vs_dev1 ~]$ ls /u01/app/oracle/exp_schema ls /u01/app/oracle/exp_schema exp-sch-schema1-nfs-010224.dmp SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ VS_DEV OPEN SQL> DROP USER schema1 CASCADE; User dropped. CREATE USER schema1 IDENTIFIED BY "schema1" DEFAULT TABLESPACE schema1 TEMPORARY TABLESPACE TEMP01 PROFILE SERVICE_PROFILE ACCOUNT UNLOCK; -- 3 Roles for schema1 GRANT CONNECT TO schema1 WITH ADMIN OPTION; GRANT DBA TO schema1 WITH ADMIN OPTION; GRANT RESOURCE TO schema1 WITH ADMIN OPTION; ALTER USER schema1 DEFAULT ROLE ALL; -- 1 System Privilege for schema1 GRANT UNLIMITED TABLESPACE TO schema1 WITH ADMIN OPTION; -- 1 Tablespace Quota for schema1 ALTER USER schema1 QUOTA UNLIMITED ON schema1; -- 2 Object Privileges for schema1 GRANT INSERT ON STAT.STAT_PRODUIT TO schema1; GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO schema1; Import Preparation -- step 2 : create the par file [oracle@vs_dev1 ~]$ cd sqlldr [oracle@vs_dev1 sqlldr]$ pwd /home/oracle/sqlldr [oracle@vs_dev1 sqlldr]$ ls email.ctl email.dat email.log email.par [oracle@vs_dev1 sqlldr]$ vi imp-schema-schema1.par [oracle@rac1 sqlldr]$ vi imp-schema-schema1.par [oracle@vs_dev1 sqlldr]$ cat imp-schema-schema1.par DIRECTORY=EXP_SCHEMA DUMPFILE=exp-sch-schema1-nfs-010224.dmp LOGFILE=exp-sch-schema1-nfs-010224.dmp.log SCHEMAS=schema1 METRICS=YES LOGTIME=ALL /* This is my parameter file for all 3 databases: DIRECTORY=DATA_PUMP_DIR DUMPFILE=dumpfile.dmp LOGFILE=logfile.log SCHEMAS=TPCC METRICS=YES LOGTIME=ALL PARALLEL=6 */ -- user == schema1/schema1 impdp schema1/schema1@VS_DEV parfile=imp-schema-schema1.par [oracle@vs_dev1 sqlldr]$ impdp schema1/schema1@VS_DEV parfile=imp-schema-schema1.par Import: Release 11.2.0.4.0 - Production on Thu Feb 1 16:21:17 2024 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 Startup took 1 seconds Master table "schema1"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "schema1"."SYS_IMPORT_SCHEMA_01": schema1/********@VS_DEV parfile=imp-schema-schema1.par Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"schema1" already exists Completed 1 USER objects in 0 seconds Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Completed 1 SYSTEM_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/ROLE_GRANT Completed 3 ROLE_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Completed 1 DEFAULT_ROLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Completed 1 TABLESPACE_QUOTA objects in 0 seconds Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed 1 PROCACT_SCHEMA objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Completed 4 SEQUENCE objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 16 OBJECT_GRANT objects in 1 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE Completed 8 TABLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "schema1"."table_BARIDI_ADSL" 536.3 MB 3709183 rows . . imported "schema1"."table_BARIDI_GLTE" 149.8 MB 1029823 rows . . imported "schema1"."table_BARIDI_FACTURE" 115.8 MB 1024084 rows . . imported "schema1"."table_BARIDI_FTTH" 122.1 MB 843182 rows . . imported "schema1"."TRANSACTION" 5.273 KB 16 rows . . imported "schema1"."LIST_GLTE" 2.481 MB 198992 rows . . imported "schema1"."LIST_GLTE_BKP1" 762.3 KB 59660 rows . . imported "schema1"."table_BARIDI_ADSL_REGENERE" 12.74 KB 1 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 46 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Completed 5 INDEX objects in 11 seconds Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Completed 14 CONSTRAINT objects in 6 seconds Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Completed 4 TRIGGER objects in 0 seconds Completed 8 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 67 seconds Job "schema1"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Feb 1 16:22:45 2024 elapsed 0 00:01:27 /**************************************************/ [oracle@rac1 sqlldr]$ vi exp_schema-by-system.par [oracle@rac1 sqlldr]$ expdp system/atmanager parfile=exp_schema-by-system.par Export: Release 11.2.0.4.0 - Production on Thu Feb 1 15:48:57 2024 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** parfile=exp_schema-by-system.par Startup took 2 seconds Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Estimated 8 TABLE_DATA objects in 1 seconds Total estimation using BLOCKS method: 1.083 GB Processing object type SCHEMA_EXPORT/USER Startup took 3 seconds Startup took 368 seconds Completed 1 USER objects in 0 seconds Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Completed 1 SYSTEM_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/ROLE_GRANT Completed 3 ROLE_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Completed 1 DEFAULT_ROLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Startup took 368 seconds Completed 1 TABLESPACE_QUOTA objects in 0 seconds Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed 1 PROCACT_SCHEMA objects in 1 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Completed 4 SEQUENCE objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 16 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE Completed 8 TABLE objects in 2 seconds Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 46 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Completed 5 INDEX objects in 1 seconds Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Completed 14 CONSTRAINT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Completed 4 TRIGGER objects in 1 seconds . . exported "schema1"."table_BARIDI_ADSL" 536.6 MB 3711101 rows . . exported "schema1"."table_BARIDI_GLTE" 149.8 MB 1030279 rows . . exported "schema1"."table_BARIDI_FTTH" 122.2 MB 843920 rows . . exported "schema1"."table_BARIDI_FACTURE" 115.9 MB 1024643 rows . . exported "schema1"."TRANSACTION" 5.273 KB 16 rows . . exported "schema1"."LIST_GLTE" 2.481 MB 198992 rows . . exported "schema1"."LIST_GLTE_BKP1" 762.3 KB 59660 rows . . exported "schema1"."table_BARIDI_ADSL_REGENERE" 12.74 KB 1 rows Completed 8 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 414 seconds Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is: /stage/export/exp-sch-schema1-by-system--010224.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Feb 1 15:50:03 2024 elapsed 0 00:01:05 You have new mail in /var/spool/mail/oracle scp /stage/export/exp-sch-schema1-by-system--010224.dmp oracle@10.100.114.7:/u01/app/oracle/exp_schema [oracle@rac1 sqlldr]$ scp /stage/export/exp-sch-schema1-by-system--010224.dmp oracle@10.100.114.7:/u01/app/oracle/exp_schema oracle@10.100.114.7's password: exp-sch-schema1-by-system--010224.dmp 100% 928MB 103.1MB/s 00:09 [oracle@rac1 sqlldr]$ [oracle@vs_dev1 sqlldr]$ cp imp-schema-schema1.par imp-schema-schema1_system.par [oracle@vs_dev1 sqlldr]$ vi imp-schema-schema1_system.par [oracle@vs_dev1 sqlldr]$ impdp system/atmanager parfile=imp-schema-schema1_system.par Import: Release 11.2.0.4.0 - Production on Thu Feb 1 17:18:55 2024 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 Startup took 0 seconds Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** parfile=imp-schema-schema1_system.par Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"schema1" already exists Completed 1 USER objects in 0 seconds Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Completed 1 SYSTEM_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/ROLE_GRANT Completed 3 ROLE_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Completed 1 DEFAULT_ROLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Completed 1 TABLESPACE_QUOTA objects in 0 seconds Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Completed 1 PROCACT_SCHEMA objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Completed 4 SEQUENCE objects in 0 seconds Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 16 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE Completed 8 TABLE objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "schema1"."table_BARIDI_ADSL" 536.6 MB 3711101 rows . . imported "schema1"."table_BARIDI_GLTE" 149.8 MB 1030279 rows . . imported "schema1"."table_BARIDI_FACTURE" 115.9 MB 1024643 rows . . imported "schema1"."table_BARIDI_FTTH" 122.2 MB 843920 rows . . imported "schema1"."TRANSACTION" 5.273 KB 16 rows . . imported "schema1"."LIST_GLTE" 2.481 MB 198992 rows . . imported "schema1"."LIST_GLTE_BKP1" 762.3 KB 59660 rows . . imported "schema1"."table_BARIDI_ADSL_REGENERE" 12.74 KB 1 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Completed 46 OBJECT_GRANT objects in 0 seconds Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Completed 5 INDEX objects in 8 seconds Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Completed 14 CONSTRAINT objects in 4 seconds Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Completed 4 TRIGGER objects in 0 seconds Completed 8 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 60 seconds Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Feb 1 17:20:09 2024 elapsed 0 00:01:13 /**********************************************************************/ I hope that this article help you!