平凡的幸福(備站）

先建立一個沒有啟用壓縮功能的測試表格
SQL> create table test01
2 ( a varchar2(100),
3 b varchar2(20) );


確認壓縮功能沒有啟用
SQL> SELECT table_name --資料表名稱
2 ,compression --是否啟用壓縮
3 ,compress_for --壓縮類型
4 FROM dba_tables
5 where table_name='TEST01';

TABLE_NAME COMPRESSION COMPRESS_FOR
--------------- ---------------- ------------------------
TEST01 DISABLED


寫入資料
SQL> insert into test01 select object_name,object_id from dba_objects;
74838 rows created.

SQL> insert into test01 select object_name,object_id from dba_objects;
74838 rows created.

SQL> commit;
Commit complete.


查詢test01的大小
SQL> select segment_name,bytes,blocks
2 from dba_segments
3 where segment_name='TEST01';

SEGMENT_NAME BYTES BLOCKS
-------------------- ---------- ----------
TEST01 6291456 768


將test01資料刪除
SQL> truncate table test01;
Table truncated.


啟用test01的壓縮功能，並指定壓縮類型為OLTP
SQL> ALTER TABLE test01 COMPRESS FOR OLTP;
Table altered.


也可以在建立表格時指定，語法如下：
create table my_compressed_table
( col1 number(20), col2 varchar2(300), ... ) compress for all operations ;


確認縮壓功能有啟用且類型為OLTP
SQL> SELECT table_name
2 ,compression
3 ,compress_for
4 FROM dba_tables
5 where table_name='TEST01';

TABLE_NAME COMPRESSION COMPRESS_FOR
--------------- ---------------- ------------------------
TEST01 ENABLED OLTP


寫入資料到test01
SQL> insert into test01 select object_name,object_id from dba_objects;
74838 rows created.

SQL> insert into test01 select object_name,object_id from dba_objects;
74838 rows created.

SQL> commit;
Commit complete.


重新查詢資料大小，發現容量比未壓縮小
SQL> select segment_name,bytes,blocks
2 from dba_segments
3 where segment_name='TEST01';

SEGMENT_NAME BYTES BLOCKS
-------------------- ---------- ----------
TEST01 5242880 640