Oracle 11g 管理与编程基础

大型数据库技术（Oracle11g）定位oracle分两大块，一块是开发，一块是管理。开发主要是写写存储过程、触发器什么的，还有就是用Oracle的Develop工具做form。有点类似于程序员，需要有较强的逻辑思维和创造能力，个人觉得会比较辛苦，是青春饭；管理则需要对oracle数据库的原理有深刻的认识，有全局操纵的能力和紧密的思维，责任较大，因为一个小的失误就会down掉整个数据库，相对前者来说，后者更看重经验。因为数据库管理的责任重大，很少公司愿意请一个刚刚接触oracle的人去管理数据库。对于刚刚毕业的年轻人来说，可以先选择做开发，有一定经验后转型，去做数据库的管理。当然，这个还是要看个人的实际情况来定。王鹏杰，王存睿，郑海旭.Oracle11g管理与编程基础，人民邮电出版社同时关注Oracle管理和开发两个方面，内容全面而基础案例丰富，理论深入，两者完美结合信息系统综合案例代表性强，统合了Oracle管理与开发配套教学学习资源丰富价格适中（30元左右）使用教材使用教材（续）《Oracle数据库管理实务》王孝枕人民邮电出版社。侧重于核心概念和理论的介绍。相对对读者要求高。深入解析Oracle:DBA入门、进阶与诊断案例，盖国强，人民邮电出版社《Oracle10g数据库管理员使用大全》滕永昌清华出版社。偏重于基础知识和操作的讲解。入门教程。但是没有编程方面介绍。其他参考教材其他参考教材（续）清华大学出版社的一套翻译的书对读者要求较高。《Oracle8iDBA考试学习指南》内容比较全面，不过是英文版的。JasonS.Couchman机械工业出版社推荐网站国内：/http://

国外：//课程目的掌握大型数据库（Oracle）的管理和开发技术培养动手实践能力，特别是服务器的管理能力怎么学好Oracle认真学习Oracle的基本概念和基本运作原理多动手实践Oracle的体系结构OracleInstance数据库实例：进程与内存的集合。动态的概念。OracleDatabase数据库：相关OS文件的集合。静态的概念。Instance&Database对应关系OracleRAC（RealApplicationCluster）Oracle的服务OracleService<SID>：oracle实例Oracle<HOME_NAME>TNSListener：Oracle监听服务OracleVssWriter<SID>OracleDBConsole<SID>：Oracle控制台程序OracleJobScheduler<SID>

：作业调度数据库名、实例名、服务名数据库名：一个数据库区分它数据库的标志。实例名：用于数据库与操作系统之间的联系。全局数据库名：数据库名＋域名服务名＝全局数据库名oracle_sid：操作系统的一个环境变量参数文件中的一个名字实例##################################db_name=‘soft07’db_domain=instance_name=dlnuservice_name=##################################Oracle_sid作用Oracle的目录结构童鞋们，下课了。。。任课：***Email:***@***.大型数据库技术（Oracle11g）欢迎全国读者加入教材/课程讨论QQ群：249449451问题探讨、授课咨询请加作者QQ：535413184第3章Oracle数据库（OracleDatabase）目标掌握Oracle的物理存储结构掌握Oracle的逻辑存储结构理解两种结构之间的联系Oracle数据库的两种结构逻辑存储结构是数据概念上的组织，主要由表空间（Tablespace）、数据段（Segment）、区（Extent）和数据块（DatabaseBlock）组成。物理结构是现实的数据存储单元，由文件（数据文件、日志文件、参数文件和控制文件）构成。3.1Oracle数据库的逻辑结构逻辑结构物理结构数据库表空间Oracle数据库块段区硬盘操作系统文件操作系统块Createtablestudent(

snovarchar2(10)primarykey,

Snamevarchar2(20),

Sagenumber)

Pctfree10

Pctused40

TablespaceuserData

Storage(initial1Mnext512Kminextents5pctincrease0);

两种存储结构之间的关系数据文件只和一个表空间联系存储在表空间的可能的几个数据文件中表空间索引1表1表2表4索引3表5索引2表3数据文件1数据文件2数据库、表空间、数据文件数据库和表空间。一个Oracle数据库包含一个或者多个逻辑存储单元，称为表空间，用于存储数据库的所有数据。表空间和数据文件。Oracle数据库的每个表空间包含一个或者多个‘.dbf’的操作系统文件，称为数据文件。数据库和数据文件。数据库的数据存储在数据文件中，一个或者几个数据文件存储一个表空间中的所有数据。各种类型的表空间Tablespace类型用途说明SYSTEM存储系统数据字典的相关表、视图TEMP存放系统处理排序时所用的过渡型数据UNDOTBS存放系统执行交易回滚所需的建议前数据TOOLS存放Oracle一些工具程序执行时所需的表APP_DATA存放用户的用户程序表APP_INDEX存放应用程序索引表Oracle数据库中的数据逻辑地存储在表空间并物理地存储在数据文件中。DataFileTablespaceSegmentExtentExtentDataBlocks表空间创建表空间（数据字典表空间）表空间Oracle表空间类型数据字典管理表空间(Oracle7,8,8i)本地化管理表空间

