更多精彩內容請光臨

《Oracle 資料結構知多少（二）》

一開場白

上週剛剛完成了“Oracle 資料結構（一）”，重點講述了資料塊的儲存屬性，空閒空間合併，segment-extent-block 相互關係等，主要說明了extent和block在資料庫上的作用和為什麼要這麼設定。

感興趣的朋友，瀏覽《Oracle 資料結構知多少（一）》 http://space.itpub.net/26686207/viewspace-757937

本週又要繼續開始“Oracle 資料結構系列之二”重點講述表空間的儲存屬性，資料塊的壓縮，段空間儲存屬性等知識，歡迎廣大親們一起交流討論，營造一個專家圈子，一起更上一層樓。長話短說，進入正題。我們在上一節已經把segment-extent-block概念一一講明瞭，這裡呢我們主要講一講它們在生產庫上應用場景和如何設定

二資料庫版本

* from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux:Version 11.2.0.1.0 - Production

NLSRTL Version11.2.0.1.0 - Production

三實驗

1.建立表，分割槽表，大物件欄位，分別查詢出它們是否為段物件，給出SQL演示的整個過程。

段物件：Oracle所有分配儲存空間的物件都叫段物件。例如表索引分割槽大物件等都稱之為段物件

段物件是接近於應用層的，它是業務層面的一個含義，表空間邏輯上是由段物件組成的。

段的種類

資料段

LEO1@LEO1>create table t1 (x int,y int); 建立個表

Table created.

LEO1@LEO1>create index idx_t1 on t1(x); 表上建立個索引

Index created.

LEO1@LEO1>insert into t1 values(1,1); 只有插入資料後oracle才會真正建立索引

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from t1;

X Y

----------------------------------

1 1

select segment_name,segment_type,tablespace_name,extents,blocks,bytes/1024/1024from dba_segments where segment_name in ('T1','IDX_T1');

SEGMENT_NAMESEGMENT_TYPE TABLESPACE_N EXTENTS BLOCKS BYTES/1024/1024

------------------------------------------ ------------------ ------------------------------- ------------------------------

T1 TABLE LEO1 1 8 .0625

IDX_T1 INDEX LEO1 1 8 .0625

上面的表和索引都佔用空間（多少extent block 容量），因此都可稱為段物件

臨時段

是臨時表和中間狀態的資料儲存放的位置，常用於sort hash merge，不用永久儲存，只做臨時儲存，臨時段組成了臨時表空間，只有在記憶體不夠時，oracle才會在臨時表空間上建立臨時段。

注意：臨時段上的操作不產生redo，因為無需保護，是一種中間狀態，這樣可以提高效率

LEO1@LEO1>select file#,name,status,enabled,bytes,blocks,block_size from v$tempfile;

FILE# NAME STATUS ENABLED BYTES BLOCKS BLOCK_SIZE

--------------------------------------------------------------------------------------------------------------------------------

1 /u01/app/oracle/oradata/LEO1/temp01.dbf ONLINE READ WRITE 128974848 15744 8192

經過檢視臨時段也分配儲存空間了，塊大小8k，15744個塊，大小為128974848位元組，因此說臨時表索引啊都可以稱之為段物件，臨時表的使用和演示後面進行，敬請期待！

回滾段

《Oracleundo我們需要掌握什麼》 http://space.itpub.net/26686207/viewspace-757488這篇文章對undo段做了非常詳細介紹，朋友們可以參考。

這裡我們只是演示一下undo段所佔用的空間

LEO1@LEO1>select tablespace_name,file_name,bytes/1024/1024,autoextensible fromdba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_N FILE_NAME BYTES/1024/1024 AUT

---------------------------------------------------------------------------------------------------------------------------------

UNDOTBS1 /u01/app/oracle/oradata/LEO1/undotbs01.dbf 165 YES

這就是undo表空間對應的undo資料檔案，在資料庫需要做回滾時就在undo表空間中建立undo段。

分割槽表的段物件

