Oracle企业资产管理

1、第七章 管理Oracle对象管理表管理表q概览概览 表是最基本的数据库对象，它用于存储用户数据，关系数据库的所有操作最终都是围绕用户表进行的。在Oracle数据库中，按照存储方式的不同，表可以分为普通表、分区表、索引组织表以及簇表等四种表。 1.表结构 表是Oracle数据库数据存储的基本单元，在表中是通过行和列来组织数据的。一张表一般都具有多个列，或者称为字段，每个字段都具有特定的属性，包括字段名、字段数据类型、字段长度、约束、默认值等，这些属性在表创建时即被确定。而表的每一行存放一条信息 。 2.基本的数据类型 当建立表时，不仅要指定表名、列名，而且要根据情况为列选择合适的数据类型以及长度

2、。下面介绍一些常用的Oracle数据类型： CHAR(N) 该数据类型用于定义固定长度的字符串，其最大长度为2000字节。假定定义COLA列为CHAR(100)，并且该列的数据为“ACCESS”，这时虽“ACCESS”只有六个字符，但COLA列仍将占用100字节的空间。 VARCHAR2(N) 该数据类型用于定义可变长度的字符串，其最大长度为4000字节。假定定义COLA列为VARCHAR2(100)，并且该列的数据为“ACCESS”，那么COLA列将占用6字节的空间。所以使用VARCHAR2类型可以节省空间，但CHAR类型存取速度更快。 NUMBER(P，S) 该数据类型用于定义数字类型的数

3、据，其中P是精度，表示数字的总位数，而S是刻度范围，表示小数点后的位数。精度和刻度范围都是可选的，但若指定了刻度范围，那么必须指定精度。假定定义SAL列为NUMBER(4，3)，若该列的数据为123.4567，则存储错误，因为超出了精度范围；若该列的数据为1.234567，则实际存储的数据为1.235，因为当被指派值超出了刻度范围时，存储值按照刻度范围指定的数字位的位数进行舍入。另外，定义整数还可以直接使用INT数据类型。 DATE 该数据类型用于定义日期时间数据，其长度为7个字节。 RAW(N) 该数据类型用于定义二进制数据，其最大长度为2000字节。通常用来存储小型二进制数据。 存储大对象

4、的数据类型 Oracle为存储大对象（LOB）提供了六种数据类型 Long，long rawLob(CLOB、BLOBBFILE、NCLOB) 一个表只能有一个LONG或LONG RAW列 一个表上可以有多个LOB列 最多2gb最多4gbSelect返回数据Select返回定位器与其他列数据存放在一起 小于4000：与其他列存放在一起大于4000：存放到LOB段 无对象类型支持支持对象类型顺序访问随机访问 其中：CLOB和LONG用于存储大型的、固定宽度字符数据；BLOB和LONG RAW用于存储非结构化数据，如二进制图象；NCLOB用于存储大型的、固定宽度字符集数据；BFILE用于存储操作系

5、统文件中的非结构化数据。 3.数据的存储方式 当创建表时，Oracle会自动从指定的表空间中为新建的表创建一个数据段，而该表的所有数据都会存放到相应的表段中。 表的行数据存储在数据块中，如果一个块的大小足够容纳一条记录，Oracle就将一条记录完整的存储在一个数据块中。一般情况，记录按列定义顺序来存放，但若使用LONG或LONG RAW类型，那么它们的数据总是放在记录的尾部。 如果表中某字段允许为NULL，若NULL值字段位于非NULL值字段的中间，则需使用1字节的空间来存储NULL值字段的长度 ；若NULL值字段位于一条记录的末尾，将不需要任何存储空间来存储NULL值。因此，在定义表时候，应

6、当将可能包含NULL值的字段放在字段列表的末尾，这样可以节省存储空间。 4. ROWID ROWID用于惟一标识表行。当执行INSERT操作时，服务器进程会将数据插人到表段的相应数据块中，并且Oracle会生成惟一的ROWID对应于该行数据。 ROWID间接地给出了表行的物理存放位置，它是定位表行最快速的方式。ROWID占用10个字节的存储空间，而显示结果为18个字符，具体格式如下： OOOOOOFFFBBBBBBRRR数据对象号 相对文件号 数据块号 行号 数据对象号：表的惟一对象标识号。当建立表时，Oracle会为该表分配惟一的数据对象号。 相对文件号：表空间内数据文件的惟一标识号。 块号

