重温Oracle(10)

第二十四章 用户管理

 

注意：

如果要创建用户只能在管理员下完成：超级管理员（sys）、普通管理员(system)

示例用户：scott/tiger

 

创建用户

Create users username identified by passwd;

 

删除用户

Drop user username;

Drop user username cascade; --如果用户存在表则级联删除。

 

创建session权限

一般在数据库中，一个用户的连接称为建立一个session，如果一个新的用户想访问数据库，则必须授予创建session权限。

Grant 权限 to 用户;

如：grant create session to test; --可以连接数据库，但无法进行任何操作，如建表。

 

用户角色

角色是权限的集合

在oracle中提供了两种角色，可以直接将这两个角色给用户：

Connect角色

Resource角色

Grant connect,resource to test; --给test用户分配角色

 

Connect，resource，dba

这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。Oracle建议用户自己设计数据库库管理和安全的权限规划，而不要简单的使用这些预定角色。将来的版本中这些角色可能不会作为预定义角色。

 

Delete_datalog_role，execut_catalog_role，select_catalog_role

这些角色主要用于访问数据字典试图和包

 

Exp_full_database，imp_full_database

这两个角色用于数据导入导出工具的使用。

 

锁定/解锁用户

ALTER USER username ACCOUNT LOCK|UNLOCK;

 

密码失效

ALTER USER username PASSWORD EXPIRE; --设定密码失效，用户下次登录后需要修改密码

 

对象授权

以上对所有操作只针对于test用户。如果test要访问其他用户的对象时怎么办？

GRANT 权限(select,insert,update,delete) ON schema.table TO 用户;

|--grant select ON scott.emp TO test;

|--grant all on scott.temp to test;--将表相关的所有权限授予test

|--grant update(ename) on emp to test;--可以控制列的操作权限（还有insert）

 

权限回收

REVOKE 权限 ON schema.table FROM 用户;

|--revoke select on scott.emp from test;

 

查看权限

Select * from user_sys_privs;

 

权限传递

Grant create session to test with admin option;

 

Q：如果权限sys>test>test1，这时断掉test的权限，test1还会有权限吗？

A：在oracle9i是，答案是还会有。

 

角色

角色就是一堆权限的集合

Create role myrole;

Grant create table to myrole;

Drop role myrole;

 

第二十五章 备份/恢复 SQLLoader

 

exp和imp是oracle提供的导出和导入工具。

 

Imp 导入（import）

imp username/password file=/var/bak/oracle.dmp full=y;

Oracle不同用户之间导入dmp文件 

imp system/system@oracle fromuser=olduser touser=newuser file=/var/bak/oracle.dmp;

olduser: 导出该dmp的用户 

newuser: 将导入该用户

 

Exp 导出（export）

exp username/password@database file=/var/bak/oracle.dmp full=y;

 

SQL Loader

在oracle数据库总，我们通常在不同数据库的表间记录进行复制或迁移时会用以下几种方法：

A表的记录导出为一条条分号隔开的insert语句，然后执行插入到B表中；

建立数据库建的dblink，然后用create table B as select * from A@dblink where … 或insert inot B select * from A@dblink where …；

Exp A表，再imp到B表，exp时可加查询条件；

程序实现select from A … 然后insert into B… 也要分批提交；

再就是SQL Loader（sqlldr）来导入数据，效果比起逐条insert来很明显

 

说明：第一种方法在记录多时是个噩梦，需3~500条分批提交，否则客户端会死掉，而且导入过程很慢。如果要不产生REDO来提高insert into的性能，就要 alter table B nologging;

 

SQL Loader的用法：

只使用一个控制文件，在这个控制文件中包含数据（推荐）；

使用一个控制文件（作为模板）和一个数据文件；

 

Csv文件如下：（dept1.csv）

“ “,”DEPTON”,”DNAME”,”LOC”

“1”,”10”,”ACCOUNTING”,”NEW YORK”

…

 

Ctl文件如下：（dept1.ctl）

方式一：

Load data

Infile c:\dept1.csv

Truncate

Into table dept1

(

Deptno position(1:2),

Dname position(3:5),

Loc position(6:8)

)

 

方式二：

Options（skip=1，rows=128）--sqlldr 命令显示的选项可以写到这里边来，skip=1用来跳过数据中的第一行

Load data

Infile “c:\\dept1.csv” --指定外部数据文件，可以写多个infile “another.csv” 指定多个数据文件，这里还可以使用badfile、discardfile来指定坏数据和丢弃数据的文件。

Append --操作类型，用truncate table来清楚表中原有记录

Into table dept1` --要插入记录的表

Fileds terminated by “,” --数据中每行记录用逗号分割

Optionally enclosed by ‘”’ --数据中每个字段用双引号分割

Trailing nulcols --表的字段没有对应的值时允许为空

(

Vitual_column FILLER,--跳过由PL/SQL Developer生成的第一列序号

Deptno,--“dept_seq.nextval”，--这一列直接取序列的下一值，而不是数据中提供的值

Dname,--“’Hi||upper(:dname)”,--还能用SQL函数或运算对数据进行加工处理

Loc

)

 

说明：在操作类型truncate位置可用以下中的任意一值：

Insert --为缺省方式，在数据装载开始时要求表为空

Append --在表中追加新记录

Replace --删除旧记录(用delete from table 语句)，替换成新装载的记录

Truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

 

执行命令

Sqlldr scott/tiger control=dept1.ctl

 

看看日志文件，坏数据文件，从中可让你更好的理解SQL Loader，里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。

 

第二十六章 数据库设计范式

 

第一范式：字段要设计的不可再分

Name字段 可拆分成FirshName+LastName

 

第二范式：两个表的关系，在第三张关系表中体现

学生和课程表。为多对多的关系，这种关系需要在第三张选课表中体现

 

第三范式：多张表中，只存在关系，不存在具体信息（具体开发中用得最多）

比如雇员和部门，在雇员表中标注部门的外键引用来表示关系。

 

总范式 数据库表关联越少越好，SQL语句复杂度越低越好

三种范式其实只供参考。数据库设计原则：数据库表关联越少越好，SQL语句复杂度越低越好。所以有时候，违反了第三范式，但是简化了查询语句。加载了检索速度。

例如：在雇员表中，第三范式不允许出现除部门ID外其他部门信息（信息冗余），但如果系统中经常出现雇员信息页面要显示所在部门的名称，则可以考虑在雇员表中增加雇员名称的字段，减少每次显示雇员信息的时候使用联表查询，显著提升检索速度。