分割槽表中，一個分割槽對應一個段物件，如果有3個分割槽那麼這張分割槽表就包含3個段物件，這時表已經不是段物件了，倒成了一個標籤。

LEO1@LEO1>create tablespace par_data01 datafile size 10M autoextend off;

Tablespacecreated.

LEO1@LEO1>create tablespace par_data02 datafile size 10M autoextend off;

Tablespacecreated.

LEO1@LEO1>create tablespace par_data03 datafile size 10M autoextend off;

Tablespacecreated.

建立三個表空間分別儲存三個分割槽

LEO1@LEO1>create table part_leo (name varchar2(20),age number,part_date date);

Table created.

LEO1@LEO1>insert into part_leo values ('leonarding1',100,to_date('2001-01-01','yyyy-mm-dd'));

insert intopart_leo values ('leonarding2',200,to_date('2002-02-02','yyyy-mm-dd'));

insert intopart_leo values ('Alan1',300,to_date('2003-03-03','yyyy-mm-dd'));

insert intopart_leo values ('Alan2',400,to_date('2004-04-04','yyyy-mm-dd'));

insert intopart_leo values ('tigerfish1',500,to_date('2005-05-05','yyyy-mm-dd'));

insert intopart_leo values ('tigerfish2',600,to_date('2006-06-06','yyyy-mm-dd'));

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from part_leo;

NAME AGE PART_DATE

--------------------------------- ---------

leonarding1 100 01-JAN-01

leonarding2 200 02-FEB-02

Alan1 300 03-MAR-03

Alan2 400 04-APR-04

tigerfish1 500 05-MAY-05

tigerfish2 600 06-JUN-06

建立一個表並插入6行記錄，做分割槽表的原表

LEO1@LEO1>CREATE TABLE part_leo_super

PARTITION BY RANGE (part_date)

( PARTITION p1 VALUES LESS THAN(to_date('2002-12-01','yyyy-mm-dd'))

TABLESPACE par_data01, 比2002-12-01小的記錄放入p1分割槽

PARTITION p2 VALUES LESS THAN(to_date('2004-12-01','yyyy-mm-dd'))

TABLESPACE par_data02, 比2004-12-01小的記錄放入p2分割槽

PARTITION p3 VALUES LESS THAN(to_date('2006-12-01','yyyy-mm-dd'))

TABLESPACE par_data03, 比2006-12-01小的記錄放入p3分割槽

PARTITION other VALUES LESS THAN(maxvalue)

TABLESPACE par_data03) 餘下的記錄放入par_data03分割槽

as select * from part_leo; 2 3 4 5 6 7 8 9 10 11

Table created.

採用CTAS方式建立分割槽表，三個分割槽分別存放在三個表空間上

LEO1@LEO1>select * from part_leo_super partition (p1); 第一分割槽的資料

NAME AGE PART_DATE

--------------------------------- ---------

leonarding1 100 01-JAN-01

leonarding2 200 02-FEB-02

LEO1@LEO1>select * from part_leo_super partition (p2); 第二分割槽的資料

NAME AGE PART_DATE

--------------------------------- ---------

Alan1 300 03-MAR-03

Alan2 400 04-APR-04

LEO1@LEO1>select * from part_leo_super partition (p3); 第三分割槽的資料

NAME AGE PART_DATE

--------------------------------- ---------

tigerfish1 500 05-MAY-05

tigerfish2 600 06-JUN-06

LEO1@LEO1>select segment_name,partition_name,tablespace_name,extents,blocks,bytes/1024/1024from dba_segments where segment_name='PART_LEO_SUPER';

SEGMENT_NAME PARTITION_NAME TABLESPACE_N EXTENTS BLOCKS BYTES/1024/1024

----------------------------------------------------------------------------------------------------------------------------------------

PART_LEO_SUPER OTHER PAR_DATA03 1 8 .0625

PART_LEO_SUPER P1 PAR_DATA03 1 8 .0625

PART_LEO_SUPER P2 PAR_DATA03 1 8 .0625

