Oracle补习班第八天
The best hearts are always the bravest.
心灵最高尚的人,也总是最勇敢的人。
1,权限、角色、与用户
创建用户
create user aa identified by 123456;
create user bb identified by 123456;
create user cc identified by 123456;
创建角色
create role hr_clerk;
create role hr_mgr;
给角色赋权
grant create table to hr_mgr;
grant select on scott.emp to hr_mgr;
grant create session to hr_mgr;
grant select on scott.emp to hr_clerk;
grant create session to hr_clerk;
grant insert on scott.emp to hr_clerk;
grant update on scott.emp to hr_clerk;
给用户角色
grant hr_mgr,hr_clerk to aa;
grant hr_clerk to bb,cc;
总结:角色是权限的集合
oracle是有预定义角色的,常用的CONNECT,RESOURCE,DBA
2,将数据加载到数据库中使用SQL loader工具
操作步骤逻辑如下:
新建一个txt文档,写入需要插入的数据;
新建一个ctl文件,写入参数;
sqlldr scott/tiger@orcl02 control=/tmp/sqlldr.ctl;
实验具体步骤如下:
select ename from scott.emp;
vim /tmp/'the data need to be insert.txt' #粘贴上面数据到txt
vim/tmp/sqlldr.ctl
load data
infile '/tmp/'the data need to be insert.txt''
into table emp1
fields terminated by ','
(ename)
sqlldr scott/tiger@orcl2 control=/tmp/sqlldr.ctl
select * from emp1;
实验插入两行数据步骤如下:
select ename||','||empno from scott.emp
vim /tmp/'the data need to be insert2.txt'
vim /tmp/sqlldr2.ctl
load data
infile '/tmp/'the data need to be insert2.txt''
into table emp2
fields terminated by ','
(ename,empno)
sqldr scott/tiger control=/tmp/sqlldr2.ctl
3,在数据库之间传输数据,使用工具exp,imp
交互式导出用户
exp
username:scott
passwd:tiger
enter array fetch buffer size:4096>1000000
export file:expdat.dmp>/tmp/scott.dmp
user,or table>U
export grants>yes
export table data>yes
compress extents>no
命令式导出用户
exp "'sys/oracle as sysdba'" BUFFER=1000000 FILE=/tmp/sys.dmp OWNER=sys
参数文件方式导出
vim /tmp/scott.par
USERID=scott/tiger
FILE=/tmp/sys.dmp
BUFFER=1000000
OWNER=sys
LOG=/tmp/sys_exp.log
exp parfile=/tmp/scott.par
总结:exp导出有三种方法,交互式,命令行,参数式。
关键字:数据库full=y,用户owner,表空间tablespaces,表tables,查看exp help=y
exp scott/tiger buffer=1000000 file=/tmp/test.dmp tables=emp quary=\'where deptno=30\'
exp scott/tiger buffer=1000000 file=/tmp/test.dmp tables=emp row=no #表结构
4,在数据库之间传输数据,使用工具expdp,impdp
$ mkdir /tmp/dmpdir
> create or replace directory dmpdir as '/tmp/dmpdir';
> grant read,write on directory dmpdir to scott;
$ expdp scott/tiger dumpfile=scott.dmp directory=dmpdir
$ expdp system/oracle dumpfile=system_scott.dmp directory=dmpdir schemas=system,scott