oracle 第10章模式对象

1、Oracle 数据库基础教程2009第10章 模式对象Oracle 数据库基础教程2007本章内容本章内容p模式概念p表管理p索引管理p索引化表管理p分区表与分区索引的管理p簇、视图、序列、同义词、数据库链接管理Oracle 数据库基础教程2007本章要求本章要求p掌握表的管理p掌握索引的管理p掌握索引化表的概念及其管理p掌握分区的概念及分区管理p了解簇、视图、序列、同义词、数据库链接等的管理Oracle 数据库基础教程200710.1模式模式p模式概念p模式与用户的关系p模式选择与切换Oracle 数据库基础教程2007p模式概念n是指一系列逻辑数据结构或对象的集合。是指一系列逻辑数据结构或

2、对象的集合。 p模式与用户的关系n模式与用户相对应，一个模式只能被一个数据库用户模式与用户相对应，一个模式只能被一个数据库用户所拥有，并且模式的名称与这个用户的名称相同。所拥有，并且模式的名称与这个用户的名称相同。n通常情况下，用户所创建数据库对象都保存在与自己通常情况下，用户所创建数据库对象都保存在与自己同名的模式中。同名的模式中。n同一模式中数据库对象的名称必须惟一，而在不同模同一模式中数据库对象的名称必须惟一，而在不同模式中的数据库对象可以同名。式中的数据库对象可以同名。n默认情况下，用户引用的对象是与自己同名模式中的默认情况下，用户引用的对象是与自己同名模式中的对象，如果要引用其他模式

3、中的对象，需要在该对象对象，如果要引用其他模式中的对象，需要在该对象名之前指明对象所属模式。名之前指明对象所属模式。 Oracle 数据库基础教程2007p模式选择与切换n如果用户以如果用户以NORMAL身份登录，则进入同名模式；身份登录，则进入同名模式；n若以若以SYSDBA身份登录，则进入身份登录，则进入SYS模式；模式；n如果以如果以SYSOPER身份登录，则进入身份登录，则进入PUBLIC模式。模式。Oracle 数据库基础教程200710.2表表pOracle常用数据类型 p表的创建p表中约束的管理 p表参数设置 p表的修改 p表的删除 p利用OEM管理表Oracle 数据库基础教程

4、2007pCREATE TABLE table_namen(column_name datatype column_level_constraintn，column_name datatype column_level_constraintn，table_level_constraint) nparameter_list 直接创建表直接创建表Oracle 数据库基础教程2007pCREATE TABLE employee(pempno NUMBER(5) PRIMARY KEY,pename VARCHAR2(15),pdeptno NUMBER(3) NOT NULL CONSTRAINT

5、fk_emp REFERENCES deptp)pTABLESPACE USERSpPCTFREE 10 PCTUSED 40pSTORAGE(INITIAL 50K NEXT 50K MAXEXTENTS 10 PCTINCREASE 25); Oracle 数据库基础教程2007p表名（table_name）n必须是合法标识符，长度为必须是合法标识符，长度为130 字节，并且以字母字节，并且以字母开头，可以包含字母开头，可以包含字母(AZ，az)、数字（、数字（09），），下划线（下划线（_）、美元符号（）、美元符号（$）和井号（）和井号（#）。此外，）。此外，表名称不能是表名称不能是Or

6、acle数据库的保留字数据库的保留字 p数据类型(datatype) n字符类型字符类型n数字类型数字类型n日期类型日期类型n大对象类型大对象类型n其他类型其他类型Oracle 数据库基础教程2007pCHAR（n）n定长字符串，n的取值范围为1-2000字节pVARCHAR2（n）n可变字符串，n取值范围为1-4000字节n自动调整数据长度pNCHAR(n)n用来存储Unicode类型字符串。pNVARCHAR2(n) n它用来存储它用来存储Unicode类型字符串。类型字符串。pLONGn可变长字符列，最大长度为2GBn用于不需设置成索引的字符，不常用字符类型字符类型Oracle 数据库基

7、础教程2007数字数据类型数字数据类型pNUMBER（m，n） 可变长的数值列，m是所有有效数字的位数，n为小数点后的位数Oracle 数据库基础教程2007日期数据类型日期数据类型pDATEn公元前公元前4712年年1月月1日到公元日到公元4712年年1月月1日日n默认格式：默认格式：DD-MON-YYn习惯格式：习惯格式：YYYY-MM-DD HH：MM：SSOracle 数据库基础教程2007大对象数据类型大对象数据类型pBLOB、CLOB、NCLOBn用来保存较大的图形文件或带格式的文本文件、音用来保存较大的图形文件或带格式的文本文件、音频和视频等非文本文件频和视频等非文本文件n最大长

