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

REM necessary groundwork within ade...

Rem drop user sh cascade;
Rem create user sh identified by sh;
Rem grant connect, dba to sh;
Rem alter user sh default tablespace example;
Rem create tablespace example datafile 'example.f' size 50m autoextend on;
Rem create temporary tablespace temp tempfile 'temp.f' size 20m autoextend on;

set serverout on
set echo on
set termout on

PROMPT MODIFICATIONS OF SH SCHEMA FOR HANDS-ON WORKSHOP
PROMPT The following changes are necessary for the Oracle10g BETA installation for OW 2003
PROMPT Note that some necessary changes for this Hands-on workshop have been applied to
PROMPT the default SH schema in this Oracle10g pre-release, so that you cannot run this
PROMPT workshop without applying this script beforehand

PROMPT Some additional privileges and DB settings

connect / as sysdba

PROMPT This privilege should only be granted in a test environment
GRANT dba TO sh;

GRANT select ON v_$sql_plan TO sh;

PROMPT ensure that your TEMP tablespace for user SH is big enough
declare
  f varchar2(1000);
begin
  select 'alter database tempfile '''||file_name||''' autoextend on'  into f from dba_temp_files t, dba_users u where t.tablespace_name=u.temporary_tablespace and u.username='SH';
  execute immediate(f);
end;
/

PROMPT ensure that the UNDO tablespace for user SH is big enough
Rem **92** to be implemented

begin
  for crec in (select distinct file_name from dba_rollback_segs rs, dba_data_files f where rs.tablespace_name=f.tablespace_name and rs.tablespace_name <> 'SYSTEM') loop
    execute immediate ('alter database datafile '''||crec.file_name||''' autoextend on');
  end loop;
end;
/

PROMPT ensure that the DEMO/EXAMPLE tablespace for user SH is big enough
declare
  f varchar2(1000);
begin
  select 'alter database datafile '''||file_name||''' autoextend on' into f from dba_data_files t, dba_users u where t.tablespace_name=u.default_tablespace and u.username='SH';
  execute immediate(f);
end;
/


connect sh/sh

PROMPT Initial data volume
select count(*) from sales;
select count(*) from costs;

PROMPT  MODIFY PARTITIONS OF TABLE COSTS and SALES...
PROMPT  DROP emtpy partitions 2002 - 2003
ALTER TABLE sales DROP PARTITION sales_q1_2002;
ALTER TABLE sales DROP PARTITION sales_q2_2002;
ALTER TABLE sales DROP PARTITION sales_q3_2002;
ALTER TABLE sales DROP PARTITION sales_q4_2002;
ALTER TABLE sales DROP PARTITION sales_q1_2003;
ALTER TABLE sales DROP PARTITION sales_q2_2003;
ALTER TABLE sales DROP PARTITION sales_q3_2003;
ALTER TABLE sales DROP PARTITION sales_q4_2003;

ALTER TABLE costs DROP PARTITION costs_q1_2002;
ALTER TABLE costs DROP PARTITION costs_q2_2002;
ALTER TABLE costs DROP PARTITION costs_q3_2002;
ALTER TABLE costs DROP PARTITION costs_q4_2002;
ALTER TABLE costs DROP PARTITION costs_q1_2003;
ALTER TABLE costs DROP PARTITION costs_q2_2003;
ALTER TABLE costs DROP PARTITION costs_q3_2003;
ALTER TABLE costs DROP PARTITION costs_q4_2003;

PROMPT Ensure that everything is fine - same amount of data
select count(*) from sales;
select count(*) from costs;

PROMPT delete records for Dec 2001

DROP TABLE sales_dec2001_before_obe$;
CREATE TABLE sales_dec2001_before_obe$ NOLOGGING
AS SELECT * FROM  sales WHERE time_id >=to_date('01-DEC-2001','dd-mon-yyyy');
DROP TABLE costs_dec2001_before_obe$;
CREATE TABLE costs_dec2001_before_obe$ NOLOGGING
AS SELECT * FROM costs WHERE time_id >=to_date('01-DEC-2001','dd-mon-yyyy');

DELETE FROM sales WHERE time_id >=to_date('01-DEC-2001','dd-mon-yyyy');
DELETE FROM costs WHERE time_id >=to_date('01-DEC-2001','dd-mon-yyyy');
COMMIT;

PROMPT disable compression for the two most recent partitions for COST and SALES
ALTER TABLE costs MOVE PARTITION costs_q4_2001 NOCOMPRESS UPDATE INDEXES;
ALTER TABLE sales MOVE PARTITION sales_q4_2001 NOCOMPRESS UPDATE INDEXES;

PROMPT control it ... latest partitions are uncompressed
SELECT partition_name, compression, high_value
FROM user_tab_partitions
WHERE table_name='SALES' ORDER BY partition_position;

SELECT partition_name, compression, high_value
FROM user_tab_partitions
WHERE table_name='COSTS' ORDER BY partition_position;

PROMPT add some extra data into table CHANNELS
INSERT INTO channels VALUES (99,'N/A','N/A',15,'Channel total',1);

PROMPT add some extra data into table PROMOTIONS
INSERT INTO promotions
  (promo_id, promo_name, promo_subcategory_id, promo_subcategory,
                         promo_category_id, promo_category, promo_cost,
   promo_begin_date, promo_end_date, promo_total_id,promo_total)
VALUES
  (9999,'special partner ',15, 'ad magazine',7, 'magazine', 10243, TO_DATE('01-JAN-2001','dd-mon-yyyy'), TO_DATE('01-APR-2001','dd-mon-yyyy'),1,'Promotion Total');
COMMIT;

PROMPT "Fix" products table

DROP TABLE products_before_obe$;
CREATE TABLE products_before_obe$ NOLOGGING
AS SELECT * FROM products;

UPDATE products set prod_category= 'Peripherals and Accessories',
                    prod_category_desc= 'Peripherals and Accessories'
WHERE prod_subcategory='Accessories';

PROMPT PRODUCT.PROD_STATUS is an unused field in the base schema
Rem note that this distribution is totally arbitrary and is for
Rem demonstration purposes only

UPDATE products t SET prod_status = (
   SELECT ps FROM (
      SELECT prod_id, decode(mod(rownum,4),0,'not available',
                                           1,'obsolete',
                                           2,'ordered',
                                           3,'avaiable - on stock',
                                           4,'available, not on stock') ps FROM products) s
      WHERE s.prod_id=t.prod_id);
COMMIT;

PROMPT "Fix" customers table and validate PK constraint
ALTER TABLE customers MODIFY CONSTRAINT customers_pk VALIDATE;

PROMPT fill in additional data in times dimension ...
PROMPT !!! ATTENTION !!!
PROMPT The way the TIMES table is populated is not in accordance with the initial
PROMPT population for the OLAP option and its dimesnion definition.
PROMPT After applying this change you will not be able to run any OLAP examples
PROMPT again. You have to repopulate table TIMES and redefine the original
PROMPT dimension object. This can be done with the script uncorrectSH_10g.sql
PROMPT or with a reinstallation of the SH schema
PROMPT
PROMPT Build intermediate TIMES table

DROP TABLE times_before_obe$;
CREATE TABLE times_before_obe$ NOLOGGING
AS SELECT * FROM times;

Rem update all columns blindly again because of some mismatches in attribute seetings
DECLARE
   curdate DATE := TO_DATE('01-jan-1995','dd-mon-yyyy');
   weekend DATE default NULL;
   actual_min_date DATE default '01-JAN-2222';
BEGIN
   dbms_session.set_nls('NLS_TERRITORY','GERMANY');
   -- necessary for week/day_of_week synchronisation
   dbms_session.set_nls('NLS_DATE_LANGUAGE','AMERICAN');
   -- necessary for week/day_of_week synchronisation

   -- probe for actual MIN value, HBaer, 07/10/03
   select min(time_id) into actual_min_date from times;
   dbms_output.put_line(actual_min_date);

 --   WHILE curdate < TO_DATE('01-jan-1998','dd-mon-yyyy') LOOP
   WHILE curdate < actual_min_date LOOP

   SELECT curdate+DECODE(TO_CHAR(curdate, 'DAY'),'MONDAY   ', 6
                                                ,'TUESDAY  ', 5
                                                ,'WEDNESDAY', 4
                                                ,'THURSDAY ', 3
                                                ,'FRIDAY   ', 2
                                                ,'SATURDAY ', 1
                                                ,'SUNDAY   ', 0
                        )
   INTO   weekend
   FROM   dual;
   -- omitting the ID columns at the moment
   INSERT INTO times VALUES (
	-- current date, midnight
        curdate,
	-- day name, REPEATING
        INITCAP(TO_CHAR(curdate, 'DAY')),
	-- day number in week, week starts MONDAY, REPEATING
        TO_NUMBER(TO_CHAR(curdate,'D')),
        -- day number in month, REPEATING
        TO_NUMBER(TO_CHAR(curdate,'DD')),
	-- ISO calendar week number, week starts MONDAY, REPEATING
        TO_NUMBER(TO_CHAR(curdate, 'IW')),
	-- ISO fiscal week number, week starts MONDAY, REPEATING
        TO_NUMBER(TO_CHAR(weekend, 'IW')),
	-- calculation of week ending day date, CONTINOUS
	weekend,
        -- ARBITRARY week_ending_day_id
        1,
	-- calendar month number in year, REPEATING
        TO_NUMBER(TO_CHAR(curdate, 'MM')),
	-- fiscal month number in fiscal year, REPEATING
        TO_NUMBER(TO_CHAR(weekend, 'MM')),
	-- calendar month desc, CONTINOUS
        TO_CHAR(curdate,'YYYY')||'-'||TO_CHAR(curdate, 'MM'),
        -- ARBITRARY calendar_month_id
        1,
	-- fiscal month desc, CONTINOUS
        TO_CHAR(weekend,'YYYY')||'-'||TO_CHAR(weekend, 'MM'),
        -- ARBITRARY fiscal_month_id
        1,
        -- four placeholders, updated later
        999,
        999,
        TO_DATE('01-JAN-1860','DD-MON-YYYY'),
        TO_DATE('01-JAN-1860','DD-MON-YYYY'),
	-- calendar_month name, REPEATING
        rtrim(INITCAP(TO_CHAR(curdate, 'MONTH'))),
	-- fiscal_month name, REPEATING
        rtrim(INITCAP(TO_CHAR(weekend, 'MONTH'))),
	-- calendar quarter description, CONTINOUS
        TO_CHAR(curdate, 'YYYY')||'-0'||TO_CHAR(curdate, 'Q'),
        -- ARBITRARY calendar_quarter_desc_id
        1,
	-- fiscal quarter description calculation, CONTINOUS
        TO_CHAR(weekend, 'YYYY')||'-0'||TO_CHAR(weekend, 'Q'),
        -- ARBITRARY fiscal_quarter_desc_id
        1,
        -- four placeholders, updated later
        999,
        999,
        TO_DATE('01-JAN-1860','DD-MON-YYYY'),
        TO_DATE('01-JAN-1860','DD-MON-YYYY'),
	-- calendar quarter number, REPEATING
        TO_NUMBER(TO_CHAR(curdate, 'Q')),
	-- fiscal quarter number, REPEATING
        TO_NUMBER(TO_CHAR(weekend, 'Q')),
	-- calendar year, CONTINOUS
        TO_NUMBER(TO_CHAR(curdate, 'YYYY')),
        -- ARBITRARY calendar_year_id
        1,
	-- fiscal year, CONTINOUS
        TO_NUMBER(TO_CHAR(weekend, 'YYYY')),
        -- ARBITRARY fiscal_year_id
        1,
        999,
        999,
        TO_DATE('01-JAN-1860','DD-MON-YYYY'),
        TO_DATE('01-JAN-1860','DD-MON-YYYY')
	);
        curdate := curdate + 1;
   END LOOP;

   UPDATE times SET FISCAL_WEEK_NUMBER=1
   WHERE FISCAL_WEEK_NUMBER IN (52,53) AND TO_CHAR(WEEK_ENDING_DAY,'MM')='01'
;
--   and time_id < actual_min_date;

   UPDATE times a SET days_in_cal_month =
   (SELECT COUNT(*) FROM times b
                    WHERE a.calendar_month_desc = b.calendar_month_desc
                    GROUP BY calendar_month_desc)
;
--   where time_id < actual_min_date;

   UPDATE times a SET days_in_fis_month =
   (SELECT COUNT(*) FROM times b
                    WHERE a.fiscal_month_desc = b.fiscal_month_desc
                    GROUP BY fiscal_month_desc)
;
--   where time_id < actual_min_date;

   UPDATE times a SET days_in_cal_quarter =
   (SELECT COUNT(*) FROM times b
                    WHERE a.calendar_quarter_desc = b.calendar_quarter_desc
                    GROUP BY calendar_quarter_desc)
;
--   where time_id < actual_min_date;

   UPDATE times a SET days_in_fis_quarter =
   (SELECT COUNT(*) FROM times b
                    WHERE a.fiscal_quarter_desc = b.fiscal_quarter_desc
                    GROUP BY fiscal_quarter_desc)
;
--   where time_id < actual_min_date;

   UPDATE times a SET days_in_cal_year =
   (SELECT COUNT(*) FROM times b
                    WHERE a.calendar_year = b.calendar_year
                    GROUP BY calendar_year)
;
--   where time_id < actual_min_date;

   UPDATE times a SET days_in_fis_year =
   (SELECT COUNT(*) FROM times b
                    WHERE a.fiscal_year = b.fiscal_year 
                    GROUP BY fiscal_year)
;
--   where time_id < actual_min_date;

   UPDATE times a SET end_of_cal_month = (SELECT max_time FROM (
     SELECT DISTINCT calendar_month_desc, MAX(time_id) AS max_time FROM times
     GROUP BY calendar_month_desc) b WHERE a.calendar_month_desc=b.calendar_month_desc)
;
--   where time_id < actual_min_date;

   UPDATE times a SET end_of_fis_month = (SELECT max_time FROM (
     SELECT DISTINCT fiscal_month_desc, MAX(time_id) AS max_time FROM times
     GROUP BY fiscal_month_desc) b WHERE a.fiscal_month_desc=b.fiscal_month_desc)
;
--   where time_id < actual_min_date;

   UPDATE times a SET end_of_cal_quarter = (SELECT max_time FROM (
     SELECT DISTINCT calendar_quarter_desc, MAX(time_id) AS max_time FROM times
     GROUP BY calendar_quarter_desc) b WHERE a.calendar_quarter_desc=b.calendar_quarter_desc)
;
--   where time_id < actual_min_date;

   UPDATE times a SET end_of_fis_quarter = (SELECT max_time FROM (
     SELECT DISTINCT fiscal_quarter_desc, MAX(time_id) AS max_time FROM times
     GROUP BY fiscal_quarter_desc) b WHERE a.fiscal_quarter_desc=b.fiscal_quarter_desc)
;
--   where time_id < actual_min_date;

   UPDATE times a SET end_of_cal_year = (SELECT max_time FROM (
     SELECT DISTINCT calendar_year, MAX(time_id) AS max_time FROM times
     GROUP BY calendar_year) b WHERE a.calendar_year=b.calendar_year)
;
--   where time_id < actual_min_date;

   UPDATE times a SET end_of_fis_year = (SELECT max_time FROM (
     SELECT DISTINCT fiscal_year, MAX(time_id) AS max_time FROM times
     GROUP BY fiscal_year) b WHERE a.fiscal_year=b.fiscal_year)
;
--   where time_id < actual_min_date;

  COMMIT;
 END;
/

PROMPT Redefine dimension object times_dim for modified table TIMES
PROMPT !!! ATTENTION !!!
PROMPT This is a different dimension definition than the one that is shipped.
PROMPT After applying this change you will not be able to run any OLAP examples
PROMPT again. you have to repopulate table TIMESS and redefine the original
PROMPT dimension object


DROP DIMENSION times_dim;
CREATE DIMENSION times_dim
   LEVEL day         IS TIMES.TIME_ID
   LEVEL month       IS TIMES.CALENDAR_MONTH_DESC
   LEVEL quarter     IS TIMES.CALENDAR_QUARTER_DESC
   LEVEL year        IS TIMES.CALENDAR_YEAR
   LEVEL fis_week    IS TIMES.WEEK_ENDING_DAY
   LEVEL fis_month   IS TIMES.FISCAL_MONTH_DESC
   LEVEL fis_quarter IS TIMES.FISCAL_QUARTER_DESC
   LEVEL fis_year    IS TIMES.FISCAL_YEAR
   HIERARCHY cal_rollup    (
             day     CHILD OF
             month   CHILD OF
             quarter CHILD OF
             year
   )
   HIERARCHY fis_rollup    (
             day         CHILD OF
             fis_week    CHILD OF
             fis_month	 CHILD OF
             fis_quarter CHILD OF
             fis_year
   )
   ATTRIBUTE day DETERMINES 
	(day_number_in_week, day_name, day_number_in_month,
         calendar_week_number)
   ATTRIBUTE month DETERMINES
	(calendar_month_desc,
         calendar_month_number, calendar_month_name,
         days_in_cal_month, end_of_cal_month)
   ATTRIBUTE quarter DETERMINES
	(calendar_quarter_desc,
         calendar_quarter_number,days_in_cal_quarter,
	 end_of_cal_quarter)
   ATTRIBUTE year DETERMINES
	(calendar_year,
         days_in_cal_year, end_of_cal_year)
   ATTRIBUTE fis_week DETERMINES
	(week_ending_day,
         fiscal_week_number)
   ATTRIBUTE fis_month DETERMINES
	(fiscal_month_desc, fiscal_month_number, fiscal_month_name,
	 days_in_fis_month, end_of_fis_month)
   ATTRIBUTE fis_quarter DETERMINES
	(fiscal_quarter_desc,
         fiscal_quarter_number, days_in_fis_quarter,
	 end_of_fis_quarter)
   ATTRIBUTE fis_year DETERMINES
	(fiscal_year, 
         days_in_fis_year, end_of_fis_year)
;

execute dbms_olap.validate_dimension('times_dim','sh',false,true)
SELECT COUNT(*) FROM mview$_exceptions;

PROMPT Redefine dimension object customers_dim
PROMPT !!! ATTENTION !!!
PROMPT This is a different dimension definition than the one that is shipped.
PROMPT After applying this change you will not be able to run any OLAP examples 
PROMPT again. you have to recreate the original dimension object

DROP DIMENSION customers_dim;
CREATE DIMENSION customers_dim
	LEVEL customer	IS (customers.cust_id)
	LEVEL city 	IS (customers.cust_city)
	LEVEL state 	IS (customers.cust_state_province)
	LEVEL country 	IS (countries.country_id)
	LEVEL subregion IS (countries.country_subregion)
	LEVEL region IS (countries.country_region) 
	HIERARCHY geog_rollup (
		customer	CHILD OF
		city 		CHILD OF 
		state 		CHILD OF
		country 	CHILD OF
		subregion 	CHILD OF
		region
	JOIN KEY (customers.country_id) REFERENCES country
	)
	ATTRIBUTE customer DETERMINES
	(cust_first_name, cust_last_name, cust_gender,
	 cust_marital_status, cust_year_of_birth,
	 cust_income_level, cust_credit_limit,
         cust_street_address, cust_postal_code,
         cust_main_phone_number, cust_email)
        ATTRIBUTE city DETERMINES (cust_city)
        ATTRIBUTE state DETERMINES (cust_state_province)
	ATTRIBUTE country DETERMINES (countries.country_name)
        ATTRIBUTE subregion DETERMINES (countries.country_subregion)
        ATTRIBUTE region DETERMINES (countries.country_region)
;

execute dbms_olap.validate_dimension('customers_dim','sh',false,true)
SELECT COUNT(*) FROM mview$_exceptions;

PROMPT Redefine dimension object products_dim
PROMPT !!! ATTENTION !!!
PROMPT This is a different dimension definition than the one that is shipped.
PROMPT After applying this change you will not be able to run any OLAP examples
PROMPT again. you have to recreate the original dimension object

DROP DIMENSION products_dim;
CREATE DIMENSION products_dim
	LEVEL product 		IS (products.prod_id)
 	LEVEL subcategory 	IS (products.prod_subcategory)
	LEVEL category		IS (products.prod_category)
	HIERARCHY prod_rollup (
		product		CHILD OF
		subcategory 	CHILD OF
		category
	)
	ATTRIBUTE product DETERMINES
        (products.prod_name, products.prod_desc,
         prod_weight_class, prod_unit_of_measure,
         prod_pack_size,prod_status, prod_list_price, prod_min_price)
	ATTRIBUTE subcategory DETERMINES
        (prod_subcategory, prod_subcategory_desc)
	ATTRIBUTE category DETERMINES
        (prod_category, prod_category_desc)
;

execute dbms_olap.validate_dimension('products_dim','sh',false,true)
SELECT COUNT(*) FROM mview$_exceptions;

Rem some problems in the original data ...
Rem select prod_subcategory, prod_category from products where prod_subcategory='Accessories';


PROMPT Redefine dimension object promotions_dim
PROMPT !!! ATTENTION !!!
PROMPT This is a different dimension definition than the one that is shipped.
PROMPT After applying this change you will not be able to run any OLAP examples
PROMPT again. you have to recreate the original dimension object

DROP DIMENSION promotions_dim;
CREATE DIMENSION promotions_dim
	LEVEL promo 	  IS (promotions.promo_id)
	LEVEL subcategory IS (promotions.promo_subcategory)
	LEVEL category 	  IS (promotions.promo_category)
	HIERARCHY promo_rollup (
		promo 		CHILD OF
		subcategory 	CHILD OF
		category
	)
	ATTRIBUTE promo DETERMINES
        (promo_name, promo_cost,
         promo_begin_date, promo_end_date)
        ATTRIBUTE subcategory DETERMINES (promo_subcategory)
        ATTRIBUTE category DETERMINES (promo_category)
;

execute dbms_olap.validate_dimension('promotions_dim','sh',false,true)
SELECT COUNT(*) FROM mview$_exceptions;

PROMPT Redefine dimension object channels_dim
PROMPT !!! ATTENTION !!!
PROMPT This is a different dimension definition than the one that is shipped.
PROMPT After applying this change you will not be able to run any OLAP examples
PROMPT again. you have to recreate the original dimension object

DROP DIMENSION channels_dim;
CREATE DIMENSION channels_dim
	LEVEL channel 	   IS (channels.channel_id)
	LEVEL channel_class IS (channels.channel_class)
	HIERARCHY channel_rollup (
		channel		CHILD OF
		channel_class
	)
        ATTRIBUTE channel DETERMINES (channel_desc)
        ATTRIBUTE channel_class DETERMINES (channel_class)
;

execute dbms_olap.validate_dimension('channels_dim','sh',false,true)
SELECT COUNT(*) FROM mview$_exceptions;

COMMIT;

PROMPT delete existing MVs for module IV
DROP MATERIALIZED VIEW cal_month_sales_mv;
DROP MATERIALIZED VIEW fweek_pscat_sales_mv;

PROMPT import additional objects
Rem TABLE sales_input_table
Rem TABLE dw_handson_stat_table
Rem TABLE cust_sales_aggr
Rem PACKAGE dw_handson
Rem host imp sh/sh file=/private/hbaer/WORK/HandsOn10g/files/HandsOn10g.dmp full=y

DROP DIRECTORY data_dir;
REM for HandsOn workshop
REM CREATE DIRECTORY data_dir as 'c:\obetmp';
Rem changing directory to cvs checkout dir
CREATE DIRECTORY data_dir as 'c:\wkdir';

PROMPT try to drop the maintenance package
DROP PACKAGE dw_handson;

REM expdp x/y directory=DATA_DIR dumpfile=HandsOn10gR2.dmp schemas=x
host impdp sh/sh directory=DATA_DIR dumpfile=HandsOn10gR2.dmp nologfile=y remap_schema=x:sh table_exists_action=replace

Rem temporary to get newest package
Rem @c:\wkdir\cleanup_procedure10g  --no need for this with .dmp file

PROMPT update statistics
set serveroutput on
declare
  list_of_objects dbms_stats.objectTab := dbms_stats.objectTab();
begin
  dbms_output.enable(200000);
  -- list objects
  for i in 1 ..list_of_objects.count loop
    dbms_output.put_line('updated: '||list_of_objects(i).objtype||' '||list_of_objects(i).objname||' '||list_of_objects(i).partname);
  end loop;
  -- do stats
  dbms_stats.gather_schema_stats('SH',options=>'GATHER EMPTY',cascade=>true,estimate_percent=>10,objlist=>list_of_objects);
end;
/


PROMPT Create the necessary table for explaining MV rewrite and MV capabilities
Rem Harcopy from server files (10.1.0.3) )to being able to run remotely
CREATE TABLE MV_CAPABILITIES_TABLE (
   STATEMENT_ID         VARCHAR(30),  -- Client-supplied unique statement identifier
   MVOWNER              VARCHAR(30),  -- NULL for SELECT based EXPLAIN_MVIEW
   MVNAME               VARCHAR(30),  -- NULL for SELECT based EXPLAIN_MVIEW
   CAPABILITY_NAME      VARCHAR(30),  -- A descriptive name of the particular 
                                      -- capability: 
                                      -- REWRITE
                                      --   Can do at least full text match
                                      --   rewrite
                                      -- REWRITE_PARTIAL_TEXT_MATCH
                                      --   Can do at leat full and partial 
                                      --   text match rewrite
                                      -- REWRITE_GENERAL
                                      --   Can do all forms of rewrite
                                      -- REFRESH
                                      --   Can do at least complete refresh 
                                      -- REFRESH_FROM_LOG_AFTER_INSERT
                                      --   Can do fast refresh from an mv log
                                      --   or change capture table at least 
                                      --   when update operations are 
                                      --   restricted to INSERT 
                                      -- REFRESH_FROM_LOG_AFTER_ANY
                                      --   can do fast refresh from an mv log
                                      --   or change capture table after any
                                      --   combination of updates
                                      -- PCT
                                      --   Can do Enhanced Update Tracking on
                                      --   the table named in the RELATED_NAME
                                      --   column.  EUT is needed for fast
                                      --   refresh after partitioned 
                                      --   maintenance operations on the table
                                      --   named in the RELATED_NAME column 
                                      --   and to do non-stale tolerated 
                                      --   rewrite when the mv is partially 
                                      --   stale with respect to the table
                                      --   named in the RELATED_NAME column.
                                      --   EUT can also sometimes enable fast
                                      --   refresh of updates to the table
                                      --   named in the RELATED_NAME column
                                      --   when fast refresh from an mv log 
                                      --   or change capture table is not 
                                      --   possilbe.
   POSSIBLE             CHARACTER(1), -- T = capability is possible
                                      -- F = capability is not possible 
   RELATED_TEXT         VARCHAR(2000),-- Owner.table.column, alias name, etc. 
                                      -- related to this message.  The
                                      -- specific meaning of this column 
                                      -- depends on the MSGNO column.  See
                                      -- the documentation for
                                      -- DBMS_MVIEW.EXPLAIN_MVIEW() for details
   RELATED_NUM          NUMBER,       -- When there is a numeric value 
                                      -- associated with a row, it goes here.
                                      -- The specific meaning of this column 
                                      -- depends on the MSGNO column.  See
                                      -- the documentation for
                                      -- DBMS_MVIEW.EXPLAIN_MVIEW() for details
   MSGNO                INTEGER,      -- When available, QSM message # 
                                      -- explaining why not possible or more 
                                      -- details when enabled.
   MSGTXT               VARCHAR(2000),-- Text associated with MSGNO.
   SEQ                  NUMBER);    


CREATE TABLE REWRITE_TABLE(
                  statement_id          VARCHAR2(30),  -- id for the query
                  mv_owner              VARCHAR2(30),  -- owner of the MV
                  mv_name               VARCHAR2(30),  -- name of the MV
                  sequence              INTEGER,       -- sequence no of the error msg
                  query                 VARCHAR2(2000),-- user query
                  query_block_no        INTEGER,       -- block no of the current subquery 
                  rewritten_txt         VARCHAR2(2000),-- rewritten query
                  message               VARCHAR2(512), -- EXPLAIN_REWRITE error msg
                  pass                  VARCHAR2(3),   -- rewrite pass no
                  mv_in_msg             VARCHAR2(30),  -- MV in current message 
                  measure_in_msg        VARCHAR2(30),  -- Measure in current message 
                  join_back_tbl         VARCHAR2(30),  -- Join back table in current msg 
                  join_back_col         VARCHAR2(30),  -- Join back column in current msg 
                  original_cost         INTEGER,       -- Cost of original query
                  rewritten_cost        INTEGER,       -- Cost of rewritten query
                  flags                 INTEGER,       -- associated flags
                  reserved1             INTEGER,       -- currently not used 
                  reserved2             VARCHAR2(10))  -- currently not used
/



PROMPT Change all existing primary keys on the dimension objects to RELY
ALTER TABLE channels MODIFY CONSTRAINT CHANNELS_PK RELY;
ALTER TABLE countries MODIFY CONSTRAINT COUNTRIES_PK RELY;
ALTER TABLE customers MODIFY CONSTRAINT CUSTOMERS_PK RELY;
ALTER TABLE products MODIFY CONSTRAINT PRODUCTS_PK RELY;
ALTER TABLE promotions MODIFY CONSTRAINT PROMO_PK RELY;
ALTER TABLE times MODIFY CONSTRAINT TIMES_PK RELY;

PROMPT check setup
select * from table(dw_handson.verify_env);
