Oracle模式对象基础教案

第9章模式对象本章内容模式概念表管理索引管理分区表与分区索引的管理视图、序列、同义词、数据库链接管理本章要求掌握表的管理掌握索引的管理掌握分区的概念及分区管理了解视图、序列、同义词、数据库链接等的管理9.1模式模式概念模式与用户的关系模式选择与切换模式概念是指一系列逻辑数据结构或对象的集合。模式与用户的关系模式与用户相对应，一个模式只能被一个数据库用户所拥有，并且模式的名称与这个用户的名称相同。通常情况下，用户所创建数据库对象都保存在与自己同名的模式中。同一模式中数据库对象的名称必须惟一，而在不同模式中的数据库对象可以同名。默认情况下，用户引用的对象是与自己同名模式中的对象，如果要引用其他模式中的对象，需要在该对象名之前指明对象所属模式。模式选择与切换如果用户以NORMAL身份登录，则进入同名模式；若以SYSDBA身份登录，则进入SYS模式；如果以SYSOPER身份登录，则进入PUBLIC模式。9.2表创建表表约束的管理表参数设置修改表删除表利用OEM管理表9.2.1创建表表的创建创建临时表利用子查询创建表（1）表的创建表的规划与设计表名称列的数量列名称与类型表约束表内部数据的组织方式（标准表、索引化表、分区表）表存储位置、存储空间分配等。使用CREATETABLE语句创建表CREATETABLEtable_name(column_namedatatype[column_level_constraint][，column_namedatatype[column_level_constraint]…][，table_level_constraint])[parameter_list];CREATETABLEemployee(empnoNUMBER(5)PRIMARYKEY,enameVARCHAR2(15),deptnoNUMBER(3)NOTNULLCONSTRAINTfk_empREFERENCESdept（deptno）)TABLESPACEUSERSPCTFREE10PCTUSED40STORAGE(INITIAL50KNEXT50KMAXEXTENTS10PCTINCREASE25);表名（table_name）必须是合合法标识识符，长长度为1～30字节，并并且以字字母开头头，可以以包含字字母(A～Z，a～z)、数字（（0～9），下划划线（_）、美元元符号（（$）和井号号（#）。此外外，表名名称不能能是Oracle数据库的的保留字字数据类型型(datatype)字符类型型数字类型型日期类型型LOB类型二进制类类型行类型CHAR（n）定长字符符串，n的取值范范围为1-2000字节VARCHAR2（n）可变字符符串，n取值范围围为1-4000字节自动调整整数据长长度NCHAR(n)用来存储储Unicode类型字符符串。NVARCHAR2(n)它用来存存储Unicode类型字符符串。LONG可变长字字符列，，最大长长度为2GB用于不需需设置成成索引的的字符，，不常用用字符类型型数字类型型NUMBER（m，n）用于存储储整数和和实数。。m表示数值值的总位位数（精精度），，取值范范围为1～38，默认为为38；n表示小数数位数，，若为负负数则表表示把数数据向小小数点左左边舍入入，默认认值为0。日期类型型DATE可以存储储的日期期范围为为公元前前4712年1月1日到公元元4712年1月1日，由世世纪、年年、月、、日、时时、分、、秒组成成。可以以在用户户当前会会话中使使用参数数NLS_DATE_FORMAT指定日期期和时间间的格式式，或者者使用TO_DATE函数，将将表示日日期和时时间的字字符串按按特定格格式转换换成日期期和时间间。TIMESTAMP[(n)]：表示时时间戳，，是DATE数据类型型的扩展展，允许许存储小小数形式式的秒值值。n表示秒的的小数位位数，取取值范围围为1～9，默认值值为6。TIMESTAMP[(n)]WITHTIMEZONE：通过存存储一个个时区偏偏差来扩扩展TIMESTAMP类型。时时区偏偏差值为为相对于于通用协协调时间间（或称称UTC，以前称称为格林林威治时时间或GMT）的时差差。TIMESTAMP[(n)]WITHLOCALTIMEZONE与TIMESTAMP[(n)]WITHTIMEZONE的不同之之处在于于，存储储日期时时直接转转换为数数据库时时区日期期，而读读取日期期时将数数据库时时区日期期转换为为用户会会话时区区日期。。INTERVALYEAR(n)TOMONTH存储以年年份和月月份表示示的时间间段。n是表示YEAR的最多数数字位数数，默认认为2。例如，，INTERVAL'2-11'YEARTOMONTH表示2年11个月。INTERVALDAY(m)TOSECOND(n)存储以天天数、小小时数、、分钟数数和秒数数表示的的时间段段。m是表示DAY的最多数数字位数数，默认认为2。n是表示SECOND的小数部部分的位位数，默默认为6。例如，，INTERVAL'510:30:40'DAYTOSECOND表示5天10小时30分40秒。LOB类型CLOB用于存储储可变长长度的字字符数据据，如文文本文件件等，最最大数据据量为4GB。NCLOB用于存储储可变长长度的Unicode字符数据据，最大大数据量量为4GB。BLOB用于存储储大型的的、未被被结构化化的可变变长度的的二进制制数据（（如二进进制文件件、图片片文件、、音频和和视频等等非文本本文件）），最大大数据量量为4GB。BFILE用于存储储指向二二进制格格式文件件的定位位器，该该二进制制文件保保存在数数据库外外部的操操作系统统中，文文件最大大为4GB。二进制类类型RAW(n)用于存储储可变长长度的二二进制数数据，n表示数据据长度，，取值范范围为1～2000字节；LONGRAW用于存储储可变长长度的二二进制数数据，最最大存储储数据量量为2GB。行类型ROWID行标识符符，表中中行的物物理地址址的伪列列类型。。ROWID类型数据据由18位十六进进制数构构成，包包括对象象编号、、文件编编号、数数据块编编号和块块内行号号。UROWID行标识符符，用于于表示索索引化表表中行的的逻辑地地址。约束（constraint）在Oracle数据库中中对列的的约束包包括主键键约束、、惟一性性约束、、检查约约束、外外键约束束和空/非空约束束等5中，定义义方法有有表级约约束和列列级约束束2种。参数（parameter_list）在定义表表时，可可以通过过参数设设置表存存储在哪哪一个表表空间中中，和存存储空间间分配等等。（2）创建临临时表临时表中中的数据据在特定定条件下下自动释释放，但但其结构构将一直直存在。。根据临时时表中数数据被释释放的时时间不同同，临时时表分为为事务级级别的临临时表和和会话级级别的临临时表两两类。ONCOMMITDELETEROWS//事务级临临时表ONCOMMITPRESERVEROWS//会话级临临时表语法使用CREATEGLOBALTEMPORARYTABLE语句创建建临时表表使用ONCOMMIT子句说明明临时表表的类型型，默认认为事务务级别的的临时表表。事务级别别的临时时表是在在事务提提交时系系统自动动删除表表中所有有记录。。CREATEGLOBALTEMPORARYTABLEtran_temp(IDNUMBER(2)PRIMARYKEY,nameVARCHAR2(20))ONCOMMITDELETEROWS;会话级别的临临时表是在会会话终止时系系统自动删除除表中所有记记录。CREATEGLOBALTEMPORARYTABLEsess_temp(IDNUMBER(2)PRIMARYKEY,nameVARCHAR2(20))ONCOMMITPRESERVEROWS;（3）利用子查询询创建表语法CREATETABLEtable_name(column_name[column_level_constraint][，column_name[column_level_constraint]…][，table_level_constraint])[parameter_list]ASsubquery;注意通过该方法创创建表时，可可以修改表中中列的名称，，但是不能修修改列的数据据类型和长度度；源表中的约束束条件和列的的缺省值都不不会复制到新新表中；子查询中不能能包含LOB类型和LONG类型列；当子查询条件件为真时，新新表中包含查查询到的数据据；当查询条条件为假时，，则创建一个个空表。创建一个标准准表，保存工工资高于3000的员工的员工工号、员工名名和部门号。。语句为CREATETABLEemp_select(emp_no,emp_name,dept_no)ASSELECTempno,ename,deptnoFROMemployeeWHEREsal>3000;创建一个会话话级临时表，，保存部门号号、部门人数数和部门的平平均工资。CREATEGLOBALTEMPORARYTABLEept_tempONCOMMITPRESERVEROWSASSELECTdeptno,count(*)num,avg(sal)avgsalFROMempGROUPBYdeptno;9.2.2表约束约束的类别定义约束添加和删除约约束设置约束状态态约束的延迟检检查查询约束信息息（1）约束的类别别约束作用是在表中定义义的用于维护护数据库完整整性的一些规规则。通过对对表中列定义义约束，可以以防止在执行行DML操作时，将不不符合要求的的数据插入到到表中。约束类型PRIMARYKEYUNIQUECHECKFOREIGNKEYNULL/NOTNULLPRIMARYKEY特点定义主键，起起惟一标识作作用，其值不不能为NULL，也不能重复复；一个表中只能能定义一个主主键约束；建立主键约束束的同时，在在该列上建立立一个惟一性性索引，可以以为它指定存存储位置和存存储参数；主键约束可以以是列级约束束，也可以是是表级约束。。UNIQUE特点定义为惟一一性约束的的某一列或或多个列的的组合的取取值必须惟惟一；如果某一列列或多个列列仅定义惟惟一性约束束，而没有有定义非空空约束，则则该约束列列可以包含含多个空值值；Oracle自动在惟一一性约束列列上建立一一个惟一性性索引，可可以为它指指定存储位位置和存储储参数；惟一性约束束可以是列列级约束，，也可以是是表级约束束。在一个基本本表中只能能定义一个个PRIMARYKEY约束，但可可定义多个个UNIQUE约束；对于指定为为PRIMARYKEY的一个列或或多个列的的组合，其其中任何一一个列都不不能出现空空值，而对对于UNIQUE所约束的唯唯一键，则则允许为空空。不能为同一一个列或一一组列既定定义UNIQUE约束，又定定义PRIMARYKEY约束。PRIMARYKEY与UNIQUE比较CHECK特点检查约束用用来限制列列值所允许许的取值范范围，其表表达式中必必须引用相相应列，并并且表达式式的计算结结果必须是是一个布尔尔值；约束表达式式中不能包包含子查询询，也不能能包含SYSDATE、USER等SQL函数，和ROWID、ROWNUM等伪列；一个列可以以定义多个个检查约束束；检查约束可可以是列级级约束，也也可以是表表级约束。。概念FOREIGNKEY约束指定某某一个列或或一组列作作为外部键键，其中，，包含外部部键的表称称为从表，，包含外部部键所引用用的主键或或唯一键的的表称主表表。系统保证从从表在外部部键上的取取值要么是是主表中某某一个主键键值或唯一一键值，要要么取空值值。以此保保证两个表表之间的连连接，确保保了实体的的参照完整整性。FOREIGNKEY特点定义外键约约束的列的的取值要么么是主表参参照列的值值，要么为为空；外键列只能能参照于主主表中的主主键约束列列或惟一性性约束列；；可以在一列列或多列组组合上定义义外键约束束；外键约束可可以是列级级约束，也也可以是表表级约束。。NULL/NOTNULL特点在同一个表表中可以定定义多个NOTNULL约束；只能是列级级约束。（2）定义约束束列约束对某一个特特定列的约约束，包含含在列定义义中，直接接跟在该列列的其他定定义之后，，用空格分分隔,不必指定列列名；表约束与列定义相相互独立，，不包括在在列定义中中。通常用用于对多个个列一起进进行约束，，与列定义义用’,’分隔。定义义表约束时时必须指出出要约束的的那些列的的名称。定义列级约约束的语法法为：[CONSTRAINTconstraint_name]constraint_type[conditioin];定义表级约约束的语法法为：[CONSTRAINTconstraint_name]constraint_type([column1_name,column2_name,…]|[condition]);注意Oracle约束通过名名称进行标标识。在定定义时可以以通过CONSTRAINT关键字为约约束命名。。如果用户户没有为约约束命名，，Oracle将自动为约约束命名。。创建一个student表。CREATETABLEstudent(snoNUMBER(6)CONSTRAINTS_PKPRIMARYKEY,snameVARCHAR2(10)NOTNULL,sexCHAR(2)CONSTRAINTS_CK1CHECK(sexin('M','F')),sageNUMBER(6,2),CONSTRAINTS_CK2CHECK(sagebetween18and60));创建一个course表，同时为为主键约束束列上的唯唯一性索引引设置存储储位置和存存储参数，，语句为CREATETABLEcourse(cnoNUMBER(6)PRIMARYKEY,cnameCHAR(20)UNIQUEUSINGINDEXTABLESPACEindxSTORAGE(INITIAL64KNEXT64K));创建一个SC表，语句为为CREATETABLESC(snoNUMBER(6)REFERENCESstudent(sno),cnoNUMBER(6)REFERENCEScourse(cno),gradeNUMBER(5,2),CONSTRAINTSC_PKPRIMARYKEY(sno,cno));定义列级FOREIGNKEY约束[CONSTRAINTconstraint_name][FOREIGNKEY]REFERENCESref_table_name(column_name,…)定义表级FOREIGNKEY约束[CONSTRAINTconstraint_name]FOREIGNKEY(column_name,…)REFERENCESref_table_name(column_name,…)[ONDELETECASCADE|SETNULL];ONDELETECASCADE删除子表中中所有相关关记录ONDELETESETNULL将子表中相相关记录的的外键约束束列值设置置为NULLONDELETERESTRICTED受限删除，，即如果子子表中有相相关子记录录存在，则则不能删除除主表中的的父记录，，默认引用用方式。DEFAULT如果用户在在插入新行行时没有显显示为列提提供数据，，系统将默默认值赋给给该列。语法[CONSTRAINT<约束名>]DEFAULT表达式（3）添加和删删除约束添加约束语语法为：ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]constraint_type(column1_name,column2_name,…)[condition];创建一个player表CREATETABLEplayer(IDNUMBER(6),snoNUMBER(6),snameVARCHAR2(10),sageNUMBER(6,2),resumeVARCHAR2(1000));添加主键约约束ALTERTABLEplayerADDCONSTRAINTP_PKPRIMARYKEY(ID);添加惟一性性约束ALTERTABLEplayerADDCONSTRAINTP_UKUNIQUE(sname);添加检查约约束ALTERTABLEplayerADDCONSTRAINTP_CKCHECK(sageBETWEEN20AND30);添加外键约约束ALTERTABLEplayerADDCONSTRAINTP_FKFOREIGNKEY(sno)REFERENCESstudent(sno)ONDELETECASCADE;添加空/非空约束为表列添加加空/非空约束时时必须使用用MODIFY子句代替ADD子句ALTERTABLEplayerMODIFYresumeNOTNULL;ALTERTABLEplayerMODIFYresumeNULL;删除约束使用ALTERTABLE…DROP语句删除已已经定义的的约束。可以通过直直接指定约约束的名称称来删除约约束，或指指定约束的的内容来删删除约束。。删除指定内内容的约束束ALTERTABLEplayerDROPUNIQUE(sname);删除指定名名称的约束束ALTERTABLEplayerDROPCONSTRAINTP_CK;删除主键约约束、唯一一性约束的的同时将删删除唯一性性索引，如如果要在删删除约束时时保留唯一一性索引，，则必须在在ALTERTABLE…DORP语句中指定定KEEPINDEX子句。ALTERTABLEplayerDROPCONSTRAINTP_UKKEEPINDEX;如果要在删删除约束的的同时，删删除引用该该约束的其其他约束，，则需要在在ALTERTABLE…DORP语句中指定定CASCADE关键字。ALTERTABLEplayerDROPCONSTRAINTP_PKCASCADE;（4）设置约束束状态激活（ENABLE）状态当约束处于于激活状态态时，约束束将对表的的插入或更更新操作进进行检查，，与约束规规则冲突的的操作被回回退。禁用（DISABLE）状态当约束处于于禁用状态态时，约束束不起作用用，与约束束规则冲突突的插入或或更新操作作也能够成成功执行。。利用SQL*Loader从外部数据据源提取大大量数据到到数据库中中；进行数据库库中数据的的大量导入入、导出操操作；针对表执行行一项包含含大量数据据操作的批批处理工作作时。禁用约束在定义约束束时，可以以将约束设设置为禁用用状态，默默认为激活活状态。也也可以在约约束创建后后，修改约约束状态为为禁用。创建表时禁禁用约束CREATETABLES(SNOCHAR(11)PRIMARYKEYDISALBE,…);利用ALTERTABLE…DISABLE禁用约束ALTERTABLESTUDENTDISABLECONSTRAINTS_CK1;ALTERTABLESTUDENTDISABLEUNIQUE(sname);禁用主键约约束、惟一一性约束时时，会删除除其对应的的惟一性索索引，而在在重新激活活时，Oracle为它们重建建惟一性索索引。若在在禁用约束束时，保留留对应的惟惟一性索引引，可使用用ALTERTABLE…DISABLE…KEEPINDEX语句。ALTERTABLESTUDENTDISABLEUNIQUE(sname)KEEPINDEX;ALTERTABLESTUDENTDISABLEPRIMARYKEYKEEPINDEX;若当前约束束(主键约束、、惟一性约约束)列被引用，，则需要使使用ALTERTABLE…DISABLE…CASCADE语句同时禁禁用引用该该约束的约约束。ALTERTABLESTUDENTDISABLEPRIMARYKEYKEEPINDEXCASCADE;激活约束创建或添加加约束时，，默认为激激活状态。。利用ALTERTABLE…ENABLE…语句激活约约束ALTERTABLESTUDENTENABLEUNIQUE(sname);禁用主键约约束、惟一一性约束时时，会删除除其对应的的惟一性索索引，而在在重新激活活时，Oracle为它们重建建惟一性索索引，可以以为索引设设置存储位位置和存储储参数(索引与表尽尽量分开存存储)。ALTERTABLESTUDENTENABLEPRIMARYKEYUSINGINDEXTABLESPACEindxSTORAGE(INITIAL32KNEXT16K);通过ALTERTABLE…MODIFY…DISABLE|ENABLE语句改变约约束状态ALTERTABLESTUDENTMODIFYCONSTRAINTS_CK2DISABLE;（5）约束的延延迟检查默认情况下下，在表中中的约束都都是不可延延迟约束，，Oracle在一条DML语句执行完完毕之后立立即进行约约束检查（（除非禁用用）。创建约束时时可以显式式使用DEFERRABLE关键字，创创建可延迟迟的约束。。INITIALLYIMMEDIATE或INITIALLYDEFERRED说明可延迟迟约束在初初始状态下下是立即检检查还是延延迟检查如果在定义义约束时设设定为不可可延迟，则则约束创建建后不能更更改其可延延迟性。只只有创建时时设定为可可延迟的约约束，创建建后才能更更改其可延延迟性。创建两个表表，其约束束都是可延延迟的。CREATETABLEnew_dept(deptnoNUMBERPRIMARYKEYDEFERRABLEINITIALLYIMMEDIATE,dnameCHAR(11)UNIQUE);CREATETABLEnew_emp(empnoNUMBERPRIMARYKEY,enameCHAR(11),deptnoNUMBERCONSTRAINTNE_FKREFERENCESnew_dept(deptno)ONDELETECASCADEDEFERRABLE);由于外键约约束的作用用，执行下下面语句时时会产时错错误。INSERTINTOnew_empVALUES(1,'ZHANG',11);*ERROR位于第1行:ORA-02291:违反完整约约束条件(SCOTT.E_FK)-未找到父项项关键字将new_emp表的外键约约束检查延延迟。ALTERTABLEnew_empMODIFYCONSTRAINTNE_FKINITIALLYDEFERRED;此时时，，由由于于将将new_emp表外外键键约约束束延延迟迟到到事事务务结结束束后后进进行行检检查查，，因因此此可可以以先先向向new_emp中插插入入数数据据，，而而后后向向new_dept中插插入入数数据据。。INSERTINTOnew_empVALUES(1,'ZHANG',11);INSERTINTOnew_deptVALUES(11,'COMPUTER');COMMIT;操作作完完后后，，应应将将new_emp外键键约约束束检检查查恢恢复复原原来来状状态态。。ALTERTABLEnew_empMODIFYCONSTRAINTNE_FKINITIALLYIMMEDIATE;注意意在修修改改约约束束的的检检查查延延迟迟性性时时，，如如果果无无法法确确定定约约束束的的名名称称或或需需要要设设置置多多个个约约束束的的延延迟迟性性，，可可以以一一次次性性将将所所有有可可延延迟迟的的约约束束延延迟迟或或恢恢复复。。SETCONSTRAINTALLDEFERRED;SETCONSTRAINTALLIMMEDIATE;（6）查查询询约约束束信信息息ALL_CONSTRAINTSUSER_CONSTRAINTSDBA_CONSTRAINTSALL_CONS_COLUMNSUSER_CONS_COLUMNSDAB_CONS_COLUMNS查看看student表中中的的所所有有约约束束。。SELECTCONSTRAINT_NAME,CONSTRAINT_TYPE,DEFERRED,STATUSFROMUSER_CONSTRAINTSWHERETABLE_NAME='STUDENT';查看看student表中中各各个个约约束束所所作作用用的的列列。。SELECTCONSTRAINT_NAME,COLUMN_NAMEFROMUSER_CONS_COLUMNSWHERETABLE_NAME='STUDENT';9.2.3表参参数数设设置置TABLESPACETABLESPACE子句句用用于于指指定定表表存存储储的的表表空空间间。。STORAGESTORAGE子句句用用于于设设置置表表的的存存储储参参数数。。若若不不指指定定，，则则继继承承表表空空间间的的存存储储参参数数设设置置。。NITIALNEXTPCTINCREASEMINEXTENTSMAXEXTENTSBUFFER_POOL(KEEP、RECYCLE、DEFAULT)STORAGE参数数设设置置需需注注意意如果果表表空空间间管管理理方方式式为为EXTENTMANAGEMENTLOCALAUTOALLOCATE，则则在在STORAGE中只只能能指指定定INITIAL，NEXT和MINEXTENTS这3个参参数数；；如果果表表空空间间管管理理方方式式为为EXTENTMANAGEMENTLOCALUNIFORM，则不不能指指定任任何STORAGE子句；；如果表表空间间管理理方式式为EXTENTMANAGEMENTDICTIONARY，则在在STORAG中可以以设置置任何何参数数。数据块块管理理参数数PCTFREE：用于于指定定数据据块中中必须须保留留的最最小空空闲空空间。。PCTUSED：用于于指定定当数数据块块空闲闲空间间达到到PCTFREE参数的的限制制后，，数据据块能能够被被再次次使用用前，，已占占用的的存储储空间间必须须低于于的比比例。。INITRANS：用于于指定定能够够并发发访问问同一一个数数据块块的事事务的的数量量。MAXTRANS：用于于指定定能够够并发发访问问同一一个数数据块块的事事务的的最大大数量量。LOGGING与NOLOGGING子句默认为为NOLOGGING，即表表的创创建操操作不不会记记录到到重做做日志志文件件中，，尤其其适合合通过过查询询创建建表的的情况况。使使用LOGGING子句，，表的的创建建操作作（包包括通通过查查询创创建表表时的的插入入记录录操作作）都都将记记录到到重做做日志志文件件中。。PARALLEL、NOPARALLELCACHE、NOCACHEMONITORING、NOMONITORING9.2.4修改表表基本语语法列的添添加、、删除除、修修改表参数数修改改表结构构重组组表重命命名等等为表和和列添添加注注释（１））修改改表基基本语语法ALTERTABLE<表名>ADD<新列名名><数据类类型>[<完整性性约束束定义义]MODIFY<列名><数据类类型>RENAMECOLUMNoldnameTOnewnameSETUNUSEDCOLUMNcolumn//singlecolumnSETUNUSEDCOLUMNS（column1，column2…）DROPCOLUMN<col>//singlecolumnDROP<col1，col2…>//multicolumnDROPUNUSEDCOLUMNS（２））列的的添加加、删删除、、修改改添加列列语法ALTERTABLEtable_nameADD(new_column_namedatatype[NOTNULL][DEFAULTvalue]);示例ALTERTABLEemployeeADD(phoneVARCHAR2(11),hiredateDATEDEFAULTSYSDATENOTNULL);修改列列类型型语法ALTERTABLEtable_nameMODIFYcolumn_namenew_datatype;修改表表中列列类型型时，，必须须满足足下列列条件件：可以增增大字字符类类型列列的长长度和和数值值类型型列的的精度度；如果字字符类类型列列、数数值类类型列列中数数据满满足新新的长长度、、精度度，则则可以以缩小小类型型的长长度、、精度度；如果不不改变变字符符串的的长度度，可可以将将VARCHAR2类型和和CAHR类型转转换；；如果更更改数数据类类型为为另一一种非非同系系列类类型，，则列列中数数据必必须为为NULL。示例ALTERTABLEemployeeMODIFYenameCHAR(20);ALTERTABLEemployeeMODIFYphoneNUMBER;修改列列名语法ALTERTABLEtable_nameRENAMECOLUMNoldnameTOnewname;示例ALTERTABLEemployeeRENAMECOLUMNenameTOemployee_name；删除列列直接删删除列列语法法ALTERTABLEtable_nameDROP[COLUMNcolumn_name]|[(column1_name,column2_name,…)][CASCADECONSTRAINTS];直接删删除列列示例例ALTERTABLEscDROPCOLUMNsnoCASCADECONSTRAINTS;ALTERTABLEemployeeDROP(phone,hiredate);将列标标记为为UNUSED，然后后进行行删除除。ALTERTABLEtable_nameSETUNUSED[COLUMNcolumn_name]|[(column1_name,column2_name,…)][CASCADECONSTRAINTS];示例ALTERTABLEplayerSETUNUSEDCOLUMNsage;ALTERTABLEplayerSETUNUSED(sname,resume);ALTERTABLEplayerDROPUNUSEDCOLUMNS;如果数数据库库表空空间处处于字字典管管理方方式，，可以以对表表的参参数进进行修修改，，包括括存储储参数数、存存储位位置、、数据据块设设置等等。ALTERTABLEemployeePCTFREE30PCTUSED60STORAGE(NEXT512KPCTINCREASE11);注意表创建建后不不能对对INITIAL、MINEXTENTS两个参参数进进行修修改。。（３））表参参数修修改将一个个非分分区的的表移移动到到一个个新的的数据据段中中，或或者移移动到到其他他的表表空间间中，，通过过这种种操作作可以以重建建表的的存储储结构构，称称为表表结构构重组组。如果发发现表表的数数据段段具有有不合合理的的区分分配方方式，，但是是又不不能通通过别别的方方法来来进行行调整整（改改变存存储参参数不不会影影响到到已经经分配配的区区），，可以以考虑虑将表表移动动到一一个新新的数数据段段中。。此外外，如如果频频繁地地对表表进行行DML操作，，会产产生大大量空空间碎碎片和和行迁迁移、、行连连接，，可以以考虑虑进行行表结结构重重组。。（４））表结结构重重组语法ALTERTABLEtbnameMOVE[TABLESPACEtbs_name]注意直到表表被完完全移移动到到新的的数据据段中中之后后，Oracle才会删删除原原来的的数据据段；；表结构构重组组后，，表中中每个个记录录的ROWID会发生生变化化，因因此该该表的的所有有索引引失效效，需需要重重新建建立索索引；；如果表表中包包含LOB列，则则默认认情况况下不不移动动LOB列数据据和LOB索引段段。语法ALTERTABLEold_nameRENAMETOnew_name;RENAMEold_nameTOnew_name;说明表重命名名后，Oracle会自动将将旧表上上的对象象权限、、约束条条件等转转换到新新表上，，但是所所有与旧旧表相关关联的对对象都会会失效，，需要重重新编译译。（５）表表重命名名为表添加加注释COMMENTONTALBEtable_nameIS…；为列添加加注释COMMENTONCOLUMNtable_name.column_nameIS…注释可以以通过以以下数据据字典来来查看.ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTS（６）为为表和列列添加注注释语法DROPTABLEtable_name[CASCADECONSTRAINTS][PURGE]删除一个个表同时时，Oracle将执行下下列操作作：删除该表表中所有有记录；；从数据字字典中删删除该表表定义；；删除与该该表相关关的所有有索引和和触发器器；回收为该该表分配配的存储储空间；；依赖于该该表的数数据库对对象处于于INVALID状态。9.2.5删除表注意在Oracle10g中，使用用DROPTABLE语句删除除一个表表时，并并不立即即回收该该表的空空间，而而只是将将表及其其关联对对象的信信息写入入一个称称为“回收站”（RECYCLEBIN）的逻辑辑容器中中，从而而可以实实现闪回回删除表表操作。。如果要要回收该该表空间间，可以以采用清清空“回收站”（PURGERECYCLEBIN）或在DROPTABLE语句中使使用PURGE语句。9.2.6利用OEM管理表创建表删除表修改表查询表（1）创建表表（2）表的其其他管理理9.3索引索引概述述索引管理理利用OEM管理索引引9.3.1索引概述述索引概念念及作用用索引是为为了加速速对表中中元组的的检索而而创建的的一种分分散存储储结构；；是对表而而建立的的，由除除存放表表的数据据页面以以外的索索引页面面组成，，独立于于被索引引的表；；通过使用用索引加加速行的的检索，，但减慢慢更新的的速度；；快速定位位数据，，减少磁磁盘I/O；Oracle自动使用用、维护护索引索引分类类惟一性索索引与非非惟一性性索引平衡树索索引与位位图索引引单列索引引与复合合索引函数索引引聚簇索引引全局索引引与本地地索引索引使用用原则导入数据据后再创创建索引引在适当的的表和字字段上创创建索引引经常查询询的记录录数目少少于表中中所有记记录总数数的5%时就应当当创建索索引；经常进行行连接查查询表时时，在连连接列上上建立索索引能够够显著提提高查询询的速度度；对于取值值范围很很大的列列应当创创建B树索引；；对于取值值范围很很小的列列应当创创建位图图索引；；不能在LONG，LONGRAW，LOB数据类型型的列上上创建索索引；Oracle会自动在在PRIMARYKEY和UNIQUE约束的列列上创建建唯一性性索引。。合理设置置复合索索引中的的字段顺顺序限制表中中索引的的数目表中索引引数目越越多，查查询速度度越快，，但表的的更新速速度越慢慢。因为为索引越越多，维维护索引引所需开开销越大大，当更更新表时时，需要要同时更更新与表表相关的的所有索索引。为索引设设置合适适的PCTFREE参数选择存储储索引的的表空间间默认情况况下，索索引与表表存储在在同一表表空间中中。索引引与表存存储在同同一表空空间中，，有利于于数据库库维护操操作，具具有较高高的可用用性；反反之，若若索引与与表存储储在不同同的表空空间中，，则可提提高系统统的存取取性能，，减少硬硬盘I/O冲突，但但是表与与索引可可用状态态可能出出现不一一致，如如一个处处于联机机状态，，另一个个处于脱脱机状态态。9.3.2管理索引引创建索引引修改索引引监视索引引删除索引引索引的查查询语法CREATE[UNIQUE]|[BITMAP]INDEXindex_nameONtable_name([column_name[ASC|DESC],…]|[expression])[REVERSE][parameter_list]；说明UNIQUE表示建立立惟一性性索引；；BITMAP表示建立立位图索索引；ASC/DESC用于指定定索引值值的排列列顺序，，ASC表示按升升序排序序，DESC表示按降降序排序序，缺省省值为ASC；REVERSE表示建立立反键索索引；parameter_list用于指定定索引的的存放位位置、存存储空间间分配和和数据块块参数设设置。（1）创建索索引创建非惟惟一性索索引CREATEINDEXemployee_enameONemployee(ename)TABLESPACEusersSTORAGE(INITIAL20KNEXT20kPCTINCREASE75);创建惟一一性索引引CREATEUNIQUEINDEXdeptartment_indexONdepartment(dname);创建位图图索引CREATEBITMAPINDEXstudent_sexONstudent(sex);创建反序序索引CREATEINDEXplayer_sageONplayer(sage)REVERSE;创建函数数索引CREATEINDEXidxONemployee(UPPER(ename));定义约束束时创建建索引CREATETABLEnew_employee(empnoNUMBER(5)PRIMARYKEYUSINGINDEXTABLESPACEusersPCTFREE0,enameVARCHAR2(20));（2）修改索索引合并索引引合并索引引是对索索引进行行合并操操作，但但只是简简单地将将B树叶节点点中的存存储碎片片合并在在一起，，并不会会改变索索引的物物理组织织结构语法ALTERINDEX…COALESCE示例ALTERINDEXemployee_enameCOALESCE;重建索引引重建索引引的实质质是在指指定的表表空间中中重新建建立一个个新的索索引，然然后再删删除原来来的索引引，这样样不仅能能够消除除存储碎碎片，还还可以改改变索引引的存储储参数设设置，并并且将索索引移动动到其他他的表空空间中。。语法ALTERINDEX…REBUILD示例ALTERINDEXplayer_sageREBUILD;合并索引引与重建建索引比比较合并索引重建索引不能将索引移到其他表空间中可以将索引移到其他表空间中代价较低，不需要使用额外的存储空间代价较高，需要使用额外的存储空间只能在B树的同一子树中进行合并，不会改变树的高度重建整个B树，可能会降低树的高度可以快速释放叶子节点中未使用的存储空间可以快速更改索引的存储参数。在重建过程中如果指定了ONLINE关键字，不会影响对当前索引的使用索引重命命名语法ALTERINDEX…RENAMETO语句为索索引重命命名。示例ALTERINDEXemployee_enameRENAMETOemployee_new_ename;（3）监视索索引已经建立立的索引引是否能能够有效效地工作作，取决决于在查查询执行行过程中中是否会会使用到到这个索索引。要要查看某某个指定定索引的的使用情情况，可可以使用用ALTERINDEX语句打开开索引的的监视状状态。打开索引引监视状状态ALTERINDEXindex_enameMONITORINGUSAGE;关闭索引引监视状状态ALTERINDEXindex_enameNOMONITORINGUSAGE;查看索引引使用情情况通过V$OBJECT_USAGE动态性能能视图中中查看索索引的使使用情况况。USED列为YES，表示索索引正被被引用，，否则为为NO。（4）删除索索引语法DROPINDEXindex_name;在下面几几种情况况下，可可以考虑虑删除索索引该索引不不再使用用。通过一段段时间监监视，发发现几乎乎没有查查询或只只有极少少数查询询会使用用该索引引。由于索引引中包含含损坏的的数据块块或包含含过多的的存储碎碎片等，，需要删删除该索索引，然然后重建建索引。。由于移动动了表数数据而导导致索引引失效（5）索引的的查询DBA_INDEXES、ALL_INDEXES、USER_INDEXES包含索引引的基本本描述信信息和统统计信息息，包括括索引的的所有者者、索引引的名称称、索引引的类型型、对应应表的名名称、索索引的存存储参数数设置、、由分析析得到的的统计信信息等信信息DBA_IND_COLUMNS、ALL_IND_COLUMNS、USER_IND_COLUMNS包含索引引列的描描述信息息，包括括索引的的名称、、表的名名称和索索引列的的名称等等信息DBA_IND_EXPRESSIONS、ALL_IND_EXPRESSIONS、USER_IND_EXPRESSIONS包含函数数索引的的描述信信息，通通过该视视图可以以查看到到函数索索引的函函数或表表达式V$OBJECT_USAGE包含通过过ALTERINDEX…MONITORINGUSAGE语句对索索引进行行监视后后得到的的索引使使用信息息。9.3.3利用OEM管理索引引创建索引引修改索引引删除索引引查询索引引（1）创建索索引（2）索引的的其他管管理9.4分区表与与分区索索引分区概述述创建分区区表维护分区区表查询分区区表和分分区索引引信息利用OEM管理分区区表和分分区索引引分区概述述分区概念念所谓的分分区是指指将一个个巨型表表或巨型型索引分分成若干干独立的的组成部部分进行行存储和和管理，，每一个个相对小小的、可可以独立立管理的的部分，，称为原原来表或或索引的的分区。。每个分区区都具有有相同的的逻辑属属性，但但物理属属性可以以不同。。如具有有相同列列、数据据类型、、约束等等，但可可以具有有不同的的存储参参数、位位于不同同的表空空间等。。分区后，，表中每每个记录录或索引引条目根根据分区区条件分分散存储储到不同同分区中中。分区原则则表的大小小超过2GB要对一个个表进行行并行DML操作，必必须分区区为了平衡衡硬盘的的I/O操作，将将一个表表分散存存储在不不同的表表空间中中，必须须对它进进行分区区如果需要要将表一一部分设设置为只只读，另另一部分分为可更更新的，，必须对对表进行行分区9.4.1创建分区区表分区方法法范围分区区列表分区区散列分区区复合分区区（1）范围分分区概述范围分区区是按照照分区列列值的范范围来对对表进行行分区的的。参数通过PARTITIONBYRANGE子句说明明根据范范围进行行分区，，其后括括号中列列出分区区列，可可以进行行多列分分区。每每个分区区以PARTITION关键字开开头，其其后是分分区名。。VALUESLESSTHAN子句用于于设置分分区中分分区列值值的范围围。可以以对每个个分区的的存储进进行设置置，也可可以对所所有分区区采用默默认的存存储设置置。示例创建一个个分区表表，将学学生信息息根据其其出生日日期进行行分区，，将1980年1月1日前出生生的学生生信息保保存在ORCLTBS1表空间中中，将1980年1月1日到1990年1月1日出生的的学生信信息保存存在ORCLTBS2表空间中中，将其其他学生生信息保保存在ORCLTBS3表空间中中。CREATETABLEstudent_range(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sageint,birthdayDATE)PARTITIONBYRANGE(birthday)(PARTITIONp1VALUESLESSTHAN(TO_DATE('1980-1-1','YYYY-MM-DD'))TABLESPACEORCLTBS1,PARTITIONp2VALUESLESSTHAN(TO_DATE('1990-1-1','YYYY-MM-DD'))TABLESPACEORCLTBS2,PARTITIONp3VALUESLESSTHAN(MAXVALUE)TABLESPACEORCLTBS3STORAGE(INITIAL10MNEXT20M))STORAGE(INITIAL20MNEXT10MMAXEXTENTS10);（2）列表分分区概述如果分区区列的值值并不能能划分范范围（非非数值类类型或日日期类型型），同同时分区区列的取取值范围围只是一一个包含含少数值值的集合合，则可可以对表表进行列列表分区区（LIST），如按按地区、、性别等等分区。。与范围分分区不同同，列表表分区不不支持多多列分区区，只能能根据一一个单独独的列来来进行分分区。创创建列表表分区时时需要指指定分区区列和分分区描述述。通过PARTITIONBYLIST子句说明根根据列表进进行分区，，其后括号号中列出分分区列。每每个分区以以PARTITION关键字开头头，其后是是分区名。。VALUES子句用于设设置分区所所对应的分分区列取值值。示例创建一个分分区表，将将学生信息息按性别不不同进行分分区，男学学生信息保保存在表空空间ORCLTBS1中，而女学学生信息保保存在ORCLTBS2中。CREATETABLEstudent_list(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sexCHAR(2)CHECK(sexin('M','F')))PARTITIONBYLIST(sex)(PARTITIONstudent_maleVALUES('M')TABLESPACEORCLTBS1,PARTITIONstudent_femaleVALUES('F')TABLESPACEORCLTBS2);（3）散列分区区概述在进行范围围分区或列列表分区时时，由于无无法对各个个分区中可可能具有的的记录数量量进行预测测，可能导导致数据在在各个分区区中分布不不均衡，某某个分区中中数据很多多，而某个个分区中数数据很少。。此时可以以采用散列列分区（HASH）方法，在在指定数量量的分区中中均等地分分配数据。。为了创建散散列分区，，需要指定定分区列、、分区数量量或单独的的分区描述述。通过PARTITIONBYHASH指定分区方方法，其后后的括号指指定分区列列。使用PARTITION子句指定每每个分区名名称和其存存储空间。。或者使用用PARTITIONS子句指定分分区数量，，用STOREIN子句指定分分区存储空空间。示例创建一个分分区表，根根据学号将将学生信息息均匀分布布到ORCLTBS1和ORCLTBS2两个表空间间中。CREATETABLEstudent_hash(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10))PARTITIONBYHASH(sno)(PARTITIONp1TABLESPACEORCLTBS1,PARTITIONp2TABLESPACEORCLTBS2);CREATETABLEstudent_hash2(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10))PARTITIONBYHASH(sno)PARTITIONS2STOREIN(ORCLTBS1,ORCLTBS2);（4）复合分区区复合分区包包括：范围-列表复合分分区范围-散列复合分分区。创建复合分分区时需要要指定分区方法（（PARTITIONBYRANGE）分区列子分区方法法（SUBPARTITIONBYHASH，SUBPARTITIONBYLIST）子分区列每个分区中中子分区数数量或子分分区的描述述。范围-列表复合分分区范围-列表复合分分区先对表表进行范围围分区，然然后再对每每个分区进进行列表分分区，即在在一个范围围分区中创创建多个列列表子分区区。范围-列表复合分分区示例创建一个范范围-列表复合分分区表，将将1980年1月1日前出生的的男、女学学生信息分分别保存在在ORCLTBS1和ORCLTBS2表空空间间中中，，1980年1月1日到到1990年1月1日出出生生的的男男、、女女学学生生信信息息分分别别保保存存在在ORCLTBS3和ORCLTBS4表空空间间中中，，其其他他学学生生信信息息保保存存在在ORCLTBS5表空空间间中中。。CREATETABLEstudent_range_list(snoNUMBER(6)PRIMARYKEY,snameVARCHAR2(10),sexCHAR(2)CHECK(sexIN('M','F')),sageNUMBER(4),birthdayDATE)PARTITIONBYRANGE(birthday)SUBPARTITIONBYLIST(sex)(PARTITIONp1VALUESLESSTHAN(TO_DATE('1980-1-1','YYYY-MM-DD'))(SUBPARTITIONp1_sub1VALUES('M')TABLESPACEORCLTBS1,SUBPARTITIONp1_sub2VALUES('F')TABLESPACEORCLTBS2),PARTITIONp2VALUESLESSTHAN(TO_DATE('1990-1-1','YYYY-MM-DD'))(SUBPARTITIONp2_sub1VALUES('M')TABLESPACEORCLTBS3,SUBPARTITIONp2_sub2VALUES('F')TABLESPACEORCLTBS4),PARTITIONp3VALUESLESSTHAN(MAXVALUE)TABLESPACEORCLTBS5);范围围-散列列复复合合分分区区范围围-散列列复复合合分分区区先先对对表表进进行行范范围围分分区区，，然然后后再再对对每每个个分分区区进进行行散散列列分分区区，，即即在在一一个个范范围围分分区区中中创创建建多多个个散散列列子子分分区区。。示例例创建建一一个个范范围围-散列列复复合合分分区区表表，，将将1980年1月1日前前出出生生的的学学生生信信息息均均匀匀地地保保存存在在ORCLTBS1和ORCLTBS2表空空间间中中，，1980年1月1日到到1990年1月1日出出生生的的学学生生信信息息保保存存在在ORCLTBS3和ORCLTBS4表空空间间中中，，其其他他学学生生信信息息保保存存在在ORCLTBS5表空空间间中中。。