8、度最大长度4GBpBFILEn在数据库外部保存的大型二进制对象文件。在数据库外部保存的大型二进制对象文件。n不能写，只能读、查询不能写，只能读、查询n大小有操作系统决定大小有操作系统决定Oracle 数据库基础教程2007其他数据类型其他数据类型pRAW（n）n可变长二进制数据可变长二进制数据n保存较小的图形文件或带格式的文本文件保存较小的图形文件或带格式的文本文件nN取取1-2000字节字节pLONG RAWn可变长二进制数据可变长二进制数据n保存较大的图形文件或带格式的文本文件保存较大的图形文件或带格式的文本文件nN取取2GB字节字节pROWIDpUROWIDOracle 数据库基础教程2

9、007p约束（constraint）n在在Oracle数据库中对列的约束包括主键约束、惟一性数据库中对列的约束包括主键约束、惟一性约束、检查约束、外键约束和空约束、检查约束、外键约束和空/非空约束等非空约束等5中，定中，定义方法有表级约束和列级约束义方法有表级约束和列级约束2种。关于表的约束详见种。关于表的约束详见10.2.2表约束中介绍。表约束中介绍。p参数（parameter_list）n在定义表时，可以通过参数设置表存储在哪一个表空在定义表时，可以通过参数设置表存储在哪一个表空间中，和存储空间分配等。间中，和存储空间分配等。Oracle 数据库基础教程2007利用子查询创建表利用子查询创

10、建表 p语法语法nCREATE TABLE table_namen(column_name column_level_constraintn，column_name column_level_constraintn，table_level_constraint) nparameter_listnAS subquery; Oracle 数据库基础教程2007p注意注意n通过该方法创建表时，可以修改表中列的名称，通过该方法创建表时，可以修改表中列的名称，但是不能修改列的数据类型和长度；但是不能修改列的数据类型和长度；n源表中的约束条件和列的缺省值都不会复制到源表中的约束条件和列的缺省值都不会复制到

11、新表中；新表中；n子查询中不能包含子查询中不能包含LOB类型和类型和LONG类型列；类型列；n当子查询条件为真时，新表中包含查询到的数当子查询条件为真时，新表中包含查询到的数据；当查询条件为假时，则创建一个空表。据；当查询条件为假时，则创建一个空表。Oracle 数据库基础教程2007p创建一个表，保存工资高于3000的员工的员工号、员工名和部门号。语句为：nCREATE TABLE emp_select(nemp_no,emp_name,dept_non)nASnSELECT empno,ename,deptno FROM employee WHERE sal3000; Oracle 数据库

12、基础教程2007CREATE TABLE EMP_SELECT （EMPNO,ENAME,JOB, DEPTNO, SALARY）PCTFREE 10 PCTUSED 40TABLESPACE usersSTORAGE (INITIAL 50K NEXT 50K MAXEXTENTS 10 PCTINCREASE 25)AS SELECT EMPNO,ENAME ,JOB,EDPTNO,SAL FROM EMPLOYEES WHERE SAL1000 Oracle 数据库基础教程2007创建临时表创建临时表 p临时表中的数据在特定条件下自动释放，但其结构将一直存在。p临时表中的数据只在当前会话

13、或当前事务中是有效的。p根据临时表中数据被释放的时间不同，临时表分为事务级别的临时表和会话级别的临时表两类。nON COMMIT DELETE ROWS /ON COMMIT DELETE ROWS /事务级事务级临时表临时表nON COMMIT PRESERVE ROWS /ON COMMIT PRESERVE ROWS /会话级会话级临时表临时表Oracle 数据库基础教程2007p事务级别的临时表是在事务提交时系统自动删除表中所有记录。nCREATE GLOBAL TEMPORARY TABLE tran_temp(nID NUMBER(2) PRIMARY KEY,nname VARC

14、HAR2(20)n)nON COMMIT DELETE ROWS;Oracle 数据库基础教程2007p会话级别的临时表是在会话终止时系统自动删除表中所有记录 。nCREATE GLOBAL TEMPORARY TABLE sess_temp(nID NUMBER(2) PRIMARY KEY,nname VARCHAR2(20)n)nON COMMIT PRESERVE ROWS;Oracle 数据库基础教程200710.2.2表约束表约束p约束的类别 p定义约束 p添加和删除约束 p设置约束状态 p约束的延迟检查 p查询约束信息 Oracle 数据库基础教程2007约束的类别约束的类别 p