7、：行所在数据块的位置。 行号：行在块内的位置。 因为创建了一个表就创建了一个段，而一个段只能驻留在一个表空间中，所以使用数据对象编号，Oracle服务器可以确定表所在的表空间；又因为表空间的数据都存放在数据文件中，使用相对文件编号Oracle可以确定存放表数据的数据文件；数据文件用数据块来存储数据，使用数据块编号可知表的某一行数据存放在哪个数据块中；而一个数据块可能存放多行数据，利用行号就可以定位表中的任一行了。 ROWID是表的伪列，用户在查看表的结构时不会看到ROWID字段，但和其它字段一样，可以在执行SELECT操作时直接引用。但是因为用户无法直接读懂ROWID内容，所以在引用ROWID

8、时通常使用DBMS_ROWID将其转变为可读取的内容。 select deptno,dbms_rowid.rowid_relative_fno(rowid) | . | dbms_rowid. rowid_block_number(rowid) | . | dbms_rowid. rowid_row_number(rowid) 行位置from dept ;7.1.2 建表 建表是使用create table命令完成的，执行该命令要求用户必须具有create table系统权限；如果要在其他用户模式中建表，则要求用户必须具有create any table系统权限。当建立表时，Oracle会为该

9、表分配相应的表段，因为表段所需空间是从表空间上分配的，所以要求表的所有者必须要在表空间上具有相应的空间配额或具有unlimited tablespace系统权限。 1.建立普通表 普通表是存储用户数据最常用的方式。当建立普通表时，Oracle会自动为该表建立相应的段，并且段的名称与表名完全相同，而且段的数据只能存放在一个表空间中。创建普通表的语法如下： CREATE TABLE schema.table(column datatype,column datatype) TABLESPACE tablespace PCTFREE integer PCTUSED integer INITRANS

10、integer MAXTRANS integer STORAGE storage-clause LOGGING| NOLOGGING CACHE | NOCACHE 现对命令中各参数说明如下 ： TABLESPACE:标识要在其中创建表的表空间。如果创建表时没有显式的指定表所处的表空间，则表被创建在当前用户的默认表空间中。 PCTFREE和PCTUSED: PCTFREE参数用于指定块中必须保留的最小空闲空间比例，PCTUSED参数用于指定当数据块达到PCTFREE参数的限制之后（这时数据块被标记为不可用），数据块能够被再次使用前，已占用的存储空间必须低于的比例。 INITRANS和MAXTR

11、ANS：INITRANS与MAXTRANS参数用于指定针对同一个块所允许的并发事务数目。每当一个DML事务访问表中的一个数据块时，该事务会在数据块的块头部（Block Header）中保存一个条目，用于标记该事务正在使用这个数据块。当该事务结束时，它所对应的条目被删除。 在创建表时，Oracle将在表中每个数据块的头部空间中分配可以存储INITRANS个事务条目的空间。这部分存储空间是永久性的，只能用来存储事务条目。当一个DML事务访问这个数据块时，Oracle首先将该事务的条目存储在块头部空间中。当块头部空间已经存储了INITRANS个事务条目后，再没有多余的空闲空间来存储其他的事务条目了。

12、这时若还有别的事务要访问这个数据块，Oracle将在数据块的空闲空间中为事务条目分配存储空间（如果在块中还有空闲空间的话）。这部分空闲空间是动态分配的，回收以后可以用于存储其他数据。能够在空闲空间中存储的事务条目数量等于MAXTRANS参数值减去INITRANS参数的值。 STORAGE:用于指定段的存储参数，若不指定存储参数，那么Oracle会使用表空间的默认存储参数。在STORAGE子句中可以设置下面6个个存储参数： INITIAL：为表的数据段分配的第一个区的大小。 NEXT：为表的数据段分配的第二个区的大小。 PCTINCREASE：指定从第二个区开始，为表的数据段分配的区的大小增加比

13、例。即每个区的大小等于前一个区的大小乘以（1+PCTINCREASE/100）。如果表处于本地管理方式的表空间中，则该参数被忽略。 MINEXTENTS：允许为表的数据段分配的最小区数目。 MAXEXTENTS：允许为表的数据段分配的最大区数目。如果表处于本地管理方式的表空间中，则该参数被忽略。 BUFFER_POOL：指定表的数据块的缓存池。 LOGGING和NOLOGGING：使用了LOGGING，则表的创建操作（包括通过查询创建表时的插入记录操作）都将记录到重做日志中，若用NOLOGGING，则表的创建操作不会被记录到重做日志中。默认情况将使用LOGGING子句。NOLOGGING子句适