PART_LEO_SUPER P3 PAR_DATA03 1 8 .0625

每個分割槽都有獨立的儲存空間，因此可以說每個分割槽都是一個段物件（PARTITION_NAME）PART_LEO_SUPER這個名在分割槽表中不算段名了可以看成一個總名稱！

大物件欄位的段物件

我們為什麼要建立大物件欄位，普通的varchar2欄位：最大支援4000個字元！

如果我們想在欄位中存放一篇小說，一個文章，一篇blog怎麼辦？大物件欄位就是解決這個問題的。

注：大物件與小物件欄位的長度不一致導致碎片的一個原因，一般把大物件單獨放在一個表空間中減少碎片的產生。

建立存放CLOB欄位的表空間clob_data

LEO1@LEO1>create tablespace clob_data datafile size 10M autoextend off;

Tablespacecreated.

建立含CLOB(character large object)欄位表

LEO1@LEO1>create table clob_table

(

name varchar2(20), 姓名

employment varchar2(30), 職業

school varchar2(30), 畢業院校

current_date date, 當前時間

discription clob 描述（字元型大物件欄位）

)

tablespace leo1 存放於leo1表空間

lob (discription) 指定大物件欄位

store as

(

tablespace clob_data 大物件存放的表空間

);

2 3 4 5 6 7 8 9 10 11 12 13 14

Table created.

我們透過下列資料字典找到剛剛建立的大物件

LEO1@LEO1>select table_name,column_name,segment_name,tablespace_name from dba_lobs wheretable_name='CLOB_TABLE';

TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_N

--------------------------------------------------------------------------------------------------------------------

CLOB_TABLE DISCRIPTION SYS_LOB0000074408C00005$$ CLOB_DATA

小結：由此看來大物件欄位也佔用表空間，凡是佔用空間的物件都可稱為段物件。

2.分別建立一個ASSM，MSSM管理的表空間。

MSSM—ManualSegment Space Management 手動段空間管理（手動設定物件的儲存屬性）

這種管理方式就是使用freelist管理資料塊的分配和回收，是一種只針對資料塊分配的管理方式，這種方式可以讓DBA有更大的空間管理餘地，更大自由發揮空間，在早期的Oracle上都是透過這種方式管理塊分配的。

場景：對於一些資料塊操作非常敏感的場合相對適用

引數：MSSM-由你設定freelists、freelistgroups、pctused、pctfree、initrans等引數來控制如何分配、使用段中的空間

缺點：1.需要設定更多的引數，例如上面所寫的引數，操作複雜度更強

2.引數設定值比較難評估，需要大量的測試過程

3.需要了解資料庫體系結構的DBA設定

注意：1.freelist空閒列表是放在段頭裡面的，如果有多個使用者同時訪問列表勢必會引發段頭爭用，導致“buffer busy waits”等待事件發生，建議多設幾個freelist，防止爭用。

ASSM—AutomaticSegment Space Management 自動段空間管理（自動設定物件的儲存屬性）

這種管理方式就是使用“點陣圖bitmap”管理資料塊的分配和回收，1為佔用塊不可分配，0為空閒塊可分配，由於計算機就是由二進位制編碼的，所以操作二進位制程式碼是非常快捷的。現在Oracle 10g 11g 預設值都是ASSM段空間管理方式。

場景：希望資料塊由Oracle自動分配管理的場合，不需要DBA介入太多

引數：ASSM-你只需控制一個引數pctfree，其他引數由Oracle自動管理，如果強行設定也將被忽略。

三層點陣圖模式管理段空間：第一層BMB（bit map block）記錄每個extent中資料塊的儲存資訊，只管理當前的extent內塊，放在extent頭中，這是leaf節點

第二層BMB管理第一層BMB記錄，這是branch節點

第三層BMB管理第二層BMB記錄，放在段頭中，這是root節點

ASSM段頭包含了每個Extent儲存資訊

Extent區頭包含BMB資訊

優點：1.自動化管理段空間，無需手動設定大量引數，簡化了操作

