8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Read-Only Partitions and Subpartitions in Oracle Database 12c Release 2 (12.2)

From Oracle Database 12c Release 2 (12.2) onward you can mark partitions and subpartitions as read-only to protect their data from accidental change.

Read-Only Partitions

Related articles.

Read-Only Partitions

A partitioned table can be created with read-only partitions by specifying the READ ONLY clause at the partition level in the CREATE TABLE statement.

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id            NUMBER,
  code          VARCHAR2(10),
  description   VARCHAR2(50),
  created_date  DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
  PARTITION t1_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
  PARTITION t1_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')),
  PARTITION t1_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')) READ ONLY
);


SET LINESIZE 100
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
COLUMN read_only FORMAT A9

SELECT table_name,
       partition_name,
       read_only
FROM   user_tab_partitions
WHERE  table_name = 'T1'
ORDER BY 1, 2;

TABLE_NAME		       PARTITION_NAME		      READ_ONLY
------------------------------ ------------------------------ ---------
T1			       T1_2016			      NO
T1			       T1_2017			      NO
T1			       T1_2018			      YES

SQL>

Alternatively the table can be created as read-only, with some partitions marked as read-write by exception.

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id            NUMBER,
  code          VARCHAR2(10),
  description   VARCHAR2(50),
  created_date  DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
)
READ ONLY
PARTITION BY RANGE (created_date)
(
  PARTITION t1_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
  PARTITION t1_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')),
  PARTITION t1_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')) READ WRITE
);


SET LINESIZE 100
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
COLUMN read_only FORMAT A9

SELECT table_name,
       partition_name,
       read_only
FROM   user_tab_partitions
WHERE  table_name = 'T1'
ORDER BY 1, 2;

TABLE_NAME		       PARTITION_NAME		      READ_ONLY
------------------------------ ------------------------------ ---------
T1			       T1_2016			      YES
T1			       T1_2017			      YES
T1			       T1_2018			      NO

SQL>

The status of a partition can be switched using the ALTER TABLE statement.

ALTER TABLE t1 MODIFY PARTITION t1_2016 READ WRITE;
ALTER TABLE t1 MODIFY PARTITION t1_2017 READ WRITE;
ALTER TABLE t1 MODIFY PARTITION t1_2018 READ ONLY;


SET LINESIZE 100
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
COLUMN read_only FORMAT A9

SELECT table_name,
       partition_name,
       read_only
FROM   user_tab_partitions
WHERE  table_name = 'T1'
ORDER BY 1, 2;

TABLE_NAME		       PARTITION_NAME		      READ_ONLY
------------------------------ ------------------------------ ---------
T1			       T1_2016			      NO
T1			       T1_2017			      NO
T1			       T1_2018			      YES

SQL>

Attempting to insert into a read-only partition results in an error.

