Oracle database Import and export steps

Database backup (Export):

First: Connect DBA database method: Connect database with SYSDBA, user name: sys password: Oracle

The first method:

Sqlplus/nolog

Conn Sys/[email protected] as Sysdba

The second method:

Sqlplus Sys/[email protected] as Sysdba

I. Backing up the entire database

1), the whole library, to export all the objects in the entire database, but does not include the SYS user objects, that is, the data dictionary cannot be exported.

Exp user/[email protected]_name full=y file=d:\database.dmp log=d:\database.log

/*XX Bank Database

# # #备份 Ivsd60

Exp Ivsd60/[email protected] full=y file=c:\database\oracle_exp\ivsd60\ivsd60.dmp log=c:\database\oracle_exp\ivsd60 \ivsd60_exp.log

Exp Ivsd60/[email protected] full=y grants=y file=c:\database\oracle_exp\ivsd60\ivsd60.dmp log=c:\database\oracle_ Exp\ivsd60\ivsd60_exp.log

# # #备份 Goldperson60

Exp Goldperson60/[email protected] full=y grants=y file=c:\database\oracle_exp\ivsd60\goldperson60.dmp log=C:\ Database\oracle_exp\ivsd60\goldperson60_exp.log

# # #备份 Goldcorp60

Exp Goldcorp60/[email protected] full=y file=c:\database\oracle_exp\ivsd60\goldcorp60.dmp Log=c:\database\oracle_ Exp\ivsd60\goldcorp60_exp.log

*/

2), User mode: Export all objects under a user, authorized users can export objects owned by other users. Complementary applications as a full-library export

Exp user/[email protected]_name owner= (system,sys) file=d:\owner.dmp Log=d:\owner.log

2. Backing Up Database tables

1), table mode: Only one user is exported under the specified table, not all tables.

Exp user/[email protected]_name tables= (TB1,TB2) file= D:\table.dmp Log=e:\table.log

3. Supplement: Export the field filed1 in table table1 in the database with data beginning with "00"

Exp user/[email protected]_name tables= (table1) query= "where filed1 like ' 00% '" File=d:\query.dmp log=d:\query.log

The above is commonly used for export * * More parameters Exp help=y view

Two. Create a database user

1. Log in to the DBA User:

The first method:

#sqlplus/nolog

#conn Sys/[email protected] as Sysdba

The second method:

#sqlplus Sys/[email protected] as Sysdba

2. Create a database user from a DBA user

/*XX Bank Database

# # #ivsd60 Users

Create user ivsd60 identified by IVSD60;

Grant DBA to Ivsd60;

Conn Ivsd60/ivsd60;

# # #goldperson60 Users

Create user goldperson60 identified by GOLDPERSON60;

Grant DBA to GOLDPERSON60;

Conn goldperson60/goldperson60;

# # #goldcorp60 Users

Create user goldcorp60 identified by GOLDCORP60;

Grant DBA to Goldcorp60;

Conn Goldcorp60/goldcorp60;

*/

Three. Database import:

1. Import All

Imp user/[email protected]_name full=y file=d:\database.dmp log=d:\impdatabase.log ignore=y

/*XX Bank Database

# # #导入 Ivsd60

Imp ivsd60/[email protected] full=y file=c:\database\oracle_exp\ivsd60\ivsd60.dmp log=c:\database\oracle_exp\ivsd60 \ivsd60_imp.log Ignore=y

# # #导入 Goldperson60

Imp goldperson60/[email protected] full=y file=c:\database\oracle_exp\ivsd60\goldperson60.dmp log=C:\Database\ Oracle_exp\ivsd60\goldperson60_imp.log Ignore=y

# # #导入 Goldcorp60

Imp goldcorp60/[email protected] full=y file=c:\database\oracle_exp\ivsd60\goldcorp60.dmp Log=c:\database\oracle_ Exp\ivsd60\goldcorp60_imp.log Ignore=y

*/

2. Select Table Import to import the table table1 in D:\table.dmp

Imp user/[email protected]_name file=d:\table.dmp log=d:\imptable.log tables= (table1)

If the source table already exists, import the times wrong. Add Ignore=y to the back.

In many cases, you must first delete the table and then import it. or create a temporary table with the same structure as the original table and then import into the staging table.

Note: Issues that may be encountered in statement execution:

EXP-00091 is exporting problematic statistics

Cause: Character Set issues

FIX: Exp command plus statistics=none option

EXPDP and IMPDP (data pump) are the new tools introduced by oracle10g. It not only includes the functions of imp/exp, but also expands and strengthens. Its speed is also fast. However, it can only be run on the database server.

The use example is as follows: http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

--Modify the database password

Alter user IVSD60 identified by Oracle;

--Delete database users and data

Drop user ivsd60 cascade;

Four. Precautions:

1.exp export will be reported when the command does not exist, you need to add the EXP, IMP before the $, such as $exp, $imp.

2. DBA authority is required to export the table, database DBA user name: sys, password: Oracle

Login command: Sqlplus sys/[email protected] as SYSDBA;

3. The following error occurred while using DBA login to export data: EXP-00008: Encountering ORACLE error 904 ORA-00904: "Poltyp": Invalid identifier EXP-00000: When the export termination fails,

You will need to swipe the script from the DBA User: @?/rdbms/admin/catexp.sql, and then re-export with exp.

This article is from the "Technology Sharing garden" blog, please be sure to keep this source http://zhangtianshan.blog.51cto.com/1500414/1774819

Oracle database Import and export steps