Chapter 5 -- The examples in chapter 5 assume there is an oltp database with an orders -- table. This script creates the orders tablespace, the oltp user, and the -- orders table. drop tablespace orders including contents cascade constraints; create tablespace orders datafile 'C:\ORA9IR2\ORADATA\ORCL1\orders.f' size 5m reuse autoextend on default storage (initial 16k next 16k pctincrease 0 maxextents unlimited); -- create a user called oltp -- this will be the schema where the -- objects will reside drop user oltp cascade; create user oltp identified by oltp default tablespace orders temporary tablespace temp profile default account unlock; grant unlimited tablespace to oltp; grant dba to oltp; -- Add privileges grant select any table to oltp; grant execute any procedure to oltp; connect oltp/oltp; -- Create orders table drop TABLE oltp.orders CASCADE CONSTRAINTS; CREATE TABLE oltp.orders (product_id varchar2(8) constraint not_null_product_id NOT NULL, customer_id varchar2(10) constraint not_null_customer_id NOT NULL, purchase_date date, purchase_time number(4,0), purchase_price number(6,2), shipping_charge number(5,2), today_special_offer varchar2(1), constraint special_offer CHECK (today_special_offer IN ('Y','N'))); Page 215 EXEC DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE - ('oltp', 'orders_CT', 'SYNC_SET', 'oltp', 'orders', 'product_id varchar2(8), - customer_id varchar2(10),- purchase_date date, - purchase_time number(4,0), - purchase_price number(6,2), - shipping_charge number(5,2), - today_special_offer varchar2(1)', - 'both', 'y','n','Y','n','Y','Y',null); Page 216 SELECT CHANGE_TABLE_NAME FROM CHANGE_TABLES; SELECT * FROM ALL_SOURCE_TABLES; Creating A Subscription VARIABLE subhandle NUMBER; EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE - (CHANGE_SET => 'SYNC_SET', - DESCRIPTION => 'Changes to orders table', - SUBSCRIPTION_HANDLE => :subhandle); Page 217 SQL> INSERT INTO oltp.orders(order_id,product_id, - customer_id, purchase_date, purchase_time, -purchase_price,shipping_charge,today_special_offer - sales_person_id,payment_method) - VALUES ('123','SP1031','AB123495','01-JAN-02', 1031,156.45,6.95,'N','SMITH','VISA'); SQL> INSERT INTO oltp.orders(order_id,product_id, - customer_id, purchase_date, purchase_time, -purchase_price,shipping_charge,today_special_offer - sales_person_id,payment_method) - VALUES ('123','SP1031','AB123495','01-FEB-02', 1031,156.45,6.95,'N','SMITH','VISA'); Page 218 EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW - (SUBSCRIPTION_HANDLE => :subhandle); Page 219 VARIABLE viewname VARCHAR2(4000); EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW - (SUBSCRIPTION_HANDLE => :subhandle, - SOURCE_SCHEMA => 'oltp', - SOURCE_TABLE => 'orders', - VIEW_NAME => :viewname); SELECT OPERATION$, CSCN$, COMMIT_TIMESTAMP$, USERNAME$, CUSTOMER_ID, PRODUCT_ID, RSID$ FROM cdc#cv$2131489; Page 220 EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE. DROP_SUBSCRIBER_VIEW - (SUBSCRIPTION_HANDLE => :subhandle, - SOURCE_SCHEMA => 'oltp', - SOURCE_TABLE => 'orders'); EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW - (SUBSCRIPTION_HANDLE => :subhandle); EXECUTE DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION - (SUBSCRIPTION_HANDLE => :subhandle); Page 229 - Load product dimension (product.ctl) LOAD DATA INFILE 'product.dat' append INTO TABLE product FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" (product_id, product_name, category, cost_price, sell_price, weight, shipping_charge, manufacturer, supplier) Page 230 Product.dat data file 'SP1242', 'CD LX1','MUSC', 8.90, 15.67, 2.5, 2.95, 'RTG', 'CD Inc' 'SP1243', 'CD LX2','MUSC', 8.90, 15.67, 2.5, 2.95, 'RTG', 'CD Inc' 'SP1244', 'CD LX3','MUSC', 8.90, 15.67, 2.5, 2.95, 'RTG', 'CD Inc' 'SP1245', 'CD LX4','MUSC', 8.90, 15.67, 2.5, 2.95, 'RTG', 'CD Inc' Page 237 CREATE TABLESPACE purchases_apr2002 DATAFILE 'c:\ora9ir2\oradata\orcl\PURCHASESAPR2002.f' SIZE 5M REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0 MAXEXTENTS UNLIMITED); ALTER TABLE easydw.purchases ADD PARTITION purchases_apr2002 VALUES LESS THAN (TO_DATE('30-04-2002', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0) TABLESPACE purchases_apr2002; Page 238 ALTER TABLE purchases DISABLE CONSTRAINT fk_time; ALTER TABLE purchases DISABLE CONSTRAINT fk_product_id; ALTER TABLE purchases DISABLE CONSTRAINT fk_customer_id; SELECT TABLE_NAME, CONSTRAINT_NAME, STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'PURCHASES'; SELECT TRIGGER_NAME, STATUS FROM ALL_TRIGGERS WHERE TABLE_NAME = 'PURCHASES'; Page 239 UNRECOVERABLE LOAD DATA INFILE 'purchases.dat' BADFILE 'purchases.bad' APPEND INTO TABLE purchases PARTITION (purchases_apr2002) (product_id position (1-6) char, time_key position (7-17) date "DD-MON-YYYY", customer_id position (18-25) char, purchase_date position (26-36) date "DD-MON-YYYY", purchase_time position (37-40) integer external, purchase_price position (41-45) decimal external, shipping_charge position (46-49) integer external, today_special_offer position (50) char) PURCHASES.DAT data file SP100101-APR-2002AB12345601-APR-2002002428.014.50Y SP100101-APR-2002AB12345701-APR-2002102428.014.50Y sqlldr USERID=easydw/easydw CONTROL=purchases.ctl LOG=purchases.log DIRECT=TRUE SKIP_INDEX_MAINTENANCE = TRUE Page 242 sqlldr userid=easydw/easydw CONTROL=jan.ctl DIRECT=TRUE PARALLEL=TRUE sqlldr userid=easydw/easydw CONTROL=feb.ctl DIRECT=TRUE PARALLEL=TRUE sqlldr userid=easydw/easydw CONTROL=mar.ctl DIRECT=TRUE PARALLEL=TRUE Page 244 LOAD DATA INFILE 'product.dat' append INTO TABLE product WHEN product_id != BLANKS FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" (product_id "upper(:product_id)", product_name, category, cost_price, sell_price, weight, shipping_charge, manufacturer, supplier) sqlldr userid=easydw/easydw CONTROL=product.ctl LOG=product.log BAD=product.bad DISCARD=product.dis DIRECT=true Page 245 ALTER TABLE purchases ENABLE CONSTRAINT fk_product_id EXCEPTIONS INTO exceptions; SELECT * FROM exceptions; SELECT * from purchases WHERE row_id in (select row_id from exceptions); Page 246 ALTER TABLE purchases ENABLE NOVALIDATE CONSTRAINT fk_product_id; ALTER TABLE purchases MODIFY CONSTRAINT fk_product_id RELY; Page 247 SELECT INDEX_NAME, STATUS FROM USER_INDEXES; SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS WHERE STATUS != 'VALID'; ALTER INDEX purchase_product_index REBUILD PARTITION purchases_apr2002; Page 248 CREATE OR REPLACE DIRECTORY data_file_dir as 'C:\datafiles\'; CREATE OR REPLACE DIRECTORY log_file_dir as 'C:logfiles\'; GRANT READ ON DIRECTORY data_file_dir to easydw; GRANT READ ON DIRECTORY log_file_dir to easydw; Page 249 SQL> SELECT * FROM DBA_DIRECTORIES; Page 250 CREATE TABLE new_products (product_id VARCHAR2(8), product_name VARCHAR2(30), category VARCHAR2(4), cost_price NUMBER (6,2), sell_price NUMBER (6,2), weight NUMBER (4,2), shipping_charge NUMBER (5,2), manufacturer VARCHAR2(20), supplier VARCHAR2(10)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE log_file_dir:'product.bad' LOGFILE log_file_dir:'product.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'") LOCATION ('product.dat')) REJECT LIMIT UNLIMITED PARALLEL; Page 251 'SP1000','Camera','ELEC',45.67,67.23,15.00,4.50,'Ricoh','Ricoh' 'SP1001','APS Camera','ELEC',24.67,36.23,5.00,4.50,'Ricoh','Ricoh' 'SP1010','Camera','ELEC',35.67,47.89,5.00,4.50,'Agfa','Agfa' DESCRIBE new_products; SELECT TABLE_NAME, TYPE_NAME, DEFAULT_DIRECTORY_NAME FROM USER_EXTERNAL_TABLES; SELECT * FROM USER_EXTERNAL_LOCATIONS; Page 252 SELECT * FROM NEW_PRODUCTS; INSERT INTO easydw.product (product_id, product_name, category, cost_price, sell_price, weight, shipping_charge, manufacturer, supplier) SELECT product_id, product_name, category, cost_price, sell_price, weight, (shipping_charge + (shipping_charge * .10)), manufacturer, supplier FROM new_products; SELECT * FROM easydw.product; Page 253 EXPLAIN PLAN FOR INSERT INTO easydw.product (product_id, product_name, category, cost_price, sell_price, weight, shipping_charge, manufacturer, supplier) SELECT product_id, product_name, category, cost_price, sell_price, weight, (shipping_charge + (shipping_charge * .10)), manufacturer, supplier FROM new_products; @c:\ora9iR2\rdbms\admin\utlxplp.sql Page 254 CREATE TABLE easydw.customer_changes (customer_id VARCHAR2(10), town VARCHAR2(10), county VARCHAR2(10), postal_code VARCHAR2(10), dob DATE, country VARCHAR2(20), occupation VARCHAR2(10)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE log_file_dir:'cus_changes.bad' LOGFILE log_file_dir:'cust_changes.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'") LOCATION ('customer_changes.dat')) REJECT LIMIT UNLIMITED NOPARALLEL; MERGE INTO easydw.customer c USING easydw.customer_changes cc ON (c.customer_id = cc.customer_id) WHEN MATCHED THEN UPDATE SET c.town=cc.town, c.county=cc.county, c.postal_code=cc.postal_code, c.dob=cc.dob, c.country=cc.country, c.occupation=cc.occupation WHEN NOT MATCHED THEN INSERT (customer_id, town, county, postal_code, dob, country, occupation) VALUES (cc.customer_id, cc.town, cc.county, cc.postal_code, cc.dob, cc.country, cc.occupation); SELECT * FROM customer_changes; Page 258 CREATE TABLESPACE orders datafile 'c:\ora9ir2\oradata\orcl\orders.f' SIZE 5M REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 64K PCTINCREASE 0 MAXEXTENTS UNLIMITED); CREATE TABLE apr_orders TABLESPACE orders AS SELECT * FROM purchases WHERE purchase_date BETWEEN '01-APR-2002' AND '30-APR-2002'; SELECT COUNT(*) FROM apr_orders; ALTER TABLESPACE orders READ ONLY; exp 'system/manager as sysdba' TRANSPORT_TABLESPACE=y TABLESPACES=orders TRIGGERS=n CONSTRAINTS=n GRANTS=n FILE=expdat.dmp LOG=export.log Page 259 imp 'system/manager AS SYSDBA' TRANSPORT_TABLESPACE=Y Datafiles='c:\ora9ir2\oradata\orcl\orders.f' FILE=expdat.dmp LOG=import.log Page 262 UPDATE apr_orders SET shipping_charge = (shipping_charge + shipping_charge *.10), product_id=REPLACE(product_id, '-',''); SELECT DISTINCT product_id FROM apr_orders WHERE product_id NOT IN (SELECT product_id FROM product); Page 263 SELECT * FROM APR_ORDERS; UPDATE APR_ORDERS A SET A.PRODUCT_ID = (SELECT P.PRODUCT_ID FROM PRODUCT P WHERE A.PRODUCT_ID = P.PRODUCT_CODE); Page 264 CREATE TYPE purchases_record as OBJECT (product_id VARCHAR2(8), time_key DATE, customer_id VARCHAR2(10), purchase_date DATE, purchase_time NUMBER(4,0), purchase_price NUMBER(6,2), shipping_charge NUMBER(5,2), today_special_offer VARCHAR2(1)); CREATE TYPE purchases_table AS TABLE of purchases_record; Page 265 CREATE PACKAGE cur_pack AS TYPE ref_cur_type IS REF CURSOR; END cur_pack; CREATE OR REPLACE FUNCTION transform (inputrecs IN cur_pack.ref_cur_type) RETURN purchases_table PIPELINED IS product_id VARCHAR2(8); time_key DATE; customer_id VARCHAR2(10); purchase_date DATE; purchase_time NUMBER(4,0); purchase_price NUMBER(6,2); shipping_charge NUMBER(5,2); today_special_offer VARCHAR2(1); BEGIN LOOP FETCH inputrecs INTO product_id, time_key,customer_id,purchase_date,purchase_time, purchase_price,shipping_charge,today_special_offer; EXIT WHEN INPUTRECS%NOTFOUND; product_id := REPLACE(product_id, '-',''); shipping_charge :=(shipping_charge+shipping_charge*.10); PIPE ROW(purchases_record( product_id, time_key, customer_id, purchase_date, purchase_time, purchase_price, shipping_charge, today_special_offer)); END LOOP; CLOSE inputrecs; RETURN; END; Page 266 SELECT * FROM APR_ORDERS; SELECT * FROM TABLE(transform(CURSOR(SELECT * FROM apr_orders))); CREATE TABLE TEST AS SELECT * FROM TABLE(transform(CURSOR(SELECT * FROM apr_orders))); Page 267 INSERT FIRST WHEN today_special_offer = 'Y' THEN INTO special_purchases ELSE INTO purchases SELECT * FROM apr_orders; Page 269 CREATE TABLESPACE purchases_apr2002 DATAFILE 'c:\ora9ir2\oradata\orcl\purchasesapr2002.f' SIZE 5M REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 64K NEXT 64K PCTINCREASE 0 MAXEXTENTS UNLIMITED) CREATE TABLESPACE purchases_apr2002_idx DATAFILE 'C:\ora9ir2\oradata\orcl\PURCHASESAPR2002_IDX.f' SIZE 3M REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ALTER TABLE easydw.purchases ADD PARTITION purchases_apr2002 VALUES LESS THAN (TO_DATE('30-04-2002', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITIAL 64K NEXT 164 PCTINCREASE 0) TABLESPACE purchases_apr2002; Page 271 ALTER TABLE easydw.purchases EXCHANGE PARTITION purchases_apr2002 WITH TABLE apr_orders WITHOUT VALIDATION; Page 272 INSERT /*+ APPEND */ INTO easydw.purchases SELECT * FROM apr_orders Page 273 CREATE TABLE products PARALLEL NOLOGGING AS SELECT * FROM temp_products;