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 jiajia/jiajia@mydb 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 tianyu/tianyu@mydb
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 tianyu/tianyu@mydb 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 tianyu/tianyu@mydb
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表空间的无限配额
上一页 [1] [2]
责任编辑：小草
您现在的位置： 