Oracle导入到不同的角色,Oracle中不同用户间数据的导入导出

Oracle中不同用户间数据的导入导出

实验环境：RedHat 4上装oracle 9

实验目的：将用户jiajia的数据导入到用户tianyu

实验步骤如下

1、使用系统用户登陆为用户jiajia创建一个默认表空间名为jiajia

[oracle@shanghai ~]$ sqlplus

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 8月 10 20:10:42 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> create tablespace jiajia datafile '/opt/oracle/oradata/mydb/jiajia.dbf' size 20M uniform size 64k;

Tablespace created.

2、创建用户jiajia并指定默认表空间为jiajia,临时表空间为temp

SQL> create user jiajia identified by jiajia default tablespace jiajia temporary tablespace temp;

User created.

3、授予连接，恢复，导入，导出数据库权限给jiajia

SQL> grant connect,resource,imp_full_database,exp_full_database to jiajia;

Grant succeeded.

SQL> quit

4、使用jiajia连接数据库mydb并创建用于测试的表studytable

[oracle@shanghai ~]$ sqlplus

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 8月 10 20:17:13 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> create table studytable

2  (

3  xh varchar(10) NOT NULL,

4  xm varchar(10) NOT NULL,

5  nl int,

6  xb char(4) NOT NULL

7  )

8  ;

Table created.

4、插入4条内容到该表中

SQL> insert into studytable values ('1001','aaaa',20,'男');

1 row created.

SQL> insert into studytable values ('1002','bbbb',21,'女');

1 row created.

SQL> insert into studytable values ('1003','cccc',22,'男');

1 row created.

SQL> insert into studytable values ('1004','dddd',24,'女');

1 row created.

SQL> commit;

Commit complete.

5、查看新建的表中内容

SQL> select * from studytable;

XH         XM                 NL XB

---------- ---------- ---------- ----

1001       aaaa               20 男

1002       bbbb               21 女

1003       cccc               22 男

1004       dddd               24 女

6、再次使用系统用户登陆为用户tianyu创建一个默认表空间名为tianyu

[oracle@shanghai ~]$ sqlplus

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 8月 10 20:25:00 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> create tablespace tianyu datafile '/opt/oracle/oradata/mydb/tianyu.dbf' size 20M uniform size 64k;

Tablespace created.

7、创建用户用户tianyu并指定默认表空间为tianyu，临时表空间为temp

SQL> create user tianyu identified by tianyu default tablespace tianyu temporary tablespace temp;

User created.

8、授予连接，恢复，导出，导入数据库权限给tianyu

SQL> grant connect,resource,imp_full_database,exp_full_database to tianyu;

Grant succeeded.

9、撤销tianyu表空间无限配额的权限(针对所有的用户)

SQL> revoke unlimited tablespace from tianyu;

Revoke succeeded.

10、更改tianyu用户使用默认表空间tianyu的权限为无限配额

SQL> alter user tianyu default tablespace tianyu quota unlimited on tianyu;

User altered.

SQL> exit

11、使用jiajia导出数据

[oracle@shanghai ~]$ exp file=backupjiajia.dmp owner=jiajia log=backupjiajia.log

[oracle@shanghai ~]$ ls

archive_log  backupjiajia.dmp  backupjiajia.log  install  rmanbackup

12、在没导入到 tianyu时，查看发现无法找到jiajia建立的表studytable

[oracle@shanghai ~]$ sqlplus

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 8月 10 20:36:22 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> select * from studytable;

select * from studytable

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

13、使用imp命令将数据从jiajia那边导入到tianyu这边，成功

[oracle@shanghai ~]$ imp file=backupjiajia.dmp fromuser=jiajia touser=tianyu log=backuptianyu.log ignore=y

Import: Release 9.2.0.4.0 - Production on 星期一 8月 10 20:33:27 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by JIAJIA, not by you

import done in ZHS16GBK character set and UTF8 NCHAR character set

. . importing table                   "STUDYTABLE"          4 rows imported

Import terminated successfully without warnings.

14、再来使用tianyu连接数据库查看，发现能查看到studytable表的内容

[oracle@shanghai ~]$ sqlplus

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 8月 10 20:38:07 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> select * from studytable;

XH         XM                 NL XB

---------- ---------- ---------- ----

1001       aaaa               20 男

1002       bbbb               21 女

1003       cccc               22 男

1004       dddd               24 女

SQL>

最关键的两条语句： revoke unlimited tablespace from tianyu;

alter user tianyu default tablespace tianyu quota unlimited on tianyu;

一般这两句会配合使用，第一句是撤销tianyu表空间无限配额的权限，让所有的用户都不能无限制的使用tianyu                             表空间的配额，第二句是只让tianyu这个用户使用tianyu表空间的无限配额