Oracle数据库笔记（三）

Oracle数据库笔记（三）


Undo表空间（还原段）


Undo表空间引入的原因：
1、事务回滚：rollback 回滚的时候，将从Undo表空间恢复事务前的数据。
2、读一致性：当前用户修改数据还未提交时，其他用户读取数据时，会根据scn号，读取还原段中的数据。
3、事务恢复：实例崩溃时，会从Undo表空间上的重做日志还原未提交的数据，恢复成未提交数据的原始值。、


Oracle读一致性
1、时刻一 9:00 发起读取，假设需要10分钟读取完毕。会记录时刻一 9:00 的scn号，假设是 scn1000，读取数据文件时会找小于或者等于 scn1000
2、时刻二 9:05 另一个用户发起修改，并立即提交。 此假设时被修改的数据块的scn号变为 scn1001。
3、读取到 数据块scn1001时，发现当前 scn1001大于scn1000，会根据scn1001数据块中记录的Undo地址去找修改前的数据。


Oracle读一致性（高级）
1、时刻一 9:00 发起读取，假设需要10分钟读取完毕。会记录时刻一 9:00 的scn号，假设是 scn1000，读取数据文件时会找小于或者等于 scn1000
2、时刻二 9:05 另一个用户发起修改数据块A，并立即提交。 此假设时被修改的数据块的scn号变为 scn1001。
3、时刻三 9:06 另一个用户发起修改数据块A，并立即提交。 此假设时被修改的数据块的scn号变为 scn1002。   
4、时刻三 9:07 另一个用户发起修改数据块A，并立即提交。 此假设时被修改的数据块的scn号变为 scn1003。 
5、读取到 数据块A时发现scn1003大于scn1000，根据scn1003数据块中记录的Undo地址去找修改前的数据。
找到后发现scn1002仍然大于scn1000，会根据scn1002数据块中记录的Undo地址去找修改前的数据。
找到后发现scn1001仍然大于scn1000，会根据scn1001数据块中记录的Undo地址去找修改前的数据。
如果能找到小于或者等于scn1000就读取，不能找到就会报 ora-1555（snapshoot too old）


rollback事务回滚看似是数据往回滚动，实则是事务继续向前，即由Undo段中的数据更新现有数据，scn是一直增大的。


查看undo表空间参数
show parameter undo


undo_management 不是动态参数，需要在参数文件中修改，重启后生效。
undo_tablespace 动态参数，修改后立即生效。


设置自动撤销管理
alter system set undo_management=auto scope=spfile;


查看undo表空间默认保存时间
show parameter undo_retention    --默认是900s
修改undo表空间默认保存时间
select max(maxquerylen) from v$undostat;  -- 数据库最长事务时间
alter system set undo_retention=1200;     -- 不能保证一定会保存1200s


通过设置undo_retention=0，在11g后oracle会每隔30s收集统计信息，自动调整undo_retention，且以900s为下限。
alter system set undo_retention=0;


undo表空间undo块的状态
active:表示当前数据块上有活动的或者未提交的事务。
inactive:表示当前数据块上没有活动事务。
expired:表示当前数据块上持续 inactive 的时间超过参数 undo_retention设置的时间。
freed:表示当前数据块是空的，未使用过。


当有新的事务需要使用undo块时，顺序为 freed--expired--inactive。如果还不够的话，就报错。


强制按undo_retention设置的时间保存
alter tablespace tablespace_name retention guarantee;   --此时如果undo块不够用，会报错。
撤销该参数 
alter tablespace tablespace_name retention noguarantee;
 
创建还原表空间
create undo tablespace tablespace_name
datafile 'F:\app\tdn084\oradata\orcl\UNDO_TABLESPACE.DBF'
size 100M
autoextend on;


重命名还原表空间
alter tablespace  UNDO_TABLESPACE1 rename to UNDO_TABLESPACE;


向还原表空间增加文件
alter tablespace tablespace_name
add datafile 'F:\app\tdn084\oradata\orcl\UNDO_TABLESPACE01.DBF'
size 100M;


将数据文件改为自动增长
alter database 
datafile 'F:\app\tdn084\oradata\orcl\UNDO_TABLESPACE01.DBF'
autoextend on;


切换还原表空间
alter system set undo_tablespace='UNDO_TABLESPACE';   --可不带引号


删除还原表空间
drop tablespace tablespace_name; -- 如果还原表空间正在使用，不能被删除。即使是已经切换了还原表空间，如果原还原表空间上还有活动的事务，也不能被删除。undo_retention 也会影响删除原还原表空间。


查看还原表空间块信息
select  * from dba_undo_extents;




事务
事务是一组逻辑单元，由一条或者多条SQL语句组成。执行时这些语句要么全部成功，要么全部失败。


事务结束类型
1、显示提交commit
2、显示回滚rollback
3、ddl语句隐式提交：此时该语句之前的dml语句会作为事务的一部分提交
4、正常结束程序：oracle自动提交事务
5、非正常结束程序：oracle自动回滚事务


事务的特点：ACID
A-原子性  C-一致性 I-隔离性 D-持久性


commit显示提交前后数据库内部发生的变化
commit提交前
1、在undo表空间中，生成原有数据的备份。
2、在重做日志缓冲区创建重做日志。        
3、在数据库高速缓冲区根据语句修改数据。


