How to use Oracle command for database backup and restore

This article mainly introduces how to use the Oracle command for database backup and restore, the article is very detailed, has a certain reference value, interested friends must read it!

Data export:

1. Export the database orcl completely, and export the user name system password manager to D:\ daochu.dmp

Exp system/manager@orcl file=d:\ daochu.dmp full=y

2. Export the tables of system users and sys users in the database

Exp system/manager@orcl file=d:\ daochu.dmp owner= (system,sys)

3. Export the tables table1 and table2 in the database

Exp system/manager@orcl file=d:\ daochu.dmp tables= (table1,table2)

4. Export the field filed1 in the table table1 in the database with the data starting with "00".

Exp system/manager@orcl file=d:\ daochu.dmp tables= (table1) query=\ "where filed1 like'00%'\"

The above are commonly used exports. I don't care much about compression. I can compress the dmp file very well with winzip.

But just add compress=y after the above command.

Data Import:

1. Import the data from D:\ daochu.dmp into the orcl database.

Imp system/manager@orcl file=d:\ daochu.dmp

There may be a problem above, because some table already exists, and then it reports an error and does not import the table.

Just add ignore=y at the end.

2. Import the table table1 in d:\ daochu.dmp

Imp system/manager@orcl file=d:\ daochu.dmp tables= (table1)

Basically, the above import and export is enough. In many cases, I delete the table completely and then import it.

Note:

If you have enough authority, it will prompt you if you don't have enough authority.

You can connect to the database. You can use tnsping orcl to find out whether the database orcl can be connected.

Appendix:

The operation of adding permissions to import data to users

First, start sql*puls

Second, log in with system/manager

Third, create user username IDENTIFIED BY password (if you have already created a user, this step can be omitted)

Fourth

GRANT CREATE USER,DROP USER,ALTER USER, CREATE ANY VIEW, DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, DBA,CONNECT,RESOURCE,CREATE SESSION TO user name

Fifth, run-cmd- to enter the directory where the dmp file is located

Imp userid=system/manager full=y file=*.dmp

Or

Imp userid=system/manager full=y file=filename.dmp above is all the contents of the article "how to use the Oracle command for database backup and restore". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!