(Oracle8i以上)UniformAutoAllocate创建本地化管理表空间（Uniform）CREATETABLESPACEtablespaceDATAFILE‘filespec’EXTENTMANAGEMENTLOCALUNIFORMSIZExxxM1区的大小相同，不产生磁盘碎片。2默认为1M，可在创建表空间时指定。3实体使用统一的存储参数。

SIZExxxKCREATETABLESPACEtablespaceDATAFILE‘filespec’EXTENTMANAGEMENTLOCALAUTOALLOCATESIZExxxM特点：

1.区的大小在表空间级定义，非实体级

2.表空间类型缺省为AUTOALLOCATE

创建本地化管理表空间（Autoallocate）Autoallocate类型区的分配表大小区大小64K64K1M1M64M8M1000M64M例子CREATETABLESPACEuserDataDATAFILE'e:\oracle\oradata\users001.dbf'SIZE25MAUTOEXTENDONNEXT1280KMAXSIZE1000m,'d:\oracle\oradata\users002.dbf'SIZE25MAUTOEXTENDONNEXT1280KMAXSIZE1000mEXTENTMANAGEMENTLOCALuniformsize64k;

CREATETABLESPACEuserIndexDATAFILE'e:\oracle\oradata\indx01.dbf'SIZE25MEXTENTMANAGEMENTLOCALAUTOALLOCATE;修改本地化管理表空间ALTERTABLESPACEtablespaceDATAFILE‘filespec’ADDRNAMEDATAFILE‘filespec’TO目的ONLINEOFFLINE三种增大数据库的方式添加数据文件到表空间添加新的表空间增加数据文件的大小添加数据文件到表空间表数据库系统表空间AlterTablespacesystemAdddatafile‘data2.dbf’AlterTablespacesystemAdddatafile‘data3.dbf’Data1.dbfData2.dbfData3.dbf添加新的表空间表数据库系统表空间用户表空间CreatetablespaceusersDatafile‘Data3.dbf’两个表空间Data1.dbfData2.dbfData3.dbf改变数据文件的尺寸表数据库系统表空间用户表空间AlterdatabaseDatafile‘Data3.dbf’AUTOEXTENDonnext20MMAXSIZE1000MData1.dbfData2.dbfData3.dbf区Extent一个或多个extents组成一个segment。当一个segment创建时，至少包含一个extent。当segment增长时，新的extent被加到segment中。一个extent由一系列连续的Oracleblocks组成。一个extent不能跨越datafile。通过extent来给segment分配空间DataFileTablespaceSegmentExtentExtentDataBlocks数据块（DataBlocks）Oracledatablocks是Oracleserver存储、读、写的最小的存储单位。一个datablock对应一个或多个操作系统块。在Oracle数据库创建时，初始参数文件的DB_BLOCK_SIZE确定Oracle数据库Datablock的大小。Datablock的大小应该为操作系统块的整数倍，以减少I/O量。数据块结构块头空闲空间数据

行链(RowChaining)和行迁移

(RowMigration)如果Oracleblock设置的过小，当要插入一笔数据行的长度大于Oracleblock时候，则需要串联多个block来存储，称为RowChaining当一笔数据行因更新（update）而增加数据长度时，此时如果存储此数据行的block已经没有足够的空间来存放新增加的数据，则必须把整个数据行的内容迁移到另外一个数据库块。称为RowMigration。行链(RowChaining)和行迁移

(RowMigration)

80%80%40%Inserts1234InsertsPCTFREE=20

PCTUSED=40Insert(onfreelist)Inserts(offfreelist)CreatetableSTUDENT(

Idnumber,Namevarchar2(20),Agesmallint

)

pctfree20

pctused40例子带有块参数的表创建语句Createtablestudent(snovarchar2(10)primarykey,Snamevarchar2(20),Sagenumber)Pctfree10Pctused40TablespaceuserData；检测行链和行迁移@{oracle_home}\rdbms\admin\utlchain.sql，得到sys.chained_rows表Analyzetabletable_namelistchainedrows;Selecttable_name,head_rowidfromchained_rows控制和清除行链设定db_block_size要慎重。可控制段对象的长度。比如将一个表拆成几个表。控制和清除行迁移控制行迁移增长。迁移发生的原因？3.2物理存储结构数据文件是Oracle数据库中表空间中的逻辑对象在磁盘上的物理存储文件。重做日志文件，是Oracle数据库用来存放事务记录的文件。参数文件是Oracle创建特定数据库过程中用到的一些参数信息的文件。控制文件包含有关数据的重要的参数信息。物理结构归档日志文件Oracle数据库数据文件

