[20150727]exadata压缩HCC与dataguard.txt

[20150727]exadata压缩HCC与dataguard.txt

--并不是为了使用这个功能，想测试一下这个功能在普通的dg上是否正常。

1.测试环境:

XXXX@zzzz1> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

XXXX@zzzz1> create table t as select * from dba_objects ;
Table created.

XXXX@zzzz1> ALTER TABLE t MOVE  COMPRESS FOR ARCHIVE HIGH ;
Table altered.

XXXX@zzzz1> create table tx as select * from dba_objects ;
Table created.

--分析表。
XXXX@zzzz1> select table_name,blocks from dba_tables where owner=user and table_name in ('T','TX');
TABLE_NAME     BLOCKS
---------- ----------
TX               1346
T                  73

--TX 占用11M，T 占用 640K，节约不少空间。

XXXX@zzzz1> select * from t where rownum<=1;
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SYS    ICOL$                                   20              2 TABLE               2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID   N N N          1

--而在dg上执行：
XXXX@zzzzdg2> select * from t where rownum<=1;
select * from t where rownum<=1
              *
ERROR at line 1:
ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

--看来使用这个功能要注意！除非你的dg也是exadata。

2.继续测试：
XXXX@zzzz1> ALTER TABLE t MOVE  COMPRESS FOR QUERY LOW ;
Table altered.

XXXX@zzzz1> select table_name,blocks from dba_tables where owner=user and table_name in ('T','TX');
TABLE_NAME     BLOCKS
---------- ----------
TX               1346
T                 165

XXXX@zzzzdg2> select * from t where rownum<=1;
select * from t where rownum<=1
              *
ERROR at line 1:
ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
--依旧。

XXXX@zzzz1> ALTER TABLE t MOVE COMPRESS FOR ARCHIVE LOW ;
Table altered.

XXXX@zzzz1> select table_name,blocks from dba_tables where owner=user and table_name in ('T','TX');
TABLE_NAME     BLOCKS
---------- ----------
TX               1346
T                  79

XXXX@zzzzdg2> select * from t where rownum<=1;
select * from t where rownum<=1
              *
ERROR at line 1:
ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

--以下这些都不行。不再测试了。
SQL> ALTER TABLE t MOVE COMPRESS FOR QUERY LOW ;
SQL> ALTER TABLE t MOVE COMPRESS FOR QUERY HIGH ;
SQL> ALTER TABLE t MOVE COMPRESS FOR ARCHIVE LOW ;
SQL> ALTER TABLE t MOVE COMPRESS FOR ARCHIVE HIGH ;

--看来oracle一些特性是不能乱用的，除非你的dg也是exadata。^_^。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1753362/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1753362/