14、合于通过查询创建表的情况。 CACHE和NOCACHE：在创建表时默认使用NOCACHE子句。但对于比较小又经常查询的表，可以使用CACHE子句。 下面是一个在data表空间上建立employee表的例子。 create table employee( id number(7), last_name varchar2(25), dept_id number(7) ) pctfree 20 pctused 50storage (initial 200k next 200k pctincrease 0 maxextents 50) tablespace data; 2.复制表复制表 使用CREAT

15、E TABLE命令不仅可以建立表结构，而且还可以将已存在表的结构和数据复制到另一张新表中。另外为了尽快复制表的数据，复制表时可以指定PARALLEL选项和NOLOGGlNG选项，示例如下：create table new_emp parallel 2 nologging as select * from scott.emp; 其中，parallel degree用于指定执行并行操作，parallel 2表示由两个并行服务器进程执行数据加载操作。另外使用Nologging，在重做日志中没有记录下创建表的操作 3.建立临时表 通过create table语句创建的表是永久性的表，即其中的记录可以一

16、直保存下来。与之对应，在Oracle中还可以创建临时表（Temporary Table）。 与普通表不同，临时表中的数据在使用完毕后自动删除。“使用完毕”有两种情况：事务结束和会话结束。 在创建临时表时若使用了on commit delete rows子句，则说明临时表是事务级别的。这时Oracle将在每次提交事务时对临时表进行删减操作，即删除表中的所有数据。若使用了on commit preserve rows子句，则说明临时表是会话级别的。这时Oracle将直到会话终止时才对临时表进行删减操作。 下面语句就建立了名称为employee_temp的临时表，该临时表将在每次事务提交时进行删减操

17、作： create global temporary table employee_temp on commit delete rows as select * from scott.emp ;4.建立索引组织表 一般情况下，表与索引数据分别存放在表段和索引段中。但索引组织表（IOT）比较特殊，它将表的数据和索引数据存储在一起，即以B树索引的方式来组织表中的数据。 非键列键列行头普通表及其索引索引组织表 要创建索引组织表，必须在CREATE TABLE语句中显式地指定organization index关键字。另外，在索引组织表中必须建立一个primary key主码约束。下面语句就创建了一个

18、索引组织表employees。 create table employees( empno number(5) primary key, ename varchar2(15) not null, sal number(7,2), job varchar2(10) ) organization index tablespace users; 在索引组织表中，如果要获得对常用字段更快的访问速度，可以应用“溢出”存储功能，将表中的不常访问的非主码不再存储在B树的叶节点中，而是存储在一个具有堆组织方式的溢出存储区中。对于大型的索引组织表，使用溢出存储能够大大减少索引组织表所占用的存储空间，同时又可提高

