Rem   drop unique index sales_pk. we shouldn't need it, and it
Rem   might cause some problems later on with DML operations
Rem   *****

ALTER TABLE sales DROP CONSTRAINT sales_pk;
DROP INDEX sales_pk;

Rem Cleanup - bring data back
Rem Necessary - that way we do not have to check for it at the end - top goal is no
Rem interference with the original SH schema ...
Rem - data for 1998
Rem - no data for 2001
ALTER TABLE sales 
  EXCHANGE PARTITION sales_q1_1998 
  WITH TABLE sales_old_q1_1998 INCLUDING INDEXES;


ALTER TABLE sales DROP PARTITION sales_jan_2002;

ALTER TABLE sales DROP PARTITION sales_feb_2002;

ALTER TABLE sales DROP PARTITION sales_mar_2002;

DROP TABLE sales_mar_2002_temp;

DROP TABLE sales_delta;

DROP TABLE sales_old_q1_1998;
         

PROMPT original situation again
Rem SELECT COUNT(*) FROM sales PARTITION (sales_q1_2001);
Rem SELECT COUNT(*) FROM sales PARTITION (sales_q1_1998);

PROMPT just to be safe ... -cleanup module
set serveroutput on
exec dw_handsOn.cleanup_modules

PROMPT control whether the correction script was applied properly ..
SELECT * FROM TABLE(dw_handsOn.verify_env);