commit提交后
1、在重做记录的事务表中标记上已提交事务的SCN，说明事务已提交。
2、LGWR将事务的重做日志信息和已提交事务的SCN号写入重做日志文件。
3、oracle数据将释放对更改对象拥有的锁。


事务自动提交
SQL> set autocommit on;   
SQL> set autocommit off;


角色
角色是数据库各种权限的集合。典型的应用 grant dba to scott;
使用 grant 赋予权限，revoke 回收权限。


查看角色
select * from dba_roles;


创建角色
create role role_name not identified;
create role role_name identified by password;


修改角色
alter role role_name not identified;
alter role role_name identified by password;


为角色赋权限
grant 权限 to 角色;
grant create session,select any table,create view to role_name;


赋予用户角色
grant role_name to user_name1,user_name2;
grant role_name to public;   -- 将角色赋予所有用户
grant role_name to user_name with admin option; 该用户可以将该角色赋予其他用户


设置默认角色
alter user user_name default role all except role_name; --默认除role_name之外的所有角色
alter user user_name default role role_name;   --默认role_name角色
alter user user_name default role none;   --没有默认角色
alter user user_name default role all;   --默认所有角色


禁止和激活角色
禁止角色
SET ROLE NONE;


激活角色
set role role_name;
set role role_name identified by password;


激活所有角色
SET ROLE ALL;


激活除role_name外所有角色
SET ROLE ALL EXCEPT role_name;   -- none不能和except结合使用


回收角色
revoke role_name from user_name;


删除角色
drop role role_name;


查看系统定义的角色信息
select * from role_sys_privs;


表
数据的存储类型： nvarchar2(size)/varchar2(size) 存储不定长数据。  nchar(size)/char(size) 存储定长数据。
rowid:是数据库中每一行的唯一标识，隐式存储。


创建表
create table table_name(
column_name1 type1,
column_name2 type2,
column_name3 type3
)
storage(initial 100k next 100k pctincrease 0 minextents 1 maxextents 8)
tablespace tablespace_name;


查看建表语句
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;


创建临时表
create global temporary table table_name
on commit preserve rows
as
select * from aa;


说明：
ON COMMIT PRESERVE ROWS   --表中的数据可以跨事务存在，当会话消失的时候数据被清空。但表结构还存在，所以最好在结束时删除临时表。
ON COMMIT DELETE ROWS     --表中的数据不可以跨事务存在，当事务提交或者回退时表中的数据被清空。但表结构还存在，所以最好在结束时删除临时表。


删除临时表
drop table table_name;


高水位线
表中数据曾达到的最大数据位置。
在MSSM中只会维持一个高水位线，在ASSM中还会维持一个低水位线。低水位线以下的数据都有数据，可以直接读取；低水位线到高水位线之间的数据不确定。


降低高水位线：
1、TRUNCATE语句。
2、alter table a shrink space;--在此之前表必须开启行移动功能 alter table AA ENABLE ROW MOVEMENT; 收缩索引 alter table a shrink space cascade;
3、移动表 alter table aa move tablespace tablespace_name;
4、重建表。


行链接和行迁移     --二者都应该避免，增加不必要的IO。
行迁移：如果更新时，已有块存放不了更新后的数据，新数据会存在另外一个块上，原位置留下一个指向新位置的指针。
行链接：如果存储的内容大于一个块的大小，则会在多个块上存储。


索引组织表
数据内容按索引顺序存储，类似于sqlserver 中的聚集索引表。


创建索引组织表
create table table_name(
column_name1 type1,
column_name2 type2,
column_name3 type3
constraint pk_column_name1 primary key(column_name1)
)
organization index
including column_name3      --column_name3(包括column_name3)之后的都放入溢出段
overflow tablespace tablespace_name;


删除列增加列修改列重命名列
alter table table_name add (column_name4 type4);
alter table table_name modify (column_name4 type3);
alter table table_name drop column column_name4 cascade constraints;
alter table table_name rename column old_column_name to new_column_name;


alter table table_name set unused column column_name4 cascade constraints;
alter table table_name drop unused columns;


删除表截断表
drop table table_name;
truncate table table_name;  --高水位线被降低，索引也会截断，不会触发删除触发器


索引
表访问三种方式：全表扫描、索引、rowid


索引扫描类型：索引唯一扫描(主键、唯一键)、索引全扫描、索引快速扫描（与全扫面类似，不排序）、索引范围扫描（有取范围的运算）。


B树索引不会存储null值，位图索引会。


跟索引有关的两个因素：选择性、集群因子
表中索引列数据选择性越高越好，集群因子（表中数据按索引列扫描，如果本行和前一行数据不在同一个数据块上，集群因子加一）越小越好。


直方图
表中数据值的分布。假如经过分析，70%的数据分布在500-600，则在查询500-600的值时不会走索引。


创建索引
create index index_name
on table_name(column_name1,column_name2)
tablespace tablespace_name;


create unique|bitmap index index_name
on table_name(column_name1 asc,column_name2 desc)
reverse     --反转键   1002 反转为 2001，1003 反转为 3001。解决B树右叶子节点争用问题
tablespace tablespace_name
logging|nologging
nosort;   -- 创建索引时不在排序


索引分类
B树索引、位图索引、hash索引、反向键索引、基于函数的索引。


索引重建
alter index index_name rebuild;
alter index index_name rebuild online;
alter index index_name rebuild tablespace tablespace_name;


合并索引碎片
alter index index_name coalesce;


删除索引
drop index index_name;