Oracle 11G课程学习总结文档

Oracle11G课程总结Oracle11G总结HYPERLINK数据库概念HYPERLINKOracle体系结构HYPERLINKOracle安装（高级安装）HYPERLINK启动OracleHYPERLINKSQL语句HYPERLINKOracle数据类型HYPERLINKSQL语句使用HYPERLINKSQL函数HYPERLINKOracle数据库对象HYPERLINKdirectory目录HYPERLINK外部表HYPERLINK临时表HYPERLINK序列HYPERLINK视图HYPERLINK同义词HYPERLINK索引HYPERLINK表分区HYPERLINK并发与锁HYPERLINK行级锁HYPERLINK表级锁HYPERLINKPL/SQL基础HYPERLINKPL/SQL简介HYPERLINKPL/SQL块基本结构HYPERLINKPL/SQL变量与常量HYPERLINKPL/SQL控制结构HYPERLINKPL/SQL集合HYPERLINK游标管理HYPERLINK异常处理HYPERLINKOracle数据字典HYPERLINK子程序和程序包HYPERLINK过程HYPERLINK函数HYPERLINK自主事务处理HYPERLINK程序包HYPERLINK触发器和内置程序包HYPERLINK触发器HYPERLINK内置程序包HYPERLINK备份与恢复HYPERLINK使用PowerDesignser设计数据库HYPERLINK创建CDMHYPERLINK创建PDMHYPERLINKPD连接Oracle数据HYPERLINK去除生成Oracle脚本中双引号HYPERLINK逆向工程HYPERLINK疑难HYPERLINK无法启动OracleTNSListener监听服务HYPERLINK删除Oracle中奇怪的表名称BIN$…的方法周三扩展课HYPERLINK2011年2月16日SQL语句处理过程HYPERLINK2011年2月23日Oracle优化器及执行计划HYPERLINK2011年3月2日动态SQL语句、Java创建及调用存储过程（重点）

数据库概念Oracle、DB2、SQLServer属于关系型数据库关系型数据库采用结构化查询语言（SQL）作为客户端程序与数据库服务器间沟通的桥梁——客户端发送SQL指令到服务器端，服务器端执行相关的指令并返回其查询的结果。数据建模要将显示世界中客观存在的事物以数据的形式存储到计算机中并进行处理，就需要对其进行分析、抽象，进而确定数据的结构以及数据间的内在联系，这一过程成为数据建模。数据模型应满足三个方面要求：能够比较真实地模拟现实世界容易为人所理解便于计算机实现数据模型三要素：数据结构-描述事物的静态特性数据操作-描述事物的动态特性完整性约束-描述事物内部和事物间的约束性关系E-R模型在E-R模型中，现实世界时由一且成为实体的对象和这些对象之间的关系组成的。在E-R模型三要素：实体、关系、属性。E-R图是描述概念事物，建立概念模型的实用工具。E-R三要素：实体-用矩形表示属性-用椭圆形表示，并用连线与实体连在一起。实体间联系-用菱形框表示，并用连线分别与相关实体相连，且需在连线上注明联系类型。实体间联系类型：一对一关系（1：1）：两个实体集A和B，若A中的每个实体至多和B中一个实体有联系，反之，B中的每个实体至多和A中的一个实体有联系，称A对B或B对A是一对一关系。一对多关系（1：N）：两个实体集A和B，如果A中每个实体可以和B中的0~多个实体有联系，而B中的每个实体至多和A中的一个实体有联系，则称A对B为一对多关系。多对多关系（M：N）：两个实体集A和B，若A中的每个实体可与B中的多个实体有联系，反之亦然，称A对B或B对A是多对多关系。

Oracle体系结构Oracle数据库特点：全球化、跨平台的数据库Oracle遵守数据存取语言、操作系统、用户接口和网络通信协议的工业标准支持多用户、高性能的事务处理强大的安全性控制和完整性控制支持分布式数据库和分布处理OracleDBMS（Oracle数据库管理系统）由Oracle数据库和Oracle实例组成。Oracle数据库：Oracle数据库包含操作系统文件（也称为数据库文件），这些文件为数据库信息提供了实际的物理存储。Oracle数据库：物理组件数据文件（.DBF）日志文件（.LOG）控制文件（.CTL）逻辑组件（由小到大）数据块：最小存储单元（默认8K）区：最小分配单元（8/16个块）段：数据段、回退段、临时段、索引段，由区组成，分类型存储物理组件的之间交互顺序：Oracle启动→init.ora→控制文件→数据文件、日志文件。Oracle实例（例程）：Oracle实例是由内存结构和后台进程组成。只有启动实例后，才能访问数据库中的数据。Oracle实例内存结构：SGA：Oracle核心引擎所需内存。（见下图）PGA：服务器进程所需的内存。UGA：用户进程所需的内存SGA包括：数据缓冲区：用于存储从磁盘数据文件中读入的数据，所有用户共享。服务器进程将读入的数据保存在数据缓冲区中，当后续的请求需要这些数据时可以在内存中找到，不需要再从磁盘读取，提高了读取速度。日志缓冲区：日志记录数据库的所有修改信息，日志信息首先产生于日志缓冲区。当日志缓冲区的日志数据达到一定数量时，由后台进程将日志数据写入日志文件中。相对来说，日志缓冲区对数据库的性能影响较小。共享池：共享池是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域共享池由库高速缓存和数据字典缓存组成。库高速缓存：主要负责执行过的SQL语句。数据字典高速缓冲：数据字典的信息。共享池的大小直接影响数据库的性能。Java池：使用Java程序时需要的内存。大型共享池（大池）：写入或读取大型文件、多媒体所需的内存。Oracle实例后台进程：SMON：系统监控进程在实例失败之后，重新打开数据库时自动恢复实例。整理数据文件的自由空间，将相邻区域结合起来。释放不再使用的临时段。PMON：进程监控进程清理出现故障的进程。释放所有当前挂起的锁定。释放故障进程使用的资源。DBWR：数据写入器进程管理数据缓冲区，将最近使用过的块保留在内存中。将修改后的缓冲区数据写入数据文件中。LGWR：日志写入器进程负责将日志缓冲区中的日志数据写入日志文件。系统有多个日志文件，该进程以循环的方式将数据写入文件。CKPT：检查点进程（校验点，同步点）在这个点，数据文件与日志文件是完全同步的。ARCH：归档进程查看Oracle体系结构信息1,查看块信息sql>Selectname,valuefromv$parameterwherename=‘db_block_size’2,查看盘区信息sql>select*fromuser_extents3,查看段信息sql>select*fromuser_segments4,查看段信息sql>select*fromuser_tablespaces;5,查看数据文件sql>select*fromv$datafile;5,查看控制文件sql>Select*fromv$controlfile;6,查看系统全局区sql>select*fromv$sga;7,查看数据缓冲区sql>selectname,value,DESCRIPTIONfromv$parameterwherenamelike'db_cache_size';8,查看共享池sql>selectname,value,DESCRIPTIONfromv$parameterwherenamelike'shared_pool_size';9,查看日志缓冲区sql>selectname,value,DESCRIPTIONfromv$parameterwherenamelike'log_buffer';10,程序全局区sql>selectname,value,DESCRIPTIONfromv$parameterwherename='pga_aggregate_target';

安装Oracle11GORACLE提供ORACLEUniversalInstaller(ORACLE通用安装工具，简称为OUI)是一个基于java技术的图形界面安装工具，利用它可以完成在不同操作系统平台上实现ORACLE的安装。无论是什么操作系统平台都有OUI工具。11G可以进行基本安装和高级安装。基本安装较为简单，高级安装较为复杂，但可以较对安装有一个全面的了解。我们在此就进行高级安装。步骤1：我们以administrator登录后，双击databases文件夹中的setup.exe文件步骤2：启动OUI后出现“选择安装方式”窗口，我们选择：高级安装步骤3：出现“选择安装类型”窗口，选择我们需要安装的版本。我们在此肯定是选择企业版。至于产品语言不用选择，它会根据当前系统的语言自动调整！步骤4：出现“安装位置”窗口Oracle基目录：用于安装各种与ORACLE软件和配置有关的文件的顶级目录。软件位置：用于存放安装具体ORACLE产品的主目录和路径。在此我们使用默认的配置。在此ORACLE会选择剩余空间最多的盘作为基目录和安装目录的所在盘。可以输入一个电子邮件或是metalink接收安全问题通知，在此我直接输入一个电子邮件，当然也可以是metalink用户信息！步骤5：再向下就是对安装环境进行检测，如果不满足条件则会给出相应的提示，如图所示：在此是因为我的内存不足，所以会报此提示，那么我们在此调整内存，以满足条件后再继续安装。步骤6：出现“选择配置选项”如图：在此选择安装数据库。如果是“配置自动存储管理”，则自动存储管理（ASM）可用来自动化和简化对数据文件、控制文件和日志文件的优化布局。自动存储管理（ASM）将每个文件切割成许多小扩展文件，并将它们平均分散在一个磁盘组的所有磁盘上。一旦自动存储管理（ASM）磁盘组建立，创建和删除文件的时候，Oracle数据库都会从磁盘组自动分配存储空间。如果是“仅安装软件”，则只安装ORACLE软件，在安装之后还需要再运行数据库配置助手创建数据库。步骤7：出现“选择数据库配置”窗口，在此需要选择在安装过程中创建的数据库类型。一般用途/事务处理：适合各种用途的预配置数据库。数据仓库：创建适用于特定需求并运行复杂查询环境。常用于存储并快速访问大量记录数据。高级：安装结束后运行ORACLEDBCA后，用户才可以配置数据库。在此我们选择“一般用途/事务处理”步骤8：单独下一步后，出现“指定数据库配置选项”全局数据库名：主要用于在分布式数据库系统中，区分不同的数据库使用，如北京有一个数据库db1，上海也有一个同名数据库，怎么区分它们呢，在此可以使用数据库名.域名的形式，这样即使数据库名相同，也可以区分开。SID(系统ID)：主要是区分同一台计算机上的不同数据库。应用在同一个计算机上的不高数据库的不同实例。步骤9：再向下就是“指定数据库的详细资料”：在此一般都保持默认即可，但是可以选择“创建带样本方案的数据库”也就是一个可供大家练习的一个样本数据库。步骤10：下一步后，出现下图使用默认配置即可，可以使用ORACLEEnterpriseManager(OEM)在本地管理每个ORACLE数据库。步骤11：下一步后，出现“数据库存储选项”，选择存储数据库文件的方法和存储位置在此选择使用文件系统，默认配置即可，但一般推荐此位置最好不是系统分区步骤12：指定备份和恢复选项：我们在此使用默认配置：不启用自动备份步骤13：指定数据库口令：如图所示我们在此为了方便记忆使用所有帐户都使用同一个口令。步骤14：出现“ORACLEConfigurationManage注册”界面，如图所示：但需要在此输入客户帐号及用户名,但如果没有CSI及metalink帐户，则只能是在此不安装此组件。步骤15：出现摘要后,点击下一步,开始安装：最后需要注意的是,安装成功能后会有一个"口令管理",在此需要进行相应设置,如图所示:在此可以对相应的用户进行解锁或是锁定操作,但scott一定要解锁,并设置口令,一般口令习惯设置为:tiger,因为一些工具软件如ONCA或ONM会默认使用此口令进行连接.然后根据提示即可完成ORACLE11G的安装!五、