控制文件初始化参数文件密码文件联机日志文件两种日志联机日志（OnlineLog）归档日志（ArchivedLog）联机日志的循环工作方式组2组1磁盘1磁盘2成员成员成员成员是否归档？Oracle对这些将日志有两种处理方式：一种保留起来，一种是扔掉。ARCHIVELOG模式

在覆盖之前进行复制脱机保存。是最安全的，如果有所有的日志的拷贝就能从所有类型的失败中恢复过来NOARCHIVELOG模式归档命令archiveloglist

查看是否归档设置归档过程：1、shutdown2、startupmount3、alterdatabasearchivelog;4、alterdatabaseopen;DatabaselogmodeNoArchiveModeAutomaticarchivalDisabledArchivedestinationD:\oracle\oradata\test\archiveOldestonlinelogsequence28613Currentlogsequence28615数据文件存放数据库的所有数据信息。以数据文件来存放数据库中的相关对象（如表、索引、视图等）。一个或者多个数据文件结合在一起形成一个表空间。日志文件日志文件也叫事务日志(TransactionLog)用来记录数据库的事务处理过程。因为所有的处理都记录在重做日志里，所以数据库系统可以使用这些事务记录进行恢复操作。重做日志以循环方式工作。日志以循环的方式工作，那么如果循环用到已有日志信息的一个日志文件，原先的信息将被覆盖。事务日志的工作原理

客户端应用程序将修改请求传递给服务器1Disk修改被保存到事务日志文件中3DiskCheckpoint事件对已经提交的事务写修改到数据文件上4读出数据页到缓冲区中并将修该写入缓冲区2DatabaseBufferRedoBuffer参数文件是一个ASCII文本文件。在数据库启动时，在创建实例和读取控制文件之前，先读取参数文件。参数文件的值决定着数据库和实例的特征。例如共享池、高速缓存、重做日志缓存分配、后台进程的启动，控制文件的读取等。控制文件数据库启动前先根据参数文件指定的位置读取控制文件。获取此数据库包含哪些datafiles与redologfiles以及这些文件的状态信息。如果出现了数据文件丢失或者不一致的现象，控制文件将通知数据库系统出现了故障。每当出现数据库检查点（Checkpoint）或者修改数据库结构后，都要修改控制文件。没有合法的控制文件，数据库将不能启动。控制文件中的信息1.数据库基本信息，如数据库名、数据库创建时间

2.表空间信息，数据文件的名称、位置、联机、脱机状态信息，日志文件的相关信息。

3.时间点信息，一般包括当前日志序列号和最近检查点信息数据库文件中的时间点信息控制文件初始化参数文件数据文件数据文件联机日志文件联机日志文件235534071235534071235534071235534071235534071235534071235534071

文件与数据启动过程之间关系3.3Oracle的启动与关闭Oracle的启动分为三个阶段：Nomount、Mount和OpenOracle的正常关闭也分为三个阶段：Closed、dismounted和instanceshutdownOracle关闭主要有三种方式：shutdownnormal、shutdownimmediate和shutdownabortNomount－OracleInstance启动读取数据库初始化参数文件的设置依参数文件设置分配SGA内存空间启动4个主要的后台进程和各参数文件中设置的辅助后台进程打开数据库的tracefile和alertfile。可在此阶段进行数据库的创建，可产生控制文件。Mount-OracleInstance挂上数据库读取初始化参数文件中指定的控制文件从控制文件中获取此数据库共包含哪些数据文件和重做日志文件以及这些文件的状态信息若需要InstanceRecovery，可在此步骤进行可进行数据库的备份与介质恢复Open－可联机使用打开所有的数据文件和重做日志文件，可以开始读写交易。用户可以联机使用OPENMOUNTNOMOUNTSHUTDOWNInstance

startedSTARTUPSHUTDOWN

数据库的启动过程（Nomount）OPENMOUNTNOMOUNTSHUTDOWNControlfileopenedforthisinstanceInstance

startedSTARTUPSHUTDOWN数据库的启动过程（Mount）

OPENMOUNTNOMOUNTSHUTDOWNAllfilesopenedasdescribedbythecontrolfileforthisinstanceControlfileopenedforthisinstanceInstance

