Read-Only Partitions and Subpartitions in the Oracle Database

Today's throwback feature of the week is Read-Only Partitions and Subpartitions. Read-only partitions let you block the inserts, updates, or deletes. Any attempts to change the data will fail with an error.

Why Use Read-Only partitions?

Read-only partitions are great when you're working with historical / archival data, compliance records, or data intended for reporting and audits. They benefit from consistency, accuracy and database backups. Because read-only partitions don't create redo, RMAN block-change-tracking skips them during incremental backups. This shortens backup time and can reduce storage. While you can change partitions from read-only to read write, it's a conscious decision that requires the appropriate database privileges.

Creating Tables with Read-Only Partitions

You can create read-only partitions or subpartitions when creating the table or change existing partitions later. Partitions are typically set to allow updates unless explicitly defined as read-only.

You can run the code in this blog without setting up a database using the link below. To lean more about this feature and LiveSQL, check out Layla's post here

Here's how to create a table with read-only partitions:

CREATE TABLE orders (
  order_id NUMBER(12),
  order_date DATE NOT NULL,
  state VARCHAR2(2)
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (state)
(
  PARTITION order_p1 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')) READ ONLY (
    SUBPARTITION order_p1_CA VALUES ('CA'),
    SUBPARTITION order_p1_NY VALUES ('NY')
  ),
  PARTITION order_p2 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')) (
    SUBPARTITION order_p2_CA VALUES ('CA'),
    SUBPARTITION order_p2_NY VALUES ('NY') READ ONLY
  )
);

Partition `order_p1` and its subpartitions are read-only. Partition `order_p2` is read-write except for subpartition `order_p2_NY`, which is read-only. Let's add some data:

INSERT INTO orders VALUES (101, TO_DATE('15-JUN-2021','DD-MON-YYYY'), 'CA');

INSERT INTO orders VALUES (102, TO_DATE('15-JUN-2020','DD-MON-YYYY'), 'NY');

Notice that the second insert statement fails because its marked as read only.

Creating a Read-Only Table vs Read-Only Partitions

You can also set a table as read-only and make specific parititions read write. This is useful when most data shouldn’t change and only a few partitions need updates.

Here's an example:

CREATE TABLE sales_history (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER
) READ ONLY
PARTITION BY RANGE (sale_date) (
  PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')),
  PARTITION sales_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')) READ WRITE
);

Let's try another set of inserts

INSERT INTO sales_history VALUES (1, TO_DATE('15-JUN-2020','DD-MON-YYYY'), 100);

INSERT INTO sales_history VALUES (2, TO_DATE('15-JUN-2019','DD-MON-YYYY'), 200);

The first statement works, but the read only 'older' partition fails

Checking Partition Status

-- For partitions
SELECT partition_name, read_only
FROM user_tab_partitions
WHERE table_name = 'ORDERS';

-- For subpartitions
SELECT partition_name, subpartition_name, read_only
FROM user_tab_subpartitions
WHERE table_name = 'ORDERS';

Changing Partition Status

You can also update partition statuses at any time:

ALTER TABLE orders MODIFY PARTITION order_p1 READ WRITE;
ALTER TABLE orders MODIFY SUBPARTITION order_p2_NY READ WRITE;

Cleaning Up

drop table orders purge;
drop table sales_history purge;

Final Thoughts

Read-only partitions are easy to use and helpful with:

• Archiving historical data

• Maintaining compliance

• Protecting reporting datasets

Find the read-only partitioning or subpartitioning docs here

-Killian