Rem 10gR2 version
Rem hbaer, 05/09/2005

REM for package creation in different schema
REM GRANT select ON v_$sql_plan TO x;
REM connect sh/sh
REM GRANT select ON channels TO x;
REM GRANT select ON times TO x;
REM GRANT select ON promotions TO x;


CREATE OR REPLACE PACKAGE dw_handsOn
AS
  -- simple version number and last modification date
  -- Usage:
  -- SELECT dw_handsOn.version FROM dual;
  --
  FUNCTION  version RETURN VARCHAR2;

  -- cleans up between modules - NO COMPLETE RESET procedure
  -- Usage:
  -- exec dw_handsOn.cleanup_modules
  --
  PROCEDURE cleanup_modules;

  -- verifies the initial correction setup on top of the
  -- default SH installation
  -- Usage:
  -- SELECT * FROM TABLE(dw_handsOn.verify_env);
  --
  FUNCTION verify_env RETURN SYS.DBMS_XPLAN_TYPE_TABLE
  PIPELINED;

END;
/

CREATE OR REPLACE PACKAGE BODY dw_handsOn
AS
    c_script varchar2(30) default 'modifySH_10g.sql';
    dw_version VARCHAR2(70) DEFAULT 'DW HandsOn Version 1.3.0 for 10.2.0 beta, 05/05/2005';
    -- for exception handling
    err_num integer default 0;
    err_msg varchar2(200);
    found_error boolean default false;
    dummy_number number default 0;
    dummy_char   varchar2(100) default null;

  FUNCTION version RETURN VARCHAR2
  AS
  BEGIN
    -- simplest approach of controlling version
    RETURN dw_version;
  END;



  --
  -- verifies the correction actions on top of default SH installation
  --
  FUNCTION verify_env RETURN sys.dbms_xplan_type_table
  PIPELINED
  AS
    v1          varchar2(200) default null;
    n1          number default null;
    prebuilt_mv varchar2(30) default 'CUST_SALES_AGGR';
    stat_tab    varchar2(30) default 'DW_HANDSON_STAT_TABLE';
  BEGIN

    PIPE ROW(sys.dbms_xplan_type('Start controlling the correction setup '||c_script||' ...'));
    PIPE ROW(sys.dbms_xplan_type('-------------------------------------------------------------- '));
    PIPE ROW(sys.dbms_xplan_type(' '));
    PIPE ROW(sys.dbms_xplan_type('... running package version '||dw_version));
    PIPE ROW(sys.dbms_xplan_type(' '));


    -- DBA privilege for SH
    select count(*) into n1 from user_role_privs where granted_role='DBA';
    if n1 = 1 then
      v1 :='CORRECT: DBA role is granted to SH  - do not grant this privilege in a productive environment';
    else
      v1 :='ERROR: DBA role is NOT granted to SH. Please grant the DBA role to SH  (DO NOT GRANT THIS PRIVILEGE IN A PRODUCTIVE ENVIRONMENT)';
    end if;
    PIPE ROW(sys.dbms_xplan_type(v1));


    -- SELECT on V$_SQL_PLAN
    begin
       select count(*) into dummy_number from sys.v_$sql_plan;
       v1 :='CORRECT: SELECT on V$SQL_PLAN is granted to SH';
    exception when others then
       v1 := 'ERROR: SELECT on V$SQL_PLAN is NOT granted to SH - please re-issue '|| c_script;
    end;
    PIPE ROW(sys.dbms_xplan_type(v1));


    -- TABLESPACE AUTOEXTEND ON is skipped for now

 
    -- ADDITIONAL PARTITIONS FOR SALES beyond Dec 2001 must be dropped
    select count(*) into n1 from user_tab_partitions
    where partition_name in ('SALES_Q1_2002','SALES_Q2_2002','SALES_Q3_2002','SALES_Q4_2002');
    -- assume an all-or-nothing approach
    IF n1 = 0 THEN
      v1 := 'CORRECT: Unnecessary Partitions of table SALES are dropped ...';
    ELSE
      v1 := 'ERROR: Unnecessary Partitions of table SALES are not dropped - please re-issue '|| c_script;
    END IF;
    PIPE ROW(sys.dbms_xplan_type(v1));

    -- ADDITIONAL PARTITIONS FOR COSTS  beyond Dec 2001 must be dropped
    select count(*) into n1 from user_tab_partitions
    where partition_name in ('COSTS_Q!_2002','COSTS_Q2_2002','COSTS_Q3_2002','COSTS_Q4_2002');
    -- assume an all-or-nothing approach
    IF n1 = 0 THEN
      v1 := 'CORRECT: Unnecessary Partitions of table COSTS are dropped ...';
    ELSE
      v1 := 'ERROR: Unecessary Partitions of table COSTS are not dropped - please re-issue '|| c_script;
    END IF;
    PIPE ROW(sys.dbms_xplan_type(v1));


    -- most recent partitions NOCOMPRESS ?
    for crec in (select table_name, partition_name
                 from user_tab_partitions
		 where partition_name in ('SALES_Q4_2001','COSTS_Q4_2001')
		 and compression = 'ENABLED') loop
      PIPE ROW(sys.dbms_xplan_type('ERROR: Partition '||crec.table_name||'.'||
               crec.partition_name || ' is compressed. - please re-issue '|| c_script));
    end loop;

    -- usable indexes for all tables
    for crec in (select index_name, null partition_name
                 from user_indexes where status = 'UNUSABLE'
                 union all
                 select index_name, ': '||partition_name partition_name
		 from user_ind_partitions  where status = 'UNUSABLE' ) loop
      PIPE ROW(sys.dbms_xplan_type('ERROR: Index '||crec.index_name|| crec.partition_name || ' is in an unusable state. PLEASE REBUILD INDEX '));
    end loop;


    -- additional data in channels
    select count(*) into dummy_number from sh.channels where channel_id=99;

    if dummy_number = 1 then
      v1 := 'CORRECT: CHANNELS table has all necessary entries ';
    else
      v1 := 'ERROR:  CHANNELS table DOES NOT HAVE all necessary entries -  Please re-issue '||c_script;
    end if;
    PIPE ROW(sys.dbms_xplan_type(v1));


    -- additional data in promotions
    select count(*) into dummy_number from sh.promotions where promo_id=9999;

    if dummy_number = 1 then
      v1 := 'CORRECT: PROMOTIONS table has all necessary entries ';
    else
      v1 := 'ERROR:  PROMOTIONS table DOES NOT HAVE all necessary entries -  Please re-issue '||c_script;
    end if;
    PIPE ROW(sys.dbms_xplan_type(v1));


    -- proper PK constraint on CUSTOMERS
    select count(*) into dummy_number from user_constraints 
    where constraint_name='CUSTOMERS_PK' and validated='VALIDATED';

    if dummy_number = 1 then
      v1 := 'CORRECT: Constraint CUSTOMERS_PK is validated ';
    else
      v1 := 'ERROR:  Constraint CUSTOMERS_PK is NOT VALIDATED  -  Please re-issue '||c_script;
    end if;
    PIPE ROW(sys.dbms_xplan_type(v1));

     
    -- additional data in times dimension
    select to_char(min(time_id),'DD-MON-YYYY') into v1 from sh.times;

    if v1 = '01-JAN-1995' then
      v1 := 'CORRECT: Times dimension table has all necessary entries ';
    else
      v1 := 'ERROR: Times dimension table DOES NOT HAVE all necessary entries. Min(Time_id)= '||v1||'. Please re-issue '||c_script;
    end if;
    PIPE ROW(sys.dbms_xplan_type(v1));


    -- redefinition of dimensions skipped for now ...

    -- no materialized views
    for crec in (select mview_name from user_mviews) loop
      PIPE ROW(sys.dbms_xplan_type('ERROR: Materialized View '||crec.mview_name ||
                                   ' is found in the system. PLEASE re-run dw_handsOn.cleanup_modules or drop the MV manually'));
      found_error := true;
    end loop;
      if (found_error) then
         PIPE ROW(sys.dbms_xplan_type('We expect no MVs being present; those are created '||
                                      ' throughout the workshop' ));
	 found_error := false;
      end if;


    -- import of additional objects
    -- kind of goofy, because this package is only available when
    -- the import has taken place .. ;-)

    -- additional table for prebuilt MV and statistics table
    -- also for mv rerwrite and mv capabilities
    for crec in (select table_name, count(*) n1 from user_tables 
                 where table_name in (prebuilt_mv,stat_tab,
                                      'MV_CAPABILITIES_TABLE','REWRITE_TABLE')
                 group by table_name) loop
      if crec.n1 = 1 then
        v1 :='CORRECT: Table '||crec.table_name||' is existent'; 
      else
        v1 :='ERROR: Table '||crec.table_name||' is missing - please re-issue '||c_script;
      end if;
      PIPE ROW(sys.dbms_xplan_type(v1));
    end loop;

    PIPE ROW(sys.dbms_xplan_type(' '));

    PIPE ROW(sys.dbms_xplan_type('---- END OF VERIFICATION PROCESS ----'));
    RETURN;
  exception
    when others then
        err_num := SQLCODE;
        err_msg := substr(sqlerrm,1,150);
        PIPE ROW(sys.dbms_xplan_type('An uncaught error occured: '|| err_msg));
        return;
  END verify_env;