startedSTARTUPSHUTDOWN数据库的启动过程（Open）数据库开启命令StartupStartupnomountAlterdatabasemountAlterdatabaseopenShutdownnormalShutdownimmediateShutdownabort数据库关闭命令Shutdownnormal停止接收新请求，等待所有连接都断开后才关闭关闭时间较长Shutdownimmediate未完成事务，回退已完成事务，写入数据文件Shutdownabort直接关闭数据库实例，相当于突然断电重启需要实例恢复童鞋们，下课了。。。任课：***Email:***@***.大型数据库技术（Oracle11g）欢迎全国读者加入教材/课程讨论QQ群：249449451问题探讨、授课咨询请加作者QQ：535413184第3章复习（数据库逻辑结构）逻辑结构物理结构数据库表空间Oracle数据库块段区硬盘操作系统文件操作系统块第3章复习（数据库的物理结构）归档日志文件Oracle数据库数据文件

控制文件初始化参数文件密码文件联机日志文件Oracle的进程及内存结构Oracle实例SGA重做日志缓存共享池数据字典缓存库缓存DBWRSMONPMONCKPTLGWR其他进程用户进程服务进程PGA数据块缓存归档日志文件Oracle数据库数据文件

控制文件初始化参数文件密码文件联机日志文件用户进程(UserProcess)用户进程是运行在客户端的应用程序的进程。用户进程与服务器进程的通信是通过标准的接口API来实现的（ODBC,ADO,JDBC等）。用户进程可以是SQLplus,OEM,各种开发程序开发的程序Oracle实例进程服务进程(ServerProcess)后台进程（Backgroundprocesses）服务进程(ServerProcess)

服务进程处理来自客户连接（用户进程）上的用户请求，并把处理结构传给用户进程。解析并执行（Parse&execute）UserProcess所传来的SQL指令。把UserProcess所需处理的数据从磁盘中的数据文件中的datablocks读入到SGA的databasebufferscache缓存中。将SQL指令的执行结果传回给UserProcess连接（Connection）及会话（Session）建立过程连接建立会话建立数据库用户用户进程服务进程Oracle服务器服务进程（ServerProcess）的类型专用服务器（DedicatedServer）数据库对每个UserProcess会启动一个ServerProcess与之对应。多线程服务器（Multi-TheadServer）在数据库启动时预先启动多个可以让所有UserProcesses共享的服务器(sharedserver)，当有用户请求的时候，调度程序(Dispatcher)将用户请求调度到某个服务器上。服务进程（ServerProcess）的类型（专用服务器模式）用户进程用户进程用户进程服务进程Oracle服务器服务进程服务进程服务进程（ServerProcess）的类型（多线程服务器模式）用户进程用户进程用户进程Oracle服务器服务进程调度程序服务进程后台进程（BackgroundProcesses）

四个最重要也是必须执行的后台进程，当数据库启动后，这四个进程自动于后台开始执行。SystemMonitor(SMON)ProcessMonitor(PMON)DatabaseWriter(DBWn)LogWriter(LGWR)SystemMonitor(SMON)职责:Instancerecovery（实例恢复）。收回执行如排序操作时用的临时段存储空间。将数据文件可用的片段空间合并成一个连续的较大的可用空间Oracle实例SGASMONOracle数据库数据文件

控制文件联机日志文件Instancerecovery过程前滚记录在redologs上已经提交的事务（因为oracle遵循先写日志原则，所以在实例失败的那一刻，已经提交的事务还有数据在缓存中没有来得及写入数据文件中）回退未提交的事务。打开数据库供用户连机。

当数据库实例意外失败（如操作系统死机，突然断电等）时，由于数据的更新是先在内存中更新，然后在一定的时机再写入数据文件，就有可能数据来不及存入数据文件而丢失。当重新启动实例后，应进行实例恢复。过程如下：ProcessMonitor(PMON)职责：清除非正常中断留下的孤儿会话，回退未提交事务，释放它所占用的锁，释放失败进程占用的资源。监视共享服务程序（sharedserver)与联机调度程序，当其崩溃时，重新启动它们。Oracle实例SGAPMONPGAareaDatabaseWriter(DBWn)职责：将在缓存区中脏数据块写如到数据文件中。不是每个数据块都马上写入数据文件，而是等待满足一定的条件后，才成批的将脏数据块写入到数据文件中。Oracle实例SGADBWn数据库块缓存Oracle数据库数据文件

控制文件联机日志文件DBW写入的时机发生一个检查点(checkpoint)脏数据的长度达到init.ora文件中DB_BLOCK_WRITE_BATCH参数值的一半。使用的缓存区的数量达到init.ora参数DB_BLOCK_MAX_SCAN。DBW后台进程发生超时（大约每三秒）。LogWriter(LGWR)职责：为了提高执行效率，将事务数据的日志信息写入在日志缓存区中，由LGWR在适当的时机将缓存区中的事务记录写入到磁盘的日志文件中。Oracle实例SGA日志缓存DBWnLGWROracle数据库数据文件

