how to check a partition have been compressed [message #527933] |
Thu, 20 October 2011 20:48 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Dear all,
How can i check a partition whether it has been compressed?
just as flowing test,i can not get the information about partition P_L1 whether been compressed.
SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';
COMPRESS PARTITION_NAME
-------- ------------------------------
DISABLED P_L1
DISABLED P_L2
DISABLED P_L3
DISABLED P_L4
SQL> Alter Table tb_hxl_list compress;
Table altered.
SQL> Alter Table TB_HXL_LIST
2 Move Partition P_L1 compress;
Table altered.
SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';
COMPRESS PARTITION_NAME
-------- ------------------------------
ENABLED P_L1
ENABLED P_L2
ENABLED P_L3
ENABLED P_L4
|
|
|
|
|
|
Re: how to check a partition have been compressed [message #528026 is a reply to message #527962] |
Fri, 21 October 2011 05:17 |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Hi,
Michel Cadot,Thanks a lot!
SQL> Declare
2 Rid Rowid;
3 n Number;
4 Begin
5 Select Max(Rowid) Into Rid From Tb_Hxl_List Partition(p_L3);
6 n := Dbms_Compression.Get_Compression_Type(User, 'TB_HXL_LIST', Rid);
7 Dbms_Output.Put_Line(n);
8 End;
9 /
2 -- COMP_FOR_OLTP p_L3 have been compressed
PL/SQL procedure successfully completed.
SQL> Declare
2 Rid Rowid;
3 n Number;
4 Begin
5 Select Max(Rowid) Into Rid From Tb_Hxl_List Partition(p_L4);
6 n := Dbms_Compression.Get_Compression_Type(User, 'TB_HXL_LIST', Rid);
7 Dbms_Output.Put_Line(n);
8 End;
9 /
1 -- COMP_NOCOMPRESS P_L4 have not been compressed
PL/SQL procedure successfully completed.
|
|
|
|