月归档：九月 2012

模拟普通ORA-08103并解决

发表于 2012 年 9 月 23 日由惜分飞

在上一篇中说到:模拟极端ORA-08103并解决,不能通过修改成坏块来解决,这里演示了是一个普通的数据块出现异常,然后通过bbed修改为坏块通过dbms_repair来解决该故障,补充说明:在11.2.0.3.3的库中,使用该方法不能重现该错误,而是直接提示ORA-01578,证明ORACLE的新版本在这一方面进行了改进
创建测试表

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t_xifenfei as
  2   select * from dba_objects where rownum<3000;

Table created.

SQL> SELECT owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS 
  2  FROM dba_extents 
  3  WHERE segment_name='T_XIFENFEI' AND owner='CHF';

OWNER                          SEGMENT_NAME     EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ --------------- ---------- ---------- ---------- ----------
CHF                            T_XIFENFEI               0          4         57          8
CHF                            T_XIFENFEI               1          4         65          8
CHF                            T_XIFENFEI               2          4         73          8
CHF                            T_XIFENFEI               3          4         81          8
CHF                            T_XIFENFEI               4          4         89          8
CHF                            T_XIFENFEI               5          4         97          8

6 rows selected.

SQL> SELECT DISTINCT dbms_rowid.rowid_block_number(rowid) blk#,
  2  dbms_rowid.rowid_relative_fno(rowid) file# 
  3   FROM t_xifenfei
  4   ORDER BY 2,1;

      BLK#      FILE#
---------- ----------
        60          4
        61          4
        62          4
        63          4
        64          4
        65          4
        66          4
        67          4
        68          4
        69          4
        70          4

      BLK#      FILE#
---------- ----------
        71          4
        72          4
        74          4
        75          4
        76          4
        77          4
        78          4
        79          4
        80          4
        81          4
        82          4

      BLK#      FILE#
---------- ----------
        83          4
        84          4
        85          4
        86          4
        87          4
        88          4
        90          4
        91          4
        92          4
        93          4
        94          4

      BLK#      FILE#
---------- ----------
        95          4
        96          4
        97          4
        98          4

37 rows selected.

模拟ORA-08103

SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

--破坏数据快(其实就是清空一个数据块block 95,注意dd和实际数据的block对应关系相差1)
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users01.dbf bs=8192 seek=95 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000187113 seconds, 43.8 MB/s

SQL> STARTUP
ORACLE instance started.

Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 104860124 bytes
Database Buffers 205520896 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI;
SELECT COUNT(*) FROM CHF.T_XIFENFEI
*
ERROR at line 1:
ORA-08103: object no longer exists

[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei file=/tmp/t_xifenfei.dmp

Export: Release 10.2.0.4.0 - Production on Fri Jan 13 22:09:43 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table T_XIFENFEI
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.

[oracle@xifenfei ~]$ expdp chf/xifenfei tables=t_xifenfei dumpfile=t_xifenfei.dmp

Export: Release 10.2.0.4.0 - Production on Friday, 13 January, 2012 22:10:26

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01": chf/******** tables=t_xifenfei dumpfile=t_xifenfei.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "CHF"."T_XIFENFEI" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
/u01/oracle/oracle/product/10.2.0/db_1/rdbms/log/t_xifenfei.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:10:50

测试证明当出现ORA-08103的时候,全表扫描,exp,expdp均不能正常工作

找到出现ORA-08103数据块

SQL> alter session set max_dump_file_size=unlimited;

Session altered.

SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';

Session altered.

SQL>  alter session set events '10200 trace name context forever, level 1';

Session altered.

SQL> alter session set events '8103 trace name errorstack level 3';

Session altered.

SQL> alter session set events '10236 trace name context forever, level 1';

Session altered.

SQL>  alter session set tracefile_identifier='ORA8103';

Session altered.


SQL> select * from chf.t_xifenfei;

……………………

ERROR:
ORA-08103: object no longer exists

2700 rows selected.


--在trace文件结尾发现如下记录,表示读到这个数据块时发生错误
KTRVAC: path typ=0, rdba=100005f


SQL> select to_number('100005f','xxxxxxxxxxxxx') from dual;

TO_NUMBER('100005F','XXXXXXXXXXXXX')
------------------------------------
                            16777311

SQL> select
  2  dbms_utility.data_block_address_file(16777311) FILE_NO,
  3  dbms_utility.data_block_address_block(16777311) BLOCK_NO
  4  from dual;

   FILE_NO   BLOCK_NO
---------- ----------
         4         95

bbed继续破坏异常块

BBED> set filename '/u01/oracle/oradata/XFF/users01.dbf'
        FILENAME        /u01/oracle/oradata/XFF/users01.dbf

BBED> set block 95
        BLOCK#          95

BBED> map
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95                                    Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (00)


BBED> set count 32
        COUNT           32

BBED> d
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets:    0 to   31           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

--这个就是和11gr2的区别,在11g中使用该方法来模拟ORA-08103,直接提示坏块,从而不会出现ORA-08103
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/users01.dbf
BLOCK = 95


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 1
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> d offset 8180
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 01010101 offset 8188
BBED-00215: editing not allowed in BROWSE mode


BBED> set mode edit
        MODE            Edit

--修改sumcheck
BBED> m /x 01010101 offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01010101 

 <32 bytes per line>

BBED> sum
Check value for File 0, Block 95:
current = 0x0000, required = 0x0000

测试修改为坏块效果

SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 95)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'


SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
      2918

通过让ORA-08103对应的块变为真正的坏块,然后使用dbms_repair或者event来跳过坏块,达到拯救数据的目的

发表在 ORA-xxxxx | 标签为 ORA-08103 | 评论关闭

模拟极端ORA-08103并解决

发表于 2012 年 9 月 23 日由惜分飞

ORA-08103错误在数据库日程运维和异常恢复中都可能遇到,出现该错误的原因很多,有很多情况下(模拟普通ORA-08103并解决),直接通过修改块使其在数据库查询的时候表标志为坏块,然后使用event或者dbms_repair包来标志该块,然后跳过就可以解决该问题,但是有些时候,遇到极端情况,该方法会失效,需要借助极端工具来处理该极端问题.
分析表相关EXTENT

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t_xifenfei  as
  2   select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     74504

SQL> select data_object_id,object_id from dba_objects where object_name='T_XIFENFEI';

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         75592      75592

SQL> select file_id,block_id,block_id+blocks-1
  2      from dba_extents
  3   where segment_name ='T_XIFENFEI' AND owner='CHF';

   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         4        680               687
         4        688               695
         4        696               703
         4        704               711  <---注意
         4        712               719
         4        720               727
         4        728               735
         …………
24 rows selected.

SQL> Select segment_name,header_file,header_blocK
  2  from dba_segments where
  3  segment_name in ('T_XIFENFEI') and owner='CHF'
  4  ;

SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
T_XIFENFEI                               4          682

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 682;

System altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28933.trc

