Oracle高级压缩和透明数据加密组合实验

dingdingfish

已于 2024-09-05 17:11:52 修改

阅读量530

点赞数 20

分类专栏：数据库压缩文章标签： oracle database compression TDE redefinition

于 2024-09-05 16:54:31 首次发布

本文链接：https://blog.csdn.net/stevensxiao/article/details/141723825

版权

数据库压缩专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文参考了实验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)

dingdingfish

关注

20
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
Oracle高级压缩和透明数据加密组合实验

为单表BEGINEND;为多个表自动重新定义在线压缩。其实就是通过一个循环得到所有的普通表和分区表，然后逐个迁移。注意：此步骤可能需要一段时间。END LOOP;END;没有加快，反而变慢，也许和我的CPU和I/O有关，不再细究。
复制链接

扫一扫

专栏目录