控制文件联机日志文件LGWR写入的时机发生提交重做日志缓冲区达到三分之一在DBWn要将数据缓冲区的脏数据写入数据文件之前LGWR发生超时。关于LGWR要注意当LGWR成功的将提交信息写入联机重做日志文件，Oracle就认为一个事务已经完成，将成功码返回给服务器进程。LGWR在检查点进程未启动的情况下，兼做检查点进程所做的工作LOG_CHECHPOINT_INTERVALLOG_CHECKPOINT_TIMEOUT其他可选后台进程 ARCH：archiveprocess归档进程CKPT：checkpoint检查点进程RECO：recovery恢复进程，维护分布式数据库环境下的一致性LCKn：lock锁进程，用于多个实例之间的封锁Dnnn：dispatchers调度进程，用于多线程服务器模式下（muti－threadserver）内存结构（MemoryStructure）两种内存结构SystemGlobalArea(SGA)。在实例启动的时候分配，用于存放系统信息。ProgramGlobalArea(PGA)。当服务进程（ServerProcess）启动的时候启动，用于存储特定的用户进程的信息。SGAFixedSize：引导区域VariableSize：包括shared_pool_size、java_pool_size、large_pool_sizeDatabaseBuffersRedoBuffersSGADatabasebuffercacheRedologbufferSharedpool（用于存储最近的SQL语句执行计划和最近用的数据字典信息）LibrarycacheSharedSQLareaSharedPL/SQLareaDatadictionarycacheLarge_pool_sizeMTS存储会话信息并行查询时作为消息缓存RMAN备份时使用shared_pool_size在充分使用绑定变量的环境中，建议设置足够的shared_pool_size利用软解析（在共享池中寻找相同SQL），降低硬解析select*fromEMPwherename=‘TOM’;select*fromEMPwherename=‘JERRY’;select*fromEMPwherename=:emp_name;设置建议databuffer一般可以尽可能的大shared_pool_size应该适度logbuffer在3~5MB以内就可以了建议shared_pool_size300~500M专用服务器模式VS多线程服务器模式下的PGA栈会话信息、排序区、游标信息SGASGA会话信息专用服务器模式

多线程服务器模式

Stackspace会话信息、排序区、游标信息童鞋们，下课了。。。任课：***Email:***@***.大型数据库技术（Oracle11g）欢迎全国读者加入教材/课程讨论QQ群：249449451问题探讨、授课咨询请加作者QQ：535413184OracleNet结构

监听器(LISTENER)

服务器端的一种网络服务，监听客户端向数据库服务器端提出的连接请求。每个OracleServer必须有一个listener.ora文件。listener.ora文件由三个部分构成头部（headersection）地址列表（addresslist）实例定义列表（instancedefinitions）LISTENER.ORA文件例子请参考LISTENER.ORA文件本地Net服务名（Tnsnames）存储客户端的连接描述符每个连接描述符主要由三部分构成：通信协议（protocol）服务器主机及端口（host&port）服务名（servicename）Conn用户名/密码@本地Net服务名TNSNAMES.ORA文件例子请参考TNSNAMES.ORA文件概要文件（sqlnet.ora）服务器和客户端都要用到的网络概要文件.规定了命名方式,认证方法等等Client/Server客户端/服务器端放置的文件C/S结构和会话（Session）新连接新Session客户端（Client）User

processOracleserverServer

process有监听器的会话建立过程新会话客户端用户进程:connscott/tiger@MYDATA监听器:0:1521服务进程Oracle服务器童鞋们，下课了。。。任课：***Email:***@***.大型数据库技术（Oracle11g）欢迎全国读者加入教材/课程讨论QQ群：249449451问题探讨、授课咨询请加作者QQ：535413184Oracle数据库结构回顾逻辑结构物理结构数据库表空间Oracle数据库块段区硬盘操作系统文件操作系统块Oracle段相关对象分区表B树索引位图索引索引组织表聚簇段CREATETABLE[方案名.]表名(字段名字段类型[,字段名字段类型]…)[TABLESPACE表空间名][PCTFREE][PCTUSED][INITRANS][MAXTRANS][STORAGECLAUSE][LOGGING|NOLOGGING][CACHE|NOCACHE]表创建语句的语法分区表将表中数据存储到物理划分的多个分区中，逻辑上该表仍是一个单一的表。范围分区列表分区哈希分区组合分区SQL>createtablet_OrderInfo(

2

orderNoint,

3

oBalancefloatdefault0,--订单总价

4

ordertimedate--提交订单时间

5)

6partitionbyrange(ordertime)(

7partitionplvalueslessthan('01-APR-2011'),

8partitionp2valueslessthan('01-JUL-2011'),

9partitionp3valueslessthan('01-OCT-2011'),10partitionp4valueslessthan('01-JAN-2012')11);范围分区SQL>createtableuser_by_city(

2userIDnumber,

3userNamevarchar2(25),

4cityvarchar2(10))