15、约束作用n是在表中定义的用于维护数据库完整性的一些规则。是在表中定义的用于维护数据库完整性的一些规则。通过对表中列定义约束，可以防止在执行通过对表中列定义约束，可以防止在执行DML操作时，操作时，将不符合要求的数据插入到表中。将不符合要求的数据插入到表中。p约束类型nPRIMARY KEYnUNIQUEnCHECKnFOREIGN KEY nNULL/NOT NULL Oracle 数据库基础教程2007PRIMARY KEYp特点n定义主键，起惟一标识作用，其值不能为定义主键，起惟一标识作用，其值不能为NULL，也不能重复；，也不能重复；n一个表中只能定义一个主键约束；一个表中只能定义一个主

16、键约束；n建立主键约束的同时，在该列上建立一个惟一建立主键约束的同时，在该列上建立一个惟一性索引，可以为它指定存储位置和存储参数；性索引，可以为它指定存储位置和存储参数；n主键约束可以是列级约束，也可以是表级约束。主键约束可以是列级约束，也可以是表级约束。Oracle 数据库基础教程2007UNIQUEp特点n定义为惟一性约束的某一列或多个列的组合的定义为惟一性约束的某一列或多个列的组合的取值必须惟一；取值必须惟一；n如果某一列或多个列仅定义惟一性约束，而没如果某一列或多个列仅定义惟一性约束，而没有定义非空约束，则该约束列可以包含多个空有定义非空约束，则该约束列可以包含多个空值；值；nOrac

17、le自动在惟一性约束列上建立一个惟一自动在惟一性约束列上建立一个惟一性索引，可以为它指定存储位置和存储参数；性索引，可以为它指定存储位置和存储参数；n惟一性约束可以是列级约束，也可以是表级约惟一性约束可以是列级约束，也可以是表级约束。束。 Oracle 数据库基础教程2007p在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；p对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。p不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。PRIMARY KEY

18、与与UNIQUE比较比较Oracle 数据库基础教程2007CHECK p特点n检查约束用来限制列值所允许的取值范围，其检查约束用来限制列值所允许的取值范围，其表达式中必须引用相应列，并且表达式的计算表达式中必须引用相应列，并且表达式的计算结果必须是一个布尔值；结果必须是一个布尔值；n约束表达式中不能包含子查询，也不能包含约束表达式中不能包含子查询，也不能包含SYSDATE、USER等等SQL函数，和函数，和ROWID、ROWNUM等伪列；等伪列；n一个列可以定义多个检查约束；一个列可以定义多个检查约束；n检查约束可以是列级约束，也可以是表级约束。检查约束可以是列级约束，也可以是表级约束。 O

19、racle 数据库基础教程2007p概念nFOREIGN KEY约束指定某一个列或一组约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为列作为外部键，其中，包含外部键的表称为从表，包含外部键所引用的主键或唯一键的从表，包含外部键所引用的主键或唯一键的表称主表。表称主表。n系统保证从表在外部键上的取值要么是主表系统保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。中某一个主键值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的以此保证两个表之间的连接，确保了实体的参照完整性。参照完整性。FOREIGN KEYOracle 数据库基础教程2007FORE

20、IGN KEYp特点n定义外键约束的列的取值要么是主表参照列的定义外键约束的列的取值要么是主表参照列的值，要么为空；值，要么为空；n外键列只能参照于主表中的主键约束列或惟一外键列只能参照于主表中的主键约束列或惟一性约束列；性约束列；n可以在一列或多列组合上定义外键约束；可以在一列或多列组合上定义外键约束；n外键约束可以是列级约束，也可以是表级约束。外键约束可以是列级约束，也可以是表级约束。Oracle 数据库基础教程2007NULL/NOT NULL p特点n在同一个表中可以定义多个在同一个表中可以定义多个NOT NULL约束；约束；n只能是列级约束。只能是列级约束。Oracle 数据库基础教

