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表空间的⽆限配额
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论