5partitionbylist(city)(

6partitionplvalues('北京','上海','天津'),

7partitionp2values('深圳','广州'),

8partitionp3values('郑州','武汉','西安'),

9partitionp4values('大连','沈阳')10);列表分区SQL>createtableitemInfo(

2item_idnumber(6),

3descriptionvarchar2(30))

4partitionbyhash(item_id)

5(partitionpltablespaceuserTB01,

6partitionp2tablespaceuserTB02

7);

哈希分区无法对各分区中可能具有的记录数目进行预测时，可创建哈希分区SQL>createtablet_OrderInfo(

2

orderNOint,

3

oBalancefloatdefault0,--订单总价

4

orderTimedate--提交订单时间

5)

6partitionbyrange(ordertime)

7subpartitionbyhash(orderNO)subpartitions2

8storein(userTB01,userTB02)

9(10partitionplvalueslessthan('01-APR-2010'),11partitionp2valueslessthan('01-JUL-2010'),12partitionp3valueslessthan('01-OCT-2010'),13partitionp4valueslessthan('01-JAN-2011')14);组合分区索引定义索引是对数据表中一个或多个列的值进行排序的结构，每个索引都有一个索引键与表中的记录关联。索引使得数据库程序无须对整个表进行扫描就能找到所需数据，加快搜索速度分类B树索引Bitmap索引在empno列上无索引在empno列上存在索引select*fromempwhereempno=7788

根（Root）分支叶节点表VS索引+表头键列ROWIDB*树索引B树索引例子数据B树索引ZHANGROWIDZHOUROWID

CREATE[UNIQUE]INDEX[方案名.]索引名

ON[方案名.]表名

(字段名[ASC|DESC][,字段名[ASC|DESC]]…)[TABLESPACE表空间名]

[PCTFREE]

[INITRANS]

[MAXTRANS]

[STORAGECLAUSE]

[LOGGING|NOLOGGING]创建B树索引语法关于B树索引索引加快查询速度，但会增加DML的代价，不要在经常异动的表上创建过多的索引对大的索引为了提高效率，可以考虑nologging相对于表数据，一个databaseblock能装下更多的索引项，应设置较大的initrans值查询返回过多的数据行不适合建索引B树索引例子Select*fromEMPwhereename=‘KING’Select*fromEMPcreateindexind_enameonemp(ename)pctfree30tablespaceindex;位图索引(Bitmap)考虑到B树索引的效率，对索引列有少数不同值的大表，特别适合用位图索引。位图索引的更新代价更大，所以适合很少更新键值的表。（OLAP）位图索引<Blue,1000100100010010100><Green,0001010000100100000><Red,0100000011000001001><Yellow,0010001000001000010>key

bitmapTableIndexBlock10Block11Block12File3位图索引例子SQL>CreatetableCustomer2(Customer_idnumber,--顾客id3Marital_statusvarchar2(10),--顾客婚姻状况

4Regionvarchar2(10),--顾客所在地区

5Sexvarchar2(2),--顾客性别

6Salarynumber);--工资位图索引例子（续1）Customer_idMarital_statusRegionSexSalary1201单身华东男10001202已婚华中女12001203已婚华北女85001204离婚华北男12001205单身华中女75001206已婚华中女1050位图索引（续2）SQL>createbitmapindexMaritalBIndex2oncustomer(marital_status);Indexcreated.SQL>createbitmapindexReginBIndex2oncustomer(region);Indexcreated.位图索引（续4）Region=‘华东’Region=‘华中’Region=‘华北’100010001001010010位图索引（续5）selectsum(Salary)fromcustomerwheremarital_status=‘单身’andregionin(‘华东’,‘华中’);重建索引重建索引可以:将索引移入另外的表空间由于重建的过程中移除了逻辑删除的索引，可以提高空间利用率ALTERINDEXorders_region_id_idxREBUILD

TABLESPACEindx02;检查索引有效性ANALYZEINDEXind_enameVALIDATESTRUCTURE;INDEX_STATS删除索引在批量载入数据之前，先drop掉索引然后重建将不经常使用的索引drop掉，使用的时候再重建DROPINDEXhr.deptartments_name_idx;索引组织表非键列键列头普通表及其索引索引组织表索引表SQL>createtabledepartment(2deptnonumber(4)primarykey,3deptnamevarchar2(20),4locationvarchar2(40),5)6organizationindex7tablespaceUniformTB;索引组织表一般适应于静态表，且查询多以主键列。当表的大部分列当作主键列时，且表相对静态，比较适合创建索引组织表索引组织表（续）聚簇经常被同时访问的表在物理上可以存储在一起。为了将他们存储在一起，可以建立一个簇。簇结合各个表共同拥有的关联列成为此簇的唯一索引列，称为簇键。簇例子员工表部门代码表经蔟化后的单一表存储方式创建簇例子创建簇

Createclusterempdep(department_idvarchar2(2))Size400Tablespaceusers_Index;为簇创建索引