2.增大併發度，由於ASSM沒有freelist概念，也就沒有freelist列表爭用情況，也就沒有段頭爭用的情況，提高資源利用率。

缺點：1.全表掃描效能沒有MSSM模式下好

2.大資料載入，會導致效能下降，因為要自動維護點陣圖表，需要一定的開銷

3.影響索引的叢集因子（clustering factor）

(1) 設定db_create_file_dest引數

作用：指定Oracle建立某某檔案的存放路徑，設定後就可以在建立表空間時不指定檔案路徑了。

■ Datafiles
■ Tempfiles
■ Redo log files
■ Control files
■ Block change tracking files

LEO1@LEO1> showparameter db_create_file_dest

NAME TYPE VALUE

----------------------------------------------- ------------------------------

db_create_file_dest string

空，我們現在還沒有初始化這個引數值

LEO1@LEO1>alter system set db_create_file_dest='/u01/app/oracle/oradata/LEO1/';

System altered.

LEO1@LEO1> showparameter db_create_file_dest

NAME TYPE VALUE

----------------------------------------------- ------------------------------

db_create_file_dest string /u01/app/oracle/oradata/LEO1/

現在我們已經設定好了引數

(2)建立MSSM和ASSM表空間

LEO1@LEO1>create tablespace leo_mssm segment space management manual;

Tablespacecreated.

LEO1@LEO1>create tablespace leo_assm segment space management auto;

Tablespacecreated.

我們建立了一個MSSM段管理表空間又建立一個ASSM段管理表空間，預設大小100M

LEO1@LEO1>select file#,name,bytes/1024/1024 from v$datafile;

FILE# NAME BYTES/1024/1024

----------------------------------------------------------------------------------------------------------------------------------------------

9 /u01/app/oracle/oradata/LEO1/LEO1/datafile/o1_mf_leo_mssm_8p9jorpx_.dbf 100

10 /u01/app/oracle/oradata/LEO1/LEO1/datafile/o1_mf_leo_assm_8p9jplb8_.dbf 100

資料檔名是系統自定義的。

LEO1@LEO1>select tablespace_name,segment_space_management from dba_tablespaces wheretablespace_name in ('LEO_MSSM','LEO_ASSM');

TABLESPACE_N SEGMEN

----------------------------------

LEO_ASSM AUTO

LEO_MSSM MANUAL

小結：我們根據實際需要來建立與業務匹配的表空間，宗旨技術為業務服務。

3.驗證段儲存屬性和所在表空間儲存屬性的關係，給出SQL演示，並得出結論。

表空間儲存屬性依賴於段屬性，段儲存屬性依賴於資料塊屬性

表空間管理方式

本地管理表空間（Locally Managed Tablespace）：表空間儲存資訊寫在資料檔案頭中，其中段空間管理方式又分為ASSM和MSSM，這是oracle 預設的管理方式

好處：自動監控表空間上資料變化並進行調整

避免透過查詢資料字典獲得表空間的儲存資訊，可以直接在資料檔案頭中查詢，提高效率

建立表空間的時候使用關鍵字extent management local

字典管理表空間（Dictionary Managed Tablespace）：表空間儲存資訊寫在資料字典中，所有對錶空間的操作都要先對資料字典（system表空間）進行操作效率較低，現在已經不在用這種方式了。

建立表空間的時候使用關鍵字 extent management dictionary

tablespace local_manager datafile size 10m extentmanagement local;

Tablespacecreated.

tablespace dictionary_manager datafile size 10m extent managementdictionary;

create tablespacedictionary_manager datafile size 10m extent management dictionary

*

ERROR at line 1:

ORA-12913: Cannotcreate dictionary managed tablespace

tablespace_name,extent_management from dba_tablespaces wheretablespace_name in ('LOCAL_MANAGER','DICTIONARY_MANAGER');

TABLESPACE_NAME EXTENT_MAN

----------------------------------------

LOCAL_MANAGER LOCAL

