[Dan JIU Jian] Oracle knowledge point sorting (3) Import and Export, oracle knowledge point

[Dan JIU Jian] Oracle knowledge point sorting (3) Import and Export, oracle knowledge point
Link navigation for this series:

[Lone sword] Oracle knowledge point sorting (1) Table space and users

[Gu JIU Jian] Oracle knowledge point sorting (2) database connection

[Gu JIU Jian] Oracle knowledge point sorting (3) Import and Export

[Dan JIU Jian] Oracle knowledge point sorting (4) SQL statement DML and DDL

[Dan JIU Jian] Oracle knowledge point sorting (5) Table and View of common database objects

[Lone sword] Oracle knowledge point sorting (6) Procedure, function, and Sequence of common database objects

[Dan JIU Jian] Oracle knowledge point sorting (7) database Common Object Cursor

[Lone sword] Oracle knowledge point sorting (8) Common exceptions

[Lone sword] Oracle knowledge point sorting (9) package of common database objects

[Gu JIU Jian] Oracle knowledge point sorting (10) % type and % rowtype and common functions

3. Import and export the database (run the command at the command prompt cmd)

Before Oracle 9i, use the imp/exp command to import/export data.

3.1 data export

1 -- export the database orcl to the specified directory d 2 exp userName/password @ orcl file = d: \ backup \ userName01.dmp log = d: \ backup \ userName01.log full = y3 -- export the contents of userName and userName2 in the database to 4 exp userName/password @ orcl file = d: \ backup \ userName01.dmp log = d: \ backup \ userName01.log owner = (userName, userName2) 5 -- export table t1 and t2 6 exp userName/password @ orcl file = d: \ backup \ userName01.dmp log = d: \ backup \ userName01.log tables = (t1, t2) 7 -- export data starting with 00 in Table t1 8 exp userName/password @ orcl file = d: \ backup \ userName01.dmp log = d: \ backup \ userName01.log tables = (t1) query = \ "where field1 like '000000 '\"

3.2 Data Import

Prerequisites for importing: see import for users with the same tablespace name used for the data to be imported in the database:

1 -- import all backup files to 2 imp userName/password @ orcl file = d: \ backup \ userName01.dmp log = d: \ backup \ userName01.log full = y 3 -- where ignore = y indicates that existing tables are ignored. If no existing tables are added, tables with the same name are displayed, do not import 4 imp userName/password @ orcl file = d: \ backup \ userName01.dmp log = d: \ backup \ userName01.log full = y ignore = y5 -- import table t1 to 6 imp userName/password @ orcl file = d: \ backup \ userName01.dmp log = d: \ backup \ userName01.log tables = (t1)

Import between different users:

Imp userName/password @ orcl file = d: \ backup \ userName01.dmp
Log = d: \ backup \ userName01.log
Fromuser = userNameA touser = userNameB -- Note fromuser and touser

After Oracle 10i, we recommend that you use the Data Pump Mode to import/export data [impdp/expdp]. If you use the data pump mode, you can only import/export data on the server.

3.3 Data Pump export steps:

A) create Directory

create directory dir_dp as 'D:\backup\dir_dp';

B) Authorization

create read,write on directory dir_dp on userName;

B .1) view directories and permissions

select privilege,directory_name,directory_path 
　　from user_tab_privs t,all_directories d　　　　where t.table_name(+)=d.directory_name;

C) execute Export

expdp userName/password@orcl schemas=userName 
　　directory=dir_dp dmpfile=expdp_userName.dmp logfile=expdp_userName.log

Note:

1. directory = dir_dp must be placed before, if placed last, will prompt ORA-39002: The operation is not valid
ORA-39070: Unable to open the log file.
ORA-39087: Invalid directory name DATA_PUMP_DIR;
2. During the export process, data dump creates and uses an object named SYS_EXPORT_SCHEMA_01, which is the JOB name used in the data dump export process, if you do not specify the name of the exported JOB when executing this command, a default JOB name will be generated. If you specify the JOB name during the export process, it will appear with the specified name.
Change it:

expdp userName/password@orcl schemas=userName 
　　directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1;

3. Do not use semicolons after the export statement. Otherwise, the job table name in the preceding Export Statement is 'my _ job1; 'instead of my_job1. Therefore, when executing the command "expdp userName/password attach = userName. my_job1", the system always prompts that the job table cannot be found.

4. The Created directory must be on the machine where the database is located. Otherwise, the following message is displayed:
ORA-39002: Invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: Invalid directory name DATA_PUMP_DIR;

Data Pump export modes:

• Export in Table mode:

expdp userName/password@orcl tables=userName.b$i_exch_info,userName.b$i_manhole_info 
　　dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job

• Export according to query conditions:

expdp userName/password@orcl tables=userName.b$i_exch_info 
　　dumpfile=expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query='"where rownum<11"'

• Export by tablespace:

Expdp userName/password@orcl dumpfile=expdp_tablespace.dmp 
　　tablespaces=GCOMM.DBF logfile=expdp_tablespace.log directory=dir_dp job_name=my_job

• Export Scheme

Expdp userName/password DIRECTORY=dir_dp DUMPFILE=schema.dmp SCHEMAS=userName,gwm

• Export the entire database:

expdp userName/password@orcl dumpfile =full.dmp full=y logfile=full.log directory=dir_dp job_name=my_job

3.4 Data Pump import steps:

Preparation: 1. Create a database 2. Create a tablespace 3. Create a user and authorize 4. Copy aa. dmp to the dpdump directory (if the backup file directory is not modified and specified)

• Import by table (the table in the p_street_area.dmp file is exported by the gwm user according to schemas = gwm :)

impdp gwm/gwm@fgisdb dumpfile =p_street_area.dmp logfile=imp_p_street_area.log directory=dir_dp tables=p_street_area job_name=my_job

• Import by user (user information can be directly imported if user information does not exist)

impdp gwm/gwm@fgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job

• Directly import the dmp file without using the expdp step: (import the table p_street_area to the target database from the source database)

Impdp gwm/gwm directory = dir_dp NETWORK_LINK = igisdb tables = p_street_area logfile = p_street_area.log job_name = my_job -- igisdb is the name of the link between the target database and the source data.

• Change the tablespace (use the remap_tablespace parameter, -- export all data under the gwm user)

expdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp SCHEMAS=gwm

Note:

If the user data exported by the sys user, including user creation and authorization, is exported by the user, the user data is not included.

-- The following is to import all data under the gwm user to the impdp system/orcl directory = data_pump_dir dumpfile = gwm. dmp remap_tablespace = gmapdata: gcomm in the gcomm table space (originally under the gmapdata table space ).

3.5 data import/export between databases of different versions 3.5.1, and import and export between Oracle 10g and Oracle 11g

• Export

1 create directory dir_dp as 'd: \ backup \ dir_dp '; 2 create read, write on directory dir_dp on userName; 3 expdp userName/password @ orcl schemas = userName directory = dir_dp
Dumpfile = expdp_userName.dmp logfile = expdp_userName.log version = 10.2.0.1.0 -- with the version number

• Import

Impdp userName/password @ orcl schemas = userName directory = dir_dp
Dumpfile = expdp_userName.dmp logfile = expdp_userName.log version = 10.2.0.1.0 -- with the version number

3.5.2. Other Methods

Use Notepad ++ or other text editing tools to view the dmp file and change the header to the version number that you will import to the target database.
The following Version: 11g R2: V11.02.00 11g R1: V11.01.00 10g: V10.02.01

In this way, ORACLE10g can import this file, and of course it may also test the character.