Shulou(Shulou.com)06/01 Report--

Database backup (export):

First of all: connect to the dba database method: connect to the database with sysdba, user name: sys password: oracle

The first method:

Sqlplus / nolog

Conn sys/oracle@41SGEORA as sysdba

The second method:

Sqlplus sys/oracle@41SGEORA as sysdba

one。 Database export

1. Back up the entire database

1), full database mode, export all objects in the entire database, but do not include objects in sys users, that is, the data dictionary cannot be exported.

Exp user/pwd@db_name full=y file=D:\ database.dmp log=D:\ database.log

/ * xx bank database

# backup ivsd60

Exp ivsd60/ivsd60@41SGEORA full=y grants=y file=C:\ Database\ oracle_exp\ ivsd60\ ivsd60.dmp log=C:\ Database\ oracle_exp\ ivsd60\ ivsd60_exp.log

, /

2) user mode: export all objects under a user, and authorized users can export objects owned by other users. As a supplementary application of full library export

Exp user/pwd@db_name owner= (system,sys) file=D:\ owner.dmp log=D:\ owner.log

two。 Back up database tables

1) Table mode: only the tables specified under a certain user are exported, not all tables.

Exp user/pwd@db_name tables= (tb1,tb2) file= D:\ table.dmp log=e:\ table.log

3. Add: export the field filed1 in the table table1 in the database with data starting with "00"

Exp user/pwd@db_name tables= (table1) query= "where filed1 like'00%'" file=D:\ query.dmp log=d:\ query.log

* above are commonly used exports * * more parameters exp help=y view

two。 Create a database user

1. Log in to dba users:

The first method:

# sqlplus / nolog

# conn sys/oracle@166 as sysdba

The second method:

# sqlplus sys/oracle@166 as sysdba

two。 Create database users through dba users

/ * xx bank database

# ivsd60 users

Create user ivsd60 identified by ivsd60

Grant dba to ivsd60

# # testing whether the user has created successfully

Conn ivsd60/ivsd60

, /

three。 Database Import:

1. Import all

Imp user/pwd@db_name full=y file=D:\ database.dmp log=d:\ impdatabase.log ignore=y

/ * xx bank database

# Import ivsd60

Imp ivsd60/ivsd60@166 full=y file=C:\ Database\ oracle_exp\ ivsd60\ ivsd60.dmp log=C:\ Database\ oracle_exp\ ivsd60\ ivsd60_imp.log ignore=y

, /

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

Imp user/pwd@db_name file=D:\ table.dmp log=d:\ imptable.log tables= (table1)

If the source table already exists, importing the Times is wrong. Just add ignore=y at the end.

In many cases, the table is deleted completely and then imported. Or create a temporary table with the same structure as the original table and import it into the temporary table.

Note: problems that may be encountered in statement execution:

* EXP-00091 is exporting problematic statistics

Cause: character set issu

Solution: exp command plus statistics=none option

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

Examples of use are as follows: http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

-- modify the database password

Alter user ivsd60 identified by oracle

-- deleting database users and data

Drop user ivsd60 cascade

four。 Note:

When 1.exp exports, it will report that the command does not exist, so you need to add $before exp and imp, such as $exp and $imp.

two。 Dba permission is required when exporting tables. The dba user name of the database on this side of the company is sys, and the password is oracle.

Login command: sqlplus sys/oracle@SID as sysdba

3. Encountered the following error when logging in to export data with dba: EXP-00008: encountered ORACLE error 904 ORA-00904: "POLTYP": invalid identifier EXP-00000: export termination failed

You need to swipe the script @? / rdbms/admin/catexp.sql under the dba user, and then reuse the exp export.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.