Createindexempdep_dep_idonclusterempdeptablespaceindex;将表创建在簇中将表创建在簇中

Createtableemployee(Emp_NOvarchare2(4),Emp_namevarchare2(20),depart_idvarchar2(2),Telvarchare2(10))CLUSTERempdep(depart_id);将表创建在簇中(续)Createtabledepartment(depart_novarchare2(4),depart_namevarchare2(20))CLUSTERempdep(depart_no);其他对象序列（Sequence）同义词（Synonym）数据库连接（Databaselink）快照（Snapshot）序列

利用序列可生成唯一的整数。一个序列的值是由特殊的Oracle程序自动生成，因此序列避免了在应用层实现序列而引起的性能瓶颈。

createsequencestu_sequenceincrementby1--序列的开始值为1startwith1--序列的增量为1nomaxvalue--序列无最大值nocycle--序列不循环cache10;--缓存的序列值insertintoSTUDENTS(s_id,s_name)values(stu_sequence.nextval,‘王舒镝');同义词（SYNONYM）是数据库对象（表、视图和序列）的别名。

提供能在查询里被引用的对象的别名。

可以为表、视图、序列、过程或任何其他数据库对象创建同义词。建立公有同义词createpublicsynonymSTUforTOM.students;建立私有同义词createsynonymSTUforTOM.STUDENTS;童鞋们，下课了。。。任课：***Email:***@***.大型数据库技术（Oracle11g）欢迎全国读者加入教材/课程讨论QQ群：249449451问题探讨、授课咨询请加作者QQ：535413184Oracle客户机与服务器结构T1T2Tn性质原子性(Atomicity):事务是一个不可分割的工作单元一致性(Consistency):即数据不会因事务的执行而遭受破坏

隔离性(Isolation):在多个事务并发执行时，系统应保证与这些事务先后单独执行时的结果一样

持久性(Durability):一个事务一旦完成全部操作后，它对数据库的所有更新应永久地反映在数据库中

事务的性质事务并发执行带来的问题会产生多个事务同时存取同一数据的情况可能会存取和存储不正确的数据，破坏事务一致性和数据库的一致性事务并发执行带来的问题（丢失修改）事务A

事务B读取X＝100

读取X＝100

写入X＝X+100

写入X＝X+200

事务结束X=200，写入并提交

事务结束X=300，写入提交事务并发执行带来的问题（不可重复读）事务A

事务B

读取X＝100

读取X＝100，Y=200，求和为300

X＝X+100，写入并提交

读取X＝200，Y=200，求和为400

事务并发执行带来的问题（脏读）事务A

事务B

读取X＝100

写入X＝X+100

读取X＝200

事务回退X=100Oracle的封锁机制数据不一致性：由于并发操作破坏了事务的隔离性并发控制就是要用正确的方式调度并发操作，使一个用户事务的执行不受其他事务的干扰，从而避免造成数据的不一致性。Oracle采用封锁机制对事务进行调度Oracle的封锁机制（续1）排它锁（X锁）共享锁（S锁）意向锁意向共享锁（RS锁）意向排它锁（RX锁）共享意向排他锁（SRX锁）Oracle封锁粒度TM锁的相容矩阵事务A事务BSXRSRXSRX—SYESNOYESNONOYESXNONONONONOYESRSYESNOYESYESYESYESRXNONOYESYESYESYESSRXNONOYESNONOYES—YESYESYESYESYESYES回退段与undo表空间通过保存‘先前映像’的信息，提供回退事务的方法。为事务提供读一致性。Undo表空间或回退段Transactionrollback（事务回退）事务恢复Undo表空间或回退段Readconsistency（读一致性）读一致性语句开始或者事务开始前的数据映像新数据表SELECT*

FROMtable查询：找出每个学生超过他选修课程平均成绩的课程号。

SELECTSno，CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade) FROMSCyWHEREy.Sno=x.Sno);135145135语句级读一致性隔离级别语句级的读一致性语句开始时拍一个快照，该语句认为所有的数据以快照为准事务处理过程中，一个事务不能看到另外一个未提交的事务的修改。事务级的读一致性事务开始时拍一个快照，该事务认为所有的数据以快照为准SETTRANSACTIONREADONLY序列化SETTRANSACTIONISOLATIONLEVELSERIALIZABLE两段锁协议封锁协议运用封锁方法时，对数据对象加锁时需要约定一些规则何时申请封锁持锁时间何时释放封锁等两段封锁协议(Two-PhaseLocking，简称2PL)是最常用的一种封锁协议，理论上证明使用两段封锁协议产生的是可串行化调度两段锁协议（续）“两段”锁的含义事务分为两个阶段第一阶段是获得封锁，也称为扩展阶段事务可以申请获得任何数据项上的任何类型的锁，但是不能释放任何锁第二阶段是释放封锁，也称为收缩阶段事务可以释放任何数据项上的任何类型的锁，但是不能再申请任何锁两段锁协议例子假设有两个事务A和B，分别包含如下操作：事务A：读数据n；m=n+1；写回m。事务B：读数据m；n=m+1；写回n。两段锁协议例子（错误的调度）事务A