安装后的测试：一般来说，只要是安装的过程中不出现任何问题，则说明安装是正常的，但我们还是需要一个可行的方法来验证安装是否正常。首先：我们可以查看开始菜单，会多出与ORACLE相关的程序组：再次，可以查看ORACLE相关的服务：ORACLE安装成功后，也是以服务的形式存在于系统中，所以我们会看到在系统中存在以下的服务：OracleOrclVSSWriterService:Oracle对VSS(卷影)的支持服务OracleDBConsoleorcl:控制台服务，也就是ORACLE的企业管理器OracleJobSchedulerORCL:定时器服务,用于数据库工作日程调度。OracleOraDb11g_home1TNSListener:监听器服务,此服务只有在数据库需要远程访问时才需要。OracleServiceORCL:数据库服务,这算是ORACLE的核心服务,用于启动和停止ORACLE服务.再次，当ORACLE安装完成后会修改环境变量：如图所示再有，就是ORACLE也和一般的软件一样，安装的过程也是向硬盘上复制文件和修改注册表，在这里不再详细描述。三、启动Oracle首先启动Oracle核心服务OracleServiceXXX核心服务。OracleTNSListener监听服务（默认1521端口），如刷新多次后，该服务仍未启动，即重新配置监听程序。（见下文※重新配置监听程序）3．OracleDBConsoleXXX控制台工具（基于Web客户端），如无法启动，有可能是主机名更改过，可到（上文oracle基本概念第5部分网络配置修改tnsnames.ora文件）。启动以上3个服务后，可通过以下方式启动Oracle11GPL/SQLDeveloperSQLPlusSQLDeveloperPL/SQLDeveloper启动：1．双击PL/SQLDeveloper图标后，出现以下登录界面。2.登录后界面，标题栏将会显示登录用户名和身份。3．选中菜单栏中“File”—“New”—“CommandFile”打开命令窗口，执行相关SQL语句操作。SQLPlus启动：打开SQLPlus。必需输入用户名和密码才能登录，格式：用户名/密码，如果是sys账号登录必需加sysdba，格式：用户名/密码assysdba，成功登录后，方可进行相关操作。SQLDeveloper启动（后添加）

SQL语句SQL语句类别：DDL，数据定义语言（create、alter、drop、truncate）DML，数据操纵语言（insert、update、delete、select）DCL，数据控制语言（grant、revoke）TCL，事务性控制语言（commit、rollback、savepoint）数据定义语言（DDL）：用于改变库结构，创建，更改，删除操作表结构的数据定义语言有：createtable 新建表alerttable 修改表droptable 删除表结构truncatetable 删除表内容，不可回滚deletefrom，truncatetable，droptable三者的区别：deletefrom删除表内容，可以回滚truncatetable快速删除，删除表全部内容，并且不可回滚droptable删除表结构，不可回滚查看表结构：descdescemp;利用现有表创建新表：createtablenew2_empasselect*fromempwhererownum<11 将查询结果插入另一张表：insertintonew2_empselect*fromempwhererownum<6;Oracle提供一张虚拟表：dual，可用于函数计算结果的显示，如：selectto_date('2008-10-01','yyyy-MM-dd')astodayfromdual;事务控制语言(TCL)：事务是最小的工作单元，作为整体进行工作保证事务的整体成功或失败，称事务控制。事务控制语言有：commit：提交并结束事务处理rollback：撤销事务已完成的全部工作savepoint：标记事务中可以回滚的点，用于回滚，但这破坏了事件的原子性。注意：回滚只能对DML语句的执行进行回滚，如：插入，更新，删除 不能对DDL等语句的执行结果回滚，如：建表，修改表结构，删除表。 数据控制语言（DCL）：grant，revoke分配权限：grantdbatowyqwithadminoption;withadminoption;表示wyq拥有权限可以再受权，即再次次此权限受给其它用户grantselect,update(job,sal)onemptowyq;可以对表的权限进行细粒度分配，如：update(job,sal)Oracle数据类型SQL数据类型分两大类：数据库类型，用在列上PL/SQL类型，用在变量上以下详解数据库类型：字符数据类型：char，varchar2，long当需要固定长度的字符串时，使用char，长度为1-2000个字节 varchar2存储可变大小的字符串，大小为1-4000个字节 long存储可变长度数据，长度2GBRAW：存储二进制数据，最多存储2000字节LONGRAW：可变长度的二进制数据类型，最长存储2GB数值数据类型：number 可存储整数，浮点数，实数，最高精度38位 声明：number[p[,s]]，P为精度，为小数点前后位数相加，S为小数精度，日期时间数据类型：Date：存储日期和时间，精确到秒Timestamp：时间戳，存储日期，时间，时区信息，秒值精确到小数点后6位（微秒）LOB（largeobjectB）：大对象类型，存储最大4GB的非结构信息，如音频，视频等。 CLOB：characterLOB，字符LOB，用于存储大量字符。 BLOB：BinaryLOB，二进制LOB，存储较大二进制对象，如：图像，声音，视频。 BFILE：BinaryFile，二进制文件，用于将二进制文件存储在数据库外部的操作系统文件中。存储一个指针，指示到实际存储的位置。伪列：Oracle中的伪列就像一个表的列，但它并没有存储在表中，伪列可以从表中查询，但不能插入，更新，删除它们的值，Oracle自动维护其值。常用伪列有：rowid，rownumrowid：表示行在表中的唯一标识。rownum：是查询返回的结果集中的行的序号，可以使用它来限制查询返回的行数，即结果集中的行的序号。只能用“<”，“<=”不能用“>”，“=”。如：select*fromempwhererownum<6;只返回结果集中的前5条记录SQL语句使用用户&权限类创建用户：SQL>createuserchopidentifiedbychop;分配角色：SQL>grantdbatochop;将某表权限赋给某用户：SQL>grantall[select|update|delete|insert]ontoytochop02;从某用户收回某表权限：SQL>revokeallontoyfromchop02;解锁用户：SQL>alteruserscottaccountunlock;表操作类：创建表：SQL>createtabletoy(toy_idintprimarykey,toy_namevarchar2(20));修改表（增加列）：SQL>altertabletoyaddtoy_pricenumber;删除表：SQL>droptablenew_toy;查看当前用户拥有的表：SQL>select*fromtab;查看表结构：SQL>desctoy;查看表：SQL>select*fromtoy;复制表（包括表内容和表结构）：SQL>createtablenew_toyasselect*fromtoy;复制表（复制其他用户的表）：SQL>createtableempasselect*fromscott.emp;增删改查类：插入数据：SQL>insertintotoy(toy_id,toy_name)values(001,'toy001');修改数据：SQL>updatetoysettoy_price=100wheretoy_id=001;删除数据：SQL>deletetoywheretoy_id=3;单表查询：SQL>select*fromtoy;多表查询：SQL>SELECTe.*,d.* FROMempe,deptdWHEREe.deptno=d.deptno表达式:

SQL函数SQL函数带有一个或多个参数并返回一个值SQL函数分类：单行函数分组函数分析函数Oracle中函数较多，不可能全部讲解和记牢，在须要使用时，可以查看文档，如：Ora9iSQLRef.chm单行函数：单行函数对于从表中查询的每一行只返回一个值，可以出现在SELECT子句中和WHERE子句中单行函数可以大致划分为：日期函数，数字函数，字符函数，转换函数，其他函数日期函数add_months(sysdate,1)：在当前月份上加1sysdate获取系统时间months_between：返回两个时间相差月份，前边的减去后边的months_between(add_months(sysdate,1),sysdate)，结果为1last_day(sysdate)：返回日期所在月份的最后一天。round(sysdate)：将当前日期四舍五入next_day(sysdate,'星期一')：返回距第一个参数的下一个星期一。注意：在中文系统中，第二个参数为中文，英文系统中使用英语（Monday）trunc(sysdate,'yy')：截取日期中指定部分的第一天，如：2009-1-1如果取的是“day”，刚返回的是所在星期的第一天（星期日）sysdate=2009-5-7，trunc(sysdate,'year')：2009-1-1trunc(sysdate,'mm')：2009-5-1trunc(sysdate,'dd')：2009-5-7trunc(sysdate,'day')：2009-5-3trunc(sysdate,'hh')：2009-5-717:00:00trunc(sysdate,'mi')：2009-5-717:43:00extract：取出日期或时间戳中的指定部分。sysdate=2009-5-7，extract(yearfromsysdate)：2009extract(monthfromsysdate)：5extract(dayfromsysdate)：7extract(yearfromdate'2009-10-15')：2009extract(hourfromtimestamp'2009-5-719:02:34')：19extract(minutefromtimestamp'2009-5-719:02:34')：2extract(secondfromtimestamp'2009-5-719:02:34')：34注意：取日期使用date类型，timestamp可以取日期和时间。字符函数：Initcap(char)：将一个或一系列单词的首字母转换为大写initcap(‘hello’)：HelloLower(char)：将字符串全部字母转换成小写lower(‘FUN’)：funUpper(char)：将字符串全部字母转换成大写upper(‘sun’)：SUNLtrim(char,set)：将字符串char左侧中set指定的字符集全部字符删除。如果无set，默认空格。ltrim(‘xyzadams’,’xyz’)：adamsRtrim(char,set)：将字符串char中右侧set指定的字符集全部字符删除。如果无set，默认空格。rtrim(‘xyzadams’,’ams’)：xyzadtrim(setfromchar)：将字符串char两侧set指定的字符全部字符删除。如果无set，默认空格。注意，set只能包含一个字符。trim(0from0009872348900)：98723489Translate(char,from,to)：将char中的from用to代替。translate(‘jack’,’j’,’b’)：backReplace(char,searchstring,[repstring])：将字符串char中的searchstring用repstring替换，如果未指定repstring，则删除searchshtringreplace(‘jackandjue’,’j’,’bl’)：blackandblueInstr(char,m,n)：查找一个字符在某字符串中的位置，没有则返回0。n表示开始查找的位置，没有则从第一个开始查找。instr(‘worldwide’,’d’)：5Substr(char,m,n) ：从字符串中截取一个子串substr(‘abcdefg’,3,2)：cdConcat(expr1,expr2)：连接两个字符串。相当于“||”concat(‘Hello’,’world’)：HelloworldCHR(number)：返回number的ASCII对应的字符。ASCII(chat)：返回char的ASCII值。LPAD(str1,length,str2)：用str1的左侧，用str2填充为指定长度。lpad('smith',10,'x')：xxxxxsmithRPAD(str1,length,str2)：用str1的右侧，用str2填充为指定长度。rpad('smith',10,'x')：smithxxxxxLENGTH：返回字符串长度。转换函数：TO_CHAR：将日期转换为指定的字符格式。TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日"HH24:MI:SS')TO_DATE：将字符格式转换为日期格式。TO_DATE(‘2005-12-06’,‘yyyy-mm-dd’)TO_NUMBER：将数字字符转换成数值。TO_NUMBER('100')其他函数：NVL(列名,代替值)：将空值用指定的值代替。 selectename,nvl(comm,0)fromemp;NVL2(列名,代替值1,代替值2)：当列不为空是，用第二个参数代替，当列为空是，用第三个参数代替。 selectename,comm,nvl2(comm,comm,'0')fromemp;NULLIF(exp1,exp2)：当exp1与exp2不相等时，使用exp2代替。DECODE()将特定的数据转换为另一种表示。如：员工表中，将部门号显示出部门名称，不需要联表查询，性能提高很多。

Oracle数据库对象directory目录：directory让我们可以在Oracle数据库中灵活的对文件进行读写操作，极大的提高了Oracle的易用性和可扩展性创建目录：CREATE[ORREPLACE]DIRECTORYdirectoryAS'pathname';SQL>createorreplacedirectorytest_diras'd:/test';目录授权GRANTREAD[,WRITE]ONDIRECTORYdirectoryTOusername;SQL>grantread,writeondirectorytest_dirtochop;外部表：把外部文件的数据导入数据库内部，一般外部文件都是txt文件。外部表是表结构被存储在数据字典中，而表数据被存放在OS文件中的表。创建外部表：test目录下有个a.txt文件:1|1|2|1createtableusers(idnumber(10),usernamevarchar2(20),passwordvarchar2(20),agenumber(10))organizationexternal(typeoracle_loader–固定语句defaultdirectorytest_dir–-目录（上面创建时，创建的目录）accessparameters(recordsdelimitedbynewlinebadfiletest_dir:'a.bad'logfiletest_dir:'a.log'fieldsterminatedby'|'--字段按照”|”符号分隔)location(test_dir:'a.txt')–-txt文件位置)rejectlimitunlimited--指定对在查询外部数据时可能产生的错误的数量没有限制的创建后：Select*fromusers;修改外部表：下面的语句修改外部表EXT_EMP的默认DIRECTORY对象为EXT_NEW：SQL>altertableusersdefaultdirectoryext_new;临时表：Oracle创建临时表来保存会话私有数据，这些数据只在事务或会话期间保存。临时表分类:事务型临时表:

数据在事务持续期内数据存在会话型临时表

会话持续期间数据存在创建临时表：CREATEGLOBALTEMPORARYTABLETABLE_NAME

(COUMNS…)

ASSELECT…FROMTABLE…

ONCOMMITDELETEROWS｜ONCOMMITPRESERVEROWS;

ONCOMMITDELETEROWS定义了建立事务级临时表的方法

ONCOMMITPRESERVEROWS定义了创建会话级临时表的方法创建事务型临时表：SQL>createglobaltemporarytabletable_tmp(idnumber)oncommitdeleterows;插入数据测试：（commit后数据将会被删除）创建会话型临时表：（当注销用户或者退出用户后，数据会被删除）SQL>createglobaltemporarytabletable2_tmp(idnumber)oncommitpreserverows;序列:序列是用于生成唯一、连续序号的对象，即实现列id的自增长。序列可以是升序的，也可以是降序的。创建序列CREATESEQUENCEemp_seq [STARTWITH10] 起始值，默认1 [INCREMENTBY10] 增长间隔，默认1 [MAXVALUE2000] 最大值，默认无限 [MINVALUE10] 最小值，默认1 [NOCYCLE] 是否循环，默认不循环 [CACHE10]; 在缓存中预生成的个数，默认20创建成功后，可在数据字典（user_sequences）中查看： select*fromuser_sequences; STARTWITH和MINVALUE最好相同MAXVALUE和STARTWITH之间的差，最好大于CACHE，因为CACHE是预先生成在内存的，如果生成的个数多于最多的个数，则可能会出错，这里试验的环境不会出错。 序列创建后，通过伪列来访问： NEXTVAL返回序列的下一个值CURRVAL返回序列的当前值 创建完成后，currval是没有值的，访问出错，只有执行nextval后，currval才有值。 SQL>selecttoys_seq.nextvalfromdual;SQL>selecttoys_seq.currvalfromdual;SQL>insertintodeptvalues(seq.nextval,1,1);--插入主键修改序列： ALTERSEQUENCEemp_seqMAXVALUE5000CYCLE; 不能更改序列的STARTWITH参数 删除序列： DROPSEQUENCEemp_seq; 在insert语句中使用序列： insertintoemp(empno,ename)values(emp_seq.nextval,'wyq'); 序列生成的数值，不能和表中已有数据冲突，否则可能会违反约束。 建议如果使用序列，则整个表都使用序列生成id，而不使用应编码。 例子： 创建一个序列emp_seq，只指定名字，其余使用默认值： createsequenceemp_seq; 创建成功提示：Sequencecreated 使用数据字典（user_sequences）查看序列 select*fromuser_sequences; 可以看到一条记录，sequence_name为emp_seq 使用伪列currval查看序列中当前值： selectemp_seq.currvalfromdual; 错误提示：ORA-08002:序列EMP_SEQ.CURRVAL尚未在此进程中定义 因为序列还没有使用，所以当前值未定义。 使用伪列nextval查看序列中下一个值： selectemp_seq.nextvalfromdual; 得到下一个值是1： 再次查看当前值： selectemp_seq.currvalfromdual; 得到当前值为1： 在insert中使用序列： insertintoemp(empno,ename)values(emp_seq.nextval,'wyq'); 使用emp_seq.nextval生成下empno 查询emp表： select*fromemp; 可以看到刚插入的数据： 在emp表中插入一条数据：insertintoemp(empno,ename)values(3,'3333'); 再次使用emp_seq自动生成empno：insertintoemp(empno,ename)values(emp_seq.nextval,'wyq'); 错误提示：ORA-00001:违反唯一约束条件(SCOTT.PK_EMP) 因为emp_seq.nextval的下个id号是3，和刚插入的数据冲突重复 删除序列： dropsequenceemp_seq; 删除成功提示：Sequencedropped 重新创建序列emp_seq： createsequenceemp_seqstartwith1000INCREMENTBY10MAXVALUE1200MINVALUE10NOCYCLECACHE10 查看下一个值： selectemp_seq.nextvalfromdual; 得到startwith指定的值1000： 多运行几次上一条语句，当值超过1200后，提示错误： ORA-08004:序列EMP_SEQ.NEXTVALexceedsMAXVALUE无法例程化 删除序列，重新创建，将NOCYCLE改为CYCLE，使用循环，再重复上述操作：可以看到，当得到的值超过1200后，重新开始循环，得到MINVALUE指定的值10，所以startwith和MINVALUE最好相同。视图：视图以经过定制的方式显示来自一个或多个表的数据视图可以视为“虚拟表”或“存储的查询”创建视图所依据的表称为“基表”视图的优点有：提供了另外一种级别的表安全性隐藏的数据的复杂性简化的用户的SQL命令隔离基表结构的改变通过重命名列，从另一个角度提供数据创建视图： CREATE[ORREPLACE][FORCE]VIEWview_nameASselect_statement[WITHCHECKOPTION][WITHREADONLY]; ORREPLACE：如果存在刚替换。 FORCE：强制创建，通常在还没有基本先创建视图是使用。WITHCHECKOPTION：检查约束，限制update，要求update后的数据必须符合创建视图时查询语句中的where条件，即修改后的数据，必须还能显示在视图。 WITHREADONLY：设定视图是只读的。删除视图： DROPVIEWtoys_view;在视图中的DML语句： 在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE视图上的DML语句有如下限制：只能修改一个底层的基表如果修改违反了基表的约束条件，则无法更新视图如果视图包含连接操作符、DISTINCT关键字、集合操作符、聚合函数或GROUPBY子句，则将无法更新视图如果视图包含伪列或表达式，则将无法更新视图 键保留表 在基表中的主键同样用于标识视图中记录的列，所在的表，称键保留表，即联接结果中的主键。 视图中可以使用单行函数、分组函数和表达式试验：为emp表创建视：createviewemp_viewasselectempno,ename,salfromemp;创建成功提示：Viewcreated在数据字典（user_views）中查看： select*fromuser_views; 结果可以看到有一条刚创建的视图的记录查询视图：select*fromemp_view;为一个不存在的基本xxx创建视图：createviewxxx_viewasselect*fromxxx;错误提示：ORA-00942:表或视图不存在指定force关键字强制创建：createforceviewxxx_viewasselect*fromxxx; 警告提示：Warning:Viewcreatedwithcompilationerrors查询视图： select*fromemp_view; 错误提示：view"SCOTT.XXX_VIEW"有错误 因为基本不存在，在创建时提示的警告 创建表xxx，并插入三条数据： createtablexxx(idnumber);insertintoxxxvalues(1);insertintoxxxvalues(2);insertintoxxxvalues(3); 查询视图： select*fromemp_view; 可以正常得到结果 使用WITHCHECKOPTION创建视图xxx_view2： createviewxxx_view2asselect*fromxxxwhereid>2WITHCHECKOPTION 查询视图： select*fromxxx_view2; 可以看到只有一条记录：3 修改记录3到1，使他不符合whereid>2： updatexxx_view2setid=1whereid=3 错误提示：ORA-01402:视图WITHCHECKOPTIDN违反where子句因为使用了WITHCHECKOPTION 使用WITHCHECKOPTION创建视图xxx_view3： createviewxxx_view3asselect*fromxxxWITHreadonly 修改视图中的记录： updatexxx_view3setid=1whereid=3; 错误提示：ORA-01733:此处不允许虚拟列 删除视图xxx_view3: dropviewxxx_view3; 删除成功：Viewdropped同义词:同义词是现有对象的一个别名。使用同义词有如下好处：简化SQL语句隐藏对象的名称和所有者提供对对象的公共访问同义词共有两种类型：私有同义词：只能在其模式内访问，且不能与当前模式的对象同名。公有同义词：可被所有的数据库用户访问同义词的创建：create[orreplace][public]synonymwyqforscott.emp;创建表scott.emp的同义词为wyqorreplace：可选，如果同义词存在，则替换。public：创建公有同义词。不加创建私有同义词。私有同义词在权限更高的用户中可以通过scott.wyq来访问。 删除同义词 drop[public]synonymwyq;试验：创建emp的私有同义词wyq： createpublicsynonymwyqforemp; 创建成功提示：Synonymcreated 创建完成后，可以在数据字典（user_synonyms）中查看： select*fromuser_synonyms; 结果显示一条记录，synonym_name为wyq 使用wyq访问emp表 select*fromwyq; 使用sys用户登录，再执行上句： 错误提示：ORA-00942:表或视图不存在 sys用户具备所有权限，有权访问，应该使用用户名.同义词的形式： select*fromscott.wyq;为scott用户分配创建公有同义词的权限：grantdbatoscott;重新用scott用户连接，创建公有同义词： createpublicsynonymwyq2toemp;重新用用sys连接：使用wyq2查询： select*fromwyq2; 查询成功，可见公有同义词前不需要加用户名scott在sys用户连接中执行： createpublicsynonymwyq2foremp; 提示：ORA-00955:名称已由现有对象使用 使用orreplace替换： createorreplacepublicsynonymwyq2foremp; 创建成功， 删除同义词： dropsynonymwyq; 删除成功提示：Synonymdropped索引: 索引（Index）是为了加快数据的查找而创建的数据库对象。 索引是由Oracle自动使用和维护。索引是独立于表的数据库结构，即表和索引是分开存放的，当删除索引时，对拥有索引的表的数据没有影响。用于提高SQL语句的性能。列或数据较少的表中，不适合创建索引在导入大量数据时，最好是将触发器屏蔽，删除所有索引，当数据导入完毕后，再开启触发器和重建索引，这样性能会提高很多。索引优点： 提高查询性能索引缺点： 降低增删改的性能 增加磁盘空间的开销创建索引的条件：建在上面。经常使用where/groupby/orderby的列。频繁执行的SQL如：select*fromAwhereA=1;。数据量较多。基数高的列（数据中的重复值），基数越高，重复值越少，反之。频繁增删改的表，经测试选定。在主键上建立的唯一索引。关系表上主键组合索引。使用函数索引的列。索引分类（即B树索引，默认索引，最常用索引）： 唯一索引，位图索引，组合索引，基于函数的索引，反向键索引索引创建（默认索引）：CREATEINDEXitem_indexONitemfile(itemcode)TABLESPACEindex_tbs;item_index：索引名字，通常以_idx结尾 itemfile：表名 itemcode：字段列表SQL>createindextoys_idxontoys(toy_id); 索引重建：ALTERINDEXitem_indexREBUILD;REBUILD：重新创建索引，一个索引使用时间后，可以重建索引来提高查询效率。SQL>alterindextoys_idxrebuild; 删除索引： DROPINDEXitem_index; SQL>dropindextoys_idx; 唯一索引: CREATEUNIQUEINDEXitem_indexONitemfile(itemcode); 唯一索引确保在定义索引的列中没有重复值Oracle自动在表的主键列上创建唯一索引使用CREATEUNIQUEINDEX语句创建唯一索引 如：SQL>createuniqueindexename_idxonemp(ename);组合索引： CREATEINDEXcomp_indexONitemfile(p_category,itemrate); 组合索引是在表的多个列上创建的索引索引中列的顺序是任意的如果SQL语句的WHERE子句中引用了组合索引的所有列或大多数列，则可以提高检索速度 如：SQL>createindexempno_ename_idxonemp(empno,ename);反向键索引：REVERSE CREATEINDEXrev_indexONitemfile(itemcode)REVERSE; ALTERINDEXrev_indexREBUILDNOREVERSE; 反向键索引反转索引列键值的每个字节通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上创建索引时使用REVERSE关键字可以将反向键索引转换为普通索引，但此过程不可逆，即只能将反向键索引转换为普通索引，而不能将普通索引转换为反向键索引。数据块争用：两条数据的id相邻时，很可能存放在同一个数据块，如果两事物同时访问同一数据块，而数据块只允许一个事物访问，这里就会引起数据块的争用。 将id反转，就可以将数据存放地区分开，但会影响查询性能。 示例：两个相邻Id“10001”“10002”，反转后为：“10001”“20001”（内部实现应为字节反转） 如：SQL>createindexename_idxonemp(ename)reverse; SQL>alterindexename_idxrebuildnoreverse; 位图索引：BITMAP，常用于数据仓库 CREATEBITMAPINDEXbit_indexONorder_master(orderno); 位图索引适合创建在低基数列上位图索引不直接存储ROWID，而是存储字节位到ROWID的映射减少响应时间节省空间占用基数=列上不同值的个数/总行数低基数：相同值多 如：SQL>createbitmapindexename_idxonemp(ename);索引组织表：聚集索引，常用于数据仓库 只能在创建表时创建索引组织表 CREATETABLEind_org_tab(vencodeNUMBER(4)PRIMARYKEY,vennameVARCHAR2(20))ORGANIZATIONINDEX; 索引组织表的数据存储在与其关联的索引中索引中存储的是行的实际数据，而不是ROWID基于主键访问数据CREATETABLE命令与ORGANIZATIONINDEX子句一起用于创建索引组织表普通表与索引组织表的比较：普通表索引组织表ROWID唯一地标识行主键唯一地标识行隐式的ROWID列没有隐式的ROWID列基于ROWID的访问基于主键的访问顺序扫描返回所有行完全索引扫描返回所有行，并按主键顺序排列支持分区不支持分区如：createtablexxx(idintprimarykey,namevarchar(20))organizationindex 基于函数的索引： CREATEINDEXlowercase_idxONtoys(LOWER(toyname)); 基于一个或多个列上的函数或表达式创建的索引表达式中不能出现聚合函数不能在LOB类型的列上创建创建时必须具有QUERYREWRITE权限 注：在10g，11g中不再需要拥有此权限 如：SQL>createindexename_idxonemp(lower(ename)); 在索引中使用分区： 可以将索引存储在不同的分区中与分区有关的索引有三种类型：局部分区索引：在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致全局分区索引：在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关全局非分区索引：在分区表上创建的全局普通索引，索引没有被分区 与索引有关的数据字典： USER_INDEXES－用户创建的索引的信息USER_IND_PARTITIONS－用户创建的分区索引的信息USER_IND_COLUMNS－与索引相关的表列的信息

