--orcl
conn sh/sh
CREATE MATERIALIZED VIEW LOG ON products
WITH SEQUENCE, ROWID
(prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc,
prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure,
prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON sales
WITH SEQUENCE, ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;
--PROD1
conn test/test
select * from dba_db_links;
CREATE DATABASE LINK orcl
CONNECT TO sh IDENTIFIED BY sh USING 'orcl';
CREATE MATERIALIZED VIEW product_sales_mv2
BUILD immediate
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales,
COUNT(*) AS cnt, COUNT(s.amount_sold) AS cnt_amt
FROM sales@orcl s, products@orcl p
WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;
