OCI_COST_DATA_MV.sql

-- create a temp table
CREATE TABLE OCI_COST_DATA_MV_TMP AS
SELECT * FROM OCI_COST_DATA;

-- add primary key
ALTER TABLE OCI_COST_DATA_MV_TMP ADD CONSTRAINT REFERENCE_NUMBER_TMP_PK PRIMARY KEY(REFERENCE_NUMBER);

-- Build the materialized view using the existing temp table
CREATE MATERIALIZED VIEW OCI_COST_DATA_MV_TMP
ON PREBUILT TABLE
REFRESH COMPLETE
ON DEMAND
AS
SELECT * FROM OCI_COST_DATA;

-- Create actual data table
CREATE TABLE OCI_COST_DATA_MV AS
SELECT * FROM OCI_COST_DATA_MV_TMP;

-- Create actual PK
ALTER TABLE OCI_COST_DATA_MV ADD CONSTRAINT REFERENCE_NUMBER_PK PRIMARY KEY(REFERENCE_NUMBER);