O~MY GOD11.2.0.1.0 版本只能建立“本地管理表空間”不在允許建立“字典管理表空間”了，現在已經不用這種方式了，good 沒的選，我們繼續吧！

段管理方式

段空間管理方式：資料塊的分配與回收是由點陣圖管理 or FREELIST管理，這和extent管理方式沒關係

自動段空間管理ASSM：透過點陣圖方式管理段空間(點陣圖存放在段頭) 關鍵字segment space management auto（預設方式）

手動段空軍管理MSSM：透過FREELIST方式管理段空間(FREELIST存放在段頭，如果多個使用者同時訪問列表的話建議Freelist可設多個防止latch爭用) 關鍵字segment space management manual

tablespace assm_leo datafile size 10m segmentspace management auto;

Tablespacecreated.

tablespace mssm_leo datafile size 10m segmentspace management manual;

Tablespacecreated.

tablespace_name,extent_management,segment_space_management fromdba_tablespaces where tablespace_name in ('ASSM_LEO','MSSM_LEO');

TABLESPACE_NAME EXTENT_MAN SEGMEN

---------------------------------------- ---------------------------------- ----------

ASSM_LEO LOCAL AUTO

MSSM_LEO LOCAL MANUAL

預設都是“本地管理表空間”，第一個表空間是ASSM方式，第二個表空間是MSSM方式。

Extent管理（這和資料塊管理方式沒有關係），只是說明extent應該用什麼方式擴充套件

Autoallocate：系統自動分配區大小，分配的值不固定

Uniform：手動統一分配區大小，分配的值固定

tablespace auto_leo datafile size 10m autoallocate;

Tablespacecreated.

tablespace uniform_leo datafile size 10m uniform. size 2m;

Tablespacecreated.

dba_tablespaces where tablespace_name in ('AUTO_LEO','UNIFORM_LEO');

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTSMAX_EXTENTS ALLOCATIO

-------------------------------------------- ----------- ----------- ----------- ---------

AUTO_LEO 65536 1 2147483645 SYSTEM

UNIFORM_LEO 2097152 2097152 1 2147483645 UNIFORM

SYSTEM：系統自動分配區大小，分配值不固定

UNIFORM：手動統一分配區大小，每次固定分配2M

INITIAL_EXTENT：初始化區大小2M，在auto模式下只有初始化區大小，下次分配值不固定

NEXT_EXTENT：下次分配區大小2M，在uniform模式下這兩個引數值一樣

MIN_EXTENTS：最小區個數

MAX_EXTENTS：最大區個數

4.SQL演示臨時表的機制，並說明它的適用場合

臨時表：這個東東在我們日常工作中用的可能相對不多，但它的作用有時是無法代替的，在某些場景下臨時表可以提高大大的效率，俗話說好鋼用到刀刃上。臨時表也是臨時段的一種，因為它也需要放到臨時表空間裡。

特性：1.臨時表的回收和擴充套件效率要高一點，因為它不會產生redo，是一種中間狀態

2.臨時表結構與屬性和普通表一樣

3.臨時表也可以建立索引

4.臨時表只存在於某個會話或事務的生命週期裡，臨時表中資料只對當前會話可見

5.基於事務臨時表：當臨時表上某個事務提交/回滾後，自動清空臨時表資料（delete rows）

6.基於會話臨時表：當臨時表的會話結束後，自動清空臨時表資料（preserve rows）

場景：臨時表常被用於存放操作的中間狀態（資料處理的中間環節）例如 order by hash merge

會話147基於事務臨時表測試（Oracle預設是事務）

LEO1@LEO1>select distinct sid from v$mystat;

SID

-----------------

147

LEO1@LEO1> droptable leo8 purge; 清空環境

Table dropped.

LEO1@LEO1> droptable leo9 purge; 清空環境

Table dropped.

建立基於事務臨時表

LEO1@LEO1>create global temporary table leo8 on commit deleterows as select * from dba_objects;

Table created.

LEO1@LEO1>select count(*) from leo8;

