Use of export/import

1. exp/imp is equivalent to restoration and backup of the oracle database.
2. exp exports data to the local dmp file, and imp imports the local file into the database
3. exp/impcan be executed in cmd.exe
I. Data export:
1. Export the database EDWTEST completely, and the username system Password manager is exported to D: \ daochu. dmp.
> Exp edwdata/edwdata @ EDWTEST file = c: \ fot \ daochu. dmp log = c: \ fot \ daochu. log full = y

 
2. Export the tables of system users and sys users in the EDWTEST Database
> Exp edwdata/edwdata @ EDWTEST file = c: \ fot \ daochu. dmp log = c: \ fot \ daochu. log owner = (system, sys)

 
3. Export the tables s_data_d and stu in the EDWTEST database.
> Exp edwdata/edwdata @ EDWTEST file = c: \ fot \ new. dmp log = c: \ fot \ new. log tables = (s_data_d, stu)

4. Export the data with the field statdate In the table s_data_d in the database EDWDATA equal to '123'. The query \ "------------------ \" contains one or more conditions.
> Exp edwdata/edwdata @ EDWTEST file = c: \ fot \ s_data_d.dmp log = c: \ fot \ s_data_d.log tables = (s_data_d) query = \ "where statdate = '2016 '\"

 
 
Ii. Data Import:
1 convert c: \ fot \ new. all dmp data is imported to the EDWTEST1 database (the table s_data_d, stu does not exist in EDWTEST1). 'tables = (s_data_d, stu) 'is equivalent to 'full = Y, because this file only contains data of table s_data_d and table stu.
> Imp edwdata/edwdata @ EDWTEST1 file = c: \ fot \ new. dmp log = c: \ fot \ new. log tables = (s_data_d, stu)
> Imp edwdata/edwdata @ EDWTEST1 file = c: \ fot \ new. dmp log = c: \ fot \ new. log full = y

 
2. When importing data from imp, you first need to create table. If the table already exists, an error will be reported no matter whether there is data in the table.
Import c: \ fot \ new. dmp to the edwtest database (the table s_data_d and stu already exist in EDWTEST). The result is as follows:
> Imp edwdata/edwdata @ EDWTEST file = c: \ fot \ new. dmp log = c: \ fot \ new. log tables = (s_data_d, stu)

3 if the table already exists, you can use the keyword ignore = y to directly import data.
Scenario 1: The table exists, but the table is empty. If no data exists, the table creation is skipped and all data is imported.
> Imp edwdata/edwdata @ EDWTEST file = c: \ fot \ new. dmp log = c: \ fot \ new. log tables = (s_data_d, stu) ignore = y

 
Case 2: The table exists and the table already has data, but the table does not have constraints. All data is imported regardless of whether it is repeated.
> Imp edwdata/edwdata @ EDWTEST file = c: \ fot \ s_data_d_1.dmp ignore = y
Case 3: The table exists. The table already has data, but the table has primary key constraints or other constraints. Skip table creation and start comparing data one by one. If the database table does not have this data, import it, skip this step if it already exists.
> Imp edwdata/edwdata @ EDWTEST file = c: \ fot \ s_data_d_1.dmp ignore = y
 
 
Author: "To_Be_Monster_Of_IT"