上一文中对ORACLE逻辑架构的基本体系做了说明，这部分是专门对SEGMENT部分进行说明，因为段才是管理对象的关键点，也是管理对象的基本，本文主要写
1、TABLE SEGMENT
2、INDEX SEGMENT
3、UNDO SEGMENT
4、LOBSEGMENT、LOB PARTITION、LOBINDEX、INDEX PARTITION
下面切入正题：
1、TABLE SEGMENT：
TABLE的分类大致有：
a、常规的堆表（HEAP表）
b、索引组织表，带有主键组织的表，主键自动创建唯一性索引，并将索引段放置于数据段中，所以主键是最快的索引。
c、分区表，在TABLE PARTITION中简要说明下（在前序文章中已有专门说明分区表的文章）。
d、CLUSTER表，将多个关联表存放在一个段中，并将关联字段只保存一份，此时在经常关联的表中，将会大量节约关联时间。
e、COMPRESS表，对表的数据块会进行相应的压缩存储，支持高水位插入；在UPDATE的时候会出点点问题。
f、TEMPORARY表（临时表），其自动存放于临时表空间，分事务级别和会话级别。
常规HEAP表和索引组织表不用多说，当你创建表的时候，会在DBA_SEGMENT的时候自动创建一个同名的SEGMENT，这里主要说一下在进行大量删除操作后如何释放的问题：
做一个简单试验：
SQL> drop table t1 purge;
表已删除。
SQL> create table t1 as select * from e
表已创建。
SQL> insert into t1 select * from t1;
已创建14行。
SQL> r
1* insert into t1 select * from t1
已创建28行。
SQL> r
1* insert into t1 select * from t1
已创建56行。
SQL> r
1* insert into t1 select * from t1
已创建112行。
SQL> r
1* insert into t1 select * from t1
已创建224行。
SQL> r
1* insert into t1 select * from t1
已创建448行。
SQL> commit;
提交完成。
SQL> select segment_name,BLOCKS,HEADER_BLOCK FROM dba_segments
2 WHERE segment_name='T1' AND OWNER='SCOTT';
SEGMENT_NAME BLOCKS HEADER_BLOCK
--------------------------------------------------------------------------------- ---------- ------------
T1 16 59
SQL> delete from t1;
已删除896行。
SQL> commit;
提交完成。
SQL> select segment_name,BLOCKS,HEADER_BLOCK FROM dba_segments
2 WHERE segment_name='T1' AND OWNER='SCOTT';
SEGMENT_NAME BLOCKS HEADER_BLOCK
--------------------------------------------------------------------------------- ---------- ------------
T1 16 59
SQL> alter table t1 move;
表已更改。
SQL> select segment_name,BLOCKS,HEADER_BLOCK FROM dba_segments
2 WHERE segment_name='T1' AND OWNER='SCOTT';
SEGMENT_NAME BLOCKS HEADER_BLOCK
--------------------------------------------------------------------------------- ---------- ------------
T1 8 635
此时发现：通过MOVE操作，将会对表进行重定义，其实MOVE等价于MOVE TABLESPACE 同一个表空间;其HEADER_BLOCK也发生了变化，其实如果深入试验可以发现其DATA_OBJECT_ID也会发生变化，也就是再次回顾一下内容：
1、TRUNCATE、MOVE、SHRINK SPACE、REBUILD会发生行迁移。
2、TRUNCATE、MOVE、REBUILD会使得DATA_OBJECT_ID变化，因为DATA_OBJECT_ID是物理的，而OBJECT_ID是逻辑的。
3、TRUNCATE、MOVE、REBUILD会释放表空间信息，在对表进行MOVE应当对表的相应索引进行REBUILD，在线REBUILD应当使用ONLINE，等会说索引段的时候再说。
4、回顾ROWID生成规则（其实是ORACLE 8以后ROWID才有DATA_OBJECT_ID的组成，用于解决数据库的数据文件不能超过1023个的问题），由于DATA_OBJECT_ID才是物理的，所以MOVE表空间的时候，就是修改ROWID上的DATA_OBJECT_ID，它可以唯一确定一个表空间，也就是一个段必然存在于同一个表空间，而OBJECT_ID是逻辑上的引用，当数据文件上涨的过程中，会发现达到数据文件编号1023后，RFILE#字段从新从1开始计算，FILE#会继续长大，通过仔细研究ROWID可以发现，其使用10bit来存放文件编号，所以其上限为1024，所以不可能使用的FILE#作为这几位的标识码了。
5、仔细研究可以发现，DATA_OBJECT_ID是与SYS用的是数据字典：SGE$的字段HWMINCR，每次做类似操作，肯定是这个值的最大值，而OBJECT_ID则为DBA_OBJECTS的OBJECT_ID的最大值。这部分就不用做实验了，可以自己测试即可。
对于分区表，在上一次已经有很详细的说明http://blog.youkuaiyun.com/xieyuooo/archive/2010/03/31/5437126.aspx，上次也简单说了下通过SHRINK SPACE压缩表空间的过程，这里说下分区表也可以通过MOVE释放表空间：
根据分区表原理，其实分区表就是子表，最大的区别就是可以统一按照指定的规则进行管理，所以对于分区表也是可以压缩的：
ALTER TABLE <表名称> MOVE PARTITION <分区名称>;
二级分区为:
ALTER TABLE <表名称> MOVE SUBPARTITION <子分区的名字>;
这里也不多做实验了，可以自己做点分区表测试下就可以。
CLUSTER表，也算是比较少用的，它存在不少的BUG，但是也是可以解决的，方便于经常于进行关联的几个表，它是将这些表的数据存放在一个段内部（分区除外），或者说存放到一个表中，并且将关联字段只存放一份的方式，来提高性能（这是它说的，我们看了才知道），它如何创建，不知道，那么跟着ORACLE学习一下：
首先找几个系统的CLUSTER表:
SQL> select segment_name from dba_segments
2 where segment_type='CLUSTER'
3 and rownum<10;
SEGMENT_NAME
---------------------------------------------
C_COBJ#
C_TS#
C_FILE#_BLOCK#
C_USER#
C_OBJ#
C_MLOG#
C_TOID_VERSION#
C_RG#
C_OBJ#_INTCOL#
随便找一个看看：
SQL> select dbms_metadata.get_ddl('CLUSTER','C_RG#','SYS') from dual;
DBMS_METADATA.GET_DDL('CLUSTER','C_RG#','SYS')
------------------------------------------------------------------------
CREATE CLUSTER "SYS"."C_RG#" (
"REFGROUP" NUMBER )
PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
PARALLEL (DEGREE 1 INSTANCES 1)
再找和CLUSTER相关的表（因为CLUSTER是为表服务的）：
SQL> select TABLE_NAME,CLUSTER_NAME FROM tabs
2 where cluster_name is not null
3 and rownum=1;
TABLE_NAME CLUSTER_NAME
------------------------------ --------------
ICOL$ C_OBJ#
SQL> select dbms_metadata.get_ddl('TABLE','ICOL$','SYS') from dual;
DBMS_METADATA.GET_DDL('TABLE','ICOL$','SYS')
--------------------------------------------------------------------
CREATE TABLE "SYS"."ICOL$"
( "OBJ#" NUMBER NOT NULL ENABLE,
"BO#" NUMBER NOT NULL ENABLE,
"COL#" NUMBER NOT NULL ENABLE,
"POS#" NUMBER NOT NULL ENABLE,
"SEGCOL#" NUMBER NOT NULL ENABLE,
"SEGCOLLENGTH" NUMBER NOT NULL ENABLE,
"OFFSET" NUMBER NOT NULL ENABLE,
"INTCOL#" NUMBER NOT NULL ENABLE,
"SPARE1" NUMBER,
"SPARE2" NUMBER,
"SPARE3" NUMBER,
"SPARE4" VARCHAR2(1000),
"SPARE5" VARCHAR2(1000),
"SPARE6" DATE
) CLUSTER "SYS"."C_OBJ#" ("BO#")
原来CLUSTER表是这样关联上的，它真的能提高性能吗？我们做个试验看看吧：
SQL> drop table t1 purge;
表已删除。
SQL> create table t1 as select * from emp where 1=2;
表已创建。
SQL> begin
2 for i in 1..5000 loop
3 INSERT INTO t1 values(i,'a'||i,'abc',10,sysdate,2000+i,20,10);
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> create table t2 as select * from t1;
表已创建。
SQL> set autotrace traceonly;
SQL> alter table t1 add primary key(empno);
表已更改。
SQL> alter table t2 add primary key(empno);
表已更改。
SQL> select * from t1,t2 where t1.empno=t2.empno;
已选择5000行。
执行计划
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 849K| 20 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 5000 | 849K| 20 (5)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 5000 | 424K| 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 5000 | 424K| 10 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."EMPNO"="T2"."EMPNO")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
515 recursive calls
0 db block gets
536 consistent gets
0 physical reads
0 redo size
210967 bytes sent via SQL*Net to client
4048 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
5000 rows processed
SQL> create cluster
2 TEST_EMPNO#(empno number(4));
簇已创建。
SQL> create index idx_test_cluster on cluster test_empno#;
索引已创建。
SQL> CREATE TABLE "SCOTT"."T1_2"
2 ( "EMPNO" NUMBER(4,0),
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "MGR" NUMBER(4,0),
6 "HIREDATE" DATE,
7 "SAL" NUMBER(7,2),
8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0),
10 PRIMARY KEY ("EMPNO")
11 ) cluster test_empno#(empno);
表已创建。
SQL> CREATE TABLE "SCOTT"."T2_2"
2 ( "EMPNO" NUMBER(4,0),
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "MGR" NUMBER(4,0),
6 "HIREDATE" DATE,
7 "SAL" NUMBER(7,2),
8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0),
10 PRIMARY KEY ("EMPNO")
11 ) cluster test_empno#(empno);
表已创建。
SQL> insert into t1_2 select * from t1;
已创建5000行。
SQL> insert into t2_2 select * from t1;
已创建5000行。
SQL> select * from t1_2,t2_2 where t1_2.empno=t2_2.empno;
已选择5000行。
执行计划
----------------------------------------------------------
Plan hash value: 2653609197
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3994 | 678K| 2199 (1)| 00:00:27 |
| 1 | MERGE JOIN | | 3994 | 678K| 2199 (1)| 00:00:27 |
| 2 | TABLE ACCESS CLUSTER| T2_2 | 5351 | 454K| 827 (1)| 00:00:10 |
| 3 | INDEX FULL SCAN | IDX_TEST_CLUSTER | 5351 | | 26 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3994 | 339K| 1372 (1)| 00:00:17 |
| 5 | TABLE ACCESS FULL | T1_2 | 3994 | 339K| 1370 (1)| 00:00:17 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1_2"."EMPNO"="T2_2"."EMPNO")
filter("T1_2"."EMPNO"="T2_2"."EMPNO")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
53 recursive calls
1 db block gets
10942 consistent gets
0 physical reads
176 redo size
210967 bytes sent via SQL*Net to client
4048 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5000 rows processed
其实上面可以发现它未必可以提高性能，通过分页也是类似结果，除了返回的BYTES减少了，其余的都在变大，所以有些东西一定要经过试验才可以放心使用，这类东西慎用，此时来看下如何删除它：
SQL> drop cluster test_empno#;
drop cluster test_empno#
*
第 1 行出现错误:
ORA-00951: 簇非空
查看是不是因为有数据的问题，我们把数据干掉：
SQL> truncate cluster test_empno#;
簇已截断。
SQL> select * from t1_2;
未选定行
SQL> select * from t2_2;
未选定行
SQL> drop cluster test_empno#;
drop cluster test_empno#
*
第 1 行出现错误:
ORA-00951: 簇非空
还是不行看来不是数据的问题，里面应该是放置了表，那么把表干掉：
SQL> drop table t1_2;
表已删除。
SQL> drop table t2_2;
表已删除。
SQL> drop cluster test_empno#;
簇已删除。
SQL> show recyclebin;
尽然回收站没有东西，所以注意了，CLUSTER干掉后，回收站是不会有任何东西的。
COMPRESS表，就是所谓的压缩表，它创建的区别就是创建语句上有一个COMPRESS符号，如下：
SQL> conn scott/a
已连接。
SQL> drop table tt purge;
表已删除。
--先创建一个普通表看下：
SQL> CREATE TABLE T1 AS SELECT * FROM EMP ;
表已创建。
SQL> insert into t1 select * from t1;
已创建14行。
SQL> /
已创建28行。
SQL> /
已创建56行。
SQL> /
已创建112行。
SQL> /
已创建224行。
SQL> /
已创建448行。
SQL> /
已创建896行。
SQL> /
已创建1792行。
SQL> /
已创建3584行。
SQL> /
已创建7168行。
SQL> /
已创建14336行。
SQL> /
已创建28672行。
SQL> /
已创建57344行。
SQL> /
已创建114688行。
SQL> /
已创建229376行。
SQL> /
已创建458752行。
SQL> commit;
提交完成。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select blocks,empty_blocks from tabs where table_name='T1';
BLOCKS EMPTY_BLOCKS
---------- ------------
5413 91
此时创建一个COMPRESS表来对比下：
SQL> create table t2 compress as select * from t1;
表已创建。
SQL> analyze table t2 compute statistics;
表已分析。
SQL> select blocks,empty_blocks from tabs where table_name='T2';
BLOCKS EMPTY_BLOCKS
---------- ------------
1382 26
数据块果然少了不少，我们使用普通插入的方法测试一下：
SQL> truncate table t2;
表被截断。
SQL> analyze table t2 compute statistics;
表已分析。
SQL> select blocks,empty_blocks from tabs where table_name='T2';
BLOCKS EMPTY_BLOCKS
---------- ------------
0 8
SQL> INSERT INTO t2 select * from t1;
已创建917504行。
SQL> commit;
提交完成。
SQL> analyze table t2 compute statistics;
表已分析。
SQL> select blocks,empty_blocks from tabs where table_name='T2';
BLOCKS EMPTY_BLOCKS
---------- ------------
4906 86
看来这样插入效果不佳呀，那么用APPEND数据试一试：
SQL> truncate table t2;
表被截断。
SQL> analyze table t2 compute statistics;
表已分析。
SQL> INSERT INTO t2 select * from t1;
已创建917504行。
SQL> truncate table t2;
表被截断。
SQL> INSERT /*+append*/INTO t2 select * from t1;
已创建917504行。
SQL> commit;
提交完成。
SQL> truncate table t2;
表被截断。
SQL> INSERT /*+append*/INTO t2 select * from t1;
已创建917504行。
SQL> commit;
提交完成。
SQL> analyze table t2 compute statistics;
表已分析。
SQL> select blocks,empty_blocks from tabs where table_name='T2';
BLOCKS EMPTY_BLOCKS
---------- ------------
1382 26
此时发现对APPEND的数据是有效地，但是对于普通插入无效，对上述数据再做一下UPDATE操作：
SQL> update t1 set sal=sal+1;
已更新917504行。
SQL> commit;
提交完成。
SQL> analyze table t2 compute statistics;
表已分析。
SQL> select blocks,empty_blocks from tabs where table_name='T2';
BLOCKS EMPTY_BLOCKS
---------- ------------
6532 92
现在是想做的事情没有做成功，压缩表不但没压缩，比原有表还大？试一试MOVE是否好用：
SQL> alter table t2 move;
表已更改。
SQL> analyze table t2 compute statistics;
表已分析。
SQL> select blocks,empty_blocks from tabs where table_name='T2';
BLOCKS EMPTY_BLOCKS
---------- ------------
1382 26
TEMPORARY表：是存储在临时表空间的，ORACLE有些时候自己去高一些临时表来用于辅助完成数据的中间处理，如：排序、连接、分组等。
人工建立临时表一般用在两个地方：
1、当一个非常复杂的SQL中，对于几个表的查询关联是反复调用的，而且计算的结果是很小的，我们为了避免大量重复计算得到的一个小结果集，把它第一次计算的结果放在临时表中，便于反复使用。
2、过程中一个结果集用于过程中各个部分调用，方便中间存储，并且要求事务或者会话之间的数据要相互隔离。
临时表用到恰到好处可以出奇制胜，因为有些时候ORACLE的CBO在遇到一些问题的时候真的很傻，用临时表就是将我们需要执行的大步骤修改为一个一个小步骤，由程序来控制，因为这个部分我们写程序的人最清楚如何一个顺序是最快速的。
一个实际的例子：
表A、B、C依次一对多对应下去，A为引导表，一个查询语句：关联后，要求过滤掉C中存在FLAG=2的所有的A表相应的的记录，B表中过滤只需要STATUS='T'的记录，但是C中的FLAG=2不以B中的STATUS是否为T作为参照，只要等于2，A中相应的KEY全部过滤掉，当时第一遍有人写下来是需要运行九十多秒的SQL，优化这类SQL一般从两个思路下手：
1、临时表，因为不等于于等于两个问题，都是查询一样的三个表来回查询，都是大表，但是不符合的挺多。
2、转换思维模式，从侧面思考，FLAG=2是不是指定A对应C对应FLAG=2的个数，此时反向根据个数定位A的关键字，再次提取数据也是不错的做法。
如何创建临时表呢，临时表分为事务级别和会话级别，两种临时表怎么创建，不清楚就学学ORACLE：
SQL> conn / as sysdba
已连接。
SQL> select table_name,temporary,duration
2 FROM tabs
3 where temporary ='Y' and rownum<10;
TABLE_NAME T DURATION
------------------------------ - ---------------
ATEMPTAB$ Y SYS$TRANSACTION
MAP_OBJECT Y SYS$SESSION
CLUSTER_DATABASES Y SYS$SESSION
CLUSTER_NODES Y SYS$SESSION
CLUSTER_INSTANCES Y SYS$SESSION
PSTUBTBL Y SYS$SESSION
WRI$_ADV_ASA_RECO_DATA Y SYS$SESSION
ODCI_SECOBJ$ Y SYS$SESSION
ODCI_WARNINGS$ Y SYS$SESSION
可以看到事务级别和SESSION级别都有，那么分别看看他们是怎么创建的：
SQL> select dbms_metadata.get_ddl('TABLE','ATEMPTAB$','SYS') FROM dual;
DBMS_METADATA.GET_DDL('TABLE','ATEMPTAB$','SYS')
-----------------------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE "SYS"."ATEMPTAB$"
( "ID" NUMBER
) ON COMMIT DELETE ROWS
--这就是事务级别的临时表，代表你做COMMIT操作的时候数据就被删掉了，当然包含DDL、DCL的隐含式提交方法。
SQL> select dbms_metadata.get_ddl('TABLE','MAP_OBJECT','SYS') FROM dual;
DBMS_METADATA.GET_DDL('TABLE','MAP_OBJECT','SYS')
------------------------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE "SYS"."MAP_OBJECT"
( "OBJECT_NAME" VARCHAR2(2000),
"OBJECT_OWNER" VARCHAR2(2000),
"OBJECT_TYPE" VARCHAR2(2000),
"FILE_MAP_IDX" NUMBER,
"DEPTH" NUMBER,
"ELEM_IDX" NUMBER,
"CU_SIZE" NUMBER,
"STRIDE" NUMBER,
"NUM_CU" NUMBER,
"ELEM_OFFSET" NUMBER,
"FILE_OFFSET" NUMBER,
"DATA_TYPE" VARCHAR2(2000),
"PARITY_POS" NUMBER,
"PARITY_PERIOD" NUMBER
) ON COMMIT PRESERVE ROWS
--这就是会话级别的临时表，COMMIT时保存数据，创建临时表就是这么简单，为什么，因为ORACLE把复杂的问题简单化了，你使用的时候就会觉得就这么回事，不过所有东西切忌滥用，适当使用，在适当的时候使用，用好了，就会的心应手。
至于细节这里就不多说了，自己可以写几个表来做测试。
2、INDEX SEGMENT
索引段，所有索引按照B+树管理模式，唯一性索引找到唯一ROWID直接回表，普通索引根据索引顺序链表查找相应符合条件的ROWID然后再回表，位图索引在叶子块中标记每个ROWID对于位图健是否符合条件的情况用0和1表达，所以位图用来统计很方便，但是OLTP系统中经不起频繁的修改。
索引也有分区索引，在分区表那篇文章中也已经详细说明，索引也是用块存储数据，存储键值+ROWID，非叶子节点记录：层次、叶子块、键值起始位。理论上一个块可以存放733行数据上线，实际一般存放六百多行就是上线了，根据实际键值大小有所关系，一个快存放不下，就找两个块，两个块需要一个管理者，树就加为2层，同理，当叶子块达到六七百个的时候，一个头管不住，就在请一个头，两个头就需要更高的管理者来管理，树就变为三层了。
还有更多的索引内容，为了说明这些，做一些简单试验：
SQL> create table t1 as select * from emp;
表已创建。
SQL> create index idx_emp on t1(empno);
索引已创建。
SQL> desc user_extents;
名称
----------------------
SEGMENT_NAME
PARTITION_NAME
SEGMENT_TYPE
TABLESPACE_NAME
EXTENT_ID
BYTES
BLOCKS
SQL> select extent_id,blocks from user_extents;
EXTENT_ID BLOCKS
---------- ----------
0 8
SQL> analyze index IDX_EMP VALIDATE STRUCTURE;
索引已分析
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,DEL_LF_ROWS
2 FROM index_stats;
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_ROWS,DEL_LF_ROWS
2 FROM index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ------------- ---------- -----------
1 8 0 1 14 0
解释下这几个字段：
HEIGHT：索引层数，也是树的高度
BLOCKS：索引块数
BR_BLKS：非叶子节点的块数目
LF_ROWS：叶子节点索引值的总行数
DEL_LF_ROWS：被删除的行
SQL> insert into t1 select * from emp;
已创建14行。
SQL> commit;
提交完成。
SQL> analyze index idx_emp validate structure;
索引已分析
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS
2 FROM index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ------------- ---------- -----------
1 8 0 1 28 0
重复执行这几行：
insert into t1 select * from t1;
commit;
analyze index idx_emp validate structure;
select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS
FROM index_stats;
执行多次后，当数据量有八百多行的时候（每个8K的数据块最多存储733行数据）
SQL> SELECT HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS
2 FROM index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
2 8 1 2 896 0
此时发现：BR_BLKS从0变成1，LF_BLKS从1变成2，而LF_ROWS始终与数据行数一致，HEIGHT变成了2。
SQL> insert into t1 select * from t1;
已创建896行。
SQL> /
已创建114688行。
SQL> commit;
提交完成。
SQL> analyze index idx_emp validate structure;
索引已分析
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS
2 FROM index_stats;
HEIGHT BLOCKS BR_BLKS LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
3 768 3 671 229376 0此时发现：
HEIGHT变成了3层结构
BR_BLKS有三个，即非叶子节点上面又有一个管理节点，即，第一层一个节点，下面管理两个中间节点，中间节点再在下面管理六百多个块，每个块内部最多管理七百多行ROWID，也就是两层的结构的索引极限时可以存储42万条数据的索引树，但是由于实际存储的建值数据，这个试验中的数据存储量是实际的一半，每个块大概有三百多行数据的ROWID信息，也就是三层结构内存储100万行数据应该是问题不大的，除非联合索引存储的数据导致。
这里做一下UPDATE语句：
SQL> update t1 set empno=30 where rownum=1;
已更新 1 行。
SQL> commit;
提交完成。
SQL> analyze index idx_emp validate structure;
索引已分析
SQL> select height,blocks,br_blks,br_rows,lf_rows,lf_blks,DEL_LF_ROWS
2 from index_stats;
HEIGHT BLOCKS BR_BLKS BR_ROWS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
3 768 3 670 229377 671 1
发现BR_ROWS增加了一个，而DEL_LF_ROWS变成了1，说明索引是先删除，再插入的，数据是直接删掉，但是索引不是，因为索引是有序的。
--删除数据：
SQL> delete from t1 where rownum<229400;
已删除229374行。
SQL> commit;
提交完成。
SQL> analyze index idx_emp validate structure;
索引已分析
SQL> select height,blocks,br_blks,br_rows,lf_rows,lf_blks,DEL_LF_ROWS
2 from index_stats;
HEIGHT BLOCKS BR_BLKS BR_ROWS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
3 768 3 670 229374 671 229374
此时发现索引的块个数和高度没有任何变化，要是CBO此时选择走索引，肯定是得不偿失，我们通过压缩来看看是否可以释放掉：
（alter index idx_emp coalesce;和下面的语句一样的效果：）
SQL> alter index idx_emp shrink space;
索引已更改。
SQL> analyze index idx_emp validate structure;
索引已分析
SQL> select height,blocks,br_blks,br_rows,lf_rows,lf_blks,DEL_LF_ROWS
2 from index_stats;
HEIGHT BLOCKS BR_BLKS BR_ROWS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
3 8 2 0 0 1 0
发现压缩的确释放掉叶子节点的BLOCK以及索引行数，但是为什么层数以及非叶子节点个数没有任何变化呢？---索引最好的维护方法是：REBUILD，压缩时小动作，REBUILD是物理上重建：
SQL> alter index idx_emp rebuild;
索引已更改。
SQL> analyze index idx_emp validate structure;
索引已分析
SQL> select height,blocks,br_blks,br_rows,lf_rows,lf_blks,DEL_LF_ROWS
2 from index_stats;
HEIGHT BLOCKS BR_BLKS BR_ROWS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- -----------
1 8 0 0 0 1 0
如果是在线系统需要使用ONLINE：
ALTER INDEX idx_emp rebuild online;
否则很可能报错，因为REBUILD重建时需要表处于静态情况下完成的（没有未提交的事务），ONLINE代表找到一个事务中的空隙去执行REBUILD操作。
最后做个测试，当数据大量删除后，MOVE表和TRUNCATE表是否需要维护索引：
SQL> create index idx_t2 on t2(deptno);
索引已创建。
SQL> analyze index idx_t2 validate structure;
索引已分析
SQL> select height,blocks,LF_ROWS,LF_BLKS,BR_BLKS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
2 16 5000 10 1 0
SQL> select status from user_indexes where index_name='IDX_T2';
STATUS
--------
VALID
SQL> SELECT OBJECT_ID,DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OWNER='SCOTT' AND OBJECT_NAME='IDX_T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
58675 58675
SQL> delete from t2;
已删除5000行。
SQL> commit;
提交完成。
SQL> analyze index idx_t2 validate structure;
索引已分析
SQL> select height,blocks,LF_ROWS,LF_BLKS,BR_BLKS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
2 16 5000 10 1 5000
SQL> alter table t2 move;
表已更改。
SQL> analyze index idx_t2 validate structure;
索引已分析
SQL> select height,blocks,LF_ROWS,LF_BLKS,BR_BLKS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
2 16 5000 10 1 5000
--这里MOVE表后，索引没有直接维护，所以MOVE后索引需要手工维护。
SQL> truncate table t2;
表被截断。
SQL> analyze index idx_t2 validate structure;
索引已分析
SQL> select height,blocks,LF_ROWS,LF_BLKS,BR_BLKS,DEL_LF_ROWS from index_stats;
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
1 8 0 1 0 0
SQL> SELECT OBJECT_ID,DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OWNER='SCOTT' AND OBJECT_NAME='IDX_T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
58675 58677TRUNCATE后，连带索引一起自动维护了，所以TRUNCATE可以不用重新REBUILD索引。
关于索引的规则后面专门说明一些说明：INDEX UNQUE SCAN、INDEX RANGE SCAN、INDEX FAST FULL SCAN、INDEX FULL SCAN、INDEX SKIP SCAN，因为ORACLE SQL优化并不是一两句话可以说明白的，也不是肯定怎么优化就是正确的，很多时候要结合实际问题实际分析才可以解决问题，这里说明只是说明索引应当定期进行相应的维护，尤其是表在做一些大量变化后。
3、UNDO SEGMENT
首先看下系统那些段类型：
SQL> select distinct segment_type from dba_segments;
SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE PARTITION
NESTED TABLE
ROLLBACK
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
CLUSTER
TYPE2 UNDO
可以看到有ROLLBACK、TYPE2 UNDO两类回滚段信息，貌似看不懂，那么看下这两类回滚段是什么：
SQL> SELECT segment_type,segment_name,tablespace_name from dba_segments
2 where segment_type in('ROLLBACK','TYPE2 UNDO');
SEGMENT_TYPE SEGMENT_NAME TABLESPACE_NAME
------------------ ----------------------------- ---------------
ROLLBACK SYSTEM SYSTEM
TYPE2 UNDO _SYSSMU1$ UNDOTBS1
TYPE2 UNDO _SYSSMU2$ UNDOTBS1
TYPE2 UNDO _SYSSMU3$ UNDOTBS1
TYPE2 UNDO _SYSSMU4$ UNDOTBS1
TYPE2 UNDO _SYSSMU5$ UNDOTBS1
TYPE2 UNDO _SYSSMU6$ UNDOTBS1
TYPE2 UNDO _SYSSMU7$ UNDOTBS1
TYPE2 UNDO _SYSSMU8$ UNDOTBS1
TYPE2 UNDO _SYSSMU9$ UNDOTBS1
TYPE2 UNDO _SYSSMU10$ UNDOTBS1
此时可以发现ROLLBACK是SYSTEM系统类并存在于SYSTEM表空间的一个回滚段，它不对外公开的，用于系统在创建和删除对象时对数据字典生成回滚段信息，而我们使用的是默认在UNDOTBS1表空间默认创建的回滚段，用另一种方式看下再用的回滚段信息：
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
已选择11行。
SQL> select tablespace_name,segment_name,status
2 from dba_rollback_segs
3 order by 1;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ --------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
回滚段用来干什么呢？
1、事务的回退：事务内部要么全部成功，要么全部失败，以前的数据会保存在回退段中，保证可以回退。
2、系统的恢复：当系统宕机、SHUTDOWN ABORT并启动时，此时通过日志将提交的事务提交写入数据文件，未提交的事务用回滚段信息覆盖回去。
3、读一致性，两个会话之间读取彼此隔离，一个会话未提交的事务，另一个回话无法读取；大的SELECT语句，以起始SCN号码为基准读取数据，若数据被修改，即使COMMIT，也从回滚段中获取，若还未读到那个数据，回滚段被冲掉，那么将会报错。
4、闪回历史数据信息，通过回滚段可以对历史数据进行闪回操作，ORACLE 10G已经非常容易简单。
--简单事务实验：
SQL> SELECT xidusn from v$transaction;
未选定行
SQL> update scott.emp set sal=100 where rownum=1;
已更新 1 行。
SQL> SELECT xidusn from v$transaction;
XIDUSN
----------
9
此时说明此事务使用的回滚段为9号回滚段，即：_SYSSMU9$。那么回滚段如何管理：
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
undo_management=AUTO，代表回滚段为自动管理，undo_retention=900,代表15分钟（900秒）内数据是肯定可以在回滚段中找到的，但是超过15分钟要看运气，因为它被冲掉的可能性也不一定15分钟马上就被冲掉了，undo_tablespace就不用多说了。
此时看下10g一般是怎么回滚的（9i比较麻烦一点，需要通过dbms_flashback包去做）：
首先修改一个表：
SQL> update scott.t1 set sal=1000 where empno=7369;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select versions_starttime,versions_endtime,
2 versions_xid,versions_operation,t1.*
3 FROM scott.T1 versions between timestamp minvalue and maxvalue
4 where empno=7369;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ------- ---------- ---------- --------- ---------- -------------- ---------- ---------- --
16-8月 -10 02.56.09 下午 07001600C6320100 U 7369 SMITH CLERK 7902 17-12月-80 1000 20
16-8月 -10 02.56.09 下午 7369 SMITH CLERK 7902 17-12月-80 2000 20
可以看到回滚段中的数据、被修改的时间、被修改的事务号码、做的何种操作。
通过事务号码查询要进行回滚操作的SQL语句：
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID='07001600C6320100';
UNDO_SQL
-------------------------------------------------------------------------------------
update "SCOTT"."T1" set "SAL" = '2000' where ROWID = 'AAAOYlAAGAAAAQ0AAA';
通过表名称回滚数据信息：
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='T1';
UNDO_SQL
-----------------------------------------------------------------------------
update "SCOTT"."T1" set "SAL" = '2000' where ROWID = 'AAAOYlAAGAAAAQ0AAA';
按照指定时间点查询数据：
SQL> select * from SCOTT.t1
2 as of timestamp to_timestamp('2010-08-16 14:56:08','YYYY-MM-DD HH24:MI:SS');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 2000 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
回滚数据：
SQL> alter table scott.t1 enable row movement;
表已更改。
SQL> flashback table scott.t1 to timestamp to_timestamp('2010-08-16 14:56:08','YYYY-MM-DD HH24:MI:SS');
闪回完成。
SQL> alter table scott.t1 disable row movement;
表已更改。
也可以通过上面的闪回SQL去进行回滚，另外可以发现的是闪回SQL是按照ROWID去完成的，所以如果数据发生错乱，一定不要执行MOVE、SHRINK SPACE等等操作，否则就麻烦了。
回滚段常规管理中：
创建一个自己的回滚表空间：
SQL> create undo tablespace ud datafile 'D:/ORACLE/ORADATA/ORCL102/uu.dd' size 2m;
表空间已创建。
SQL> select tablespace_name,segment_name,status
2 from dba_rollback_segs
3 order by 1;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UD _SYSSMU19$ OFFLINE
UD _SYSSMU18$ OFFLINE
UD _SYSSMU17$ OFFLINE
UD _SYSSMU16$ OFFLINE
UD _SYSSMU20$ OFFLINE
UD _SYSSMU14$ OFFLINE
UD _SYSSMU13$ OFFLINE
UD _SYSSMU12$ OFFLINE
UD _SYSSMU11$ OFFLINE
UD _SYSSMU15$ OFFLINE
SQL> alter system set undo_tablespace=UD;
系统已更改。
SQL> select * from v$rollname;
USN NAME
---------- -------------------
0 SYSTEM
11 _SYSSMU11$
12 _SYSSMU12$
13 _SYSSMU13$
14 _SYSSMU14$
15 _SYSSMU15$
16 _SYSSMU16$
17 _SYSSMU17$
18 _SYSSMU18$
19 _SYSSMU19$
20 _SYSSMU20$
SQL> select tablespace_name,segment_name,status
2 from dba_rollback_segs
3 order by 1;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ -------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1$ OFFLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ -------
UD _SYSSMU19$ ONLINE
UD _SYSSMU18$ ONLINE
UD _SYSSMU17$ ONLINE
UD _SYSSMU16$ ONLINE
UD _SYSSMU20$ ONLINE
UD _SYSSMU14$ ONLINE
UD _SYSSMU13$ ONLINE
UD _SYSSMU12$ ONLINE
UD _SYSSMU11$ ONLINE
UD _SYSSMU15$ ONLINE
此时系统的回滚段就切换到UD中来了，做一个非常小的回滚段表空间，先将系统的回滚段表空间设置回去，然后将UD删掉，并物理上删除文件：
SQL> create undo tablespace ud datafile 'D:/ORACLE/ORADATA/ORCL102/uu.dd2' size 128k;
表空间已创建。
SQL> alter system set undo_tablespace=ud;
系统已更改。
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
此时发现没有自己创建的回滚段信息，因为128K太小了，无法弄，那么此时修改操作会出现什么情况？
SQL> update scott.emp set sal=2000 where empno=7369;
update scott.emp set sal=2000 where empno=7369
*
第 1 行出现错误:
ORA-01552: 非系统表空间 'USERS' 不能使用系统回退段
SQL> alter database datafile 'D:/ORACLE/ORADATA/ORCL102/uu.dd2' resize 2m;
数据库已更改。
SQL> update scott.emp set sal=2000 where empno=7369;
已更新 1 行。
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
11 _SYSSMU11$
12 _SYSSMU12$不够用的时候自动就扩大了，当然是保证表空间有空间的情况。
当认为干预回滚段表空间管理后，此时可以根据需要在表空间内部创建回滚段以及设置某个大的提交指定回滚段，一面造成回滚段不够出现的错误：
设置事务的回滚段为某一个指定的回滚段，可以手工调整这个回滚段的属性：
SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment
回滚段内部以区位单位进行事务的写操作，每个块内部最多包含一个事务的信息，顺序按照EXTENTS去编写回滚信息，写满后若收到空间限制，将会从新写第一个EXTENT。
设置一个回滚段的回收信息，当回滚段需要回收时，是否进行回收后到那个位置由参数OPTIMAL 决定的：
ALTER ROLLBACK SEGMENT rollback_segment STORAGE(OPTIMAL 1M);
设置某个回滚段是否启用，前面是以表空间为单位，这里是以段为单位：
ALTER ROLLBACK SEGMENT rollback_segment ONLINE|OFFLINE;
手工压缩回滚段信息：
ALTER ROLLBACK SEGMENT rollback_segment SHRINK TO 2M;
若不加2M参数将会自动以OPTIMIAL参数为基准进行压缩。
删除某回滚段信息：
DROP ROLLBACK SEGMENT rollback_segment ;
不过现在基本都是自动管理，自动管理下对于ORA-015555发生概率要低很多了。
4、LOBSEGMENT、LOB PARTITION、LOBINDEX、INDEX PARTITION
本来写这篇文章写到上面就差不多了，不过由于上次我在工作中远程和另外几个子系统的同事处理一个严重的表空间浪费的问题，刚开始我由于在远程，所以很多情况不是很清楚，就知道表空间浪费很严重，也只大概清楚使用情况，后面对内部的SEGMENTS使用情况排序了一下，发现尽然是LOB，在这个问题的解决过程中发现诸多的一些规律和过程，并根据部分规律推算到有几个表，一个表占用120G左右的空间，而且还释放不掉，以及一些ORACLE对于BLOB和CLOB的空间管理秘密吧，这里给大伙分享一下。
首先我们来创建一个具有BLOB和CLOB的表：
SQL> conn scott/a
已连接。
SQL> drop table t1;
表已删除。
SQL> purge table t1;
表已清除。
SQL> create table t1(c1 number,c2 clob,c3 blob);
表已创建。
此时查看一下当前用户的段信息由哪些：
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS
2 FROM dba_segments
3 where owner='SCOTT';
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------- ------------------ ----------
DEPT TABLE 8
EMP TABLE 8
BONUS TABLE 8
SALGRADE TABLE 8
TEST TABLE 8
V_USER_OBJECTS TABLE 1536
V_DBA_OBJECTS TABLE 1536
TEST_OBJECTS TABLE 32
TABLE_01 TABLE 8
TABLE_02 TABLE 8
PERSON TABLE 8
TABLE_AJ_TEST TABLE 8
AAA TABLE 8
LODER_EMP TABLE 8
PK_DEPT INDEX 8
PK_EMP INDEX 8
DEPT1 TABLE 8
SYS_IL0000052116C00003$$ LOBINDEX 8
SYS_LOB0000052116C00003$$ LOBSEGMENT 8
T1 TABLE 8
SYS_IL0000052116C00002$$ LOBINDEX 8
SYS_LOB0000052116C00002$$ LOBSEGMENT 8
注意：红色的是我们创建的表，蓝色的是什么呢？就是BLOB和CLOB，这么怪异的名字是怎么个组合方式，我琢磨了半天，去尝试发现到了，这个段名字首先以“SYS_”开头，标注为“IL”代表是LOB字段的索引名字（因为LOB是单独存放的），标注为“LOB”代表为LOB字段的实际存储，接着是10位数字编码，这10位是以OBJECT_ID为标准左边补充0直到10位（这部分就不是DATA_OBJECT_ID了，ORACLE使用OBJECT_ID的其中一个意义也在于此，就是逻辑引用对象不会发生改变，这样在做表的MOVE、TRUNCATE的时候，相应的LOB应用无需跟着变化），上述的T1表的OBJECT_ID也就是：52116，可以通过DBA_OBJECTS WHERE OBJECT_ID=对应值，进行测试。另外C开头后携带5位数字编码代表的是这个对象或者说是这个表的第几个字段，如何看第几个字段：DESC的顺序输出，或者DBA_TAB_COLUMNS中的COLUMN_ID字段确定顺序号码。当然你也可以通过第三方工具查看，它已经为你完成中间的排序过程。此时很容易发现就是：
SYS_IL0000052116C00003$$ T1表第三个字段LOB字段，它是这个LOB字段的索引段。
SYS_LOB0000052116C00003$$ T1表第三个字段LOB的信息存储字段。
SYS_IL0000052116C00002$$ T1表第二个字段为LOB字段，它是这个LOB字段的索引段。
SYS_LOB0000052116C00002$$ 表是第二个字段LOB的信息存储字段。
分析完后，下面写一个过程来模拟插入BLOB和CLOB数据信息：
首先为了加载外部文件先要创建一个文件加载的字典信息，我们将其默认到C盘根目录下：
SQL> CREATE DIRECTORY BFILE_DATA AS 'c:/';
目录已创建。
在这个目录下，我放置了一个图片信息，名称为：xieyu.jpg，此时创建一个过程方便我们测试，代码如下：
CREATE OR REPLACE PROCEDURE P_ADD_INFO(key_id NUMBER)
IS
V_FILE BFILE := BFILENAME('BFILE_DATA', 'xieyu.jpg');
V_CLOB CLOB := EMPTY_CLOB();
V_BLOB BLOB := EMPTY_BLOB();
V_DESC_OFF NUMBER;
V_SRC_OFF NUMBER:=1;
V_APPEND_INFO VARCHAR2(256) := 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxfdfdfsdxxxxxfdfdddddddddxxfdfdfsddddddddddddddddddddddddddddd';
BEGIN
INSERT INTO t1 VALUES(key_id,V_CLOB,V_BLOB);
DBMS_LOB.CREATETEMPORARY(V_CLOB, TRUE);
DBMS_LOB.CREATETEMPORARY(V_BLOB, TRUE);
DBMS_LOB.OPEN(V_CLOB, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(V_BLOB, DBMS_LOB.LOB_READWRITE);
--这里是为了将数据多复制几次
FOR i IN 1..50 LOOP
DBMS_LOB.WRITE(V_CLOB, length(V_APPEND_INFO), DBMS_LOB.getlength(V_CLOB)+1, V_APPEND_INFO);
END LOOP;
dbms_lob.fileopen(V_FILE, dbms_lob.file_readonly);
V_DESC_OFF := dbms_lob.getlength(V_FILE);
DBMS_LOB.loadblobfromfile(V_BLOB,V_FILE,DBMS_LOB.lobmaxsize,dest_offset => V_DESC_OFF,src_offset => V_SRC_OFF);
UPDATE t1 SET c2 = V_CLOB,c3=V_BLOB WHERE c1 = key_id;
IF DBMS_LOB.ISOPEN(V_CLOB) = 1 THEN
DBMS_LOB.CLOSE(V_CLOB);
END IF;
dbms_lob.fileclose(V_FILE);
dbms_lob.close(V_BLOB);
END P_ADD_INFO;
创建完成过程后，就准备开始调用过程，我们的KEY是自己知道的一个关键字就行了，我们顺序写入1~10条信息，看看情况如何：
SQL> begin
2 for i in 1..10 loop
3 p_add_info(i);
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
此时再来看下段的变化情况：
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS
2 FROM dba_segments
3 where owner='SCOTT';
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------- ------------------ ----------
DEPT TABLE 8
EMP TABLE 8
BONUS TABLE 8
SALGRADE TABLE 8
TEST TABLE 8
V_USER_OBJECTS TABLE 1536
V_DBA_OBJECTS TABLE 1536
TEST_OBJECTS TABLE 32
TABLE_01 TABLE 8
TABLE_02 TABLE 8
PERSON TABLE 8
TABLE_AJ_TEST TABLE 8
AAA TABLE 8
LODER_EMP TABLE 8
PK_DEPT INDEX 8
PK_EMP INDEX 8
DEPT1 TABLE 8
SYS_IL0000052116C00003$$ LOBINDEX 8
SYS_LOB0000052116C00003$$ LOBSEGMENT 384
T1 TABLE 8
SYS_IL0000052116C00002$$ LOBINDEX 8
SYS_LOB0000052116C00002$$ LOBSEGMENT 32
好了，问题来了，当数据被删除的时候，数据能否像普通表一样压缩掉呢？做个测试吧：
SQL> DELETE FROM T1;
已删除10行。
SQL> commit;
提交完成。
SQL> alter table t1 move;
表已更改。
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS
2 FROM dba_segments
3 where owner='SCOTT';
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------- ------------------ ----------
DEPT TABLE 8
EMP TABLE 8
BONUS TABLE 8
SALGRADE TABLE 8
TEST TABLE 8
V_USER_OBJECTS TABLE 1536
V_DBA_OBJECTS TABLE 1536
TEST_OBJECTS TABLE 32
TABLE_01 TABLE 8
TABLE_02 TABLE 8
PERSON TABLE 8
TABLE_AJ_TEST TABLE 8
AAA TABLE 8
LODER_EMP TABLE 8
PK_DEPT INDEX 8
PK_EMP INDEX 8
DEPT1 TABLE 8
SYS_IL0000052116C00003$$ LOBINDEX 8
SYS_LOB0000052116C00003$$ LOBSEGMENT 384
T1 TABLE 8
SYS_IL0000052116C00002$$ LOBINDEX 8
SYS_LOB0000052116C00002$$ LOBSEGMENT 32
看来MOVE表对LOB字段毫无作用，通过SHRINK SPACE或着DEALLOCATE等操作结果也是一样没有任何效果，很纳闷，难道这块空间真的就浪费了吗？还是它被删除掉后可以重复利用？到底怎么删除才有效？
我们先看下它是否可以重复利用吧：
此时数据已经被删掉，如果可以重复利用，再插入10条相同的数据，应该对它的空间影响不大才是，我们怀着这样的结论做一下试验：
SQL> begin
2 for i in 1..10 loop
3 p_add_info(i);
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS
2 FROM dba_segments
3 where owner='SCOTT';
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------- ------------------ ----------
DEPT TABLE 8
EMP TABLE 8
BONUS TABLE 8
SALGRADE TABLE 8
TEST TABLE 8
V_USER_OBJECTS TABLE 1536
V_DBA_OBJECTS TABLE 1536
TEST_OBJECTS TABLE 32
TABLE_01 TABLE 8
TABLE_02 TABLE 8
PERSON TABLE 8
TABLE_AJ_TEST TABLE 8
AAA TABLE 8
LODER_EMP TABLE 8
PK_DEPT INDEX 8
PK_EMP INDEX 8
DEPT1 TABLE 8
SYS_IL0000052116C00003$$ LOBINDEX 8
SYS_LOB0000052116C00003$$ LOBSEGMENT 768
T1 TABLE 8
SYS_IL0000052116C00002$$ LOBINDEX 8
SYS_LOB0000052116C00002$$ LOBSEGMENT 48
看来不会重复利用这些空间，或者说很大部分不能重复利用，再来一次一样的实验：
SQL> delete from t1;
已删除10行。
SQL> commit;
提交完成。
SQL> begin
2 for i in 1..10 loop
3 p_add_info(i);
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS
2 FROM dba_segments
3 where owner='SCOTT';
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------- ------------------ ----------
DEPT TABLE 8
EMP TABLE 8
BONUS TABLE 8
SALGRADE TABLE 8
TEST TABLE 8
V_USER_OBJECTS TABLE 1536
V_DBA_OBJECTS TABLE 1536
TEST_OBJECTS TABLE 32
TABLE_01 TABLE 8
TABLE_02 TABLE 8
PERSON TABLE 8
TABLE_AJ_TEST TABLE 8
AAA TABLE 8
LODER_EMP TABLE 8
PK_DEPT INDEX 8
PK_EMP INDEX 8
DEPT1 TABLE 8
SYS_IL0000052116C00003$$ LOBINDEX 8
SYS_LOB0000052116C00003$$ LOBSEGMENT 1024
T1 TABLE 8
SYS_IL0000052116C00002$$ LOBINDEX 8
SYS_LOB0000052116C00002$$ LOBSEGMENT 72
继续应征了它不可重复利用的事实，我们唯一可以做的就是手工回收：
1、 通过字段的SHRINK SPACE来完成
2、 删除表或者TRUNCATE表来完成
两者各有优缺点，如何根据实际业务来把握，等试验搞定后，我们来看下：
先测试下SHRINK SPACE：
SQL> delete from t1;
已删除10行。
SQL> commit;
提交完成。
SQL> alter table t1 modify lob(c2) (shrink space);
表已更改。
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS
2 FROM dba_segments
3 where owner='SCOTT';
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------- ------------------ ----------
DEPT TABLE 8
EMP TABLE 8
BONUS TABLE 8
SALGRADE TABLE 8
TEST TABLE 8
V_USER_OBJECTS TABLE 1536
V_DBA_OBJECTS TABLE 1536
TEST_OBJECTS TABLE 32
TABLE_01 TABLE 8
TABLE_02 TABLE 8
PERSON TABLE 8
TABLE_AJ_TEST TABLE 8
AAA TABLE 8
LODER_EMP TABLE 8
PK_DEPT INDEX 8
PK_EMP INDEX 8
DEPT1 TABLE 8
SYS_IL0000052116C00003$$ LOBINDEX 8
SYS_LOB0000052116C00003$$ LOBSEGMENT 1024
T1 TABLE 8
SYS_IL0000052116C00002$$ LOBINDEX 8
SYS_LOB0000052116C00002$$ LOBSEGMENT 8
果然搞定了，继续将其用于C3也是一样的效果，我们这次用TRUNCATE来做：
SQL> truncate table t1;
表被截断。
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS
2 FROM dba_segments
3 where owner='SCOTT';
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------- ------------------ ----------
DEPT TABLE 8
EMP TABLE 8
BONUS TABLE 8
SALGRADE TABLE 8
TEST TABLE 8
V_USER_OBJECTS TABLE 1536
V_DBA_OBJECTS TABLE 1536
TEST_OBJECTS TABLE 32
TABLE_01 TABLE 8
TABLE_02 TABLE 8
PERSON TABLE 8
TABLE_AJ_TEST TABLE 8
AAA TABLE 8
LODER_EMP TABLE 8
PK_DEPT INDEX 8
PK_EMP INDEX 8
DEPT1 TABLE 8
SYS_IL0000052116C00003$$ LOBINDEX 8
SYS_LOB0000052116C00003$$ LOBSEGMENT 8
T1 TABLE 8
SYS_IL0000052116C00002$$ LOBINDEX 8
SYS_LOB0000052116C00002$$ LOBSEGMENT 8
在SHRINK SPACE中我们很清楚他是将被删掉的行对应的LOB字段信息进行回收，上次对于远程的大表，我首选了這种方式去解决，在多次测试中我还是蛮有信心可以解决这个问题的，可以有些事实往往很难像试验那么顺利，因为表太大了，120G一个表，我们将这个命令在SQLPLUS下执行了一个通宵，最后窗口钉死在那里了也没有压缩完，也不知道后台成啥样子了，看来第一次失败了，于是考虑到后期维护方便，我们必须对这么大的表进行分割处理，即减小维护的粒度，做分区表。
注意：做完分区表后，对每一个分区有一个段，而LOB也是一样，每一个LOB字段对应每个分区都会产生一个LOB索引段和一个LOB段，这个测试细节就不多说了。
做完分区后对应的段类别为：LOB PARTITION 和INDEX PARTITION
根据现场的业务，保存将近3个月左右的信息，按照这类型的表的情况，首先创建了一个新的表空间，这个表空间的数据文件指定了足够的初始化大小以及手工指定为16K大小的BLOCKSIZE，专门用来存放LOB大字段信息，然后做分区表，将数据和索引信息按照原有规则防止与两个指定的表空间，LOB的放置于新创建这个表空间中，按照时间做了分区，将分区表命名为原表的名称的一个副本，然后白天将运行时数据的N-1一个分区开始后台复制（因为有时间的顺序性，除最后一个分区，其余的分区都是定好的数据），目标分区对于所有的LOB的STORAGE的大小进行初始化大小以及NEXT的设置来提高插入的效率，并设置PCTFREE为0来节约存储空间。
到晚上暂停掉和相关表的业务，将原表进行重命名，创建一个和原表同名的结构重启业务运行，这个过程大概几十秒钟搞定，保证业务正常运行，然后将原表最后一部分数据移植到分区表中，这个过程大概花了40多分钟，但是不影响正常运行，因为有一个临时创建的表正在运行，这个表在这段时间不会有太多数据，此时再次暂停掉这部分业务工作，将这个临时表的数据也转移到分区表中，然后将原表和这个临时表都删掉，将分区表命名为原表名称，中间总共停止业务时间不会超过2分钟，将120G的数据表完全移植到分区表中，经过测试，分区表通过对分区的管理（不过总体这个过程大概需要4个小时左右），不论使用SHRINK SPACE还是DROP、TRUNCATE都是非常快速的了，每次维护几乎都是瞬间完成，这几个大表的容量目前控制在40G左右的位置，将不可回收的资源可以自动或者手工的任意管理了。
我们在实际的生产运行中遇到的问题是和实验有些不一致，不过明白原理的基础上使用将会更加得心应手，在很多原理的基础上，构造一些自动化和半自动化的管理机制。
扫一扫
1528
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
