Oracle笔记 Chapter 3.1-3.3

3.1.1 用户操作

用户锁定和解锁/密码设置：

alter user [USER] account lock;
alter user [USER] account unlock;
alter user scott identified by tiger;
select * from dba users;--查看用户信息表

创建用户：create user [USER] identified by [PASSWD];

删除用户：drop user [USER] cascade;

3.1.2 简单赋权

对于权限的赋予和收回语法如下：

grant [权限] to [用户]
revoke [权限] from [用户]

在Oracle里有两个最著名的角色：connect、resource除了dba之外的最大角色。

查看当前用户的所有权限：select * from session_privs;与scott用户下的权限进行对比，发现多了一个unlimited，其含义是拥有所有表空间配额的使用权限，这个权限太大了，一般来讲需要进行回收，然后重新进行分配一个表空间配额。

revoke unlimited tablespace from [USER];
select username,default tablespace from user_users;--查看用户缺省表空间
alter user [USER] quota 10m on users;

  • 想让新建用户拥有对scott.emp表的查询权限：grant select on scott.emp to [USER];

  • 想让新建用户拥有对scott的建立表操作：grant create any table to tim;

  • 想让新建用户拥有对scott.emp表的修改权限：grant select, update(sal) on emp to tim;<注意：可以精确到字段>

3.1.3 权限的传递和回收

1.系统权限的传递与回收：WITH ADMIN OPTION 选项<总结：覆水难收>

--新建2个用户
SQL> create user tim1 identified by tim1;
User created
SQL> create user tim2 identified by tim2;
User created

--sys赋于权限create session给tim1，tim1赋予权限create session给tim2;<注意使用权限传递语法>
SQL> grant create session to tim1 with admin option;
Grant succeeded
SQL> grant create session to tim2;
Grant succeeded

--这时候tim1、tim2都可以登录。然后sys回收tim1的create session权限，tim1肯定不能登录，但tim2还可以继续登录。
SQL> revoke create session from tim1;
Revoke succeeded

<结果>
SQL> conn tim1/tim1
ERROR：
0RA-01045:用户tim1没有CREATE SESSION权限;登录被拒绝
SQL> conn tim2/tim2
已连接。

<总结>系统权限：覆水难收

2.对象权限的传递与回收：WITH GRANT OPTION 选项<总结：株连九族>

--scott赋予权限select给tim1，tim1赋于权限select给tim2;<注意使用权限传递语法>
SQL> conn scott/scott
已连接。
SQL> grant select on emp to tim1 with grant option;
授权成功。
sQL> conn tim1/tim1
已连接。
SQL> select * from scott.emp --可以看到scott.emg表
SQL> grant select on emp to tim2;
SQL> conn tim2/tim2
已连接。
SQL> select * from scott.emp --可以看到scott.emp表
SQL> conn scott/scott
已连接。
SQL> revoke select on emp from tim1;
撤销成功。
--再用tim1、tim2登录查询scott.emp都无法看到表信息
<结果>
SQL> select * from scott.emp;
第1行出现错误：
ORA-00942:表或视图不存在

<总结>对象权限：株连九族

3.2.1 事务特性

事务必须具备以下四个属性，简称ACID属性：

  • 原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行

    • 场景：银行转账，A-100 B+100同时成功或同时失败

  • 一致性（Consistency）：一个查询的结果必须与数据库在查询开始的状态一致（读不等待写，写不等待读）。

    • 场景：查询数据，9:00开始查询数据9:15查询完毕，在这期间所查询的数据被其它操作更新，且在9:00-1:15之间查询结果显示的是9:00时候并没有被更改的数据。一般Oracle是把这个没有更新的数据放入’undo’里，如果Oracle在’undo’里没有找到数据，则宁可报错，也不会让你看到其它操作更新的新的数据。

  • 隔离性（Isolation）：对于其它会话来说，未完成的（也就是未提交的）事务必须不可见。

    • 场景：事务和事务之间相互隔离，2个session一个查询一个更新，那么在更新操作没有commit之前，查询所看到的数据是没有提交之前的，相互没有影响。

  • 持久性（Durability）：事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据库通过日志能够保持事务的持久性。

    • 场景：事务提交之后不可逆，提交数据是由内存的数据刷新到磁盘上，这个过程的快慢和性能有关。那么Oracle主要是靠’rudo’日志，先记录日志，在写到磁盘上。

