oracle事物和常用数据库对象.docx

1.1. 事物的控制控制事务银行转账李四给张三汇款ACIDA:原子性：整个事务中所有的步骤是不可分割的，原子性原则规定一个事务的各个步骤都必须完成，否则整个事务都不会完成。即保证一个事务中所有的操作都能完成或者都不能完成，如果事务在完成之前出现任何错误，那么数据库自身必须保证自动回滚所做过的任何事务部分（并且必须自动进行）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表中的内容，发现insert tom2的记录由于实例重启自动被回滚了。使用autocommit实现事物的自动提交即使执行回滚，查询结果仍然包含新插入的数据，关闭自动提交时可以使用set autocommit off关于事物的总结：1. 需要注意的是，Commit:只是用来确认这个数据已经正式的修改了，不一定非得写入硬盘，DBWn什么都不做。执行commit命令时发生的所有物理操作时LGWR进程将日志缓冲区的内容写入磁盘。DBWN进程完全没有执行任何操作。DBWN进程与提交事物处理没有关系，不过最终DBWN进程会将变化的数据块写入磁盘。2. commit和rollback语句只应于DML语句，我们无法回滚DDL语句。DDL语句一旦被执行就会立即具有持久状态。3.自动提交和隐式提交：oracle在某些情况下可以进行自动提交：执行DDL语句是一种情况，退出某个用户进程也是一种自动提交。1.2. 索引索引是oracle提供的一个对象，提供了一种快速访问数据的途径，提高了数据库的检索性能。索引使数据库程序无需对整个表进行全表扫描，就可以在其中找到所需要的数据，就想书的目录，可以通过他快速查找所需信息，无需阅读整本书。oracle的数据库管理系统在访问数据时使用以下3种访问方法；1.全表扫描2.通过ROWID3.使用索引索引的分类：1，B树索引结构索引的顶部为根，其中包含指向下一级索引的项。下一级为分支块，分支块又指向索引中下一级的块，最低一级的块称为叶节点，其中包含指向表数据行的索引项。叶节点为双向连接，有助于按关键字值得升序和降序扫描索引。1.2.1. 创建普通索引创建普通索引的语法create unique index 索引名称 on 表名（列名）tablespace 表空间名称unique用于指定唯一索引，默认情况下为非唯一索引tablespace为索引指定表空间练习环境在雇员EMP表中，在雇员名称列创建B树索引，oracle创建的普通索引如果没有说明类型就是B树索引查看刚刚创将的索引 EMP_NAME_IDX1.2.2. 创建唯一索引和非唯一索引唯一索引：保证定义索引的列中没有任何重复值，唯一索引的索引关键字只能指向表中的一行。非唯一索引：定义索引的列中可以有重复值在薪水级别salgrade表中，为级别编号（grade)列创建唯一索引1.2.3. 反向键索引与常规B树索引相反，反向键索引在保持顺序的同时，反转索引列的字节。反向键索引通过反转索引键的数据值，使得索引的修改平均分布到整个索引树上，主要应用多个实例可同时访问同一个数据库的场景中。使用反向键索引将索引插入操作分散在多个索引块键，如果使用B数索引的情况下，由于索引关键字在索引树中的位置相近而处于同一个索引块中，多个实例同时更新时会发生冲突，从而导致I/O访问上的瓶颈。语法如下：CREATE index 索引名称 on 表名（列名） REVERSE;1.2.4. 位图索引位图索引适用于低基数的列，即该列的值是有限的几个，例如雇员表中的工种（job）列，即便是几百万条雇员记录，工种也是有限的。JOB列可以作为位图索引位图索引的优点：相对B树索引而言，基于位图索引列的查询可以减少响应时间相比其他索引技术，位图索引占用空间有所减少位图索引不应当在频繁发生INSERT，update,delete操作的表上使用，这是因为单个位图索引项指向表的很多数据行，当修改索引项时需要将其指向的数据行全部锁定，这会严重降低数据库的并发处理能力。位图索引适用于数据仓库和决策支持系统中.在雇员表（emp）表中，为工种（job）列创建位图索引在雇员表（emp）中，为雇员名称（ename）列创建大写函数索引基于函数的索引查看索引列相关的信息：索引名，表名，索引列1.3. 维护索引重建索引：索引需要维护，如果建立了索引的表中有大量的删除和插入操作，会使得索引很大，因为删除操作后，删除值得索引空间不能被自动重新使用。对于大表和DML操作频繁的表，索引的维护是很重要的。ORACLE提供了REBUILD指令来重建索引，使索引空间可以重用删除值所占用的空间，使索引更加进奏。在重建索引时，也可以修改索引的表空间合并索引碎片合并索引碎片可以释放部分磁盘空间，是索引维护的一种重要方式，也是维护磁盘空间的方式删除索引DROP INDEX语句删除索引删除雇员表中的INDEX_BIT_JOB位图索引1.4. 视图视图是一个虚表，不占用物理空间，因为视图本身的定义语句存储在数据字典里。视图中的数据是从一个或多个实际的表中获得。物化视图：也成实体化视图，含有实际数据，占用存储空间，在数据仓库中经常应用物化视图创建视图的语法CREATE OR REPLACE FORCE |NO FORCE VIEW view_name (alias ,alias.) as select_statement WITH CHECK_OPTION CONSTRAINT constraint WITH READ ONLY;在语法中OR REPLACE:如果视图已经存在，此选项将重新创建该视图。FORC:如果使用此关键字，则无论基表是否存在，都将创建视图NO FORCE:这是默认值，如果使用此关键字，则仅当基表存在时才创建视图VIEW_NAME:要创建的视图名ALIAS:指定由视图的查询所选择的的表达式或列的别名。别名的数目必须与视图所选择的的表达式的数据相匹配。select_statement:SELECT 语句WITH CHECK_OPTION：此选项指定只能插入或更新视图可以访问的行，constraint标识CHECK OPTION约束指定的名称WITH READ ONLY:此选项保证不能再视图上执行任何修改操作。创建带有错误的视图如果在CREATE VIEW语法中使用FORCE选项，即使存在以下情况，也会创建视图视图定义的查询引用了一个不存在的表视图定义的查询引用了现有表中无效的列。视图的所有者没有所需的权限。在这些情况下，oracle仅检查CREATE VIEW语句中语法错误，如果语法正确，将会创建视图，并将视图的定义存储在数据字典中，但是该视图却不能使用。这种视图被认为是带有错误创建的。可以用SHOW ERRORS VIEW视图名来查看错误1.4.1. 对单表的视图操作SQL create table order_master (orderno number(5) CONSTRAINT p_ord PRIMARY KEY, 2 odate DATE,vencode number(5), 3 o_status char(1);插入数据SQL insert into order_master values (1,to_date(2010-01-01,yyyy-mm-dd),1,a);SQL insert into order_master values (2,to_date(2011-01-01,yyyy-mm-dd),2,p);创建订单状态为p的视图，提示没有创建视图的权限授予SCOTT用户创建视图的权限创建视图SQL create view pen_view as select * from order_master where o_status = p;查询视图通过视图修改数据，将状态为“p”的订单修改为“d”SQL update pen_view SET o_status=d where o_status=p;如果修改成功，在查询视图将查询不出任何记录，因为修改了创建视图是作为条件的列为了避免修改视图后查询不到记录的现象，使用with check option语句创建检查约束以防止上述情况的发生，同时可以使用CONSTRAINT指定约束名称SQL create or replace view pen_view as select * from order_master where o_status=p 2 with check option constraint penv;为order_master插入记录SQL insert into order_master values (3,to_date(2011-01-01,yyyy-mm-dd),3,p);更新视图SQL update pen_view set o_status=d where o_status=p;提示with check option违反where子句1.4.2. 创建只读视图SQL create or replace view pen_view as select * from order_master with read only;查看视图为视图插入记录1.4.3. 创建带有错误的视图因为不存在venmast表创建表venmastSQL create table venmast (id int);手动编译刚才创建的错误视图查看视图1.4.4. 创建带ORDER BY子句的视图SQL create or replace view pen_view as select * from order_master order by orderno;1.5. 复杂视图DML语句是指用于修改数据的INSERT，DELETE，UPDATE语句。因为视图是一个虚表，所以这些语句也可以与视图一同使用。一般情况下不通过视图修改数据，而是直接修改基本表，因为这样调理更清晰。在视图上使用DML语句有如下限制：（相对于表）DML语句只能修改视图中的一个基表如果对记录的修改违反了基表的约束条件，则将无法更新视图如果创建的视图包含连接运算符，DISTINCT运算符，集合运算符，聚合函数和group BY子句，则将无法更新视图。如果创建的视图包含伪列或表达式，则将无法更新视图。简单视图基于单个基表，不包括函数和分组函数，那么可以在此视图中进行INSERT，UPDATE,DELETE操作。这些操作实际上是在基表中插入、更新和删除行。复杂视图从多个提取数据，包括函数和分组函数，复杂视图不一定能进行DML操作。删除视图可以使用1.6. 物化视图物化视图是和普通视图相对应的，在oracle使用普通视图时，它会重复执行创建视图的所有sql语句，如果这样的SQL语句含有多张表的连接或者ORDER BY子句，而且表的数据量很大，则会非常耗时，效率非常低下。为了解决这个问题，oracle提出了物化视图的概念物化视图就是具有物理存储的特殊视图，占用物理空间，就象表一样，物化视图是基于表，物化视图等创建的。它需要和源表进行同步，不断的刷新物化视图中的数据。物化视图有两个重要概念：查询重写和物化视图的同步查询重写：对SQL语句进行重写。当用户使用SQL语句对基表进行查询时，如果已经建立了基于这些基表的物化视图，oracle将自动计算和使用物化视图来完成查询，在某些情况下可以节约查询时间，减少系统I/O。这种查询优化技术成为查询重写。参数QUERY_REWRITE_ENABLED决定是否使用重写查询。在创建物化视图时需要使用ENABLE QUERY REWRITE来启动查询重写功能可通过SHOW命令查看该参数的值物化视图的同步：物化视图是基于表创建的，所以当基表发生变化时，需要同步数据以更新物化视图中的数据，这样保持无话视图中的数据和基表的数据的一致性。oracle提供了两种物化视图刷新方式ON COMMIT:指物化视图在对基表的DML操作事物提交的通行进行刷新ON DEMAND：指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_IVIEW.refresh等方法来进行刷新，也可以通过JOB定时刷新选择刷新方式后，还需要选择一种刷新类型，刷新类型值刷新时基表与物化视图如何实现数据同步，oracle提供了一下4种刷新类型：COMPLETE:对整个物化视图进行完全刷新。FAST:采用增量刷新，只刷新自上次刷新以后进行的修改FORCE：oracle在刷新会判断是否可以进行快速刷新，如果可以则采用FAST刷新方式，否则使用COMPLETE方式。NEVER:物化视图不进行任何刷新1.6.1. 创建物化视图创建物化视图的前提条件：具备创建物化视图的权限，QUERY REWRITE的权限，以及对创建物化视图所涉及的表的访问权限和创建表的权限使用SCOTT用户来举例说明1.授予相应的权限SQL show user;USER is SYSSQL grant create materialized view to scott;SQL grant query rewrite to scott;SQL grant create any table to scott;SQL grant select any table to scott;2.创建物化视图日志物化视图日志是用户选择了FAST刷新类型时需要使用的，以增量同步基表的变化。对SCOTT用户的EMP表和DEPT表创建物化视图，所以对这两个基表创建物化视图日志SQL create materialized view log on dept with rowid;SQL create materialized view log on emp with rowid;创建物化视图通过CREATE MATERIALIEZED VIEW语句来创建物化视图，SQL create materialized view mtrlview_test 2 build immediate 3 refresh fast 4 on commit 5 enable query rewrite as 6 select d.dname,d.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,d.rowid d_rowid,e.rowid e_rowid 7 from dept d,emp e where d.deptno=e.deptno;其中：BUILD IMMEDIATE:该参数的意思是立即创建物化视图；也可以选择BUILD DEFFERED,该参数说明在物化视图定义以后不会立即执行，而是延迟执行，在使用该视图在创建。REFRESH FAST：刷新数据的类型选择FAST类型ON COMMIT：在基表有更新时提交后立即更新物化视图ENABLE QUERY REWRITE :启动查询重写功能，在创建物化视图是明确说明启用查询重写功能。AS：定义后面的查询语句查询体：物化视图的查询内容。该SQL语句的查询结果集输出到物化视图中，保存在由oracle自动创建的表中。删除物化视图1.7. 序列序列是用来生成唯一，连续的整数的数据库对象，序列通常用来自动生成主键或唯一键的值。序列可以按升序或降序排列。1.7.1. 创建序列授予SCOTT用户创建序列的权限SQL grant create sequence to scott;创建序列，从序号1开始，每次增加1，最大为100，不循环，SQL create sequence s1 2 start with 1 3 increment by 1 4 maxvalue 100 5 nocycle 6 cache 10;查看序列可以SQL select * from user_sequences;1.7.2. 如何从序列中取值查看当前已经取到的值SQL select s1.currval from dual;从序列中取值清除表中的记录从序列中取值在打开一个sqlplus会话，使用SCOTT用户连接会发现所有的会话会共享一个序列。执行以下命令会发现ID不连续，这个也是难免得。查询序列的当前值是8假如实例重启后按创建序列的要求，每次会拿10个序列号放到缓存中，实例重启后，缓存中的序列就会消失，会发现此时序列从11开始了，缓存中缓存的是11到20再次重新启动实例此时序列从21开始。缓存中缓存的是21到301.7.3. 修改序列 修改序列为没有最大封顶值修改序列号每次增值值为10修改序列放到缓存当中的数量为201.7.4. 删除序列1.8. 同义词同义词分为私用同义词和公有同义词私有同义词只能被当前模式的用户访问，私有同义词名称不能不可与当前模式的对象名同名。要在自身的模式创建私有同义词，用户必须拥有创建同义词的系统权限。要在其他用户模式下创建私有同义词，用户必须拥有CREATE ANY SYNONYM系统权限。创建私有同义词的语法如下：CREATE OR REPLACE SYNONYM schema.synonym_name FOR schema.object_name;OR REPLACE:在同义词存在的情况下替换该同义词synonym_name：要创建同义词的名称object_name:指定要为之创建同义词的对象的名称。创建tom用户授予create session授予tom用户创建表的权限使用tom用户创建表aaSys用户若要访问tom用户下的aa表，select语句这样写SQLselect * from tom.aa;可以给tom.aa起个别名xx，以后访问xx就代表scott.aa;查看同义词使用tom用户连接SqlDesc user_synonms;SqlSelect * from user_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用户创建的公有同义词XXXDROP SYNONYM语句用于从数据库中删除同义词，要删除同义词，用户必须拥有相应的权限。这样删除的是同义词，并不是对应的表。1.9. 分区表的含义oracle允许用户把一个表中的所有行分成几部分，并将他们存储在不同的表空间。分成的每一个部分称为一个分区，被分区的表称为分区表。对于包含大量数据的表来说，分区很有用，表分区有以下有点： 改善表的查询性能，在对表进行分区后，用户执行sql查询时可以只访问特定的分区而非整个表。 表更容易管理，因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。 便于备份和恢复，可以独立的备份和恢复每个分区 提高数据安全性，将不同的分区分布在不同的磁盘，可以减小所有分区的数据同时损坏的可能性。符合以下条件的表可以创建分区表： 数据量大