COUNT(*)

----------

0

記錄數為0這是為神馬呢，原來這與基於事務臨時表特性有關，當事務提交/回滾後立刻清空表，而恰恰DDL操作會隱含一個commit動作自動提交，從而觸發了清空臨時表資料的動作，我們才看到記錄數為0。

LEO1@LEO1>insert into leo8 select * from dba_objects; 我們插入71976行

71976 rowscreated.

LEO1@LEO1>select count(*) from leo8; 此時我們已經看到有記錄數了

COUNT(*)

----------

71976

LEO1@LEO1>commit; 根據基於事務臨時表特性，只要提交事務後就會立即清空表資料

Commit complete.

LEO1@LEO1>select count(*) from leo8; 現在是空表了

COUNT(*)

----------

0

會話153基於會話臨時表測試

LEO1@LEO1>select distinct sid from v$mystat;

SID

-----------------

153

建立基於會話臨時表

LEO1@LEO1>create global temporary table leo9 on commit preserverows as select * from dba_objects;

Table created.

LEO1@LEO1>select count(*) from leo9;

COUNT(*)

----------

71976

基於會話臨時表不會跟著事務提交而自動清空，必須退出會話後才清空表，因此我們看到是有記錄數的。

LEO1@LEO1>insert into leo9 select * from dba_objects; 插入資料

71976 rowscreated.

LEO1@LEO1>select count(*) from leo9; 記錄數翻了一番

COUNT(*)

----------

143952

LEO1@LEO1>commit; 根據基於會話臨時表特性，提交事務不清空表資料

Commit complete.

LEO1@LEO1>select count(*) from leo9; 記錄數依然存在完好無損

COUNT(*)

----------

143952

LEO1@LEO1> exit 退出會話，當臨時表的會話結束後，立即清空表資料

[oracle@leonarding1~]$ sqlplus leo1/leo1 重新登陸

LEO1@LEO1>select count(*) from leo9; 現在是空表了

COUNT(*)

----------

0

臨時表索引建立測試

臨時表也可以建立索引的，建立的索引和普通索引一樣具有高效的檢索功能，只是臨時表索引會放在臨時表空間中，在user_indexes資料字典檢視中是查不到的。

我們給臨時表leo9的object_id列上建立一個索引idx_leo9

LEO1@LEO1> droptable leo9 purge;

Table dropped.

LEO1@LEO1>create global temporary table leo9 on commit preserve rows as select * fromdba_objects;

Table created.

LEO1@LEO1>create index idx_leo9 on leo9(object_id);

create indexidx_leo9 on leo9(object_id)

*

ERROR at line 1:

ORA-14452:attempt to create, alter or drop an index on temporary table already in use

攤上事了，攤上大事了！ORA-14452：企圖建立索引，修改或刪除一個索引時在臨時表上已經被使用

這是怎麼回事不讓建立臨時索引，並且這是一個新表並沒有索引存在

LEO1@LEO1>select object_id from user_objects where object_name in ('LEO9','IDX_LEO9');

OBJECT_ID

------------------

74414

LEO1@LEO1>select * from v$lock where id1=74414;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-------------------------------- ---------- -- ---------- ---------- ---------- -------------------- ----------

000000007D85A8F8000000007D85A950 142 TO 74414 1 3 0 2435 0

發生這種情況極有可能物件被鎖定了，我們檢查一下v$lock檢視，果不其然發現了一個3號鎖，官方文件上說3號鎖是一個行級排它鎖，可以DML但不能DDL，原因找到，剩下就是解決。

3

Row Exclusive Table Lock(RX)

又叫(SX)

行級排他鎖，通常已經有事務在修改行或者select…for update 修改結果集。允許其他事務對鎖定的表進行select insert update delete 或 lock table 同時鎖定一張表

Lock table t in row exclusive mode;

解決方案：退出會話再登入，讓臨時表清空所有資料，我們建立完臨時索引後再插入資料即可。

LEO1@LEO1> exit 退出

