Nov 1, 2011

Compressed Tables Cannot Versiondisable


create table testversion ( pkl number primary key,s_var varchar2(10));

ALTER TABLE TESTVERSION CACHE COMPRESS  PARALLEL ( DEGREE Default INSTANCES Default );

exec dbms_wm.enableversioning('TESTVERSION');

exec dbms_wm.disableversioning('TESTVERSION');

ORA-20231: 'TESTVERSION'  failed during DisableVersioning. Error:
ORA-39726: unsupported add/drop column operation on compressed tables .


  This error occurs when we try to version disable a  compressed table ;its  because during version enabling a compressed table   four columns  VERSION, NEXTVER, DELSTATUS, LTLOCK are not created

SO THERE IS NO USE TO VERSION ENABLE A COMPRESSED TABLE BECAUSE FOLLOWING 
VERSION, NEXTVER, DELSTATUS, LTLOCK COLUMNS ARE NOT CREATED WHICH ARE THE NUCLUES OF THE VERSIONING 


WHEN A COMPRESSED TABLE IS VERSION ENABLED ONLY  A VIEW NAMED 'TESTVERSION' IS CREATED .NO TRIGGERS AND OTHER VIEWS ARE CREATED


WHEN A COMPRESSED TABLE IS TRIED TO VERSION DISABLED ITS STATE IS CHANGED TO 'DV' .EVEN THOUGHT IT THROWS ERROR

CHECK THE BELOW QUERY FOR VERSIONED STATUS OF A TABLE

SELECT DISABLING_VER,UNDO_CODE FROM WMSYS.WM$VERSIONED_TABLES WHERE OWNER='SCHEMAMERGE' AND TABLE_NAME='TESTVERSION' ;

AND IF YOU CHECK IN UNDO_CODE COLUMN OF THIS WMSYS.WM$VERSIONED_TABLES TABLE YOU CAN GET WHY VERSION DISABLE OF THE COMPRESSED TABLE DEOSNT WORK

--------------------

1 comment:

Damien said...

I have had the same error as you on a compressed table, and I can not find any way to drop this table and clean left oracle objects. Any suggestion ?