19、对常用字段的查询效率。 如果要启用溢出存储功能，必须在创建索引组织表时指定OVERFLOW子句，此外还需使用INCLUDING子句或PCTTHRESHOLD子句来设置溢出存储的方式。如下所示： create table sales_info ( id number(6) primary key， customer_name varchar(30), sales_amount number(10,2), sales_date date, remark varchar2(200) organization index pctthreshold 20 including remark overflo

20、w tablespace users; 其中，PCTTHRESHOLD指定在数据块中为主键列和部分非主键列所预留空间的百分比。如上例所示，假定数据块剩余空间已经低于20，那么Oracle会将INCLUDING子句后所有列的数据存放到溢出段。 而INCLUDING remark则指如果数据块剩余空间低于PCTTHRESHOLD，那么Oracle会将该子句列remark后的所有列数据存放到溢出段, OVERFLOW TABLESPACE指定了溢出段所在的表空间。 5.建立分区表 分区是指将一张大表的数据进行物理划分，并最终将其数据放到几个相对较小的表分区段中。当执行SQL语句访问分区表时，系统可以

21、直接访问某个表分区段，而不需要访问整张表的所有数据，从而降低磁盘I/O，提高系统性能。Oracle提供了范围分区、散列分区、列表分区以及组合分区四种分区方法。 范围分区 范围分区是按照分区字段中值的范围来对表进行分区，是最常用的分区类型。范围分区通常用于分区字段是日期类型的表。 假定一张销售表年数据总量达到10GB，每个季度平均2.5GB，如果使用普通表存储数据，那么10G数据会存放到一个表段SALES中，那么在统计一季度销售数据时需要扫描10GB数据；而如果使用表分区段，那么可以将一、二、三、四季度数据分别存放到不同表分区段中，此时统计一季度销售数据只需要扫描2.5GB数据。显然，使用表分区

22、段可以大大降低I/O次数，并提高I/O性能。下面以建立SALES表为例，说明使用范围分区建立分区表的方法，示例如下： create table sales( customer_id number(3), sales_amount number(10,2), sales_date date) partition by range (sales_date)(partition pl values less than (01-APR-2001), partition p2 values less than (01-JUL-2001), partition p3 values less than (0

23、1-OCT-2001), partition p4 values less than (01-JAN-2002); 当在分区表上执行INSERT操作时，系统会自动按照sales_date值的范围将数据插入到相应的分区段上。例如： insert into sales values(1,28500, 25-JAN-2001); insert into males values (2,30500, 25-MAY-2001); 当执行了上述INSERT语句之后，会将第一条数据插入到分区P1上，而第二条数据被插入到分区P2上。当执行SELECT、UPDATE、DELETE操作时，如果在WHERE子句中引

24、用了分区列，那么Oracle会自动在相应分区上执行操作，从而降低I/O操作的次数，进而提高性能。 列表分区 如果分区字段的值并不能划分范围（非数字或日期数据类型），同时分区字段的取值范围只是一个包含少数值的集合，那么可以对表进行列表分区。在进行列表分区时，需要为每个分区指定一个取值列表，分区字段值处于同一个取值列表中的记录被存储在同一个分区中。 列表分区适用于那些分区字段是一些无序的或者无关的取值集合的表。下面以建立sales_by_region表为例，说明使用列表分区建立分区表的方法，示例如下： create table sales_by_region( deptno number, dna

25、me varchar2 (20), quarterly_sales number (10,2), city varchar2(10) partition by list(city)(partition pl values (北京, 上海) ,partition p2 values (重庆, 广州) ,partition p3 values (南京, 武汉) ); 其中，PARTITION BY LIST(column)指定分区方法为列表分区，column指定分区列名。 当执行了上述命令之后，Oracle会为表sales_by_region建立三个分区段。当在分区表上执行INSERT操作时，系统

26、会自动按照city列的值将数据插入到相应的分区段上，例如： insert into sales_by_region values(10, SALES,20800, 上海);insert into sales_by_region values (10, SALES,24800, 重庆); insert into sales_by_region values (10, SALES,28800, 武汉); 当执行了上述INSERT语句之后，会将第一条数据插入到分区P1上，第二条数据则会被插入到分区P2上，而第三条数据则会被插入到分区P3上。当执行SELECT、UPDATE、DELETE操作时，如果W

27、HERE子句引用了分区列，那么Oracle会自动在相应分区上执行操作，从而降低I/O操作的次数，进而提高性能。 散列分区 在进行范围分区或列表分区的时候，有时由于用户无法对各个分区中可能具有的记录数目进行预测，可能会产生某个分区中记录很多，而某个分区中记录很少的不平衡分区情况。这时应创建散列分区。 散列分区是指按照Oracle所提供的散列(HASH)函数来计算列值数据，并最终按照函数结果来分区数据。下面以建立分区表PRODUCT为例，说明使用散列分区建立分区表的方法，示例如下： create table product( product_id number(6), description va

28、rchar2(30) partition by hash(product_id) (partition pl tablespace locall, partition p2 tablespace local2); 其中，PARTITION BY HASH(column)指定分区方法为散列分区，column指定分区列名。当在散列分区表上插入数据时，系统会自动在分区列PRODUCT_ID上使用散列函数进行运算，并根据运算结果将数据均匀地放置到不同分区。 组合分区 顾名思义，组合分区实际上组合了范围分区和散列分区，它首先按照列值范围从逻辑上进行范围分区，然后在每个范围分区的基础上再按照散列函数进行散

29、列分区。当不同范围的数据分布比较均匀时，Oracle建议直接使用范围分区。对于某些表来说，尽管数据是按照一定范围分布的，但因为不同范围的数据分布不均匀，所以此时可以使用组合分区来有效地分布表的数据。 create table sales_order(order_id number, order_date date, product_id number, quantity number) partition by range(order_date) subpartition by hash(product_id) subpartitions 2 store in(users,userl) (pa

30、rtition p1 values less than( 01-APR-2001), partition p2 values less than (01-JUL-2001), partition p3 values less than (01-OCT-2001), partition p4 values less than (01-JAN-2002); 其中，STORE IN用于指定散列分区所在表空间。 在执行了上述命令之后，首先按照ORDER_DATE列值进行范围分区，此时建立四个逻辑上的范围分区。然后按照PRODUCT_ID列值进行散列分区，并将逻辑上的四个范围分区最终转化成8个物理上的散

31、列分区段。 当给该组合分区表插入数据时，首先按照order_date列值区逻辑确定其所在区，然后用散列函数计算product_id的散列值，最终确定数据应该放到哪个分区中。 6.建立簇表 一般情况下，建立表时Oracle会为该表分配相应的表段。例如，当建立表DEPT和EMP时，Oracle会分别为这两张表分配表段DEPT、EMP。并且它们的数据会分别存放到这两个表段中 。DEPTNO DNAME LOC10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONDEPTDEPTNO DNAME SAL2

32、0 SMITH 80030 ALLEN 160030 WARD 124020 SCOTT 200030 DEVE 1800EMPDEPTEMP 表DEPT的所有数据存放在表段DEPT中，而EMP表的所有数据则存放在表段EMP中。假定用户经常需要执行类似于“SELECT dname,ename,sal FROM dept,emp WHERE dept.deptno=emp.deptno AND dept.deptno=10”的连接查询语句来检索部门及其雇员的相关信息，那么至少需要2次的I/O操作，因为表DEPT和EMP的数据分别存放在两个表段中。这时，为了降低硬盘I/O操作的开销，可以把这两张表

33、的数据组织到簇中。 簇是一种用于存储表中数据的可选方法。在一个簇中，Oracle将多个表的相关字段聚簇在相同的数据块中。比如，EMP表和DEPT表都具有DEPTNO字段，DEPTNO字段就是这两个表的相关字段（簇键）。如果将EMP表和DEPT表聚簇在一起，那么Oracle会按照部门的DEPTNO在物理上存储两个表的所有记录。 簇DEPT_EMP(DEPT、EMP) 簇键(deptno) 10 ACCOUNTING NEW YORK CLARK 2450 KING 500020 RESEARCH DALLAS SMITH 800 SCOTT 2000 DEPT_EMP 创建簇使用CREATE C

34、LUSTER语句，执行该命令要求用户要有CREATE CLUSTER系统权限；如果要在其他用户模式中建立簇，则要有CREATE ANY CLUSTER系统权限。当建立簇时，Oracle会为该簇分配相应的簇段，因为簇段所需空间是从表空间上分配的，所以要求簇所有者必须要在表空间上具有相应的空间配额或具有UNLIMITED TABLESPACE系统权限。示例如下： create cluster dept_emp(deptno number (3) pctfree 20 pctused 60 size 500tablespace users storage ( initial 200K next 20

35、0K minextents 3 pctincrease 0 maxextents 50); 创建簇后，还要建簇表。建立簇表要求用户必须具有CREATE TABLE系统权限，如果要在其他用户模式中建立簇表，则必须具有CREATE ANY TABLE系统权限。另外，因为簇表数据是放在簇段中的，所以用户不需要任何表空间配额或UNLIMITED TABLESPACE系统权限。为了将表组织到簇中，在建表时必须指定CLUSTER子句。建立簇表的示例如下： create table dept( deptno number(3) primary key, dname varchar2(14), loc var

36、char2(13) )cluster dept_emp(deptno); 当执行了上述命令后，将表DEPT增加到簇DEPT_EMP中了。需要注意的是，当建立簇表时不能指定STORAGE子句和块空间使用参数。 当建立了簇和簇表之后，在插入数据之前必须首先建立簇索引，否则会显示错误信息。 create index dept_emp_idx on cluster dept_emp tablespace indx storage( initial 20K next 20K pctincrease 0); 7.1.3 修改表修改表 1.增加和删除字段 如果目前的字段不能够完整地标识表的所有属性，那么通过

37、增加字段可以间接地增加表的属性。使用ALTER TABLE命令可以给表增加字段，示例如下： alter table department add phone varchar2(10); alter table department add manager varchar 2(10); 使用ALTER TABLEDROP语句能够删除删除表中不再需要使用的字段。但是注意不能删除表中所有的字段，也不能删除SYS模式中任何表中的字段。如果仅需要删除一个字段，必须在字段名之前指定COLUMN关键字。比如，下列语句将删除EMPLOYEES表中的AGE字段： alter table employees dr

38、op column age; 如果要在一条语句中删除多个字段，则需要将删除的字段名放在括号中，相互间用逗号隔开，并且不能用COLUMN关键字。比如： alter table employees drop(age,sal); 当删除字段时，如果该表包含了大量数据，那么删除列的时间就会很长。如果时间因素必须考虑，而字段也不再需要，那么你可以先将字段标记为UNUSED列，然后在适当时机删除该列的所有数据。标记列为UNUSED的方法如下： alter table emp set unused column comm.; 当将列标记为UNUSED之后，会从数据字典中删除该列的信息，并且在查看表结构时也不

39、会看到该列的信息。但是该列的数据仍然存在，如果要删除UNUSED列的数据，则必须执行如下语句： alter table emp drop unused columns checkpoint 1000; 其中CHECKPOINT l000用于指定每删除1000行发出一次检查点，以节省回滚段的空间使用。 2.修改表的参数设置 在表创建之后，可以使用ALTER TABLE语句来改变表的块参数设置和部分存储参数设置。语法如下： ALTER TABLE schema.table storage-clause PCTFREE integer PCTUSED integer INITRANS integer

40、 MAXTRANS integer 如，利用下面的语句可以为EMPLOYEES表重新设置PCTFREE和PCTUSED参数： alter table employees pctfree 30 pctused 60; 在表创建之后，不能再对INITIAL存储参数进行修改，但是可以修改其他的存储参数。 alter table employees storage ( next 512K pctincrease 0 maxextents unlimited);3.重建表 如果发现一个表的数据段具有不合理的区分配方式，但是又不能通过别的方法来调整（改变存储参数不会影响到已经分配的区），可以考虑将该表移到

41、一个新的数据段中。用户可以为新的数据段重新设置存储参数，以便符合表的存储需求。 比如，利用下面的语句可以将EMPLOYEES表移动到同一个表空间的新数据段中： alter table employees movestorage(initial 20Knext 40Kminextents 2maxextents 20pctincrease 0); 新的数据段可以在原来的表空间中，也可以在其他的表空间。比如，利用下面的语句可以将EMPLOYEES表移动到表空间USERS02的新数据段中： alter table employees movetablespace users02storage(ini

42、tial 20Knext 40Kminextents 2maxextents 20pctincrease 0); 4.手工分配和释放空间 默认情况下，Oracle会根据存储参数设置自动为表分配区并计算大小。若需要指定大小的区，则可使用ALTER TABLE ALLOCATE EXTENT语句以手工方式为表分配存储空间。 通过手工分配区，你可指定区的大小，也可控制将区分布到哪个数据文件上。另外当执行SQL * loader装载数据时，如果表段的空间不足，则会导致Oracle为表段动态分配空间，这样会降低数据装载速度。为了避免区的动态分配，应该在执行数据装载操作前手工为表增加足够大的区。示例如下：

43、 alter table department allocate extent (size 500K datafile e:testusers2.dbf); 使用ALTER TABLE命令不仅可以为表段分配空间，也可以释放表上多余的空间。如果表段实际占用空间多于所需空间时，你可以释放其所占用的多余空间。语法如下： ALTER TABLE schema.table DEALLOCATE UNUSED KEEP integerK|M KEEP指定在高水位标记（已经使用的存储空间和未使用的存储空间之间的分界线）以上应该保留的字节数。 如果使用上述命令时没有KEEP子句，Oracle将回收高水位标记以

44、上所有未使用空间。如果高水位标记所在的区小于MINEXTENTS的值，则Oracle释放MINEXTENTS以上的区，既默认情况下释放剩余空间后表段的区个数不会低于MINEXTENTS，而如果要释放MINEXTENTS下面的剩余空间，需要带有KEEP 0选项。示例如下： alter table department deallocate unused;5.分析表 在Oracle中，利用ANALYZE语句可以对表、索引和簇进行分析，通过分析可以获得关于指定对象的状态和统计信息，并且能够对指定对象的存储格式进行验证。 验证表的存储结构 在ANALYZE语句中使用VALIDATE STRUCTURE

45、子句，可以在分析过程中对表的存储结构的完整性进行验证。通过存储结构的验证，用户可以知道表中是否存在损坏的数据块。如果有损坏的数据块，则需删除该表并重建它。 在验证表的存储结构时，Oracle会把表中包含损坏数据块的记录的ROWID插入到一个名为INVALID_ROWS的表中。对EMPLOYEES表进行结构验证分析示例如下：analyze table employees validate structure; 然后可查询INVALID_ROWS表看是否有损坏的数据块。 收集表的统计信息 在ANALYZE语句中使用COMPUTE STATISTICS子句或ESTIMATE STATISTICS子句

46、可以收集关于表的物理存储结构和特性的统计信息，如表中记录的总数和记录链接的总数，已使用的数据块总数，未使用的数据块总数，所有记录的平均长度等。比如，下面语句对EMPLOYEES表进行精确统计信息： analyze table employees compute statistics; 而利用下列语句将通过对200条记录的分析，获得对EMPLOYEES表的近似统计信息： analyze table employees estimate statics; 统计完后，可查询USER_TABLE、ALL_TABLE和DBA_TABLE数据字典视图来获得分析后的统计信息。 查找表中的链接记录和迁移记录

47、在ANALYZE语句中使用LIST CHAINED_ROWS子句，可以找出表中的链接记录和迁移记录。Oracle将把表中所有链接记录和迁移记录的ROWID保存到一个名为CHAINED_ROWS的表中。比如，下面语句对EMPLOYEES表进行链接记录分析： analyze table employees list chained_rows; 6.重命名表重命名表 如果要修改表的名称，可以使用RENAME语句对表进行重命名。用户只能对属于自己模式中的表进行重命名。 rename employees to emp; 7.1.4 删减表删减表1. 使用使用DELETE语句语句 delete from

48、employees; 但是，用DELETE删除记录后，Oracle不会回收为表分配的存储空间，也无法手工回收，甚至高水位标记也都不会改变。 DELETE通常只来删除表中指定的记录，如果删除表中的全部记录，一般用TRUNCATE或DROP语句。 2. 使用DROP语句 DROP语句不仅删除了表中所有的记录，还删除了表结构。删除表一般是由表的所有者来完成的，如果要以其他用户身份删除表，则要求该用户必须具有DROP ANY TABLE系统权限，示例如下： drop table employees cascade constraints; 其中，CASCADE CONSTRAINTS选项用于指定级联删

49、除。当表与其他表具有主外键关系时，删除主表时必须带有该选项。 3. 使用TRUNCATE语句 当表结构必须保留，但表数据不再需要时，可以使用TRUNCATE TABLE命令截断表。当执行该命令时，会删除表的所有数据，并释放表所占用的空间，但会保留表的结构，具体命令如下： truncate table employees; 7.1.5 显示表的信息 1. 显示用户所包含的表 通过查询数据字典DBA_TABLES、USER_TABLES可以取得表的信息，其中DBA_TABLES可用于显示所有用户表的信息，而USER_TABLES则可以显示当前用户表的信息，示例如下： select table_na

50、mefrom dba_tables where owner=DEVEP; 2. 显示表的存储参数设置 当建立表时，可以指定表段的存储参数、块空间使用参数、并行度、日志属性以及CACHE 属性等。通过查询数据字典USER_TABLES，可以取得相应属性信息，示例如下： select pct_free, pct_used, degree, cache from user_tables where table_name =EMPLOYEES; 3. 显示表段所在表空间及尺寸 当建立表时，Oracle会自动为表分配相应的表段，表段的名称与表的名称完全一致，并且该表的所有数据都会存放在相应表段中。那么如

51、何取得段信息呢?通过查询数据字典DBA_SEGMENTS或USER_SEGMENTS可以取得段信息，其中DBA_SEGMENTS用于显示数据库所有段的信息，而USER_SEGMENTS则用于显示当前用户段的信息，示例如下： select tablespace_name,bytes from user_segments where segment_name=DEPARTMENT; 4.显示表数据占用的实际空间以及剩余空间 当建立表时，Oracle会为表分配相应的表段。当在表上执行INSERT操作时，Oracle会将数据放到表段的相应数据块上。那么如何取得表数据占用的实际空间以及剩余空间呢?通过查

52、询数据字典USER_TABLES可以取得这些信息，但查询之前必须首先收集统计。示例如下： analyze table emp compute statics; select blocks,empty_blocksfrom user_tables where table_name=EMP; 其中BLOCKS对应于数据已占用的实际块个数，而EMPTY_BLOCKS则对应于剩余块个数。 5.显示区信息 当建立表时，Oracle会为表建立相应的表段。段逻辑上又是由一个或多个区组成的，而区又是由相邻的数据块所组成的，那么如何确定段包含哪些区，以及区的位置及尺寸呢?通过查询数据字典DBA_EXTENTS，

53、可以显示区的详细信息，示例如下： select extent_id, file_id, block_id, blocks from dba_extentswhere owner=DEVEP and segment_name=DEPT; 其中，extent_id为区编号，file_id为区所在文件号，block_id为区的初始数据块号，blocks为区所包含的数据块个数。 6.显示行所在的实际位置 执行INSERT操作时，服务器进程会将数据插入到表段的相应数据块中，并且Oracle会生成惟一的ROWID对应于该行数据。但用户无法直接读懂ROWID，通过使用DBMS_ROWID包可以显示行所在数据

54、文件、数据块位置以及行位置，如下所示： select deptno, dname, dbms_rowid.rowid_relative_fno(ROWID) file#, dbms_rowid.rowid_block_number(ROWID) block#, dbms_rowid.rowid_row_number(ROWID) row#from dept;7.2 管理索引管理索引 1.1. 索引的作用索引的作用 2. 索引是与表和簇相关的一种数据库对象，它的作用类似于书中的目录。在没有目录的情况下，要在书中查找指定的内容必须通读全书，而有了目录之后，只需要通过目录就可以快速地找到包含所需内容

55、的页。合理地使用索引可以降低磁盘I/O操作次数，从而提高表的访问性能。 ROWID在empno列上无索引在empno列上存在索引select * from emp where empno=7788 假定表EMP数据占用了1000个数据块，如果在EMPNO列上不存在索引，那么当执行“select * from emp where empno=7788”时需要扫描表的所有数据块，也就是说需要1000次I/O操作；如果在EMPNO列上存在索引，并假定索引层次为2，那么当执行“select * from emp where empno=7788”时，在检索了3个索引块后就可以定位到行所在的ROWID，

56、然后根据ROWID可以直接定位到该行数据，也就是说只需要4次I/O操作。显然，使用索引可以大大提高查询速度。 2. 索引存储方式 索引与表一样，不仅需要在数据字典中保存索引的定义，还需要在表空间中为它分配实际的存储空间。当创建索引时，Oracle会自动在用户的默认表空间中或指定的表空间中创建一个索引段，为索引数据提供存储空间。与创建表类似，在创建索引时也可以为它设置存储参数。 在创建索引时，Oracle首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中（ROWID是数据库的伪列，用于存储一个行标识符）。例如，假设为EMPLOYEES表中的ENAME字段创

57、建了索引： create index emp_ename on employees(ename); 则Oracle将先在EMPLOYEES表中按照ENAME字段进行排序（默认为升序排序），然后按照排序后的顺序将ENAME字段值和对应的ROWID逐个保存在索引中。 在索引创建之后，如果执行如下的一条查询语句，即在WHERE子句中引用ENAME字段： select ename, sal from employees where ename=JONES ; 那么Oracle将首先对索引中ENAME字段进行一次快速搜索，找到符合条件的ENAME字段值所对应的ROWID，然后再利用ROWID到EMPLO

58、YEES表中提取相应的记录。 7.2.2 建立索引 建立索引使用CREATE INDEX命令。执行该命令的用户必须具有CREATE INDEX系统权限。如果要在其他用户模式中创建索引，则必须具有CREATE ANY INDEX系统权限。 常用的索引类型有B*树索引、位图索引、反向索引、函数索引等。 1. 建立B*树索引 B*树索引是最常用的索引，在使用CREATE INDEX语句创建索引时，默认方式下将建立B*树索引。 B*树索引是以根块、分支块、叶块来组织和存放索引数据的，在B*树的叶节点中存储索引字段的值与ROWID。 根块KINGKING分支块块KINGMILLERTURNERBLAKE

59、BLAKEJAMES叶块TURNERWARDMILLERSCOTTSMITHKINGMARTENJAMESJONESBLAKECLARKFORDADAMSALLENBLAKE ROWIDCLARK ROWIDFORD ROWID B*树索引适用于那些具有高基数的字段，即大部分值都不相同的字段。创建B*树索引的语法如下： CREATE UNIQUEINDEX schema.index ON schema.table (columnASC|DESC ,columnASC|DESC ) TABLESPACE tablespace PCTFREE integer INITRANS integer MA

60、XTRANS integer storage-clause LOGGING|NOLOGGING NOSORT TABLESPACE：用于指定索引段所在表空间。 PCTFREE：用于指定为将来INSERT操作所预留的百分比。若指定PCTFREE为 0，则可能会导致索引数据占满某些索引叶块。 INITRANS：指定每个块中预先分配的事务项的数目。 MAXTRANS：限制分配给每块的事务项的数目。 storage-clause：用于指定索引段存储参数。 LOGGING、NOLOGGING：是否在重做日志中记录创建索引的操作和在索引上进行的后续操作。 NOSORT：指定行按升序存储在数据库中，这样，O