Disconnected fromOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options

[oracle@leonarding1~]$ sqlplus leo1/leo1 再登入

SQL*Plus: Release11.2.0.1.0 Production on Thu Apr 11 14:45:11 2013

Connected to:

Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options

LEO1@LEO1>create index idx_leo9 on leo9(object_id); 臨時索引成功建立

Index created.

LEO1@LEO1>select object_id,object_name,object_type,status,temporary from user_objectswhere object_name in ('LEO9','IDX_LEO9');

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS T

--------------------------------------------------------------------------------

74418 IDX_LEO9 INDEX VALID Y

74414 LEO9 TABLE VALID Y

我們在user_objects檢視上可以看到臨時表和臨時索引的狀態都是有效的，臨時標記=Y

LEO1@LEO1>select table_name,tablespace_name from user_tables where table_name='LEO9';

TABLE_NAME TABLESPACE_NAME

------------------------------------------------------------

LEO9

LEO1@LEO1>select index_name,tablespace_name from user_indexes whereindex_name='IDX_LEO9';

INDEX_NAME TABLESPACE_NAME

------------------------------------------------------------

IDX_LEO9

LEO1@LEO1>select segment_name,tablespace_name from user_segments where segment_name in('LEO9','IDX_LEO9');

no rows selected

我們在user_tables user_indexes user_segments 這3個檢視上是查不到leo9和idx_leo9的資訊的，這是為什麼呢？

答：它們只記錄資料段上的物件，對於臨時段和回滾段上的物件都不記錄，由於臨時表和臨時索引都是放在臨時表空間上面的，因此查詢不到是正常的。

小結：要謹記臨時表的特性，資料只儲存在一個會話或者一個事務的週期中，會話結束或者事務結束都會清空表資料，預設建立的臨時表是基於事務的。

5.示例演示資料壓縮的效果

資料壓縮是一個源遠流長的話題，在SAP SYBASE MYSQL NOSQL領域中都會看到它的身影，經常會聽到我們要壓縮，我們要壓縮，資料壓縮之後有什麼什麼好處等等！往往大家在用到某一個技術的時候經常會看到它的好處殊不知任何東東都有其雙面性，資料壓縮也一樣。我們先來看看它的應用場合

1.可以節約我們的儲存空間，這是大家經常會想到的理由

2.相同資料總量下，會減少處理的資料塊

包括記憶體佔用

I/O提速

查詢提速

例如300個資料塊壓縮成100資料塊，理論上效率會提高三倍！

3.資料壓縮使用的是一套“替換演算法”，就是把相同的字串用一個標籤代替，簡單的說就是一個去重的過程，使存在的字元總量減少達到總體容量減小的目的，記錄中重複資料越多壓縮效果越好，如果沒有重複資料就不會壓縮。

4.OLAP系統中常用，一次性把資料載入入庫，如果沒有後續的DML操作，那麼查詢起來是非常高效的。如果資料會頻繁的增刪改查，頻繁壓縮解壓定會消耗更多的資源和時間，尤其是CPU資源！

實驗

LEO1@LEO1>create table leo10 compress as select * fromdba_objects; 壓縮的表

Table created.

LEO1@LEO1>create table leo11 as select * from dba_objects; 沒有壓縮的表

Table created.

LEO1@LEO1>select segment_name,tablespace_name,extents,blocks,bytes from dba_segmentswhere segment_name in ('LEO10','LEO11');

SEGMENT_NAME TABLESPACE_NAME EXTENTS BLOCKS BYTES

--------------------------------------------- ---------- ---------- ---------------------------------------- ---------- ----------

LEO10 LEO1 18 384 3145728

LEO11 LEO1 24 1152 9437184

從分配的extents blocks bytes 指標上一眼就可以看出空間減少了3倍，效果出眾，毋庸置疑，這是因為在object_type owner等欄位上存在大量重複資料。

我們來看一下如何解壓表資料

LEO1@LEO1>select table_name,compression from dba_tables where table_name in('LEO10','LEO11');