事务B

对n加读锁tmp=R(n)=200

对m加读锁

tmp=R(m)=200

释放n上的读锁

释放m上的读锁对m加写锁m=tmp+100=300写m

对n加写锁

n=tmp+100=300

写n释放m上的写锁

释放n上的写锁两段锁协议例子（串行调度1）

事务A

事务B对n加读锁tmp=R(n)=200释放n上的读锁

对m加写锁

m=tmp+100=300写m

对m加读锁

tmp=R(m)=300

对n加写锁

n=tmp+100=400

释放m上的读锁

对n加写锁

写n

释放n上的写锁两段锁协议例子（串行调度2）事务A

事务B

对m加读锁

tmp=R(m)=200

对n加写锁

n=tmp+100=300

释放m上的读锁

对n加写锁

写n

释放n上的写锁对n加读锁tmp=R(n)=300释放n上的读锁

对m加写锁

m=tmp+100=400写m两段锁协议例子（正确的调度）事务A

事务B对n加读锁tmp=R(n)=200对m加写锁m=tmp+100=300

对m加读锁

等待

等待写m

等待

等待释放m上的写锁

tmp=R(m)=300释放n上的读锁

对n加写锁

n=tmp+100=400

写n

释放m上的读锁

释放n上的写锁童鞋们，下课了。。。任课：***Email:***@***.大型数据库技术（Oracle11g）欢迎全国读者加入教材/课程讨论QQ群：249449451问题探讨、授课咨询请加作者QQ：535413184Oracle客户机与服务器结构T1T2TnOracle数据库的安全性方案(Schema)的概念用户管理权限管理角色管理数据库审计方案的概念SchemaObjectsTablesTriggersConstraintsIndexesViewsSequencesStoredprogramunitsSynonymsUser-defineddatatypesDatabaselinks方案是一些数据库对象的集合方案与用户是一一对应的。181创建用户时要指定的参数用户名、口令用户默认表空间用户临时表空间用户存储空间限额CREATEUSER用户名INDENTIFIEDBY口令

EXTERNALLYDEFAULTTABLESPACE表空间名

TEMPORARYTABLESPACE表空间名

QUOTA整数K/MON表空间名

UNLIMITED创建用户语法例：建立用户CREATEUSERoperatorIDENTIFIEDBYoralDEFAULTTABLESPACEuserDataTEMPORARYTABLESPACEtempUserQUOTA5MONuserDataQUOTA5MONtempUser;CREATEUSERdb_ownerIDENTIFIEDBYoralDEFAULTTABLESPACEuserDataTEMPORARYTABLESPACEtempUser;创建用户例子ALTERUSER用户名INDENTIFIEDBY口令

EXTERNALLYDEFAULTTABLESPACE表空间名

TEMPORARYTABLESPACE表空间名

QUOTA整数K/MON表空间名

UNLIMITED

DEFAULTROLE角色名

ALLEXCEPT角色名

NONE，，修改用户语法ALTERUSERdb_ownerINDENTIFIEDBYoracleDEFAULTTABLESPACEuserdata02；ALTERUSERdb_ownerquota500monuserData;修改用户例子删除用户：DROPUSER用户名CASCADE例如：DROPUSERbradleyCASCADE；删除用户权限分类对象权限：在某个特定的对象（表、视图、序列、过程、函数或包）上执行特定操作的权限。系统权限：与特定对象无关或作用于全体对象上的权限。系统权限分为三类授予用户建立自己的对象Createtable,createcluster,createsequence等授予用户可以对某类型的所有对象进行操作：Createanyindex,executeanyprocedure,grantanyrole授予用户对数据库系统进行操作：Createsession,createtablespace,altersystem对象权限授予系统权限或角色：GRANT系统权限名TO用户名角色名角色名

PUBLICWITHADMINOPTION，，授权语句语法（系统权限）授予对象权限或角色：GRANT对象权限名TO用户名角色名角色名

PUBLICWITHGRANTOPTION，，授权语句语法（对象权限）例1：将CREATESESSION系统权限授予operator。

GRANTcreatesessionTOoperator;例2：将CREATETABLE系统权限授予operator并允许其再传递。

GRANT

create

table

TOrichardWITHADMINOPTION;授权语句例子REVOKE系统权限名FROM用户名角色名角色名

PUBLIC，，回收权限语法例：从用户Bill和Mary回收DROPANYTABLE系统权限。

REVOKEdropanytableFROMbill,mary;回收权限例子GRANTABCREV