3.2.2 事务的开始和结束

事务采用隐性的方式，起始于session的第一条DML语句

查看事务：select * from v$transaction;

事务结束于：

  1. COMMIT（提交）或ROLLBACK（回滚）

  2. DDL语句被执行（提交）

  3. DCL语句被执行（提交）

  4. 用户退出SQLPLUS（正常退出时提交，非正常退出时回滚）

  5. 机器故障或系统崩溃（回滚）

  6. shutdown immediate（回滚）

3.3.1 锁

锁大概分为：共享锁与排它锁。

  • 排它锁（独占），排斥其它排它锁和共享锁。

  • 共享锁，排斥其它排它锁，但不排斥其它共享锁。

锁类型：

  • DML锁（data locks，数据锁），用于保护数据的完整性。TX（行级锁），TM（表级锁），日常所使用的DML操作就会产生事务和锁。

  • 查看事务：select * from v$transaction;

  • 查看锁：select * from v$lock;

  • DDL锁（dictionary locks，数据字典锁），用于保护数据库对象的结构，如表、索引等的结构定义。

  • SYSTEM锁（internal locks and latches），保护数据库的内部结构

锁用途：只有有事务才会产生锁，保证数据的完整性和正确性。

3.3.2 锁类型

TX：一种锁

TM：五种

锁模式 锁描述 解释 SQL操作
2 RS(ROW SHARE) 行级共享锁，其它对象只能查询这些数据行 select for update、lock for update、lock row share
3 RX(ROW EXCLUSIVE) 行级排它锁，在提交前不允许做DML操作 insert、update、delete、lock row share
4 S(SHARE) 共享锁 create index、lock share
5 SRX(SHARE ROW EXCLUSIVE) 共享行级排它锁 lock share row exclusive
6 X(EXCLUSIVE) 排它锁 alter table、drop table、drop index、truncate table 、lock exclusive
SQL语句 加锁模式 许可其他用户的加锁模式
select * from table_name 无 RS，RX，S，SRX，X
insert，update，delete（DML操作） RX RS，RX
select * from table_name for update RX RS，RX
lock table table_name in row share mode RS RS，RX，S，SRX
lock table table_name in row exclusive mode RX RS，RX
lock table table_name in share mode S RS，S
lock table table_name in share row exclusive mode SRX RS
lock table table_name in exclusive mode X 无

3.3.3 加锁模式

自动加锁，做DML操作时，如insert，update，delete，以及select...for update由Oracle自动完成加锁。select * from emp1 where deptno = 10 for update;

修改其部门为10的记录则会被锁定，可以进行试探要修改数据记录是否被加锁。如下三种形式均可：

select * from emp1 where empno = 7782 for update nowait;
select * from emp1 where empno = 7782 for update wait 5;
select * from emp1 where job= 'CLERK' for update skip locked;

如果这个锁占用的时间太长，可以通过管理员杀掉session用户（表霸）。

  • 首先，找到是哪个sid占用了太长时间，查看v$lock表

  • 然后，根据v$lock表的SID，去v$session里面去找到，进行kill操作。

    select sid, serial# from v$session where sid = 170;
alter system kill session 'sid,serial';

3.3.4 死锁问题

Oracle会自动解决死锁问题

CREATE TABLE A(id int);
insert into a values(100);
insert into a values(200);
insert into a values(300);
commit;
select * from a;

--建立死锁机制：
--A用户
update a set id=1000 where id=100;
--B用户
update a set id=2000 where id=200;
--A用户去触碰B用户正在使用的id=200的数据
update a set id=3000 where id=200;
--B用户则触碰A用户正在使用的id=100的数据
update a set id=4000 where id=100;
--这样就会造成死锁，Oracle会自动进行解除死锁