21、程2007定义约束定义约束p列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔,不必指定列名；p表约束与列定义相互独立，不包括在列定义中。通常用于对多个列一起进行约束，与列定义用,分隔。定义表约束时必须指出要约束的那些列的名称。Oracle 数据库基础教程2007p定义列级约束的语法为：n CONSTRAINT constraint_name constraint_type conditioin; p定义表级约束的语法为：nCONSTRAINT constraint_namenconstraint_type(column1_name,ncolumn2_name

22、,|condition);p注意nOracle约束通过名称进行标识。在定义时可以通过约束通过名称进行标识。在定义时可以通过CONSTRAINT关键字为约束命名。如果用户没有为关键字为约束命名。如果用户没有为约束命名，约束命名，Oracle将自动为约束命名。将自动为约束命名。 Oracle 数据库基础教程2007pCREATE TABLE student(p sno NUMBER(6) CONSTRAINT S_PK PRIMARY KEY,p sname VARCHAR2(10) NOT NULL,p sex CHAR(2) CONSTRAINT S_CK1 check(sex in(M,F)

23、,p sage NUMBER(6,2),p CONSTRAINT S_CK2 CHECK(sage between 18 and 60)p); pCREATE TABLE course(p cno NUMBER(6) PRIMARY KEY,p cname CHAR(20) UNIQUEp USING INDEX TABLESPACE indx STORAGE(INITIAL 64K NEXT 64K)p );Oracle 数据库基础教程2007p定义列级FOREIGN KEY约束nCONSTRAINT constraint_name nFOREIGN KEY nREFERENCES ref_

24、table_name (column_name,)p定义表级FOREIGN KEY约束nCONSTRAINT constraint_name nFOREIGN KEY (column_name,) nREFERENCES ref_table_name (column_name,) nON DELETE CASCADE|SET NULL;Oracle 数据库基础教程2007pON DELETE CASCADEn删除子表中所有相关记录删除子表中所有相关记录pON DELETE SET NULLn将子表中相关记录的外键约束列值设置为将子表中相关记录的外键约束列值设置为NULLpON DELETE R

25、ESTRICTEDn受限删除，即如果子表中有相关子记录存在，则不能受限删除，即如果子表中有相关子记录存在，则不能删除主表中的父记录，默认引用方式。删除主表中的父记录，默认引用方式。 Oracle 数据库基础教程2007pDEFAULTn如果用户在插入新行时没有显示为列提供数据，系统将默认如果用户在插入新行时没有显示为列提供数据，系统将默认值赋给该列。值赋给该列。n语法语法CONSTRAINT DEFAULT 表达式表达式Oracle 数据库基础教程2007添加约束添加约束 p添加约束语法为：nALTER TABLE table_name nADD CONSTRAINT constraint_n

26、amenconstraint_type(column1_name,column2_name,)condition; Oracle 数据库基础教程2007p创建一个表nCREATE TABLE player(n ID NUMBER(6),n sno NUMBER(6),n sname VARCHAR2(10),n sage NUMBER(6,2),n resume VARCHAR2(1000)n ); p添加主键约束nALTER TABLE player ADD CONSTRAINT P_PK PRIMARY KEY(ID);p添加惟一性约束nALTER TABLE player ADD CON

27、STRAINT P_UK UNIQUE(sname);Oracle 数据库基础教程2007p添加检查约束nALTER TABLE player ADD CONSTRAINT P_CK CHECK(sage BETWEEN 20 AND 30);p添加外键约束nALTER TABLE playernADD CONSTRAINT P_FK FOREIGN KEY(sno)REFERENCES student(sno) ON DELETE CASCADE;p添加空/非空约束n注意注意:为表列添加空为表列添加空/非空约束时必须使用非空约束时必须使用MODIFY子句代替子句代替ADD子句。子句。nALT

28、ER TABLE player MODIFY resume NOT NULL;nALTER TABLE player MODIFY resume NULL;Oracle 数据库基础教程2007修改约束修改约束nALTER TABLE nADD nMODIFY nENABLE nDISABLE nDROP Oracle 数据库基础教程2007删除约束删除约束 p删除指定内容的约束nALTER TABLE player DROP UNIQUE(sname);p删除指定名称的约束nALTER TABLE player DROP CONSTRAINT P_CK;p如果要在删除主键约束、惟一性约束同时保

29、留惟一性索引，则必须在ALTER TABLEDORP 语句中指定KEEP INDEX 子句。nALTER TABLE player DROP CONSTRAINT P_UK KEEP INDEX;p如果要删除约束同时，删除引用该约束的其他约束，则需要在ALTER TABLEDORP语句中指定CASCADE关键字。nALTER TABLE player DROP CONSTRAINT P_PK CASCADE; Oracle 数据库基础教程2007设置约束状态设置约束状态 p激活（ENABLE）状态n当约束处于激活状态时，约束将对表的插入或更新操作当约束处于激活状态时，约束将对表的插入或更新操作

30、进行检查，与约束规则冲突的操作被回退进行检查，与约束规则冲突的操作被回退。p禁用（DISABLE）状态n当约束处于禁用状态时，约束不起作用，与约束规则冲当约束处于禁用状态时，约束不起作用，与约束规则冲突的插入或更新操作也能够成功执行。突的插入或更新操作也能够成功执行。p利用SQL*Loader从外部数据源提取大量数据到数据库中；p进行数据库中数据的大量导入、导出操作；p针对表执行一项包含大量数据操作的批处理工作时。Oracle 数据库基础教程2007p禁用约束 n在定义约束时，可以将约束设置为禁用状态，默认为在定义约束时，可以将约束设置为禁用状态，默认为激活状态。也可以在约束创建后，修改约束状

31、态为禁激活状态。也可以在约束创建后，修改约束状态为禁用。用。n创建表时禁用约束创建表时禁用约束pCREATE TABLE S (SNO CHAR(10) PRIMARY KEY DISALBE,);n利用利用ALTER TABLEDISABLE禁用约束禁用约束pALTER TABLE STUDENT DISABLE CONSTRAINT S_CK1;pALTER TABLE STUDENT DISABLE UNIQUE (sname);Oracle 数据库基础教程2007n禁用主键约束、惟一性约束时，会删除其对应的惟一性索引，而禁用主键约束、惟一性约束时，会删除其对应的惟一性索引，而在重新激活

32、时，在重新激活时，Oracle为它们重建惟一性索引。若在禁用约束时，为它们重建惟一性索引。若在禁用约束时，保留对应的惟一性索引，可使用保留对应的惟一性索引，可使用ALTER TABLEDISABLEKEEP INDEX语句。语句。pALTER TABLE STUDENT DISABLE UNIQUE (sname) KEEP INDEX;pALTER TABLE STUDENT DISABLE PRIMARY KEY KEEP INDEX;n若当前约束若当前约束(主键约束、惟一性约束主键约束、惟一性约束)列被引用，则需要使用列被引用，则需要使用ALTER TABLEDISABLECASCADE

33、语句同时禁用引用该语句同时禁用引用该约束的约束。约束的约束。pALTER TABLE STUDENT DISABLE PRIMARY KEY KEEP INDEX CASCADE; Oracle 数据库基础教程2007p激活约束n创建或添加约束时，默认为激活状态。创建或添加约束时，默认为激活状态。n利用利用ALTER TABLEENABLE语句激活约束语句激活约束pALTER TABLE STUDENT ENABLE UNIQUE (sname);n禁用主键约束、惟一性约束时，会删除其对应的惟一性索引，而禁用主键约束、惟一性约束时，会删除其对应的惟一性索引，而在重新激活时，在重新激活时，Ora

34、cle为它们重建惟一性索引，可以为索引设置为它们重建惟一性索引，可以为索引设置存储位置和存储参数存储位置和存储参数(索引与表尽量分开存储索引与表尽量分开存储)。pALTER TABLE STUDENT ENABLE PRIMARY KEY pUSING INDEX TABLESPACE indx STORAGE(INITIAL 32K NEXT 16K);n通过通过ALTER TABLEMODIFYDISABLE|ENABLE语句语句改变约束状态改变约束状态pALTER TABLE STUDENT MODIFY CONSTRAINT S_CK2 DISABLE;Oracle 数据库基础教程20

35、07 约束的延迟检查约束的延迟检查 p默认情况下，在表中的约束都是不可延迟约束，Oracle在一条DML语句执行完毕之后立即进行约束检查（除非禁用）。 p创建约束时可以显式使用DEFERRABLE关键字，创建可延迟的约束。pINITIALLY IMMEDIATE 或INITIALLY DEFERRED说明可延迟约束在初始状态下是立即检查还是延迟检查p如果在定义约束时设定为不可延迟，则约束创建后不能更改其可延迟性。只有创建时设定为可延迟的约束，创建后才能更改其可延迟性。 Oracle 数据库基础教程2007p创建两个表，其约束都是可延迟的。nCREATE TABLE new_dept(ndept

36、no NUMBER PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE,ndname CHAR(10) UNIQUEn); nCREATE TABLE new_emp(nempno NUMBER PRIMARY KEY,nename CHAR(10),ndeptno NUMBER CONSTRAINT NE_FK REFERENCES new_dept(deptno)nON DELETE CASCADE DEFERRABLEn);Oracle 数据库基础教程2007p由于外键约束的作用，执行下面语句时会产时错误。nINSERT INTO new_emp VAL

37、UES(1,ZHANG,10);n*nERROR 位于第位于第 1 行行:nORA-02291: 违反完整约束条件违反完整约束条件 (SCOTT.E_FK) - 未找到父未找到父项关键字项关键字 Oracle 数据库基础教程2007p将new_emp表的外键约束检查延迟。nALTER TABLE new_emp MODIFY CONSTRAINT NE_FK INITIALLY DEFERRED;p此时，由于将new_emp表外键约束延迟到事务结束后进行检查，因此可以先向new_emp中插入数据，而后向new_dept中插入数据。nINSERT INTO new_emp VALUES(1,ZH

38、ANG,10);nINSERT INTO new_dept VALUES(10,COMPUTER);nCOMMIT; Oracle 数据库基础教程2007p操作完后，应将new_emp外键约束检查恢复原来状态。nALTER TABLE new_emp MODIFY CONSTRAINT NE_FK INITIALLY IMMEDIATE;p注意：在修改约束的检查延迟性时，如果无法确定约束的名称或需要设置多个约束的延迟性，可以一次性将所有可延迟的约束延迟或恢复。nSET CONSTRAINT ALL DEFERRED;nSET CONSTRAINT ALL IMMEDIATE; Oracle 数

39、据库基础教程2007查询约束信息查询约束信息pALL_CONSTRAINTSpUSER_CONSTRAINTSpDBA_CONSTRAINTSpALL_CONS_COLUMNSpUSER_CONS_COLUMNSpDAB_CONS_COLUMNSOracle 数据库基础教程2007p从表USER_CONSTRAINTS中查看所有约束的名字、定义 SELECT constraint_name, constraint_type search_condition FROM user_constraints WHEREtable_name = EMP;p从视图 USER_CONS_COLUMNS中查看

40、约束关联的列 SELECTconstraint_name, column_name FROM user_cons_columns WHEREtable_name = EMP;Oracle 数据库基础教程200710.2.3表参数设置表参数设置pTABLESPACEnTABLESPACE子句用于指定表存储的表空间。子句用于指定表存储的表空间。 pSTORAGE nSTORAGE子句用于设置表的存储参数。若不指定，则继承表空子句用于设置表的存储参数。若不指定，则继承表空间的存储参数设置。间的存储参数设置。 pNITIAL pNEXTpPCTINCREASEpMINEXTENTSpMAXEXTENT

41、SpBUFFER_POOL (KEEP、RECYCLE、DEFAULT) Oracle 数据库基础教程2007nSTORAGE参数设置需注意：参数设置需注意：p如果表空间管理方式为EXTENT MANAGEMENT LOCAL AUTOALLOCATE，则在STORAGE中只能指定INITIAL，NEXT和MINEXTENTS这3个参数；p如果表空间管理方式为EXTENT MANAGEMENT LOCAL UNIFORM，则不能指定任何STORAGE子句；p如果表空间管理方式为EXTENT MANAGEMENT DICTIONARY，则在STORAG中可以设置任何参数。 Oracle 数据库基

42、础教程2007p数据块管理参数 nPCTFREE：用于指定数据块中必须保留的最小空闲：用于指定数据块中必须保留的最小空闲空间。空间。nPCTUSED：用于指定当数据块空闲空间达到：用于指定当数据块空闲空间达到PCTFREE参数的限制后，数据块能够被再次使用前，参数的限制后，数据块能够被再次使用前，已占用的存储空间必须低于的比例。已占用的存储空间必须低于的比例。nINITRANS：用于指定能够并发访问同一个数据块的：用于指定能够并发访问同一个数据块的事务的数量。事务的数量。nMAXTRANS：用于指定能够并发访问同一个数据块：用于指定能够并发访问同一个数据块的事务的最大数量。的事务的最大数量。O

43、racle 数据库基础教程2007pLOGGING子句n默认为默认为NOLOGGING，即表的创建操作不会记录到，即表的创建操作不会记录到重做日志文件中，尤其适合通过查询创建表的情况。重做日志文件中，尤其适合通过查询创建表的情况。使用使用LOGGING子句，表的创建操作（包括通过查询子句，表的创建操作（包括通过查询创建表时的插入记录操作）都将记录到重做日志文件创建表时的插入记录操作）都将记录到重做日志文件中。中。pLOGGING 、NOLOGGINGpPARALLEL、NOPARALLELpCACHE、NOCACHEpMONITORING、NOMONITORINGOracle 数据库基础教程2

44、00710.2.4修改表修改表p基本语法p列的添加、删除、修改p约束添加、删除、修改p表参数修改p表结构重组p表重命名等p为表和列添加注释 Oracle 数据库基础教程2007ALTER TABLE ADD 完整性约束定义MODIFY RENAME COLUMN oldname TO newnameSET UNUSED COLUMN column /single columnSET UNUSED COLUMNS（column1，column2）DROP COLUMN /single columnDROP /multi columnDROP UNUSED COLUMNSOracle 数据库基础教

45、程2007p添加列 n语法语法pALTER TABLE table_name pADD(new_column_name datatypeNOT NULLDEFAULT value); n示例示例pALTER TABLE employee pADD(phone VARCHAR2(10),hiredate DATE DEFAULT SYSDATE NOT NULL); Oracle 数据库基础教程2007p修改列类型 n语法语法pALTER TABLE table_name MODIFY column_name new_datatype; n修改表中列类型时，必须满足下列条件：修改表中列类型时，必

46、须满足下列条件：p可以增大字符类型列的长度和数值类型列的精度；p如果字符类型列、数值类型列中数据满足新的长度、精度，则可以缩小类型的长度、精度；p如果不改变字符串的长度，可以将VARCHAR2类型和CAHR类型转换；p如果更改数据类型为另一种非同系列类型，则列中数据必须为NULL。 Oracle 数据库基础教程2007n示例示例pALTER TABLE employee MODIFY ename CHAR(20);pALTER TABLE employee MODIFY phone NUMBER; p修改列名 n语法语法pALTER TABLE table_name RENAME COLUMN

47、 oldname TO newname;n示例示例 pALTER TABLE employee RENAME COLUMN ename TO employee_name； Oracle 数据库基础教程2007p删除列 n直接删除列语法直接删除列语法pALTER TABLE table_name pDROP COLUMN column_name|(column1_name,column2_name,)pCASCADE CONSTRAINTS; n直接删除列示例直接删除列示例pALTER TABLE sc DROP COLUMN sno CASCADE CONSTRAINTS;pALTER TAB

48、LE employee DROP (phone,hiredate);Oracle 数据库基础教程2007n将列标记为将列标记为UNUSED，然后进行删除。，然后进行删除。pALTER TABLE table_name pSET UNUSED COLUMN column_name|(column1_name,column2_name,)pCASCADE CONSTRAINTS; n示例示例pALTER TABLE player SET UNUSED COLUMN sage;pALTER TABLE player SET UNUSED (sname,resume);pALTER TABLE pla

49、yer DROP UNUSED COLUMNS; Oracle 数据库基础教程2007p表参数修改n可以对表的参数进行修改，包括存储参数、存储位置、可以对表的参数进行修改，包括存储参数、存储位置、数据块设置等。数据块设置等。pALTER TABLE employee pPCTFREE 30 PCTUSED 60 STORAGE(NEXT 512K PCTINCREASE 10);n注意：表创建后不能对注意：表创建后不能对INITIAL、MINEXTENTS两个参数进行修改。两个参数进行修改。Oracle 数据库基础教程2007p表结构重组n将一个非分区的表移动到一个新的数据段中，或者移将一个非

50、分区的表移动到一个新的数据段中，或者移动到其他的表空间中，通过这种操作可以重建表的存动到其他的表空间中，通过这种操作可以重建表的存储结构，称为表结构重组。储结构，称为表结构重组。n语法语法pALTER TABLE tbname MOVE TABLESPACE tbs_name n注意注意 p直到表被完全移动到新的数据段中之后，Oracle才会删除原来的数据段；p表结构重组后，表中每个记录的ROWID会发生变化，因此该表的所有索引失效，需要重新建立索引；p如果表中包含LOB列，则默认情况下不移动LOB列数据和LOB索引段。Oracle 数据库基础教程2007p表重命名n语法语法pALTER TA

51、BLE table_old_name RENAME TO table_new_name;pRENAME table_old_name TO table_new_name; n说明说明pOracle自动更新相应的约束、索引和与此表相关的权限；p同时以此表为参考的视图、同义词、存储过程和函数为非法。Oracle 数据库基础教程2007p为表添加注释n语法语法pCOMMENT ON TALBE table_name IS；p为列添加注释n语法语法pCOMMENT ON COLUMN table_name.column_name IS p注释可以通过以下数据字典来查看.nALL_COL_COMMENT

52、SnUSER_COL_COMMENTSnALL_TAB_COMMENTSnUSER_TAB_COMMENTSOracle 数据库基础教程2007Oracle 数据库基础教程200710.2.5删除表删除表p使用DELETE语句删减表 p使用TRUNCATE语句删减表 n TRUNCATE TABLE EMP REUSE STORAGETRUNCATE TABLE EMP REUSE STORAGE p使用DROP语句删除表 nDROP TABLE EMP CASCADE CONSTRAINTSDROP TABLE EMP CASCADE CONSTRAINTS pDELETE、TRUNCATE

53、、DROP 区别Oracle 数据库基础教程2007p删除一个表同时，Oracle将执行下列操作：n删除该表中所有记录；删除该表中所有记录；n从数据字典中删除该表定义；从数据字典中删除该表定义；n删除与该表相关的所有索引和触发器；删除与该表相关的所有索引和触发器；n回收为该表分配的存储空间；回收为该表分配的存储空间；n依赖于该表的数据库对象处于依赖于该表的数据库对象处于INVALID状态。状态。Oracle 数据库基础教程200710.2.6利用利用OEM管理表管理表p创建表p删除表p修改表p查询表Oracle 数据库基础教程200710.3索引索引p索引概述p索引管理p利用OEM管理索引Or

54、acle 数据库基础教程200710.3.110.3.1索引概述索引概述p索引概念及作用n索引是为了加速对表中元组的检索而创建的一种分索引是为了加速对表中元组的检索而创建的一种分散存储结构；散存储结构；n是对表而建立的，由除存放表的数据页面以外的索是对表而建立的，由除存放表的数据页面以外的索引页面组成，独立于被索引的表；引页面组成，独立于被索引的表；n通过使用索引加速行的检索，但减慢更新的速度；通过使用索引加速行的检索，但减慢更新的速度；n快速定位数据，减少磁盘快速定位数据，减少磁盘 I/OI/O；nOracleOracle自动使用、维护索引自动使用、维护索引Oracle 数据库基础教程200

55、7p索引分类n惟一性索引与非惟一性索引惟一性索引与非惟一性索引 n平衡树索引与位图索引平衡树索引与位图索引 n单列索引与复合索引单列索引与复合索引n函数索引函数索引 n聚簇索引聚簇索引n全局索引与本地索引全局索引与本地索引Oracle 数据库基础教程2007p索引使用原则n导入数据后再创建索引导入数据后再创建索引 n在适当的表和字段上创建索引在适当的表和字段上创建索引 p经常查询的记录数目少于表中所有记录总数的5%；p经常进行连接查询表，在连接列上建立索引能够显著提高查询的速度；p对于取值范围很大的列应当创建B树索引；p对于取值范围很小的列应当创建位图索引p不能在LONG、LONG RAW、L

56、OB数据类型的列上创建索引；pOracle会自动在PRIMARY KEY和UNIQUE约束的列上创建惟一性索引。n合理设置复合索引中的字段顺序合理设置复合索引中的字段顺序 Oracle 数据库基础教程2007n限制表中索引的数目限制表中索引的数目 p表中索引数目越多，查询速度越快，但表的更新速度越慢。因为索引越多，维护索引所需开销越大，当更新表时，需要同时更新与表相关的所有索引。 n为索引设置合适的为索引设置合适的PCTFREEPCTFREE参数参数 n选择存储索引的表空间选择存储索引的表空间p默认情况下，索引与表存储在同一表空间中。索引与表存储在同一表空间中，有利于数据库维护操作，具有较高的

57、可用性；反之，若索引与表存储在不同的表空间中，则可提高系统的存取性能，减少硬盘I/O冲突，但是表与索引可用状态可能出现不一致，如一个处于联机状态，另一个处于脱机状态。 Oracle 数据库基础教程200710.3.2管理索引管理索引p创建索引p修改索引p删除索引p索引的查询Oracle 数据库基础教程2007p语法nCREATE UNIQUE|BITMAP INDEX index_namenON table_name(column_nameASC|DESC,|nexpression) nREVERSEnparameter_list；p说明nUNIQUE表示建立惟一性索引；表示建立惟一性索引；n

58、BITMAP表示建立位图索引；表示建立位图索引；nASC/DESC用于指定索引值的排列顺序，用于指定索引值的排列顺序，ASC表示按升序排序，表示按升序排序，DESC表示按降序排序，缺省值为表示按降序排序，缺省值为ASC；nREVERSE表示建立反键索引；表示建立反键索引；nparameter_list用于指定索引的存放位置、存储空间分配和数用于指定索引的存放位置、存储空间分配和数据块参数设置。据块参数设置。创建索引创建索引Oracle 数据库基础教程2007p创建非惟一性索引 nCREATE INDEX employee_ename ON employee(ename)TABLESPACE u

59、sers STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75); p创建惟一性索引 nCREATE UNIQUE INDEX deptartment_index ON department(dname); p创建位图索引 nCREATE BITMAP INDEX student_sex ON student(sex);创建反序索引创建反序索引 p创建反序索引 nCREATE INDEX player_sage ON player(sage) REVERSE; Oracle 数据库基础教程2007p创建函数索引 nCREATE INDEX idx ON em

60、ployee(UPPER(ename);p定义约束时创建索引nCREATE TABLE new_employee(nempno NUMBER(5) PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0,nename VARCHAR2(20)n); Oracle 数据库基础教程2007修改索引修改索引p修改索引的存储参数 nALTER INDEX employee_ename STORAGE (PCTINCREASE 50); p合并索引 nALTER INDEXCOALESCE nALTER INDEX employee_ename COALES