| ORA-39726: unsupported add/drop column operation on compressed tables [message #539174] |
Wed, 11 January 2012 22:13  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Team,
my user is trying to drop columns, but she gets below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 - "unsupported add/drop column operation on compressed tables"
i just checked whether table is compressed or not, it is not compressed it seems:
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
1 row selected
i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:
select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.
could you please guide me ?
thank you
kesavan
[Updated on: Thu, 12 January 2012 01:18] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: ORA-39726: unsupported add/drop column operation on compressed tables [message #539178 is a reply to message #539176] |
Wed, 11 January 2012 22:41  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
sir,
Thank you for your reply. Just for a confirmation I am writing this mail. I just checked Oracle documentation, it says
Quote:You cannot specify the DROP clause to drop a column from a compressed table or a partitioned table
containing any compressed partition, unless you first disable data compression for the table or partition. You can only use the SET UNUSED clause
further i checked below is partitioend or not...but it is not a partitioned table:
select owner,table_name,partitioned from dba_tables
where owner = 'EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME PAR
------------------------------ ------------------------------ ---
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP NO
so, even if the table is not partitioned, the only way to drop a column is to set as UNUSED ?
the only way to check whether a table is compress or not is as below or is there any other way to find out?
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
please confirm once again..
Thank you for your time.
kesavan
[Updated on: Wed, 11 January 2012 23:10] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: ORA-39726: unsupported add/drop column operation on compressed tables [message #539191 is a reply to message #539189] |
Thu, 12 January 2012 01:33 |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Hi Shaan,
please see below traces. there is no constraints for the 3 columns ( the last 3 columns ) we try to drop ...
desc equipment.ETHRNT_VRTL_CNXN_HRLY_AGG_SWP
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TIME_KEY NOT NULL DATE
HOUR_VALUE NOT NULL NUMBER
ETHRNT_VRTL_CNXN_KEY NOT NULL NUMBER
CLASS_OF_SERVICE_KEY NOT NULL NUMBER
AVG_INBOUND_LATENCY NUMBER(20,8)
AVG_OUTBOUND_LATENCY NUMBER(20,8)
AVG_LATENCY_ROUND_TRIP NUMBER(20,8)
AVG_LOSS_ROUND_TRIP NUMBER(20,8)
AVG_INBOUND_LOSS NUMBER(20,8)
AVG_OUTBOUND_LOSS NUMBER(20,8)
AVG_INBOUND_JITTER NUMBER(20,8)
AVG_OUTBOUND_JITTER NUMBER(20,8)
AVG_JITTER_ROUND_TRIP NUMBER(20,8)
CREATE_DT DATE
DATA_INTERVAL VARCHAR2(10)
DEVICE_GROUP_TYPE VARCHAR2(10)
LAST_UPDATED_DT DATE
select * from DBA_CONS_COLUMNS where owner = 'EQUIPMENT' and TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
--------------- -------------------- ------------------------------ ------------------------------ ----------
EQUIPMENT SYS_C002576941 ETHRNT_VRTL_CNXN_HRLY_AGG_SWP TIME_KEY
EQUIPMENT SYS_C002576942 ETHRNT_VRTL_CNXN_HRLY_AGG_SWP HOUR_VALUE
EQUIPMENT SYS_C002576943 ETHRNT_VRTL_CNXN_HRLY_AGG_SWP ETHRNT_VRTL_CNXN_KEY
EQUIPMENT SYS_C002576944 ETHRNT_VRTL_CNXN_HRLY_AGG_SWP CLASS_OF_SERVICE_KEY
4 rows selected.
pls advise further.
thank you
kesavan
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
