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