PROCEDURE cleanup_modules
AS
  -- misc
  v1 varchar2(200) DEFAULT null;

  -- for statistics output
  list_of_objects dbms_stats.objectTab := dbms_stats.objectTab();

begin
  dbms_output.enable(200000);
  dbms_output.put_line('Let''s try to cleanup between modules ...');
  dbms_output.put_line('-------------------------');

  -- purge recyclebin
  dbms_output.put_line('- purge users recyclebin ...');
  execute immediate('purge recyclebin');

  -- truncate plan table
  -- change it to DML because of global temp table issue
  dbms_output.put_line('- delete from plan_table ...');
  DELETE FROM plan_table;
  commit;

  -- delete global indexes on SALES
  for crec in (select ui.index_name, uc.constraint_name from user_indexes ui, user_constraints uc where ui.index_name=uc.index_name(+) and ui.table_name='SALES' and ui.status <> 'N/A') loop
    if crec.constraint_name is null then
      dbms_output.put_line(' - Drop global index '||crec.index_name||' from SALES ...');
      execute immediate('DROP INDEX '||crec.index_name);
    else
      dbms_output.put_line('- Drop constraint '||crec.constraint_name||' from SALES ...');
      execute immediate('ALTER TABLE sales DROP CONSTRAINT'|| crec.constraint_name);
    end if;
  end loop;

  -- enable constraints on SALES
  for crec in (select constraint_name from user_constraints where table_name ='SALES' and constraint_type='R') loop
    dbms_output.put_line('- Enable novalidate constraint '||crec.constraint_name||' on SALES ...');
    execute immediate('ALTER TABLE sales MODIFY CONSTRAINT '||crec.constraint_name||' ENABLE NOVALIDATE');
  end loop;

  -- drop most recent partitions of SALES and COSTS
  for crec in (select table_name, partition_name from user_tab_partitions 
               where partition_name in ('SALES_Q1_2002','COSTS_Q1_2002','COSTS_1_2002',
                                        'SALES_JAN_2002','SALES_FEB_2002','SALES_MAR_2002')) loop
    dbms_output.put_line('- DROP partition '|| crec.partition_name ||'...');
    execute immediate('ALTER TABLE '||crec.table_name||' DROP PARTITION '|| crec.partition_name);
  end loop;

  -- drop tablespace TRANSFER
  select count(*) into v1 from user_tablespaces where tablespace_name='TRANSFER';
  if v1 = 1 then
    dbms_output.put_line('- Drop tablespace TRANSFER ...');
    execute immediate('ALTER TABLESPACE transfer READ ONLY');
    execute immediate('DROP TABLESPACE transfer INCLDUING CONTENTS');
  end if;

  -- drop bitmap join indexes ...
  for crec in (select ui.index_name from user_indexes ui, user_ind_columns uic where ui.index_name=uic.index_name and ui.table_name='SALES' and uic.table_name<>'SALES') loop
    dbms_output.put_line('- Drop bitmap join index '||crec.index_name||' from SALES ...');
    execute immediate('DROP INDEX '||crec.index_name);
  end loop;

  -- drop all potential MV logs
  for crec in (select master from user_mview_logs) loop
    dbms_output.put_line('- Drop materialized view log on '||crec.master||'...');
    execute immediate('DROP MATERIALIZED VIEW LOG ON '||crec.master);
  end loop;

  -- drop all potential MVs
  for crec in (select mview_name from user_mviews) loop
    dbms_output.put_line('- Drop materialized view '||crec.mview_name||'...');
    execute immediate('DROP MATERIALIZED VIEW '||crec.mview_name);
  end loop;

  -- set session parameters we rely on ...
  dbms_output.put_line('- Set instance parameters we rely on ...');
  execute immediate('ALTER SESSION SET star_transformation_enabled=TRUE');
  execute immediate('ALTER SESSION SET query_rewrite_enabled=TRUE');
  execute immediate('ALTER SESSION SET query_rewrite_integrity=TRUSTED');

  -- disable tab monitoring
  -- cannot be switched off with 10g anymore ..

  -- recollect statistics
  dbms_stats.gather_schema_stats('SH',options=>'GATHER AUTO',objlist=>list_of_objects);
  for i in 1 ..list_of_objects.count loop
    dbms_output.put_line('- Updated statistics for:'||list_of_objects(i).objtype||': '||list_of_objects(i).objname||' '||list_of_objects(i).partname);
  end loop;

  -- STANDARD DISCLAIMER:
  dbms_output.new_line;
  dbms_output.put_line('.');
  dbms_output.put_line('NOTE:');
  dbms_output.put_line('-----');
  dbms_output.put_line('This is NOT a complete RESET of the environment to start over again! ');
  dbms_output.put_line('It only ensures a proper usage of the other non-used modules ');
  dbms_output.put_line('To reset the environment completely it is advised to recreate the environment by using ');
  dbms_output.put_line('the creation script sh_main.sql under ?/demo/schema/sales_history and to apply the ');
  dbms_output.put_line('correction script, e.g. '||c_script||' for '||dw_version||' again');
  dbms_output.put_line(' ');
  dbms_output.put_line('A best effort script for resetting the environment is avalable: un'||c_script);

  -- we do NOT clean up any additional created tables, since those do not hurt
  -- module I
  -- DROP TABLE sales_delta;
  -- DROP TABLE sales_old_q1_1998;
  -- module II
  -- DROP TABLE obsolete_products_errors;
  -- DROP FUNCTION obsolete_products;
  -- DROP FUNCTION obsolete_products_pipe;
  -- DROP FUNCTION obsolete_products_dml;
  -- module III
  -- DROP TABLE compare_idx_size;
  -- DROP TABLE sales_delta;
  -- module IV
  -- DROP TABLE sales_hash;
  -- DROP TABLE customers_hash;

exception
  when others then
      err_num := SQLCODE;
      err_msg := substr(sqlerrm,1,150);
      dbms_output.put_line('An uncaught error occured: '|| err_msg);
      return;
end cleanup_modules;

end;
/
