| how to see whether table data is using compresssion [message #579539] |
Wed, 13 March 2013 01:38  |
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 #579717 is a reply to message #579675] |
Thu, 14 March 2013 03:57  |
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 |
John Watson
Messages: 8962
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> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