--trace文件
Dump of memory from 0xB6CFD600 to 0xB6CFF600
B6CFD600 0000A223 010002AA 000DB4EA 04010000  [#...............]
B6CFD610 00008ECF 00000000 00000000 00000000  [................]
B6CFD620 00000000 00000018 00000480 00000A9C  [................]
B6CFD630 00000017 0000003F 00000080 010007BF  [....?...........]
B6CFD640 00000000 00000017 00000000 0000043F  [............?...]
B6CFD650 00000000 00000000 00000000 00000017  [................]
B6CFD660 0000003F 00000080 010007BF 00000000  [?...............]
B6CFD670 00000017 00000000 0000043F 01000780  [........?.......]
B6CFD680 01000780 00000000 00000000 00000000  [................]
B6CFD690 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
B6CFD6D0 00000001 00002000 00000000 00001434  [..... ......4...]
B6CFD6E0 00000000 010002A9 00000001 01000781  [................]
B6CFD6F0 010002A9 00000000 00000000 00000000  [................]
B6CFD700 00000000 00000000 00000018 00000000  [................]
B6CFD710 00012748 10000000 010002A8 00000008  [H'..............]
B6CFD720 010002B0 00000008 010002B8 00000008  [................]
B6CFD730 010002C0 00000008 010002C8 00000008  [................]   <----dump中找到下面值
……

  Extent Map
  -----------------------------------------------------------------
   0x010002a8  length: 8     
   0x010002b0  length: 8     
   0x010002b8  length: 8     
   0x010002c0  length: 8     <-----选择第四个exent
   0x010002c8  length: 8     
   …………   
  
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x010002a8 Data dba:  0x010002ab
   Extent 1     :  L1 dba:  0x010002a8 Data dba:  0x010002b0
   Extent 2     :  L1 dba:  0x010002b8 Data dba:  0x010002b9
   Extent 3     :  L1 dba:  0x010002b8 Data dba:  0x010002c0  <---同上
   Extent 4     :  L1 dba:  0x010002c8 Data dba:  0x010002c9
   Extent 5     :  L1 dba:  0x010002c8 Data dba:  0x010002d0
   …………
  --------------------------------------------------------

--确定Extent 3的记录在file 4 block 682 的偏移量为304

------省略了相同部分B6CFD
SQL> SELECT TO_NUMBER('730','XXXXX') FROM DUAL;

TO_NUMBER('730','XXXXX')
------------------------
                    1840

SQL> SELECT TO_NUMBER('600','XXX') FROM DUAL;

TO_NUMBER('600','XXX')
----------------------
                  1536

SQL> SELECT 1840-1536 FROM DUAL;

 1840-1536
----------
       304

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

这里我们假设修改SEGMENT HEADER中关于EXTENT 3(从0开始计数)的映射地址,从而来使得该表在查询的时候出现ORA-08103错误

bbed修改相关值

[oracle@xifenfei ~]$ bbed parfile=bbed.par
BBED> SET MODE EDIT
        MODE            Edit

BBED> INFO
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     4  /u01/oracle/oradata/ora11g/users01.dbf                               0

BBED> SET FILE 4 BLOCK 682
        FILE#           4
        BLOCK#          682

BBED> D
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 23a20000 aa020001 eab40d00 00000104 cf8e0000 ………… 

 <32 bytes per line>

BBED> m /x 1100
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 11000000 aa020001 eab40d00 00000104 cf8e0000 ………… 

 <32 bytes per line>

BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 11000000 aa020001 eab40d00 00000104 cf8e0000 ………… 

 <32 bytes per line>

BBED> sum apply
Check value for File 4, Block 682:
current = 0x2cfd, required = 0x2cfd

重现ORA-08103

SQL> startup
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             239078260 bytes
Database Buffers           67108864 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
                     *
ERROR at line 1:
ORA-08103: object no longer exists

定位坏块位置

SQL> alter session set max_dump_file_size=unlimited;

Session altered.

SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';

Session altered.

SQL> alter session set events '10200 trace name context forever, level 1';

Session altered.

SQL> alter session set events '8103 trace name errorstack level 3';

Session altered.

SQL> alter session set events '10236 trace name context forever, level 1';

Session altered.

SQL> alter session set tracefile_identifier='ORA8103';

Session altered.

SQL> select * from chf.t_xifenfei;
select * from chf.t_xifenfei
                  *
ERROR at line 1:
ORA-08103: object no longer exists

--trace文件关键内容
block_row_dump:
tab 0, row 0, @0x1f70
tl: 48 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 9]  44 49 43 54 2e 42 41 53 45
col  1: [ 1]  32
col  2: [32]

…………

end_of_block_dump
 
The buffer with tsn: 0 rdba: 0x00400321 has already been dumped
 
The buffer with tsn: 4 rdba: 0x010002aa was pinned,
but could not be dumped

SQL> Select to_number('010002aa','xxxxxxxxxxxxxxxxxx') from dual;

TO_NUMBER('010002AA','XXXXXXXXXXXXXXXXXX')
------------------------------------------
                                  16777898

SQL> select 
  2  dbms_utility.data_block_address_file(16777898) FILE_NO,
  3  dbms_utility.data_block_address_block(16777898) BLOCK_NO
  4  from dual;

   FILE_NO   BLOCK_NO
---------- ----------
         4        682

检查坏块

[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 13 18:03:13 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Block Checking: DBA = 16777898, Block Type = Unlimited data segment header with flg blks
Incorrect total map count: 24
Page 682 failed with check code 17006


DBVERIFY - Verification complete

Total Pages Examined         : 2240
Total Pages Processed (Data) : 1421
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 199
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 229
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 391
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 898278 (0.898278)

--对应块
SQL> select 
  2  dbms_utility.data_block_address_file(16777898) FILE_NO,
  3  dbms_utility.data_block_address_block(16777898) BLOCK_NO
  4  from dual;

   FILE_NO   BLOCK_NO
---------- ----------
         4        682

通过这里dbv检查发现,该数据库已经是坏块了,也就是说,网上流传的人工标志为坏块的方法在此处行不通,这里我们考虑使用dul等类此工具挖取数据,来拯救数据

dul和odu常规操作

DUL> unload table chf.t_xifenfei;
. unloading table                T_XIFENFEI
DUL: Warning: Using data objno from segment header( 16779199) instead of expected id (75592) from dictionary

DUL: Error: No entry in control file for block: ts# = 4 rfile# = 0 block# = 1087
DUL: Error: While processing unknown file block# 1087
DUL: Error: Could not read/parse data block


ODU> unload table chf.t_xifenfei

Unloading table: T_XIFENFEI,object ID: 75592
Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0)
corrupted block
0 rows unloaded

通过上面的测试证明,在该中情况下(SEGMENT HEADER)出现异常时,dul/odu均不能使用最常规的方法挖的数据

扫描数据文件方式挖
因为odu在这个方面的操作人性化于dul,所以只用odu进行相关测试,dul肯定能过实现相同功能

ODU> SCAN EXTENT TABLESPACE 4 PARALLEL 2

scan extent start: 2012-9-23 2:47:51
scanning extent...
scanning extent finished.
scan extent completed: 2012-9-23 2:47:51

ODU> unload table chf.t_xifenfei object  75592

Unloading table: T_XIFENFEI,object ID: 75592
Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0)
74504 rows unloaded

