本文参考了实验DB Security - Advanced Compression with Transparent Data Encryption(TDE)，其申请地址在这里。
本文只使用了实验中关于高级压缩和在线重定义的部分。并对要点进行说明及对实验进行了简化。
准备：环境设置
原文中的实验环境实际上是改自Oracle示例Sample Schema，其实唯一的改动就是去掉了SALES表中的分区的压缩属性。
git clone --depth 1 --branch v19c https://github.com/oracle-samples/db-sample-schemas.git
cd db-sample-schemas/
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
然后，如前所说。需要修改文件db-sample-schemas/sales_history/csh_v3.sql，去掉压缩设置：
$ grep COMPRESS csh_v3.sql.orig
...
PCTFREE 5 NOLOGGING NOCOMPRESS
(TO_DATE('1996-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1997-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1997-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1998-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1998-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1998-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1998-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1999-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1999-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1999-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('1999-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
(TO_DATE('2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
...
先将文件备份一份，然后用vi命令s/ COMPRESS//g就可以了。
为了方便比较，我们单独建立一个PDB，并且只导入SH schema。
建立PDB:
create pluggable database pdb1 admin user pdbadmin identified by Welcome1;
alter pluggable database pdb1 open;
alter pluggable database pdb1 save state;
alter session set container=pdb1;
create tablespace TEST_DATA datafile 'test_data.dbf' size 10m autoextend on extent management local uniform size 512K;
导入数据到SH schema：
SQL> !pwd
/home/oracle/db-sample-schemas/sales_history
SQL> alter session set container=pdb1;
Session altered.
SQL> @sh_main
specify password for SH as parameter 1:
Enter value for 1: ********
specify default tablespace for SH as parameter 2:
Enter value for 2: TEST_DATA
specify temporary tablespace for SH as parameter 3:
Enter value for 3: temp
specify password for SYS as parameter 4:
Enter value for 4: ********
-- directory path 是指数据库中建立的DIRECTORY对象
-- data file是需要数据泵导入的文件：sale1v3.dat
specify directory path for the data files as parameter 5:
Enter value for 5: /home/oracle/db-sample-schemas/sales_history/
writeable directory path for the log files as parameter 6:
Enter value for 6: /u01/app/oracle/product/19c/dbhome_1/demo/schema/log/
specify version as parameter 7:
Enter value for 7: v3
specify connect string as parameter 8:
Enter value for 8: localhost:/pdb1
创建索引，也许是为演示索引压缩：
create index sh.sales_cust_channel_promo_idx on sh.sales(cust_id, channel_id, promo_id);
查看当前SH schema中的对象：
connect sh/Welcome1@localhost:/pdb1
col table_name for a30
set line 2000 pages 2000
select table_name, num_rows from user_tables order by table_name;
TABLE_NAME NUM_ROWS
------------------------------ ----------
CAL_MONTH_SALES_MV 48
CHANNELS 5
COSTS 82112
COUNTRIES 23
CUSTOMERS 55500
DR$SUP_TEXT_IDX$I
DR$SUP_TEXT_IDX$K
DR$SUP_TEXT_IDX$N
DR$SUP_TEXT_IDX$U
FWEEK_PSCAT_SALES_MV 11266
PRODUCTS 72
PROMOTIONS 503
SALES 918843
SALES_TRANSACTIONS_EXT 916039
SUPPLEMENTARY_DEMOGRAPHICS 4500
TIMES 1826
16 rows selected.
各类对象空间占用情况：
select SEGMENT_TYPE,count(*),sum(bytes/(1024*1024)) SIZE_MB
from dba_segments
where TABLESPACE_NAME in ('TEST_DATA')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION', 'INDEX PARTITION', 'INDEX')
group by SEGMENT_TYPE order by SEGMENT_TYPE;
SEGMENT_TYPE COUNT(*) SIZE_MB
------------------ ---------- ----------
INDEX 19 36.5
INDEX PARTITION 112 56
TABLE 10 21
TABLE PARTITION 32 46
记录表空间当前大小，171M。
$ ls -sh /u01/app/oracle/product/19c/dbhome_1/dbs/test_data.dbf
171M /u01/app/oracle/product/19c/dbhome_1/dbs/test_data.dbf
gzip可将其压缩到1/10：
$ cp /u01/app/oracle/product/19c/dbhome_1/dbs/test_data.dbf /tmp
$ gzip /tmp/test_data.dbf
$ ls -sh /tmp/test_data.dbf.gz
17M /tmp/test_data.dbf.gz
任务 1：压缩顾问估计段压缩率
connect system/Welcome1@localhost:/pdb1
create tablespace comp_data_ts datafile size 10M autoextend on extent management local uniform size 512K default table compress for oltp;
select tablespace_name, encrypted from dba_tablespaces where tablespace_name = 'COMP_DATA_TS';
TABLESPACE_NAME ENC
------------------------------ ---
COMP_DATA_TS NO
表空间还未加密，需要将其加密，过程略。达到以下效果就可以了：
TABLESPACE_NAME ENC
------------------------------ ---
COMP_DATA_TS YES
在表空间加密前后，数据文件大小没变，说明表空间加密不会增加额外的存储空间。：
$ ls -sh /u01/app/oracle/product/19c/dbhome_1/dbs/comp_data_ts.dbf
11M /u01/app/oracle/product/19c/dbhome_1/dbs/comp_data_ts.dbf
建议用离线加密，因为在线加密会移动数据文件的位置。
运行压缩顾问以实现高级行压缩：
SET SERVEROUTPUT ON
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_row_uncmp PLS_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(32767);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
scratchtbsname => 'COMP_DATA_TS' ,
ownname => 'SH' ,
objname => 'SALES' ,
subobjname => NULL ,
comptype => DBMS_COMPRESSION.COMP_ADVANCED,
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => DBMS_COMPRESSION.comp_ratio_minrows,
objtype => DBMS_COMPRESSION.objtype_table
);
DBMS_OUTPUT.put_line( 'Number of blocks used by the compressed sample of the object : ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line( 'Number of blocks used by the uncompressed sample of the object : ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in compressed sample of the object : ' || l_row_cmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in uncompressed sample of the object : ' || l_row_uncmp);
DBMS_OUTPUT.put_line( 'Estimated Compression Ratio of Sample : ' || l_cmp_ratio);
DBMS_OUTPUT.put_line( 'Compression Type : ' || l_comptype_str);
END;
/
输出为：
Number of blocks used by the compressed sample of the object : 640
Number of blocks used by the uncompressed sample of the object : 1728
Number of rows in a block in compressed sample of the object : 560
Number of rows in a block in uncompressed sample of the object : 207
Estimated Compression Ratio of Sample : 2.7
Compression Type : "Compress Advanced"
PL/SQL procedure successfully completed.
运行分区表压缩顾问：
SET SERVEROUTPUT ON
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_row_uncmp PLS_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(32767);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
scratchtbsname => 'COMP_DATA_TS' ,
ownname => 'SH' ,
objname => 'SALES' ,
subobjname => 'SALES_Q4_2001' ,
comptype => DBMS_COMPRESSION.COMP_ADVANCED,
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => DBMS_COMPRESSION.comp_ratio_minrows,
objtype => DBMS_COMPRESSION.objtype_table
);
DBMS_OUTPUT.put_line( 'Number of blocks used by the compressed sample of the object : ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line( 'Number of blocks used by the uncompressed sample of the object : ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in compressed sample of the object : ' || l_row_cmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in uncompressed sample of the object : ' || l_row_uncmp);
DBMS_OUTPUT.put_line( 'Estimated Compression Ratio of Sample : ' || l_cmp_ratio);
DBMS_OUTPUT.put_line( 'Compression Type : ' || l_comptype_str);
END;
/
输出为：
Number of blocks used by the compressed sample of the object : 128
Number of blocks used by the uncompressed sample of the object : 333
Number of rows in a block in compressed sample of the object : 538
Number of rows in a block in uncompressed sample of the object : 206
Estimated Compression Ratio of Sample : 2.6
Compression Type : "Compress Advanced"
PL/SQL procedure successfully completed.
运行压缩顾问以进行高级索引压缩。
SET SERVEROUTPUT ON
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_row_uncmp PLS_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(32767);
BEGIN
DBMS_COMPRESSION.get_compression_ratio (
scratchtbsname => 'COMP_DATA_TS',
ownname => 'SH',
objname => 'SALES_CUST_CHANNEL_PROMO_IDX',
subobjname => NULL,
comptype => DBMS_COMPRESSION.COMP_INDEX_ADVANCED_LOW,
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => DBMS_COMPRESSION.comp_ratio_minrows,
objtype => DBMS_COMPRESSION.objtype_index
);
DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);
DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);
DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);
DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);
END;
/
输出为：
Number of blocks used (compressed) : 1468
Number of blocks used (uncompressed) : 2852
Number of rows in a block (compressed) : 626
Number of rows in a block (uncompressed) : 322
Compression ratio : 1.9
Compression type : "Compress Advanced Low"
PL/SQL procedure successfully completed.
任务 2：压缩选项 1：离线压缩方法
注意：离线压缩使用 ALTER TABLE MOVE 语法将数据段移动到不同的表空间。在数据段重组期间，由于段上的底层序列化机制，应用程序需要停机。此外，我们需要重建与移动的数据段相关的所有索引。
在线压缩使用 Oracle 在线重定义技术。对于在高级压缩和任何其他数据重组操作期间寻求最短停机时间的客户，建议采用这种方法。在线重定义只需要在很短的时间内锁定底层段以同步最终更改以完成段重组。
您可以从 MOS 说明中了解有关在线重新定义的更多信息：
- 如何在在线时压缩表（文档 ID 1353967.1）
- 主要说明：在线重新定义表 (DBMS_REDEFINITION) 概述（文档 ID 1357825.1）
- 使用“DBMS_REDEFINITION.REDEF_TABLE”进行在线重新定义（文档 ID 2412059.1）
这个实验设计还是挺巧妙的，实现将一些不能在线重定义的表先行离线迁移了。没有主键的表和物化视图都不支持在线重定义。
ALTER TABLE SH.CAL_MONTH_SALES_MV MOVE TABLESPACE COMP_DATA_TS ROW STORE COMPRESS ADVANCED;
ALTER TABLE SH.FWEEK_PSCAT_SALES_MV MOVE TABLESPACE COMP_DATA_TS ROW STORE COMPRESS ADVANCED;
ALTER TABLE SH.DR$SUP_TEXT_IDX$K MOVE TABLESPACE COMP_DATA_TS ROW STORE COMPRESS ADVANCED;
ALTER TABLE SH.DR$SUP_TEXT_IDX$U MOVE TABLESPACE COMP_DATA_TS ROW STORE COMPRESS ADVANCED;
对没有主键的表或物化视图做在线重定义，会报错如下：
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5565
ORA-12092: cannot online redefine replicated table "SH"."CAL_MONTH_SALES_MV"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4684
ORA-06512: at "SYS.DBMS_REDEFINITION", line 285
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4680
ORA-12089: cannot online redefine table "SH"."CAL_MONTH_SALES_MV" with no
primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 285
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4655
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5657
ORA-06512: at line 9
ORA-06512: at line 9
还有，物化视图和表可以同名。实际上是创建物化视图的同时，Oracle自动创建了同名的表。毕竟物化视图需要一个地方存数据。
任务 3：压缩选项 2：通过在线重新定义 REDEF_TABLE 进行在线压缩
在线压缩前检查段表空间信息：
set pagesize 20
col segment_name for a50
set pages 9999
col segment_name for a40
set lines 100
select segment_name, segment_type, tablespace_name from dba_segments where owner='SH';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------------------------------------- ------------------ ------------------------------
CHANNELS TABLE TEST_DATA
PROMOTIONS TABLE TEST_DATA
CUSTOMERS TABLE TEST_DATA
TIMES TABLE TEST_DATA
PRODUCTS TABLE TEST_DATA
COUNTRIES TABLE TEST_DATA
SUPPLEMENTARY_DEMOGRAPHICS TABLE TEST_DATA
DR$SUP_TEXT_IDX$I TABLE TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
SALES TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
COSTS TABLE PARTITION TEST_DATA
PROMO_PK INDEX TEST_DATA
TIMES_PK INDEX TEST_DATA
PRODUCTS_PK INDEX TEST_DATA
PRODUCTS_PROD_STATUS_BIX INDEX TEST_DATA
PRODUCTS_PROD_SUBCAT_IX INDEX TEST_DATA
PRODUCTS_PROD_CAT_IX INDEX TEST_DATA
CUSTOMERS_PK INDEX TEST_DATA
CHANNELS_PK INDEX TEST_DATA
COUNTRIES_PK INDEX TEST_DATA
SYS_IL0000075000C00006$$ LOBINDEX TEST_DATA
SYS_IOT_TOP_75004 INDEX TEST_DATA
DR$SUP_TEXT_IDX$X INDEX TEST_DATA
CUSTOMERS_GENDER_BIX INDEX TEST_DATA
CUSTOMERS_MARITAL_BIX INDEX TEST_DATA
CUSTOMERS_YOB_BIX INDEX TEST_DATA
SALES_CUST_CHANNEL_PROMO_IDX INDEX TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_PROD_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_CUST_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_TIME_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_CHANNEL_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
SALES_PROMO_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_PROD_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
COSTS_TIME_BIX INDEX PARTITION TEST_DATA
SYS_LOB0000075000C00006$$ LOBSEGMENT TEST_DATA
CAL_MONTH_SALES_MV TABLE COMP_DATA_TS
FWEEK_PSCAT_SALES_MV TABLE COMP_DATA_TS
171 rows selected.
为单表SH.PRODUCTS重新定义表在线压缩:
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => 'SH',
tname => 'PRODUCTS',
table_compression_type => 'ROW STORE COMPRESS ADVANCED',
table_part_tablespace => 'COMP_DATA_TS',
index_key_compression_type => 'COMPRESS ADVANCED LOW',
index_tablespace => 'COMP_DATA_TS',
lob_compression_type => 'COMPRESS HIGH',
lob_tablespace => 'COMP_DATA_TS',
lob_store_as => 'SECUREFILE');
END;
/
输出为：
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.65
在目标表空间中确认表和索引均已迁移成功：
SQL> select segment_name, segment_type, tablespace_name from dba_segments where owner='SH' and tablespace_name in ('COMP_DATA_TS');
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------------------------------------- ------------------ ------------------------------
CAL_MONTH_SALES_MV TABLE COMP_DATA_TS
FWEEK_PSCAT_SALES_MV TABLE COMP_DATA_TS
PRODUCTS TABLE COMP_DATA_TS
PRODUCTS_PK INDEX COMP_DATA_TS
PRODUCTS_PROD_STATUS_BIX INDEX COMP_DATA_TS
PRODUCTS_PROD_SUBCAT_IX INDEX COMP_DATA_TS
PRODUCTS_PROD_CAT_IX INDEX COMP_DATA_TS
7 rows selected.
为多个表自动重新定义在线压缩。其实就是通过一个循环得到所有的普通表和分区表，然后逐个迁移。注意：此步骤可能需要一段时间。
DECLARE
v_table_name VARCHAR2(100);
BEGIN
FOR rec IN (SELECT segment_name FROM dba_segments WHERE owner = 'SH' AND segment_type = 'TABLE' AND tablespace_name='TEST_DATA' UNION SELECT distinct table_name FROM dba_tab_partitions WHERE table_owner='SH' AND tablespace_name='TEST_DATA')
LOOP
v_table_name := rec.segment_name;
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => 'SH',
tname => v_table_name,
table_compression_type => 'ROW STORE COMPRESS ADVANCED',
table_part_tablespace => 'COMP_DATA_TS',
index_key_compression_type => 'COMPRESS ADVANCED LOW',
index_tablespace => 'COMP_DATA_TS',
lob_compression_type => 'COMPRESS HIGH',
lob_tablespace => 'COMP_DATA_TS',
lob_store_as => 'SECUREFILE');
END;
END LOOP;
END;
/
输出为：
PL/SQL procedure successfully completed.
Elapsed: 00:03:58.74
确认：
select segment_name, segment_type, tablespace_name from dba_segments where owner='SH' and tablespace_name in ('COMP_DATA_TS');
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------------------------------------- ------------------ ------------------------------
CAL_MONTH_SALES_MV TABLE COMP_DATA_TS
FWEEK_PSCAT_SALES_MV TABLE COMP_DATA_TS
PRODUCTS TABLE COMP_DATA_TS
CHANNELS TABLE COMP_DATA_TS
COUNTRIES TABLE COMP_DATA_TS
CUSTOMERS TABLE COMP_DATA_TS
PROMOTIONS TABLE COMP_DATA_TS
SUPPLEMENTARY_DEMOGRAPHICS TABLE COMP_DATA_TS
TIMES TABLE COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
COSTS TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
SALES TABLE PARTITION COMP_DATA_TS
PRODUCTS_PK INDEX COMP_DATA_TS
PRODUCTS_PROD_STATUS_BIX INDEX COMP_DATA_TS
PRODUCTS_PROD_SUBCAT_IX INDEX COMP_DATA_TS
PRODUCTS_PROD_CAT_IX INDEX COMP_DATA_TS
CHANNELS_PK INDEX COMP_DATA_TS
COUNTRIES_PK INDEX COMP_DATA_TS
CUSTOMERS_GENDER_BIX INDEX COMP_DATA_TS
CUSTOMERS_MARITAL_BIX INDEX COMP_DATA_TS
CUSTOMERS_YOB_BIX INDEX COMP_DATA_TS
CUSTOMERS_PK INDEX COMP_DATA_TS
PROMO_PK INDEX COMP_DATA_TS
SALES_CUST_CHANNEL_PROMO_IDX INDEX COMP_DATA_TS
TIMES_PK INDEX COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
SALES_CHANNEL_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_PROD_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
COSTS_TIME_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROD_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_CUST_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
SALES_PROMO_BIX INDEX PARTITION COMP_DATA_TS
166 rows selected.
任务 4：Oracle TDE 压缩的空间优势
比较压缩前后的空间使用情况：
select SEGMENT_TYPE,count(*),sum(bytes/(1024*1024)) SIZE_MB
from dba_segments
where TABLESPACE_NAME in ('COMP_DATA_TS')
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION', 'INDEX PARTITION', 'INDEX')
group by SEGMENT_TYPE order by SEGMENT_TYPE;
SEGMENT_TYPE COUNT(*) SIZE_MB
------------------ ---------- ----------
INDEX 13 22.5
INDEX PARTITION 112 56
TABLE 9 11
TABLE PARTITION 32 44
-- 以下是之前的数据
/*
SEGMENT_TYPE COUNT(*) SIZE_MB
------------------ ---------- ----------
INDEX 19 36.5
INDEX PARTITION 112 56
TABLE 10 21
TABLE PARTITION 32 46
*/
数据文件大小由171M减到143M：
$ ls -sh /u01/app/oracle/product/19c/dbhome_1/dbs/comp_data_ts.dbf
143M /u01/app/oracle/product/19c/dbhome_1/dbs/comp_data_ts.dbf
任务 5：通过在线重新定义启用会话并行性以加快压缩过程
在离线或在线压缩前，可以加入以下语句：
ALTER SESSION ENABLE PARALLEL DML ;
ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 2;
以之前的在线重定义所有表为例，从3分58秒变为了7分钟：
PL/SQL procedure successfully completed.
Elapsed: 00:07:17.14
没有加快，反而变慢，也许和我的CPU和I/O有关，不再细究。
参考
- Primary Note: Overview of Oracle Tablespace Management (Doc ID 1493350.1)