[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。^_^。
