-- In Oracle versions earlier than 11g, the table was setup as READ ONLY 
-- by granting SELECT access for the table to users other than the table
-- owner.  The OS READ ONLY privilege had to be used on underlying 
-- tablespace to make the table read only for the owner.  In 11g, even 
-- owner can set the table to be READ ONLY to prevent accidental DML
-- and switch over to READ WRITE when needed.

-- Create Table (for simplicity PK/FK not setup)

CREATE TABLE edw_sales_fact
(division_id     NUMBER,
 invoice_id      NUMBER,
 quantity        NUMBER,
 sales_amt       NUMBER,
 invoice_date    DATE,
 created_date    DATE DEFAULT SYSDATE NOT NULL
);



-- Load Data

INSERT INTO edw_sales_fact
(division_id, invoice_id, quantity,
 sales_amt, invoice_date)
SELECT 100, 9001, 255.5, 25005, '15-JAN-2011' FROM DUAL;

INSERT INTO edw_sales_fact
(division_id, invoice_id, quantity,
 sales_amt, invoice_date)
SELECT 100, 9002, 171, 15005, '11-FEB-2011'  FROM DUAL;

COMMIT;



-- Alter table and set it to READ ONLY
-- Load data script fails (ORA-12081 error) even for the user as is setup
-- to be READ ONLY

ALTER TABLE edw_sales_fact READ ONLY;

INSERT INTO edw_sales_fact
(division_id, invoice_id, quantity,
 sales_amt, invoice_date)
SELECT 100, 9003, 217, 27120, '11-MAR-2011'
FROM DUAL;



-- Alter table and set it to READ WRITE
-- Load data executes inserting a row

ALTER TABLE edw_sales_fact READ WRITE;

INSERT INTO edw_sales_fact
(division_id, invoice_id, quantity,
 sales_amt, invoice_date)
SELECT 100, 9003, 217, 27120, '11-MAR-2011'
FROM DUAL;

COMMIT;