SQL> INSERT INTO t1 VALUES (1, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2018', 'DD-MON-YYYY'));
INSERT INTO t1 VALUES (1, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2018', 'DD-MON-YYYY'))
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.


SQL>

Read-Only Subpartitions

A subpartitioned table can be created with read-only subpartitions by specifying the READ ONLY clause at the subpartition level in the CREATE TABLE statement.

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id            NUMBER,
  code          VARCHAR2(10),
  description   VARCHAR2(50),
  created_date  DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
)
PARTITION BY LIST (code)
SUBPARTITION BY RANGE (created_date) (
  PARTITION part_gbr VALUES ('GBR') (
    SUBPARTITION subpart_gbr_2016 VALUES LESS THAN (TO_DATE('01-JUL-2017', 'DD-MON-YYYY')) READ ONLY,
    SUBPARTITION subpart_gbr_2017 VALUES LESS THAN (TO_DATE('01-JUL-2018', 'DD-MON-YYYY')),
    SUBPARTITION subpart_gbr_2018 VALUES LESS THAN (TO_DATE('01-JUL-2019', 'DD-MON-YYYY'))
  ),
  PARTITION part_ire VALUES ('IRE') (
    SUBPARTITION subpart_ire_2016 VALUES LESS THAN (TO_DATE('01-JUL-2017', 'DD-MON-YYYY')) READ ONLY,
    SUBPARTITION subpart_ire_2017 VALUES LESS THAN (TO_DATE('01-JUL-2018', 'DD-MON-YYYY')),
    SUBPARTITION subpart_ire_2018 VALUES LESS THAN (TO_DATE('01-JUL-2019', 'DD-MON-YYYY'))
  )
);


SET LINESIZE 120
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN read_only FORMAT A9

SELECT table_name,
       partition_name,
       subpartition_name,
       read_only
FROM   user_tab_subpartitions
WHERE  table_name = 'T1'
ORDER BY 1, 2;

TABLE_NAME	     PARTITION_NAME	  SUBPARTITION_NAME    READ_ONLY
-------------------- -------------------- -------------------- ---------
T1		     PART_GBR		  SUBPART_GBR_2016     YES
T1		     PART_GBR		  SUBPART_GBR_2017     NO
T1		     PART_GBR		  SUBPART_GBR_2018     NO
T1		     PART_IRE		  SUBPART_IRE_2016     YES
T1		     PART_IRE		  SUBPART_IRE_2017     NO
T1		     PART_IRE		  SUBPART_IRE_2018     NO

SQL>

Alternatively the table or partition can be created as read-only, with some subpartitions marked as read-write by exception.

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id            NUMBER,
  code          VARCHAR2(10),
  description   VARCHAR2(50),
  created_date  DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
)
READ ONLY
PARTITION BY LIST (code)
SUBPARTITION BY RANGE (created_date) (
  PARTITION part_gbr VALUES ('GBR') READ WRITE (
    SUBPARTITION subpart_gbr_2016 VALUES LESS THAN (TO_DATE('01-JUL-2017', 'DD-MON-YYYY')) READ ONLY,
    SUBPARTITION subpart_gbr_2017 VALUES LESS THAN (TO_DATE('01-JUL-2018', 'DD-MON-YYYY')),
    SUBPARTITION subpart_gbr_2018 VALUES LESS THAN (TO_DATE('01-JUL-2019', 'DD-MON-YYYY'))
  ),
  PARTITION part_ire VALUES ('IRE') (
    SUBPARTITION subpart_ire_2016 VALUES LESS THAN (TO_DATE('01-JUL-2017', 'DD-MON-YYYY')),
    SUBPARTITION subpart_ire_2017 VALUES LESS THAN (TO_DATE('01-JUL-2018', 'DD-MON-YYYY')) READ WRITE,
    SUBPARTITION subpart_ire_2018 VALUES LESS THAN (TO_DATE('01-JUL-2019', 'DD-MON-YYYY')) READ WRITE
  )
);


SET LINESIZE 120
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN read_only FORMAT A9

SELECT table_name,
       partition_name,
       subpartition_name,
       read_only
FROM   user_tab_subpartitions
WHERE  table_name = 'T1'
ORDER BY 1, 2;

TABLE_NAME	     PARTITION_NAME	  SUBPARTITION_NAME    READ_ONLY
-------------------- -------------------- -------------------- ---------
T1		     PART_GBR		  SUBPART_GBR_2016     YES
T1		     PART_GBR		  SUBPART_GBR_2017     NO
T1		     PART_GBR		  SUBPART_GBR_2018     NO
T1		     PART_IRE		  SUBPART_IRE_2016     YES
T1		     PART_IRE		  SUBPART_IRE_2017     NO
T1		     PART_IRE		  SUBPART_IRE_2018     NO

SQL>

The status of a subpartition can be switched using the ALTER TABLE statement.

ALTER TABLE t1 MODIFY SUBPARTITION subpart_gbr_2016 READ WRITE;
ALTER TABLE t1 MODIFY SUBPARTITION subpart_gbr_2017 READ WRITE;
ALTER TABLE t1 MODIFY SUBPARTITION subpart_gbr_2018 READ ONLY;
ALTER TABLE t1 MODIFY SUBPARTITION subpart_ire_2016 READ WRITE;
ALTER TABLE t1 MODIFY SUBPARTITION subpart_ire_2017 READ WRITE;
ALTER TABLE t1 MODIFY SUBPARTITION subpart_ire_2018 READ ONLY;


SET LINESIZE 120
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN read_only FORMAT A9

SELECT table_name,
       partition_name,
       subpartition_name,
       read_only
FROM   user_tab_subpartitions
WHERE  table_name = 'T1'
ORDER BY 1, 2;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME    READ_ONLY
-------------------- -------------------- -------------------- ---------
T1                   PART_GBR             SUBPART_GBR_2016     NO
T1                   PART_GBR             SUBPART_GBR_2017     NO
T1                   PART_GBR             SUBPART_GBR_2018     YES
T1                   PART_IRE             SUBPART_IRE_2016     NO
T1                   PART_IRE             SUBPART_IRE_2017     NO
T1                   PART_IRE             SUBPART_IRE_2018     YES

SQL>

Attempting to insert into a read-only subpartition results in an error.

SQL> INSERT INTO t1 VALUES (1, 'GBR', 'Description for GBR', TO_DATE('01-JUL-2018', 'DD-MON-YYYY'));
INSERT INTO t1 VALUES (1, 'GBR', 'Description for GBR', TO_DATE('01-JUL-2018', 'DD-MON-YYYY'))
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.


SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.