Team,
Here is my testcase which got failed during Synchronous refresh in mview.
create table products as
select rownum as prod_id,
object_name as prod_name,
object_type as prod_category,
object_id as prod_category_id,
data_object_id as prod_subcategory_id,
round(dbms_random.value(1,2000)) as prod_min_price,
round(dbms_random.value(1,4000)) as prod_max_price,
'Total' as prod_total,
to_date('01/01/2010','mm/dd/yyyy')+rownum as effective_from,
add_months(to_date('01/01/2010','mm/dd/yyyy')+rownum,3) as effective_to,
status as prod_status
from all_objects ;
alter table products
add constraint prod_pk
primary key(prod_id);
create table customers as
select rownum as cust_id,
object_name as cust_fname,
subobject_name as cust_lname,
decode(mod(rownum,2),0,'M','F') gender,
to_char(created,'YYYY') as cust_birth_year,
'Single' as cust_marital_status,
dbms_random.string('A',30) as cust_street_address,
dbms_random.value(1,10000) as cust_postal_code,
dbms_random.string('E',5) as cust_city,
dbms_random.string('C',5) as cust_state_province,
round(dbms_random.value(1,9999))||'-'||
round(dbms_random.value(1,999))||'-'||
round(dbms_random.value(1,999)) as cust_phone_no,
object_name||'@company.com' as cust_email,
'CustomerTotal' as cust_total,
to_date('01/01/2010','mm/dd/yyyy')+rownum as cust_effective_from,
add_months(to_date('01/01/2010','mm/dd/yyyy')+rownum,3) as cust_effective_to
from all_objects ;
alter table customers
add constraint cust_pk
primary key(cust_id);
create table sales
partition by range( quantity_sold )
( partition p1 values less than (6),
partition p2 values less than (11) )
as
select mod(rownum,10000)+1 as prod_id,
mod(rownum,1000)+1 as cust_id,
to_date('01-Jan-1998','dd-mon-yyyy')
+mod(rownum,4000) as time_id,
mod(rownum,10) as quantity_sold,
round(dbms_random.value(1,5000)) as amount_sold
from all_objects ;
alter table sales
add constraint fk_sales_prod
foreign key(prod_id)
references products
modify prod_id not null;
alter table sales
add constraint fk_sales_cust
foreign key(cust_id)
references customers
modify cust_id not null;
create materialized view sales_mv
partition by range( quantity_sold )
( partition p1 values less than (6),
partition p2 values less than (11) )
build immediate
refresh on demand using trusted constraint
as
select p.prod_id,p.prod_name,
c.cust_id,c.cust_fname, s.quantity_sold,
sum(s.quantity_sold), count(s.quantity_sold),
count(*)
from products p, sales s,
customers c
where p.prod_id = s.prod_id
and c.cust_id = s.cust_id
group by p.prod_id,p.prod_name,
c.cust_id,c.cust_fname, s.quantity_sold;
create materialized view log on products for synchronous refresh using products_st ;
create materialized view log on customers for synchronous refresh using customers_st;
create materialized view log on sales for synchronous refresh using sales_st;
create table sales_tmp (
prod_id number not null references products(prod_id),
cust_id number not null references customers(cust_id),
time_id date ,
quantity_sold number ,
amount_sold number );
exec dbms_sync_refresh.register_mviews('sales_mv');
begin
dbms_sync_refresh.register_partition_operation(
partition_op =>'EXCHANGE',
schema_name =>user,
base_table_name =>'SALES',
partition_name =>'P2',
outside_partn_table_schema =>USER,
outside_partn_table_name =>'SALES_TMP',
validation =>TRUE) ;
end;
/
insert into sales_tmp( prod_id, cust_id,time_id,quantity_sold,amount_sold)
values(1,1,sysdate,7,62);
commit;
exec dbms_sync_refresh.PREPARE_STAGING_LOG(user,'products');
exec dbms_sync_refresh.PREPARE_STAGING_LOG(user,'customers');
exec dbms_sync_refresh.PREPARE_STAGING_LOG(user,'sales');
while preparing the contents on SALES table got this error - have included both FK and partition key in the definition, but still error kindly advice.
demo@PDB1> exec dbms_sync_refresh.PREPARE_STAGING_LOG(user,'sales');
BEGIN dbms_sync_refresh.PREPARE_STAGING_LOG(user,'sales'); END;
*
ERROR at line 1:
ORA-31922: Foreign key must contain partition key in table "DEMO"."SALES".
ORA-06512: at "SYS.DBMS_SYNC_REFRESH_INTERNAL", line 75
ORA-06512: at "SYS.DBMS_SYNC_REFRESH_INTERNAL", line 1379
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 150
ORA-06512: at line 1
The error seems straightforward to me: none of the foreign keys include columns from the partition key!
As the docs say about this error:
The foreign key did not contain a partition key. If a table does not have a primary key, the foreign key must contain a partition key.So either:
- Change the partition scheme so includes foreign key columns
- Add a primary key to SALES
For example, immediately after creating SALES, I ran:
create sequence sale_seq;
alter table sales
add sale_id integer default sale_seq.nextval;
alter table sales
add primary key ( sale_id );
And everything else succeeded without error.