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

No comments: