Home » RDBMS Server » Server Administration » how to check a partition have been compressed (11.2.0.1.0 Windos XP)
how to check a partition have been compressed [message #527933] Thu, 20 October 2011 20:48 Go to next message
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 #527947 is a reply to message #527933] Thu, 20 October 2011 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain your question, why "ENABLED" does not give you the information?

Regards
Michel
Re: how to check a partition have been compressed [message #527953 is a reply to message #527947] Fri, 21 October 2011 00:37 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Michel Cadot wrote on Thu, 20 October 2011 23:20
Explain your question, why "ENABLED" does not give you the information?

Regards
Michel


hi,
The partition named P_L2,P_L3,P_L4 are not compressed,but there are "ENABLEED" also,in my case just P_L1 is compressed.
Re: how to check a partition have been compressed [message #527962 is a reply to message #527953] Fri, 21 October 2011 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to use DBMS_COMPRESSION.GET_COMPRESSION_RATIO procedure.

Regards
Michel

[Updated on: Fri, 21 October 2011 01:27]

Report message to a moderator

Re: how to check a partition have been compressed [message #528026 is a reply to message #527962] Fri, 21 October 2011 05:17 Go to previous messageGo to next message
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.
Re: how to check a partition have been compressed [message #528029 is a reply to message #528026] Fri, 21 October 2011 05:25 Go to previous message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback (I pointed to the wrong procedure of the package, out of 3, I have not so much luck).

Regards
Michel
Previous Topic: My question about compressing table
Next Topic: ASM add disk issue
Goto Forum:
  


Current Time: Thu Sep 18 03:40:04 CDT 2014

Total time taken to generate the page: 0.13406 seconds