表分区ORACLE的分区是一种处理超大型表、索引等的技术。表分区优点：增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。表分区缺点：分区表相关：已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能。该缺点可以忽略，可以先创建有表分区的表，再将旧表的数据导去有表分区的新表。创建表分区：Oracle允许用户将一个表分成多个分区用户可以执行查询，只访问表中的特定分区将不同的分区存储在不同的磁盘，提高访问性能和安全性可以独立地备份和恢复每个分区分区方法：范围分区散列分区列表分区复合分区范围分区 以表中的一个列或一组列的值的范围分区，如：日期，价格 语句： PARTITIONBYRANGE(column_name) ( PARTITIONpart1VALUELESSTHAN(range1), PARTITIONpart2VALUELESSTHAN(range2), ... [PARTITIONpartNVALUELESSTHAN(MAXVALUE)] ); column_name：创建分区依据列 part：分区的名字 range：分区存放的值的范围（小于range） [PARTITIONpartNVALUELESSTHAN(MAXVALUE)]：可选，格式固定，表示前边分区的剩余值。 示例：CREATETABLESales(Product_IDvarchar2(5),Sales_Costnumber(10))PARTITIONBYRANGE(Sales_Cost)(PARTITIONP1VALUESLESSTHAN(1000),PARTITIONP2VALUESLESSTHAN(2000),PARTITIONP3VALUESLESSTHAN(3000),PARTITIONP4VALUESLESSTHAN(MAXVALUE)); 解释： 使用Sales_Cost字段创建分区 分区P1的范围是Sales_Cost小于1000的记录（不含1000） 分区P2的范围是Sales_Cost在1000到2000的记录（不含2000） 分区P3的范围是Sales_Cost在2000到3000的记录（不含3000） 分区P4存放剩余的记录散列分区 允许用户对不具有逻辑范围的数据进行分区 通过在分区键上执行HASH函数决定存储的分区，用户无法决定记录存放在哪一分区 将数据平均地分布到不同的分区（大量数据时才能体现） 语法： PARTITIONBYHASH(column_name) PARTITIONSnumber_of_partitions; 或 PARTITIONBYHASH(column_name) ( PARTITIONpart1[TABLESPACEtbs1], PARTITIONpart2[TABLESPACEtbs2], ... PARTITIONpartN[TABLESPACEtbsN] ); 第一种：只指定要散列的字段和分区的数目，系统自动通过HASH函数维护所有分区，分区名字由系统指定。 第二种：由用户指定每个安区的名字。 示例：CREATETABLEEmployee(Employee_IDvarchar2(5),Employee_Namevarchar2(20),Departmentvarchar2(10))PARTITIONBYHASH(Department)(PartitionD1,PartitionD2,PartitionD3); 解释： Department：分区依据的字段 分区的名字由用户指定：D1，D2，D3列表分区 允许用户将不相关的数据组织在一起，如：城市。 语法： PARTITIONBYLIST(column_name) ( PARTITIONpart1VALUES(values_list1), PARTITIONpart2VALUES(values_list2), ... PARTITIONpartNVALUES(DEFAULT) ); column_name：分区依据的列 values_list1：指定分区存放的值列表。 注：分区后，不允许修改column_name字段的值 示例：CREATETABLEEmployee(Emp_IDnumber(4),Emp_Namevarchar2(14),Emp_Addressvarchar2(15))PARTITIONBYLIST(Emp_Address)(Partitionnorthvalues('北京'),Partitionwestvalues('上海'),Partitionsouthvalues('广州','深圳'),PARTITIONotherVALUES(DEFAULT)); 解释： 根据地址分区，北京存放north区，上海存放west区，广州和深圳存放south区，其余的存放other区。复合分区 范围分区与散列分区或列表分区的组合 语法： PARTITIONBYRANGE(column_name1) SUBPARTITIONBYHASH(column_name2) SUBPARTITIONSnumber_of_partitions ( PARTITIONpart1VALUELESSTHAN(range1), PARTITIONpart2VALUELESSTHAN(range2), ... PARTITIONpartNVALUELESSTHAN(MAXVALUE) ); 将一个分区中数据再次分区，即分区的嵌套。 首先按PARTITION指定的方式分区，再为每个分区按照SUBPARTITION指定的方式划分子分区。 注：分区过多不利维护 示例：CREATETABLESALES(PRODUCT_IDVARCHAR2(5),SALES_DATEDATENOTNULL,SALES_COSTNUMBER(10))PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYHASH(PRODUCT_ID)SUBPARTITIONS5(PARTITIONS1VALUESLESSTHAN(TO_DATE(‘01/4月/2001','DD/MON/YYYY')),PARTITIONS2VALUESLESSTHAN(TO_DATE(‘01/7月/2001','DD/MON/YYYY')),PARTITIONS3VALUESLESSTHAN(TO_DATE(‘01/9月/2001','DD/MON/YYYY')),PARTITIONS4VALUESLESSTHAN(MAXVALUE));解释：首先根据SALES_DATE字段范围分区，分4个区（S1，S2，S3，S4），再将每个分区根据PRODUCT_ID散列分区，分5个分区，共20个分区表分区内数据的操作 在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区 如：SQL>INSERTINTOSALESVALUES(‘P002’,’10-5月-2001',2508); 查询、修改和删除分区表时可以显式指定要操作的分区 如： 查询指定分区的数据：SQL>SELECT*FROMSALES3PARTITION(P3); 删除指定分区的数据：SQL>DELETEFROMSALES3PARTITION(P2);

并发与锁事务概念：从业务角度来看，有多个操作同生共死的工作单元，要么同时成功，要么同时失败。事务ACID：原子性：事务的操作不可分割，要么同时成功，要么同时失败。一致性：数据正确性。隔离性：多事务之间的交叉，当前的事务与其他未完成的事务是隔离的。持久性：事务完成后，必须保证数据持久化到数据库中。事务隔离性级别：Readuncommitted（脏读）：读未提交的数据（Oracle中没有该级别）。Readcommitted：读提交后的数据，不会出现脏读，会不可重复读（Oracle默认级别）。Readrepeatable：可重复读，不会发生不可重复的问题（Oracle中没有该级别）。Serializable：序列化，事务排队一个个执行，相当会不会并发。Oracle中有两种事务提交：隐式事务（DDL语句自动提交）和显示事务（DML语句，需要commit才会提交）。锁一般数据库都有并发机制，不过带来的问题就是数据访问的冲突。为了解决这个问题，大多数数据库用的方法就是数据的锁定。锁：数据库用来控制共享资源并发访问的机制，用于保护正在被修改的数据。 关键字：共享资源，并发访问，数据可能被修改。并发与共享的区别：并发指同时访问，而共享不一定同时访问。使用锁的优点： 一致性：一次只允许一个用户修改数据完整性：为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户并行性：－允许多个用户访问同一数据加锁和释放锁的时机：（见下图） 加锁时机： 悲观锁：一旦使用数据（事务开始）时就加锁，提交或回滚后才释放。 乐观锁：事务提交前不加锁，当事务提交（修改数据库）时加锁，提交后立即释放。 注：悲观锁是针对并发的可能性比较大时，乐观锁可能造成事务提交失败，所以在并发可能性较低时使用，通常使用版号进行控制。 释放锁的时机： 事务提交，回滚，连接超时，故障（使用SMON，PMON释放）。锁的类型（级别）：数据库级锁，表空间级锁，表级锁，行级锁。 Oracle默认悲观行级锁注：加锁一般不影响查询。行级锁：对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行，使用行级锁的性能损失较少。行级锁是级别最低的锁，是Oracle默认的锁，执行insert，update，delete，select...forupdate语句时自动加锁。加悲观行级锁 select…forupdate子句允许用户一次锁定多条记录进行操作（更新），如果select语句中没有where子名，将锁定整张表，这里锁自动升级为表级锁。 自动加锁后，需要使用commit或rollback释放锁。例子：oracle自动加的行级锁 打开2个PL/SQL模拟2个用户在上一个事务中,事务没有结束,此行一直被锁定.等待上一个事务结束在上一个事务中,事务没有结束,此行一直被锁定.等待上一个事务结束结束事务:回滚事务,释放锁,下面操作就可以执行了回滚事务,释放锁,下面操作就可以执行了例子:手动让oracle应用行级锁（SELECT…FORUPDATE语句允许用户一次锁定多条记录进行更新)。手动加锁||查询就锁定标示这些数据可能要做更改操作。手动加锁||查询就锁定标示这些数据可能要做更改操作。提交事务：回滚事务,释放锁,下面操作就可以执行了。回滚事务,释放锁,下面操作就可以执行了。表级锁：语法： locktabletable_nameinmode_typemode;表级锁的类型有： 共享，排他，共享排他，行共享，行排他共享锁(SHARE)锁定表，仅允许其他用户查询表中的行禁止其他用户插入、更新和删除行多个用户可以同时在同一个表上应用此锁排他(EXCLUSIVE)：限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表行共享(ROWSHARE)：禁止排他锁定表行排他(ROWEXCLUSIVE)：禁止使用排他锁和共享锁共享行排他(SHAREROWEXCLUSIVE)：比共享锁更多的限制，禁止使用共享锁及更高的锁例子：共享锁:（锁表,其他用户无法增删改,可锁N个）。第一个用户开启了共享锁,其他用户无法修改数据只能查看只有当第一个用户结束了事务才能操作第一个用户开启了共享锁,其他用户无法修改数据只能查看只有当第一个用户结束了事务才能操作例子：排他锁[仅仅允许加锁,不允许DML操作,直到解锁]不能加锁[排他,最高级别]不能执行更改例子：死锁:交叉加锁（导致