Home » RDBMS Server » Server Administration » how to see whether table data is using compresssion (oracle 11.2.0.2.0 linux 2.6)
how to see whether table data is using compresssion [message #579539] Wed, 13 March 2013 01:38 Go to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Team,

1)i have 2 SWP TABLES. while dropping a column, i am getting error -
ORA-39726: unsupported add/drop column operation on compressed tables.

2) when i checked compression status, those were not compressed. But as per our code standard, SWP tables should not be in compress mode.

OWNER                          TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR                          PAYMENT_SWP                    DISABLED

OWNER                          TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR                          PREPAYMENT_SWP                 DISABLED


3) as a workaround, i compressed these 2 SWP tables with OLTP option, and then i was able to drop the column from these 2 SWP tables.

4) please confirm whether below statement is correct or not ?
IF A TABLE USING BLOCK LEVEL COMPRESSION, THEN this error will come - ORA-39726: unsupported add/drop column operation on compressed tables.

if above statement is correct, then how to find out whether table data is using block level compression ?

5) we have DBMS_COMPRESSION.GET_COMPRESSION_TYPE. using this i just tried to find out, but i am getting "1" as output. I am not getting the exact meaning of it.

Please confirm what is the conclusion on this ?

SQL> declare
        rid rowid;
        n number;
begin
        select max(rowid) into rid from NOVAR.PAYMENT_SWP;
        n := dbms_compression.get_compression_type('NOVAR','PAYMENT_SWP',rid);
        dbms_output.put_line(n);
end;
/
  2    3    4    5    6    7    8    9  1

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT ON
SQL> /
1

PL/SQL procedure successfully completed.

SQL> SELECT max(rowid) from NOVAR.PAYMENT_SWP;

MAX(ROWID)
------------------
AAsz4fAHSAAAD3IABs



(ii) 2nd table

SQL> set serveroutput on
SQL> declare
        rid rowid;
        n number;
begin
        select max(rowid) into rid from NOVAR.PREPAYMENT_SWP;
        n := dbms_compression.get_compression_type('NOVAR','PREPAYMENT_SWP',rid);
        dbms_output.put_line(n);
end;
  2    3    4    5    6    7    8    9
 10  /
1

PL/SQL procedure successfully completed.

SQL> SELECT max(rowid) from NOVAR.INVOICELINE_SWP;

MAX(ROWID)
------------------
AAsz4ZAEkAAAp8XAAA


Thank you very much for your time on this.

-Kesavan



Re: how to see whether table data is using compresssion [message #579675 is a reply to message #579539] Wed, 13 March 2013 21:48 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
A value of 1 on DBMS_COMPRESSION.GET_COMPRESSION_TYPE would mean "No compression"

See http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_compress.htm#BEIJJEAD


Hemant K Chitale
Re: how to see whether table data is using compresssion [message #579717 is a reply to message #579675] Thu, 14 March 2013 03:57 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Thanks for your reply.

Yes, 1 means, no compresssion. But i have one more SWP table, compressed with ARCHIVE LOW option. i am getting value as 1 for that table also.


OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR INVOICELINE_SWP ENABLED ARCHIVE LOW
NOVAR INVOICEHEAD_SWP ENABLED ARCHIVE LOW
NOVAR BREAK_SWP ENABLED ARCHIVE LOW

SQL> set serveroutput on
SQL>
SQL> declare
  2          rid rowid;
  3          n number;
  4  begin
  5          select max(rowid) into rid from NOVAR.INVOICELINE_SWP;
  6          n := dbms_compression.get_compression_type('NOVAR','INVOICELINE_SWP',rid);
  7          dbms_output.put_line(n);
  8  end;
  9  /
1

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> set serveroutput on
SQL>
declare
SQL>   2          rid rowid;
  3          n number;
  4  begin
  5          select max(rowid) into rid from NOVAR.INVOICEHEAD_SWP;
  6          n := dbms_compression.get_compression_type('NOVAR','INVOICEHEAD_SWP',rid);
  7          dbms_output.put_line(n);
  8  end;
  9  /
1

PL/SQL procedure successfully completed.


for all tables, this function gives output "1". I am not able to conclude now. just guide me.
my target is to suppress that error during the column drop, as a workaround, i compressed the table with OLTP option and dropped the column.

but for few tables, it returns the value correctly. for e.g value "32" is for ARCHIVE LOW option.

SQL>
SQL>
SQL>
SQL> set serveroutput on

declare
        rid rowid;
        n number;
begin
        select max(rowid) into rid from NOVAR.BREAK_SWP;
        n := dbms_compression.get_compression_type('NOVAR','BREAK_SWP',rid);
        dbms_output.put_line(n);
end;
/SQL> SQL>   2    3    4    5    6    7    8    9
32

PL/SQL procedure successfully completed.


Thank you
kesavan
Re: how to see whether table data is using compresssion [message #579722 is a reply to message #579717] Thu, 14 March 2013 05:49 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Could it be that issue you are facing is because some of the rows are compressed? You get odd effects like this:
orcl>
orcl> create table t1 as select * from all_objects;

Table created.

orcl> alter table t1 compress for all operations;

Table altered.

orcl> insert into t1 select * from t1;

73617 rows created.

orcl> commit;

Commit complete.

orcl> select min(rowid) from t1;

MIN(ROWID)
------------------
AAAU2WAAFAAABKTAAA

orcl> select dbms_compression.get_compression_type('SCOTT','T1','AAAU2WAAFAAABKTAAA') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','T1','AAAU2WAAFAAABKTAAA')
------------------------------------------------------------------------
                                                                       1

orcl> select max(rowid) from t1;

MAX(ROWID)
------------------
AAAU2WAAFAAAB5+ACV

orcl> select dbms_compression.get_compression_type('SCOTT','T1','AAAU2WAAFAAAB5+ACV') from dual;

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','T1','AAAU2WAAFAAAB5+ACV')
------------------------------------------------------------------------
                                                                       2

orcl>
orcl> alter table t1 drop column owner;

Table altered.

orcl> alter table t1 nocompress;

Table altered.

orcl> alter table t1 drop column object_name;
alter table t1 drop column object_name
                           *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


orcl> alter table t1 move;

Table altered.

orcl> alter table t1 drop column object_name;

Table altered.

orcl>
Previous Topic: Moving OMF files in ASM
Next Topic: ORA-01652
Goto Forum:
  


Current Time: Thu Mar 28 07:57:53 CDT 2024