Showing posts with label WMSYS.WM$VERSIONED_TABLES. Show all posts
Showing posts with label WMSYS.WM$VERSIONED_TABLES. Show all posts

Aug 3, 2014

Interanl execution during Disable versioning a table


Step 1:
SELECT 1  FROM DUAL WHERE EXISTS (SELECT 1 FROM  scott.experiment_lt   WHERE VERSION IN (SELECT VERSION FROM wmsys.wm$version_hierarchy_table 
WHERE workspace != 'LIVE'))
                       
Step 2:       
DELETE FROM experiment_lt  WHERE nextver = ',0,' OR nextver IN ( SELECT next_vers FROM wmsys.wm$nextver_table
WHERE VERSION IN (SELECT VERSION FROM wmsys.wm$version_hierarchy_table  WHERE workspace = 'LIVE')) OR delstatus < 0
           
Step 3:
ALTER TABLE  scott.experiment_lt   ADD CONSTRAINT sys_c0036728 PRIMARY KEY (experiment_id) USING INDEX scott.sys_c0036728

Step 4:
SELECT dtc.column_name FROM dba_constraints dc,dba_cons_columns dcc, dba_tab_columns dtc WHERE dc.owner ='SCOTT' AND dc.table_name ='ADDRESS' || '_LT'
AND dc.constraint_type = 'P' AND dcc.owner = 'SCOTT'  AND dcc.table_name = 'EXPERIMENT'|| '_LT' AND dc.constraint_name = dcc.constraint_name AND
dcc.column_name NOT IN ('VERSION','LTLOCK','DELSTATUS','NEXTVER') AND dcc.column_name NOT LIKE 'WM$%' AND dcc.column_name NOT LIKE 'WM@_%' ESCAPE '@'
AND dtc.owner = 'SCOTT' AND dtc.table_name = 'EXPERIMENT'||'_LT' AND dcc.column_name = dtc.column_name  ORDER BY dtc.column_id

Step 5:
INSERT INTO wmsys.wm$vt_errors_table
     VALUES (:b4, :b3, :b2, :b1, 'DV STEP BEING EXECUTED', NULL)

Step 6:
ALTER TABLE EXPERIMENT_lt DROP COLUMN ltlock;
ALTER TABLE EXPERIMENT_lt DROP COLUMN delstatus;
ALTER TABLE EXPERIMENT_lt DROP COLUMN VERSION;
ALTER TABLE EXPERIMENT_lt DROP COLUMN NEXTVER;

Step 7:

DELETE FROM TABLE (SELECT undo_code FROM wmsys.wm$versioned_tables WHERE owner ='SCOTT' AND table_name ='EXPERIMENT')
WHERE index_type =:b4 AND index_field =:b3

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

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