试验证明通过odu扫描表空间/数据文件找回来所有的数据,然后truncate掉问题表,导入该数据库,问题可以得意顺利解决

发表在 ORA-xxxxx | 标签为 ORA-08103 | 评论关闭

EXADATA EHCC初试

发表于 2012 年 9 月 21 日由惜分飞

今天有幸见识了下EXADATA的强大功能之一EHCC(Exadata Hybrid Columnar Compression),发现压缩效果确实很让人心动,压缩效率大大超过我的预计,压缩97%左右(1-628.1875/20573)
创建模拟表T_FF_SOURCE

14:32:52 SQL> create table t_FF_source as select * from dba_objects;

Table created.

Elapsed: 00:00:00.16

14:35:54 SQL> begin
14:37:05   2  for i in 1..100000 loop
14:37:05   3     insert into t_FF_source select * from dba_jects;
14:37:05   4    commit;
14:37:05   5   end loop;
14:37:05   6  end;
14:37:05   7   /

Elapsed: 00:13:07.76

14:51:05 SQL> select count(*) from t_FF_source;

  COUNT(*)
----------
 197015655

Elapsed: 00:00:33.18


14:51:56 SQL>  col segment_name format a45 heading "Segment Name"
14:52:55 SQL> select segment_name Segment_Name
14:52:55   2  ,      segment_type             "Segment Type"
14:52:55   3  ,      round(sum(bytes)/1024/1024/1024,2)     "Size In GB"
14:52:55   4  from dba_segments
14:52:55   5  where 
14:52:56   6  segment_name ='T_FF_SOURCE'
14:52:56   7  group by segment_name,segment_type
14:52:56  8  order by 1;

Segment Name                                  Segment Type       Size In GB
--------------------------------------------- ------------------ ----------
T_FF_SOURCE                                   TABLE                   20.09

创建各种情况下压缩表

--BASE
create table t_FF_c  compress  NOLOGGING as select /*+ PARALLE 24*/ * from t_FF_source;

--OLTP
create table t_FF_c_o compress for oltp NOLOGGING as select /*+ PARALLE 24 */ * from t_FF_source;

--QUERY LOW
create table t_FF_q_l  compress for query low  NOLOGGING as select /*+ PARALLE 24 */ * from t_FF_source;

--QUERY HIGH
create table t_FF_q_h compress for query high parallel 24 nologging as select /*+ PARALLE 12 */ * from t_FF_source;

--ARCHIVE LOW
create table t_FF_a_l compress for archive low parallel 24 nologging as select /*+ PARALLE 12 */ * from t_FF_source;

--ARCHIVE HIGH
create table t_FF_a_h compress for archive high  parallel 24 nologging as select  /*+ PARALLE 12 */ * from t_FF_source;

其实BASE和OLTP是数据库基本的压缩功能,该功能不仅限于EXADATA,但是后面的四种压缩就是我们所说的EHCC,也只有EXADATA用户才能够体验到.

数据压缩结果

16:19:13 SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for 
16:19:20   2  from dba_segments s,dba_tables t
16:19:20   3  where s.owner=t.owner and t.table_name=s.segment_name 
16:19:20   4  and s.owner='FF' and t.table_name like 'T_FF%';

OWNER                          SEGMENT_NAME                            T_SIZE COMPRESS_FOR
------------------------------ ----------------------------------- ---------- ------------
FF                             T_FF_A_L                              1244.625 ARCHIVE LOW
FF                             T_FF_SOURCE                              20573
FF                             T_FF_Q_H                              1244.875 QUERY HIGH
FF                             T_FF_A_H                              628.1875 ARCHIVE HIGH
FF                             T_FF_C                                6961.625 BASIC
FF                             T_FF_Q_L                              2799.875 QUERY LOW
FF                             T_FF_C_O                             7759.1875 OLTP

试验结果证明
1.BASE也OLTP的压缩效率差不多(可能是因为BASIC的PCTFREE为0,OLTP的PCTFREE为10)
2.在EHCC的四种压缩中:QUERY LOW相对压缩率不高,采用LZO压缩算法,但是也比ORACLE自带的压缩效果高很多
3.QUERY HIGH和ARCHIVE LOW压缩率差不多,都是使用ZLIB压缩算法
4.ARCHIVE HIGH是压缩率极高,采用Bzip2压缩算法实现.

EXADATA EM性能监控