Oracle Tutorial: Use expdp and impdp to migrate Databases

Expdp and impdp are used only in Oracle10g. the following source database is Oracle10.2 and the target database is Oracle11.21.

Expdp and impdp are used only in Oracle10g. the following source database is Oracle10.2 and the target database is Oracle11.21.

Expdp and impdp are used only in Oracle10g. the following source database is Oracle10.2 and the target database is Oracle11.2.
1. Create an expdp export directory on source database server.
$ Pwd
/Home/oraoms
$ Mkdir exp_dir

SQL> create or replace directory exp_dir as '/home/oraoms/exp_dir ';

Directory created.

2. query large tables on source database A without affecting system operation and not importing them to target database B.
Select *
From (select table_name,
Round (blocks * 8192/1024/1024), 2) "MB"
From user_tables
Where blocks is not null
Order by blocks desc)
Where rownum <21

The following tables are very large and cannot be exported:
'Mlog _ engi_gtb', 'mlog _ ENGI_MUDV ', 'mlog _ engi_hyd'

3. Export in source database
Expdp A_user/A_user directory = exp_dir dumpfile = 20100506. dump logfile = 20100506.log schemas = A_user exclude = table: \ "IN \ (\ 'mlog _ ENGI_GTB \ ', \ 'mlog _ ENGI_MUDV \', \ 'mlog _ ENGI_HYD \'\)\"
To export a pair of single quotes and parentheses, you must use "\" as the conversion character.
The exported data is more than 8 GB, which is shared for about 18 minutes.
4. Check the number of user objects in source database A and verify whether the import is successful.
Select count (*) from user_objects

7532 objects

5. view the tablespace in source database A and create the tablespace in the target database B.
Select tablespace_name, count (*)
From user_tables
Group by tablespace_name
Order by 2;

MLOG_NORM_SPACE

Create a tablespace in the target database B:
View the data file location of the target database
Select name from v $ datafile;
For example:
Create tablespace MLOG_NORM_SPACE
Datafile '/oratest/app/oracle/oradata/orcl/mlog_norm_space.dbf'
Size 5 M autoextend on

Create and authorize corresponding users
Create user test
Identified by test
Default tablespace PUB_NORM_SPACE

Grant dba to test;

6. Create an import directory in the target database B.
> Mkdir/oratest/imp_dir
Authorize this directory to oracle users
> Chown-R oracle: dba/oratest/imp_dir

7. ftp the exported data to the target database B.
Ip address of the ftp target database
Put 20100506. dump

8. Create an import directory in the target database B.
SQL> create or replace directory imp_dir as '/oratest/imp_dir ';

9. import data to the target database B
> Su-oracle
Impdp test/test DIRECTORY = imp_dir DUMPFILE = 20100506. dump logfile = 20100506imp. log REMAP_SCHEMA = A_user: test

10. Create A large table structure in the target database B that does not export source database.
You can copy the table structure to the target database B.
Or create db_link in the target database B, and then create the corresponding table structure.

11. Check the number of user objects in the target database B to verify that the objects are complete.
Select count (*) from user_objects

Note:
If Oracle keywords are used for fields in some tables, they must be enclosed in double quotation marks. For example, change time to "time" date.

,