Oracle全库导出和特定用户导入

1 创建新用户并插入数据

C:\Users\Administrator> sqlplus system/oracle

SQL> create user haishu identified by haishu;

SQL> grant connect, resource to haishu;

SQL> conn haishu/haishu;

SQL> create table test_table (id number);

SQL> insert into test_table values(1);

SQL> commit;

SQL> exit;

2 用system用户导出全库

C:\Users\Administrator> exp system/oracle file=e:\fulldb.dmp log=e:\fulldb_exp.log full=y

3 删除haishu.test_table

C:\Users\Administrator> sqlplus haishu/haishu;

SQL> drop table test_table;

SQL> exit

4 导入haishu.test_table

C:\Users\Administrator> imp system/oracle file=e:\fulldb.dmp log=e:\fulldb_imp.log fromuser=haishu touser=haishu tables=(test_table)

导入过程会有很多警告，但不影响数据的导入。

5 验证

C:\Users\Administrator> sqlplus haishu/haishu;

SQL> select * from test_table;

6 注意

数据库导入导出尽量不要使用sys用户。若不慎误用了sys用户，导入导出语句应该像下面这样：

C:\Users\Administrator> exp \"/ as sysdba\" file=e:\fulldb.dmp log=e:\fulldb_exp.log full=y

C:\Users\Administrator> imp \"/ as sysdba\" file=e:\fulldb.dmp log=e:\fulldb_imp.log fromuser=haishu touser=haishu tables=(test_table)

ITPub博客

Oracle全库导出和特定用户导入