TABLE_NAME COMPRESS

--------------------------------------

LEO10 ENABLED 壓縮版

LEO11 DISABLED 非壓縮版

LEO1@LEO1>alter table leo10 nocompress; 解壓leo10

Table altered.

LEO1@LEO1>select table_name,compression from dba_tables where table_name in('LEO10','LEO11');

TABLE_NAME COMPRESS

--------------------------------------

LEO10 DISABLED 完成解壓

LEO11 DISABLED

LEO1@LEO1>select segment_name,tablespace_name,extents,blocks,bytes from dba_segmentswhere segment_name in ('LEO10','LEO11');

SEGMENT_NAME TABLESPACE_NAME EXTENTS BLOCKS BYTES

--------------------------------------------- ---------- ---------- ----------

LEO10 LEO1 18 384 3145728

LEO11 LEO1 24 1152 9437184

leo10解壓後的資料儲存屬性和以前沒變化（原資料塊沒有被釋放），只對以後插入的資料生效。

如果你想解壓後立刻釋放資料塊可以採用 move 方式

LEO1@LEO1> alter table leo11 move compress; leo11壓縮

Table altered.

LEO1@LEO1>select segment_name,tablespace_name,extents,blocks,bytes from dba_segmentswhere segment_name in ('LEO10','LEO11');

SEGMENT_NAME TABLESPACE_NAME EXTENTS BLOCKS BYTES

--------------------------------------------- ---------- ---------- ----------

LEO10 LEO1 18 384 3145728

LEO11 LEO1 18 384 3145728

立刻壓縮

LEO1@LEO1> alter table leo11 move nocompress; leo11解壓

Table altered.

LEO1@LEO1>select segment_name,tablespace_name,extents,blocks,bytes from dba_segmentswhere segment_name in ('LEO10','LEO11');

SEGMENT_NAME TABLESPACE_NAME EXTENTS BLOCKS BYTES

--------------------------------------------- ---------- ---------- ----------

LEO10 LEO1 18 384 3145728

LEO11 LEO1 23 1024 8388608

立刻釋放

Move原理：就是把資料塊打散重新分配，重新整理，重新排序，要比直接壓縮/解壓消耗CPU I/O資源，好處就是立竿見影。根據業務需求來設定吧！呼哈哈

表空間級壓縮

答：1.如果建立一個具有壓縮屬性的表空間，那麼在此表空間上所有物件都自動繼承壓縮屬性。

2.可以在表空間級別上定義COMPRESS屬性，既可以在生成時利用CREATE TABLESPACE來定義，也可以稍後利用ALTER TABLESPACE來定義。

3.可以在一個表空間直接壓縮或解壓一個表，而無需考慮表空間級別上的COMPRESS屬性。

現在建立一個壓縮表空間並檢查其壓縮屬性

LEO1@LEO1>create tablespace compress_leo1 datafile size 10M default compress;

Tablespacecreated.

LEO1@LEO1>select tablespace_name,def_tab_compression from user_tablespaces where tablespace_name='COMPRESS_LEO1';

TABLESPACE_NAME DEF_TAB_

--------------------------------------

COMPRESS_LEO1 ENABLED

使現有表空間轉換為壓縮表空間

alter tablespacecompress_leo1 nocompress;

alter tablespacecompress_leo1 compress;

我們在COMPRESS_LEO1表空間上建立一個表看看是否自動繼承壓縮屬性

LEO1@LEO1>create table comp_leo tablespace compress_leo1 as select * from dba_objects;

Table created.

LEO1@LEO1>select table_name,compression from dba_tables where table_name in ('COMP_LEO');

TABLE_NAME COMPRESS

--------------------------------------

COMP_LEO ENABLED

表建立後自動成為壓縮版

段物件 ASSM MSSM 臨時表臨時索引壓縮表空間 uniform autoallocate

2013.4.13
天津&spring
分享技術~成就夢想
Blog：

【原創】Oracle 資料結構知多少（二）

相關文章