Read only partitions

Posted by

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! Smile

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.