711
魔獸
ORACLE--邏輯架構（二）
上一文中對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的最大值。這部分就不用做實驗了，可以自己測試即可。
對於分區表，在上一次已經有很詳細的說明https://blog.csdn.net/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 58677
TRUNCATE後，連帶索引一起自動維護了，所以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最後更新：2017-04-02 06:51:23
上一篇：
下一篇：