Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle Read-Only Tables and Partitions and Aurora MySQL Replicas

Oracle Usage

Beginning with Oracle 11g, tables can be marked as read-only to prevent DML operations from altering table data.

Prior to Oracle 11g, the only way to set a table to read-only mode was by limiting table privileges to SELECT. The table owner was still able to perform read and write operations. Starting from Oracle 11g, users can run an ALTER TABLE statement and change the table mode to either READ ONLY or READ WRITE.

Oracle 12c Release 2 introduces greater granularity for read-only objects and supports read-only table partitions. Any attempt to perform a DML operation on a partition, or sub-partition, set to READ ONLY results in an error.

SELECT FOR UPDATE statements aren’t allowed.

DDL operations are permitted if they don’t modify table data.

Operations on indexes are allowed on tables set to READ ONLY mode.

Examples

CREATE TABLE EMP_READ_ONLY (
EMP_ID NUMBER PRIMARY KEY,
EMP_FULL_NAME VARCHAR2(60) NOT NULL);

INSERT INTO EMP_READ_ONLY VALUES(1, 'John Smith');

1 row created

ALTER TABLE EMP_READ_ONLY READ ONLY;

INSERT INTO EMP_READ_ONLY VALUES(2, 'Steven King');

ORA-12081: update operation not allowed on table "SCT"."TBL_READ_ONLY"

ALTER TABLE EMP_READ_ONLY READ WRITE;

INSERT INTO EMP_READ_ONLY VALUES(2, 'Steven King');

1 row created

COMMIT;

SELECT * FROM EMP_READ_ONLY;

EMP_ID  EMP_FULL_NAME
1       John Smith
2       Steven King

For more information, see ALTER TABLE and Changes in This Release for Oracle Database VLDB and Partitioning Guide in the Oracle documentation.

MySQL Usage

MySQL doesn’t provide a built-in feature for read only tables, but the same functionality can be achieved using Aurora Replicas. The main disadvantage of this approach is that you must use two separated instances.

It is important to note that there is a granularity difference between this workaround and options with Oracle. you cannot mimic a single read-only table, this workaround creates a read-only copy of the database.

Example

The following walkthrough demonstrates how to create an Aurora replica:

For more information, see Create an Amazon Aurora Read Replica from an RDS MySQL DB Instance in the Amazon Web Services News Blog.