The ability for part of a table to be read-only and other parts of the same table to allow full DML is a cool feature in the Oracle Partitioning option. Perhaps the most common example you will typically see for this is range-based partitioning on a date/timestamp column. As data “ages”, setting older partitions to read-only can yield benefits such as:
- moving the older partitions to cheaper, or write-once storage
- guaranteeing that older data cannot be tampered with
- shrinking backup times because read-only data only needs to be backed up once (or twice to be sure)
But if you try this in 18c, you might get a surprise:
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
SQL>
SQL> create table t
2 ( d date,
3 id int,
4 data varchar2(20)
5 )
6 partition by range (d )
7 (
8 partition p1 values less than ( date '2018-01-01' ),
9 partition p2 values less than ( date '2019-01-01' ),
10 partition p3 values less than ( date '2020-01-01' ),
11 partition p4 values less than ( date '2021-01-01' )
12 );
Table created.
SQL>
SQL> insert into t
2 select date '2017-01-01' + rownum, rownum, 'data'||rownum
3 from dual connect by level <= date '2021-01-01' - date '2017-01-01' - 1;
1460 rows created.
--
-- Make older data read only prevent tampering
--
SQL> alter table t modify partition p1 read only;
Table altered.
SQL>
SQL> alter table t modify partition p2 read only;
Table altered.
--
-- Expose a view for the current year
--
SQL> create or replace
2 view VW as
3 select d,id from t
4 where d > date '2020-01-01';
View created.
--
-- And DML on this view should be fine...
--
SQL> delete from VW where id = 1100;
1 row deleted.
--
-- This *SHOULD* work but it does not in 18c
--
SQL> insert into VW values ( sysdate, 100);
insert into VW values ( sysdate, 100)
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
Luckily this is not an implementation restriction, its just a bug in 18c that has since been fixed. All is well in 19c, which is yet another reason this should be your “go to” release when upgrading.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
SQL> create table t
2 ( d date,
3 id int,
4 data varchar2(20)
5 )
6 partition by range (d )
7 (
8 partition p1 values less than ( date '2018-01-01' ),
9 partition p2 values less than ( date '2019-01-01' ),
10 partition p3 values less than ( date '2020-01-01' ),
11 partition p4 values less than ( date '2021-01-01' )
12 );
Table created.
SQL>
SQL> insert into t
2 select date '2017-01-01' + rownum, rownum, 'data'||rownum
3 from dual connect by level <= date '2021-01-01' - date '2017-01-01' - 1;
1460 rows created.
SQL> alter table t modify partition p1 read only;
Table altered.
SQL> alter table t modify partition p2 read only;
Table altered.
SQL> create or replace
2 view VW as
3 select d,id from t
4 where d > date '2020-01-01';
View created.
SQL> delete from VW where id = 1100;
1 row deleted.
SQL> insert into VW values ( sysdate, 100);
1 row created.
Views and read-only partitions are a great way of controlling access to particular subsets of data to ensure your users can’t get themselves with either performance issues or auditor issues! 
Learning is not a spectator sport 
Got some thoughts? Leave a comment