/* -------------------------------------------------------- */ /* */ /* Code from Oracle8 Data Warehousing */ /* Osborne McGraw-Hill / Oracle Press 1998 */ /* */ /* Please note: code to only be run by consenting adults. */ /* */ /************************************************************/ /************************************************************/ /* SOME but NOT ALL of this code can be run */ /* R T as is from SQL*Plus, Server Manager, or */ /* SQL*Worksheet. A large part of the text in */ /* E H this file requires editing and further */ /* intervention before it can be executed. As */ /* A I well, there are references to objects in the */ /* code that may not already be in your database. */ /* D S We do not guarantee that any (or all) of this */ /* code will run error free in your database. */ /************************************************************/ /************************************************************/ /* */ /* Mike, Ian, Ben, and I are pleased to collect the code */ /* from this book and make it available to you in */ /* electronic format. Please download and use where you */ /* see fit. */ /* */ /* Mike Corey mcorey@dbtinc.com */ /* Michael Abbey masint@istar.ca */ /* Ian Abramson ias@magi.com */ /* Ben Taub btaub@dspace.com */ /* */ /* This code is supplied as is and there is no warranty */ /* intended or implied. © Oracle Press April 1998. */ /* */ /* Michael Abbey Ottawa ON Canada April 1998 */ /* */ /* -------------------------------------------------------- */ * Non-partitioned version of SALE create table sale (sale_id number, fy number ..) storage (initial 14m next 2m) tablespace trans_mn); * Partitioned version of SALE create table sale (sale_id number, fy number ..) partition by range (fy) (partition sale_p1 values less than ('92') storage (initial 4m next 1m) tablespace trans_b1, partition sale_p2 values less than ('94') storage (initial 8m next 1m) tablespace trans_b2, partition sale_p3 values less than (maxvalue) storage (initial 2m next 1m) tablespace trans_b3); SQL>> create table sale (sale_id number, 2 cust_id number, 3 init_date date, .. .. 17 desc_f varchar2(90)) 18 partition by range (sale_id) 19 (partition sale_p1 values less than (33333333) 20 tablespace sale_ts1, 21 partition sale_p2 values less than (55555555) 22 tablespace sale_ts2, 23 partition sale_p3 values less than (88888888) 24 tablespace sale_ts3, 25 partition sale_p4 values less than (maxvalue) 26 tablespace sale_ts4); Table created. create table tst_surp_p ( surp_id number, .. region_code varchar2(1), branch_code varchar2(1), .. .. gl_group_code varchar2(2) not null, .. .. project_title varchar2(85)) storage (initial 100m next 100m pctincrease 0) partition by range (region_code) (partition tst_surp_p1 values less than ('F') tablespace tsurp_1, partition tst_surp_p2 values less than (maxvalue) tablespace tsurp_2); SQL>> alter table tst_surp_p add constraint tst_surp_p_pk 2 primary key (sale_id) using index 3 storage (initial 60m next 40m pctincrease 0) 4 initrans 8 maxtrans 8 tablespace tst_surp_idx1 5 unrecoverable; Index created. SQL>> alter table tst_surp_p add constraint tst_surp_p_fk1 2 foreign key (region_code) references region; Table altered. SQL>> alter table tst_surp_p add constraint tst_surp_p_fk2 2 foreign key (branch_code) references branch; Table altered. SQL>> alter table tst_surp_p add constraint tst_surp_p_fk3 2 foreign key (gl_group_code) references gl_group; Table altered. SQL>> create index tst_surp_1 on tst_surp_p (region_code) 2 storage (initial 60m next 40m pctincrease 0) 3 initrans 8 maxtrans 8 tablespace tst_surp_idx1 4 unrecoverable; Index created. SQL>> create index tst_surp_2 on tst_surp_p (branch_code) 2 storage (initial 60m next 40m pctincrease 0) 3 initrans 8 maxtrans 8 tablespace tst_surp_idx1 4 unrecoverable; Index created. SQL>> create index tst_surp_3 on tst_surp_p (gl_group_code) 2 storage (initial 60m next 40m pctincrease 0) 3 initrans 8 maxtrans 8 tablespace tst_surp_idx1 4 unrecoverable; Index created. create table tst_surp_p ( surp_id number, .. region_code varchar2(1), branch_code varchar2(1), .. .. gl_group_code varchar2(2) not null, .. .. project_title varchar2(85)) storage (initial 40m next 40m pctincrease 0) partition by range (gl_group_code,branch_code) (partition tst_surp_p1 values less than ('02', 'X') tablespace tsurp_1, partition tst_surp_p2 values less than ('03', 'X') tablespace tsurp_2, partition tst_surp_p3 values less than ('03', 'Z') tablespace tsurp_3, partition tst_surp_p4 values less than ('04', 'X') tablespace tsurp_4, partition tst_surp_p5 values less than (maxvalue,maxvalue) tablespace tsurp_5); set echo off feed off ver off pages 0 spool audon.sql select 'audit select on '||owner||'.'||object_name|| ' by access;' -- The default is by SESSION, so this keyword from dba_objects -- is IMPORTANT for this scenario. where object_type in ('VIEW','TABLE') and owner in ('FINANCE','HR','PURCHASE','CABLE'); spool off set echo on feed on ver on set echo off feed off ver off pages 0 spool audoff.sql select 'noaudit select on '||owner||'.'||object_name||';' from dba_objects where object_type in ('VIEW','TABLE') and owner in ('FINANCE','HR','PURCHASE','CABLE'); spool off set echo on feed on ver on create table aud_summary ( obj_name varchar2(30), owner varchar2(30), hits number) storage (initial 5m next 5m pctincrease 0); create tablespace paytv_index datafile 'c:_index.dbf' size 500m, 'g:_index2.dbf' size 500m; create index sale_spec on sale (fy,region) partition by range (fy,region) (partition ss_ip1 values less than ('96','G') tablespace sales_idx1, partition ss_ip2 values less than (maxvalue,maxvalue) tablespace sales_idx2); SQL>> create table country ( 2 country_code varchar2(3), 3 name varchar2(30), 4 capital varchar2(30), 5 free_form1 varchar2(1200), 6 free_form2 varchar2(1200), 7 constraint country_pk primary key (country_code)) 8 organization index tablespace misc_look 9 pctthreshold 20 overflow tablespace misc_look_overflow; Table created. * The table already exists, so must be altered to set a primary key. alter table sale add constraint-We name the constraint rather than sale_pk primary key (sale_id) -- let Oracle build a default name that using index storage (initial -- is unnecessarily cryptic. Notice how we 410m next 410m pctincrease 0) -- place the primary key index in our own tablespace dw_idx21 -- tablespace with appropriate storage unrecoverable; -- parameters and the unrecoverable * word. SQL>> alter table customer add constraint customer_pk 2 primary key (cust_id) using index storage 3 (initial 1m next 1m pctincrease 0) 3 tablespace customer_idx; Table altered. SQL>> alter table sale add consstraint sale_customer_fk 2 foreign key (cust_id) references 3 customer (cust_id); Table altered. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 32 MAXINSTANCES 16 MAXLOGHISTORY 1630 LOGFILE GROUP 1 'D:ORCL.ORA' SIZE 200K, GROUP 2 'D:ORCL.ORA' SIZE 200K DATAFILE 'D:ORCL.ORA', 'D:ORCL.ORA', 'D:ORCL.ORA', 'D:ORCL.ORA', 'FINANCE.DBF' ; # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. RECOVER DATABASE # Database can now be opened normally. ALTER DATABASE OPEN; * Create a view of DEPT_RLLUP for each region create or replace view dept_north as select * from dept_rllup where reg_cd = 'N'; create or replace view dept_west as select * from dept_rllup where reg_cd = 'W'; create or replace view dept_east as select * from dept_rllup where reg_cd = 'E'; create or replace view dept_south as select * from dept_rllup where reg_cd = 'S'; * Grant select on each region's view to the proper role grant select on dept_south to southern; grant select on dept_north to northern; grant select on dept_east to eastern; grant select on dept_west to western; select c19 as c1, c18 as c2, c22 as c3, c21 as c4, rsum(c21 for c19,c18) as c5, rsum(c21 for c19,c18) as c6, rsum(c21) as c7, c20 as c8, rsum(c20 for c19,c18) as c9, rsum(c20 for c19,c18) as c10, rsum(c20) as c11 from (select t1."PROD_LINE" as c18, t1."PROD_TYPE" as c19, (t1."PROD_PRICE" - t1."PROD_COST") as c20, t1."PROD_COST" as c21, t1."PRODUCT" as c22 from "PRODUCT" t1 where (t1."prod_line" in ('TENTS ', 'BACK PACKS ')) order by c19 asc,c18 asc ) d1 /* -------------------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* rolota.sql Read-only look and take. */ /* */ /* Corey, Abbey, Abramson, Taub */ /* -------------------------------------------------------- */ spool rolook select tablespace_name from sys.dba_tablespaces where status = 'READ ONLY'; spool off -- * Only run the output from this part if you want ALL your * tablespaces taken out of read-only mode. -- set echo off feed off pages 0 ver off trimsp on spool takoutro.sql select 'alter tablespace '||tablespace_name||' read write;' from sys.dba_tablespaces where status = 'READ ONLY'; spool off set echo on feed on -- * Uncomment (i.e., remove the double dash) the next line if you * want to run output from previous SQL statement. -- * start takoutro /* -------------------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* db_block.sql Look at database buffer usage. */ /* */ /* Corey, Abbey, Abramson, Taub */ /* -------------------------------------------------------- */ set pages 0 select 'DB_BLOCK_BUFFERS is '||value from v$parameter where name = 'db_block_buffers'; select decode(state,0,'FREE','Other'),count(*) from x$bh group by decode(state,0,'FREE','Other'); /* -------------------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* analyze.sql */ /* */ /* Corey, Abbey, Abramson, Taub */ /* -------------------------------------------------------- */ set pagesize 0 echo off verify off feedback off set linesize 200 trimspool off spool all_ana.sql select 'analyze table '||owner||'.'||table_name|| ' estimate statistics sample 20 percent;' from sys.dba_tables where owner not in ('SYS','SYSTEM'); select 'analyze index '||owner||'.'||index_name|| 'compute statistics;' from sys.dba_indexes where owner not in ('SYS','SYSTEM'); select 'analyze table '||table_name||' partition ('||partition_name|| ') estimate statistics sample 20 percent;' from sys.dba_tab_partitions where owner not in ('SYS','SYSTEM'); select 'analyze index '||index_name||' partition ('||partition_name|| ') compute statistics;' from sys.dba_ind_partitions where owner not in ('SYS','SYSTEM'); spool off set feed on echo on ------------------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* no_stats.sql */ /* */ /* Corey, Abbey, Abramson, Taub */ /* -------------------------------------------------------- */ set pages 0 echo off feed off pages 0 ver off lines 200 trimsp on spool missing.sql select 'analyze table '||owner||'.'||table_name|| ' estimate statistics sample 20 percent;' from sys.dba_tables where last_analyzed is null and owner not in ('SYS','SYSTEM'); select 'analyze index '||owner||'.'||index_name|| ' compute statistics;' from sys.dba_indexes where last_analyzed is null and owner not in ('SYS','SYSTEM'); spool off set echo on feed on spool missing start missing spool off /* --------------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* Corey, Abbey, Abramson, Taub */ /* --------------------------------------------------- */ drop tablespace temporary_data; * Remember to delete the file that used to belong to this * tablespace. Oracle no longer needs it after the drop * tablespace command finishes, so get rid of the file as well. create tablespace temporary_data datafile 'd:.dbf' size 2048m default storage (initial 100m next 100m pctincrease 0); alter tablespace temporary_data temporary; create tablespace rollback_extra datafile 'd:.dbf' size 500m default storage (initial 25m next 25m pctincrease 0); create rollback segment big_rbs tablespace rollback_extra storage (initial 25m next 25m minextents 19 maxextents 19); alter rollback segment big_rbs online; PURCHASE: PURCHASE_ID NUMBER PURCHASE_DATE DATE TERMS VARCHAR2(2) LINE_ITEM: PURCHASE_ID NUMBER LINE_NO NUMBER ITEM_ID NUMBER QUANTITY NUMBER ITEM: ITEM_ID NUMBER ITEM_DESCRIPTION VARCHAR2(40) MANU_ID NUMBER MANUFACTURER: MANU_ID NUMBER MANU_NAME VARCHAR2(40) ADDRESS_ID NUMBER ADDRESS: ADDRESS_ID NUMBER SEQ NUMBER ADDR_TEXT VARCHAR2(40) SQL>> select * from purchase; PURCHASE_ID PURCHASE_ TE ----------- --------- -- 9000 12-DEC-99 CA 9001 22-DEC-99 SX 9002 25-DEC-99 NY 3 rows selected. SQL>> select * from line_item; PURCHASE_ID LINE_NO ITEM_ID QUANTITY ----------- ---------- ---------- ---------- 9002 1 881991 9 9002 2 992811 10 9002 3 221221 90 3 rows selected. SQL>> select * from item; ITEM_ID ITEM_DESCRIPTION MANU_ID ---------- ---------------------------------------- ---------- 881991 Diagonal flange B67 90 992811 Circular blade 90 221221 Ford Fiesta 89 3 rows selected. SQL>> select * from manufacturer; MANU_ID MANU_NAME ADDRESS_ID ---------- ---------------------------------------- ---------- 90 Dave's Part Mart 9095 89 Tamara Car 2355 SQL>> select * from address; ADDRESS_ID SEQ ADDR_TEXT ---------- ---------- ---------------------------------------- 9095 1 Bay 16, 789 Flora 9095 2 Toronto ON M8U 7Y6 2355 1 General Delivery 2355 2 Chapman AB T5Y 6Y7 4 rows selected. SQL>> select a.purchase_id,a.purchase_date,a.terms, 2 c.item_description,b.quantity,d.manu_name 3 from purchase a,line_item b,item c,manufacturer d 4 where a.purchase_id = b.purchase_id 5 and b.item_id = c.item_id 6 and c.manu_id = d.manu_id 7 and a.purchase_id = 9002 8 order by b.line_no; PURCHASE_ID PURCHASE_DATE TERMS ITEM_DESCRIPTION QUANTITY MANU_NAME ----------- --------------- ----- ------------------- -------- ---------------- 9002 25-DEC-99 NY Diagonal flange B67 9 Dave's Part Mart Circular blade 10 Dave's Part Mart Ford Fiesta 90 Tamara Car 3 rows selected. SQL>> create table dwpurchase as 2 select a.purchase_id,a.purchase_date,a.terms, 3 c.item_description,b.quantity,d.manu_name 4 from purchase a,line_item b,item c,manufacturer d 5 where a.purchase_id = b.purchase_id 6 and b.item_id = c.item_id 7 and c.manu_id = d.manu_id unrecoverable; Table created. SQL>> desc dwpurchase Name Null? Type ------------------------------- -------- ---- PURCHASE_ID NOT NULL NUMBER PURCHASE_DATE DATE TERMS VARCHAR2(2) ITEM_DESCRIPTION VARCHAR2(40) QUANTITY NUMBER MANU_NAME VARCHAR2(40) /* ---------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* Corey, Abbey, Abramson, Taub */ /* ---------------------------------------------- */ -- * Create the PAYTVORDER table. -- create table paytvorder (showing_key number, promotion_key number, event_key number, subscriber_key number, company_key number, total_cost_of_order number(6,2), discount number, telephone_order_indicator varchar2(1), computer_order_indicator varchar2(1)) partition by range (subscriber_key) storage (initial 400m next 400 pctincrease 0) (partition ptvo_1 values less than (2222222), tablespace ptvo_pdata1, partition ptvo_2 values less than (4444444) tablespace ptvo_pdata2, partition ptvo_3 values less than (7777777), tablespace ptvo_pdata3, partition ptvo_4 values less than (8888999) tablespace ptvo_pdata4), partition ptvo_5 values less than (maxvalue) tablespace ptvo_pdata5); -- * Build the primary key for PAYTVORDER. -- alter table paytvorder add constraint paytvorder_pk primary key (showing_key,promotion_key, event_key,subscriber_key,company_key) using index storage (initial 400m next 400m pctincrease 0) tablespace ptvo_idx; -- * Build SHOWING_TIME, PROMOTION, CABLE_COMPANY, EVENT, * and SUBSCRIBER. Remember, these are the dimension tables and are * nothing like their OLTP counterparts. Only the columns needed for * analysis are included. -- create table showing_time (showing_key number, showing_date date, time varchar2(10), year number, month number, day number, prime_time_flag varchar2(1)) storage (initial 6m next 6m pctincrease 0) tablespace dim_tabs_data1; create table promotion (promotion_key number, promotion_description varchar2(100)) storage (initial 2m next 2m pctincrease 0) tablespace dim_tabs_data1; create table cable_company (company_key number, company_name varchar2(60), region varchar2(2), franchise_area varchar2(2)) storage (initial 2m next 2m pctincrease 0) tablespace dim_tabs_data1; create table event (event_key number, event_name varchar2(40), event_type varchar2(2), rating number, target_audience varchar2(3)) storage (initial 12m next 12m pctincrease 0) tablespace dim_tabs_data2; create table subscriber (subscriber_key number, customer_code varchar2(10), name varchar2(30), street_address varchar2(40), city varchar2(20), province_state varchar2(2), postal_code varchar2(10), country varchar2(20), cable_profile varchar2(8), service_type varchar2(3)) storage (initial 80m next 80m pctincrease 0) tablespace dim_tabs_data2; -- * Create the primary keys for the dimension tables. -- alter table showing_time add constraint showing_time_pk primary key (showing_key) using index storage (initial 800k next 800k pctincrease 0); alter table promotion add constraint promotion_pk primary key (promotion_key) using index storage (initial 200k next 200k pctincrease 0); alter table cable_company add constraint cable_company_pk primary key (company_key) using index storage (initial 800k next 800k pctincrease 0); alter table event add constraint event_pk primary key (event_key) using index storage (initial 3m next 3m pctincrease 0); alter table subscriber add constraint subscriber_pk primary key (subscriber_key) using index storage (initial 16m next 16m pctincrease 0); -- * Add foreign keys to fact table pointing at all the dimensions. -- alter table paytvorder add constraint showing_key_fk foreign key (showing_key) references showing_time; alter table paytvorder add constraint promotion_key_fk foreign key (promotion_key) references promotion; alter table paytvorder add constraint cable_company_fk foreign key (company_key) references cable_company; alter table paytvorder add constraint event_fk foreign key (event_key) references event; alter table paytvorder add constraint subscriber_fk foreign key (subscriber_key) references subscriber; /* ---------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* ckschema.sql */ /* */ /* Corey, Abbey, Abramson, Taub */ /* ---------------------------------------------- */ set echo on feed on pages 0 spool ckschema select table_name from user_tables; select column_name,table_name from user_tab_columns; select distinct name,type from user_source; select table_name,index_name from user_indexes; select table_name,index_name,column_name from user_tab_columns; select index_name,locality from user_part_indexes; select table_name,partition_name,high_value from user_tab_partitions; select index_name,partition_name,high_value from user_ind_partitions; spool off /* ---------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* givesel.sql */ /* */ /* Corey, Abbey, Abramson, Taub */ /* ---------------------------------------------- */ set echo off feed off pages 0 spool givesel select 'grant '||decode(object_type,'TABLE','select on ', 'VIEW', 'select on ', 'PROCEDURE', 'execute on ', 'PACKAGE', 'execute on ', 'FUNCTION', 'execute on ')|| object_name||' to offlook;' from user_objects where object_type in ('TABLE','VIEW','PROCEDURE', 'PACKAGE','FUNCTION'); spool off set echo on feed on grant select on offence to offlook; grant select on term to offlook; grant execute on format_cell to offlook; grant execute on yesno to offlook; /* ---------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* syndrop.sql */ /* */ /* Corey, Abbey, Abramson, Taub */ /* ---------------------------------------------- */ set echo off feed off pages 0 spool syndrop select 'drop synonym '||synonym_name||';' from user_synonyms; spool off set echo on feed on start syndrop.lst /* ---------------------------------------------- */ /* Oracle8 Data Warehousing Oracle Press 1998 */ /* */ /* syncre.sql */ /* */ /* Corey, Abbey, Abramson, Taub */ /* ---------------------------------------------- */ set echo off feed off pages 0 spool syncre select 'create synonym '||table_name||' for '||owner||'.'|| table_name||';' from user_tab_privs_recd where owner = 'OFFLOOK2'; -- Or OFFLOOK1 if activating the other schema. spool off set echo on feed on start syncre.lst /* -------------------------------------------------------- */ /* End of Oracle8 Data Warehousing code */ /* */ /* Thanks for downloading and BUY lots of books-they are */ /* good for your technical health!! */ /* -------------------------------------------------------- */ + * Michael S. Abbey Ottawa Canada * GMT -5 +1.613.290.5848 + * Co-author of Oracle8: A Beginner's Guide * Oracle8 Data Warehousing * Oracle8 Tuning * Oracle: A Beginner's Guide * Oracle Data Warehousing * Tuning Oracle