oracle事物和常用数据库对象.docx
《oracle事物和常用数据库对象.docx》由会员分享，可在线阅读，更多相关《oracle事物和常用数据库对象.docx（31页珍藏版）》请在冰豆网上搜索。
oracle事物和常用数据库对象
1.1.事物的控制
控制事务
银行转账
李四给张三汇款
ACID
A:
原子性：
整个事务中所有的步骤是不可分割的，原子性原则规定一个事务的各个步骤都必须完成，否则整个事务都不会完成。
即保证一个事务中所有的操作都能完成或者都不能完成，如果事务在完成之前出现任何错误，那么数据库自身必须保证自动回滚所做过的任何事务部分（并且必须自动进行）
C:
一致性：
无论是事务前，事务中，事务后，数据始终处于一致的状态。
例如李四给张三汇款10000，那么就需要从李四账户减去10000，给张三的账户增加10000。
Oracle使用撤销段来保证数据的一致性。
I:
隔离性：
隔离性的原则规定，未完成的事务必须不可视。
在某个事务进行期间，只有执行该事务的会话能看见所做的变化，而其他所有的会话看见的都是没有变化的数据（而不是更改后的新值）。
这个规定的逻辑含义是：
首先，由于整个事务可能没有全部完成，因此不允许其他用户看到可能回滚的变化；其次，在某个事务进行期间，数据是不连贯的，李四的账户减掉10000，但张三的账户还没有增加10000。
事务的隔离性要求数据库必须对其他用户隐藏正在进行的事务，这些用户只能看到没有被更新的数据，只有在事务完成时，他们才能看到所有变化。
Oracle使用撤销段来保证事务的隔离性。
创建练习环境：
创建一个aa表，插入3条记录，并提交事物。
查看aa表的记录，插入一条记录，当我们执行一条DML语句时候，就自动开始了一个事物
此时insert记录的事务并没有提交，没有提交事务就没有真正的完成，此时还有rollback的机会。
Scott用户在当前会话中查看aa表时可以看到被插入的记录
打开一个新的sqlplus会话，查看aa表时会发现并没有新插入的记录：
这是事物的隔离性
在第一个sqlplus会话中提交事物
提交之后才能在第二个会话中看到被插入的第四条记录
D:
持久性：
一旦使用commit命令来结束某个事务，那么就必须保证数据库不丢失这个事务。
在事务进行期间，隔离性的原则要求除了指定会话涉及的用户之外的任何用户都不能查看当前所做的变化。
不过事务一旦完成，所有用户都必须能够立即看到所做的变化，同时数据库必须保证这些变化绝不会丢失。
Oracle通过使用日志文件来满足这个需求。
日志文件具有两种形式：
联机重做日志文件，归档重做日志文件。
一个正确配置的oracle数据库是不可能丢失数据的。
当然用户的错误（包括不恰当的DML或删除对象）也会造成数据的丢失
DDL语句有自动提交功能（create、drop、truncate、alter）
创建表AA，插入一条记录
回退事物
在表中插入一条记录
在sqlplus中正常退出
在另外一个sqlplus中查看aa表中的记录，会发现新插入的tom1的记录了。
如果使用sqlplus工具更改了数据之后，正常退出sqlplus时，oracle会自动提交事物。
目前aa表中只有tom1的记录，
模拟实例重启
使用scott用户连接，查看aa表中的内容，发现inserttom2的记录由于实例重启自动被回滚了。
使用autocommit实现事物的自动提交
即使执行回滚，查询结果仍然包含新插入的数据，关闭自动提交时可以使用setautocommitoff
关于事物的总结：
1.需要注意的是，Commit:
只是用来确认这个数据已经正式的修改了，不一定非得写入硬盘，DBWn什么都不做。
执行commit命令时发生的所有物理操作时LGWR进程将日志缓冲区的内容写入磁盘。
DBWN进程完全没有执行任何操作。
DBWN进程与提交事物处理没有关系，不过最终DBWN进程会将变化的数据块写入磁盘。
2.commit和rollback语句只应于DML语句，我们无法回滚DDL语句。
DDL语句一旦被执行就会立即具有持久状态。
3.自动提交和隐式提交：
oracle在某些情况下可以进行自动提交：
执行DDL语句是一种情况，退出某个用户进程也是一种自动提交。
1.2.索引
索引是oracle提供的一个对象，提供了一种快速访问数据的途径，提高了数据库的检索性能。
索引使数据库程序无需对整个表进行全表扫描，就可以在其中找到所需要的数据，就想书的目录，可以通过他快速查找所需信息，无需阅读整本书。
oracle的数据库管理系统在访问数据时使用以下3种访问方法；
1.全表扫描
2.通过ROWID
3.使用索引
索引的分类：
1，B树索引结构
索引的顶部为根，其中包含指向下一级索引的项。
下一级为分支块，分支块又指向索引中下一级的块，最低一级的块称为叶节点，其中包含指向表数据行的索引项。
叶节点为双向连接，有助于按关键字值得升序和降序扫描索引。
1.2.1.创建普通索引
创建普通索引的语法
create[unique]index索引名称on表名（列名）[tablespace表空间名称]
[unique]用于指定唯一索引，默认情况下为非唯一索引
[tablespace]为索引指定表空间
练习环境
在雇员EMP表中，在雇员名称列创建B树索引，oracle创建的普通索引如果没有说明类型就是B树索引
查看刚刚创将的索引EMP_NAME_IDX
1.2.2.创建唯一索引和非唯一索引
唯一索引：
保证定义索引的列中没有任何重复值，唯一索引的索引关键字只能指向表中的一行。
非唯一索引：
定义索引的列中可以有重复值
在薪水级别salgrade表中，为级别编号（grade）列创建唯一索引
1.2.3.反向键索引
与常规B树索引相反，反向键索引在保持顺序的同时，反转索引列的字节。
反向键索引通过反转索引键的数据值，使得索引的修改平均分布到整个索引树上，主要应用多个实例可同时访问同一个数据库的场景中。
使用反向键索引将索引插入操作分散在多个索引块键，如果使用B数索引的情况下，由于索引关键字在索引树中的位置相近而处于同一个索引块中，多个实例同时更新时会发生冲突，从而导致I/O访问上的瓶颈。
语法如下：
CREATEindex索引名称on表名（列名）REVERSE;
1.2.4.位图索引
位图索引适用于低基数的列，即该列的值是有限的几个，例如雇员表中的工种（job）列，即便是几百万条雇员记录，工种也是有限的。
JOB列可以作为位图索引
位图索引的优点：
相对B树索引而言，基于位图索引列的查询可以减少响应时间
相比其他索引技术，位图索引占用空间有所减少
位图索引不应当在频繁发生INSERT，update,delete操作的表上使用，这是因为单个位图索引项指向表的很多数据行，当修改索引项时需要将其指向的数据行全部锁定，这会严重降低数据库的并发处理能力。
位图索引适用于数据仓库和决策支持系统中.
在雇员表（emp）表中，为工种（job）列创建位图索引
在雇员表（emp）中，为雇员名称（ename）列创建大写函数索引
基于函数的索引
查看索引列相关的信息：
索引名，表名，索引列
1.3.维护索引
重建索引：
索引需要维护，如果建立了索引的表中有大量的删除和插入操作，会使得索引很大，因为删除操作后，删除值得索引空间不能被自动重新使用。
对于大表和DML操作频繁的表，索引的维护是很重要的。
ORACLE提供了REBUILD指令来重建索引，使索引空间可以重用删除值所占用的空间，使索引更加进奏。
在重建索引时，也可以修改索引的表空间
合并索引碎片
合并索引碎片可以释放部分磁盘空间，是索引维护的一种重要方式，也是维护磁盘空间的方式
删除索引
DROPINDEX语句删除索引
删除雇员表中的INDEX_BIT_JOB位图索引
1.4.视图
视图是一个虚表，不占用物理空间，因为视图本身的定义语句存储在数据字典里。
视图中的数据是从一个或多个实际的表中获得。
物化视图：
也成实体化视图，含有实际数据，占用存储空间，在数据仓库中经常应用物化视图
创建视图的语法
CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview_name[（alias[,alias]..）]asselect_statement[WITHCHECK_OPTION[CONSTRAINTconstraint]][WITHREADONLY];
在语法中
ORREPLACE:
如果视图已经存在，此选项将重新创建该视图。
FORC:
如果使用此关键字，则无论基表是否存在，都将创建视图
NOFORCE:
这是默认值，如果使用此关键字，则仅当基表存在时才创建视图
VIEW_NAME:
要创建的视图名
ALIAS:
指定由视图的查询所选择的的表达式或列的别名。
别名的数目必须与视图所选择的的表达式的数据相匹配。
select_statement:
SELECT语句
WITHCHECK_OPTION：
此选项指定只能插入或更新视图可以访问的行，constraint标识CHECKOPTION约束指定的名称
WITHREADONLY:
此选项保证不能再视图上执行任何修改操作。
创建带有错误的视图
如果在CREATEVIEW语法中使用FORCE选项，即使存在以下情况，也会创建视图
视图定义的查询引用了一个不存在的表
视图定义的查询引用了现有表中无效的列。
视图的所有者没有所需的权限。
在这些情况下，oracle仅检查CREATEVIEW语句中语法错误，如果语法正确，将会创建视图，并将视图的定义存储在数据字典中，但是该视图却不能使用。
这种视图被认为是带有错误创建的。
可以用SHOWERRORSVIEW视图名来查看错误
1.4.1.对单表的视图操作
SQL>createtableorder_master（ordernonumber（5）CONSTRAINTp_ordPRIMARYKEY,
2odateDATE,vencodenumber（5）,
3o_statuschar
（1））;
插入数据
SQL>insertintoorder_mastervalues（1,to_date（'2010-01-01','yyyy-mm-dd'）,1,'a'）;
SQL>insertintoorder_mastervalues（2,to_date（'2011-01-01','yyyy-mm-dd'）,2,'p'）;
创建订单状态为"p"的视图，提示没有创建视图的权限
授予SCOTT用户创建视图的权限
创建视图
SQL>createviewpen_viewasselect*fromorder_masterwhereo_status='p';
查询视图
通过视图修改数据，将状态为“p”的订单修改为“d”
SQL>updatepen_viewSETo_status='d'whereo_status='p';
如果修改成功，在查询视图将查询不出任何记录，因为修改了创建视图是作为条件的列
为了避免修改视图后查询不到记录的现象，使用withcheckoption语句创建检查约束以防止上述情况的发生，同时可以使用CONSTRAINT指定约束名称
SQL>createorreplaceviewpen_viewasselect*fromorder_masterwhereo_status='p'
2withcheckoptionconstraintpenv;
为order_master插入记录
SQL>insertintoorder_mastervalues（3,to_date（'2011-01-01','yyyy-mm-dd'）,3,'p'）;
更新视图
SQL>updatepen_viewseto_status='d'whereo_status='p';
提示withcheckoption违反where子句
1.4.2.创建只读视图
SQL>createorreplaceviewpen_viewasselect*fromorder_masterwithreadonly;
查看视图
为视图插入记录
1.4.3.创建带有错误的视图
因为不存在venmast表
创建表venmast
SQL>createtablevenmast（idint）;
手动编译刚才创建的错误视图
查看视图
1.4.4.创建带ORDERBY子句的视图
SQL>createorreplaceviewpen_viewasselect*fromorder_masterorderbyorderno;
1.5.复杂视图
DML语句是指用于修改数据的INSERT，DELETE，UPDATE语句。
因为视图是一个虚表，所以这些语句也可以与视图一同使用。
一般情况下不通过视图修改数据，而是直接修改基本表，因为这样调理更清晰。
在视图上使用DML语句有如下限制：
（相对于表）
DML语句只能修改视图中的一个基表
如果对记录的修改违反了基表的约束条件，则将无法更新视图
如果创建的视图包含连接运算符，DISTINCT运算符，集合运算符，聚合函数和groupBY子句，则将无法更新视图。
如果创建的视图包含伪列或表达式，则将无法更新视图。
简单视图基于单个基表，不包括函数和分组函数，那么可以在此视图中进行INSERT，UPDATE,DELETE操作。
这些操作实际上是在基表中插入、更新和删除行。
复杂视图从多个提取数据，包括函数和分组函数，复杂视图不一定能进行DML操作。
删除视图可以使用
1.6.物化视图
物化视图是和普通视图相对应的，在oracle使用普通视图时，它会重复执行创建视图的所有sql语句，如果这样的SQL语句含有多张表的连接或者ORDERBY子句，而且表的数据量很大，则会非常耗时，效率非常低下。
为了解决这个问题，oracle提出了物化视图的概念
物化视图就是具有物理存储的特殊视图，占用物理空间，就象表一样，物化视图是基于表，物化视图等创建的。
它需要和源表进行同步，不断的刷新物化视图中的数据。
物化视图有两个重要概念：
查询重写和物化视图的同步
查询重写：
对SQL语句进行重写。
当用户使用SQL语句对基表进行查询时，如果已经建立了基于这些基表的物化视图，oracle将自动计算和使用物化视图来完成查询，在某些情况下可以节约查询时间，减少系统I/O。
这种查询优化技术成为查询重写。
参数QUERY_REWRITE_ENABLED决定是否使用重写查询。
在创建物化视图时需要使用ENABLEQUERYREWRITE来启动查询重写功能
可通过SHOW命令查看该参数的值
物化视图的同步：
物化视图是基于表创建的，所以当基表发生变化时，需要同步数据以更新物化视图中的数据，这样保持无话视图中的数据和基表的数据的一致性。
oracle提供了两种物化视图刷新方式
ONCOMMIT:
指物化视图在对基表的DML操作事物提交的通行进行刷新
ONDEMAND：
指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_IVIEW.refresh等方法来进行刷新，也可以通过JOB定时刷新
选择刷新方式后，还需要选择一种刷新类型，刷新类型值刷新时基表与物化视图如何实现数据同步，oracle提供了一下4种刷新类型：
COMPLETE:
对整个物化视图进行完全刷新。
FAST:
采用增量刷新，只刷新自上次刷新以后进行的修改
FORCE：
oracle在刷新会判断是否可以进行快速刷新，如果可以则采用FAST刷新方式，否则使用COMPLETE方式。
NEVER:
物化视图不进行任何刷新
1.6.1.创建物化视图
创建物化视图的前提条件：
具备创建物化视图的权限，QUERYREWRITE的权限，以及对创建物化视图所涉及的表的访问权限和创建表的权限
使用SCOTT用户来举例说明
1.授予相应的权限
SQL>showuser;
USERis"SYS"
SQL>grantcreatematerializedviewtoscott;
SQL>grantqueryrewritetoscott;
SQL>grantcreateanytabletoscott;
SQL>grantselectanytabletoscott;
2.创建物化视图日志
物化视图日志是用户选择了FAST刷新类型时需要使用的，以增量同步基表的变化。
对SCOTT用户的EMP表和DEPT表创建物化视图，所以对这两个基表创建物化视图日志
SQL>creatematerializedviewlogondeptwithrowid;
SQL>creatematerializedviewlogonempwithrowid;
创建物化视图
通过CREATEMATERIALIEZEDVIEW语句来创建物化视图，
SQL>creatematerializedviewmtrlview_test
2buildimmediate
3refreshfast
4oncommit
5enablequeryrewriteas
6selectd.dname,d.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,d.rowidd_rowid,e.rowide_rowid
7fromdeptd,empewhered.deptno=e.deptno;
其中：
BUILDIMMEDIATE:
该参数的意思是立即创建物化视图；也可以选择BUILDDEFFERED,该参数说明在物化视图定义以后不会立即执行，而是延迟执行，在使用该视图在创建。
REFRESHFAST：
刷新数据的类型选择FAST类型
ONCOMMIT：
在基表有更新时提交后立即更新物化视图
ENABLEQUERYREWRITE:
启动查询重写功能，在创建物化视图是明确说明启用查询重写功能。
AS：
定义后面的查询语句
查询体：
物化视图的查询内容。
该SQL语句的查询结果集输出到物化视图中，保存在由oracle自动创建的表中。
删除物化视图
1.7.序列
序列是用来生成唯一，连续的整数的数据库对象，序列通常用来自动生成主键或唯一键的值。
序列可以按升序或降序排列。
1.7.1.创建序列
授予SCOTT用户创建序列的权限
SQL>grantcreatesequencetoscott;
创建序列，从序号1开始，每次增加1，最大为100，不循环，
SQL>createsequences1
2startwith1
3incrementby1
4maxvalue100
5nocycle
6cache10;
查看序列可以
SQL>select*fromuser_sequences;
1.7.2.如何从序列中取值
查看当前已经取到的值
SQL>selects1.currvalfromdual;
从序列中取值
清除表中的记录
从序列中取值
在打开一个sqlplus会话，使用SCOTT用户连接
会发现所有的会话会共享一个序列。
执行以下命令
会发现ID不连续，这个也是难免得。
查询序列的当前值是8
假如实例重启后
按创建序列的要求，每次会拿10个序列号放到缓存中，实例重启后，缓存中的序列就会消失，
会发现此时序列从11开始了，缓存中缓存的是11到20
再次重新启动实例
此时序列从21开始。
缓存中缓存的是21到30
1.7.3.修改序列
修改序列为没有最大封顶值
修改序列号每次增值值为10
修改序列放到缓存当中的数量为20
1.7.4.删除序列
1.8.同义词
同义词分为私用同义词和公有同义词
私有同义词只能被当前模式的用户访问，私有同义词名称不能不可与当前模式的对象名同名。
要在自身的模式创建私有同义词，用户必须拥有创建同义词的系统权限。
要在其他用户模式下创建私有同义词，用户必须拥有CREATEANYSYNONYM系统权限。
创建私有同义词的语法如下：
CREATE[ORREPLACE]SYNONYM[schema.]synonym_nameFOR[schema.]object_name;
[ORREPLACE]:
在同义词存在的情况下替换该同义词
synonym_name：
要创建同义词的名称
object_name:
指定要为之创建同义词的对象的名称。
创建tom用户授予createsession
授予tom用户创建表的权限
使用tom用户创建表aa
Sys用户若要访问tom用户下的aa表，select语句这样写
SQL>select*fromtom.aa;
可以给tom.aa起个别名xx，以后访问xx就代表scott.aa;
查看同义词
使用tom用户连接
Sql>Descuser_synonms;
Sql>Select*fromuser_synonyms;
1.8.1.创建私有同义词
使用sys用户连接
使用TOM用户连接
创建的xx同义词是私有的同义词，tom用户创建的xx同义词，只有tom用户能使用
使用sys用户连接
使用TOM用户连接
使用sys用户连接，为tom用户创建公有同义词的权限
1.8.2.创建公有同义词
使用sys用户访问XXX公有同义词，就是在访问tom.aa表了
1.8.3.删除公有同义词
tom用户没有删除公有同义词的权限。
为tom用户授权删除公有同义词的权限
删除tom用户创建的公有同义词XXX
DROPSYNONYM语句用于从数据库中删除同义词，要删除同义词，用户必须拥有相应的权限。
这样删除的是同义词，并不是对应的表。
1.9.分区表的含义
oracle允许用户把一个表中的所有行分成几部分，并将他们存储在不同的表空间。
分成的每一个部分称为一个分区，被分区的表称为分区表。
对于包含大量数据的表来说，分区很有用，表分区有以下有点：
✧改善表的查询性能，在对表进行分区后，用户执行sql查询时可以只访问特定的分区而非整个表。
✧表更容易管理，因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。
✧便于备份和恢复，可以独立的备份和恢复每个分区
✧提高数据安全性，将不同的分区分布在不同的磁盘，可以减小所有分区的数据同时损坏的可能性。
符合以下条件的表可以创建分区表：
✧数据量大于2GB
✧已有的数据和新添加的数据有明显的界限划分
表分区对用户是透明的，即应用程序可以不知道表已被分区，在更新和查询分区表是当做普通表来操作，但oracle优化程序是知道表已经被分区了。
要分区的表不能具有LONG和LONGRAW数据类型的列。
分区表的分类：
oracle提供的分区方法有范围分区，列表分区，散列分区，复合分区，间隔分区和虚拟列分区等。
其中间隔分区和虚拟列分区是oracle11g的新增特性。
范围分
升级会员 