CREATE TABLE

Purpose

This statement is used to create a new table in the database.

Syntax

CREATE [hint_options] [GLOBAL TEMPORARY] TABLE table_name
      (table_definition_list) [table_option_list] [partition_option] [on_commit_option]
CREATE [GLOBAL TEMPORARY] TABLE table_name
      (table_definition_list) [table_option_list] [partition_option] [table_column_group_option] [AS] select;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
     | [,
        | [CONSTRAINT [constraint_name]] { PRIMARY KEY|UNIQUE } (column_name) //Add a constraint after all columns are created
        | [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constraint_state
        | [CONSTRAINT [constraint_name]] CHECK(expression) constraint_state
       ]

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [VISIBLE | INVISIBLE] [GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY]
    {
    [DEFAULT expression]
    [NULL | NOT NULL]
    [CONSTRAINT [constraint_name]] [ PRIMARY KEY|UNIQUE ]       //Add a constraint when creating a column
    [CONSTRAINT [constraint_name] CHECK(expression) constraint_state]
    [CONSTRAINT [constraint_name] references_clause]
    |
    [GENERATED ALWAYS] AS (expression) [VIRTUAL]
    [NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY] [SKIP_INDEX(skip_index_option_list)]
    }

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

references_clause:
     REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {SET NULL | CASCADE}]

constraint_state:
    [RELY | NORELY] [USING INDEX index_option_list] [ENABLE | DISABLE] [VALIDATE | NOVALIDATE]

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)

table_option_list:
    table_option [ table_option ...]

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    | compression
    | ENABLE ROW MOVEMENT
    | DISABLE ROW MOVEMENT
    | physical_attribute
    | parallel_clause
    | DUPLICATE_SCOPE [=] 'none|cluster'
    | TABLE_MODE [=] 'table_mode_value'
    | enable_macro_block_bloom_filter [=] {True | False}
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | MICRO_BLOCK_FORMAT_VERSION [=] {1|2}
    | COLUMN_NAME_CASE_SENSITIVE [=] {True | False}
    | MERGE_ENGINE = {delete_insert | partial_update | append_only}
    | TTL [=]col_name + INTERVAL interval_num ttl_unit BY COMPACTION
    | DELTA_FORMAT [=] 'flat | encoding'
    | SKIP_INDEX_LEVEL [=] {1 | 0}

physical_attribute_list:
    physical_attribute [physical_attribute]

physical_attribute:
    PCTFREE [=] num
    | PCTUSED num
    | INITRANS num
    | MAXTRANS num
    | STORAGE(storage_option [storage_option] ...)
    | TABLESPACE tablespace

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | TIME_ZONE = {'default' | 'time_zone'}
    | BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}

compression:
    NOCOMPRESS
    | COMPRESS { BASIC | FOR OLTP | FOR QUERY [LOW | HIGH]  | FOR ARCHIVE [LOW | HIGH]}

storage_option:
    INITIAL num [K|M|G|T|P|E]
    | NEXT num [K|M|G|T|P|E]
    | MINEXTENTS num [K|M|G|T|P|E]
    | MAXEXTENTS num [K|M|G|T|P|E]

partition_option:
      PARTITION BY HASH(column_name_list)
      [subpartition_option] hash_partition_define
    | PARTITION BY RANGE (column_name_list)
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST (column_name_list)
      [subpartition_option] (list_partition_list)
    | PARTITION BY RANGE([column_name_list]) [SIZE('size_value')] [range_partition_list]
    | PARTITION BY RANGE (column_name) INTERVAL (expr)
        [subpartition_option]
        (range_partition)

/*Template subpartitioning*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
    | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
      (range_subpartition_list)
    | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE
      (list_subpartition_list)

/*Non-template subpartitioning*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list)
    | SUBPARTITION BY RANGE (column_name_list)
    | SUBPARTITION BY LIST (column_name_list)

subpartition_list:
      (hash_subpartition_list)
    | (range_subpartition_list)
    | (list_subpartition_list)

hash_partition_define:
      PARTITIONS partition_count [TABLESPACE tablespace] [compression]
    | (hash_partition_list)

hash_partition_list:
    hash_partition [, hash_partition ...]

hash_partition:
    partition [partition_name] [subpartition_list/*Only applicable for non-template subpartitioning*/]

hash_subpartition_define:
      SUBPARTITIONS subpartition_count
    | SUBPARTITION TEMPLATE (hash_subpartition_list)

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

hash_subpartition:
    subpartition [subpartition_name]

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION [partition_name]
    VALUES LESS THAN {(expression_list) | (MAXVALUE)}
    [subpartition_list/*Only applicable for non-template subpartitioning*/]
    [ID = num] [physical_attribute_list] [compression]

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]

list_partition_list:
    list_partition [, list_partition] ...

list_partition:
    PARTITION [partition_name]
    VALUES (DEFAULT | expression_list)
    [subpartition_list /*Only applicable for non-template subpartitioning*/]
    [ID num] [physical_attribute_list] [compression]

list_subpartition_list:
    list_subpartition [, list_subpartition] ...

list_subpartition:
    SUBPARTITION [partition_name] VALUES (DEFAULT | expression_list) [physical_attribute_list]

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

on_commit_option:
    ON COMMIT DELETE ROWS
  | ON COMMIT PRESERVE ROWS

table_column_group_option:
    WITH COLUMN GROUP(all columns)
  | WITH COLUMN GROUP(each column)
  | WITH COLUMN GROUP(all columns, each column)

Parameters

table_mode_value

• NORMAL: The default value, indicating normal mode. In this mode, the probability of triggering a major compaction after a data dump is very low.
• QUEUING: In this mode, the probability of triggering a major compaction after a data dump is low.
• MODERATE: Indicates moderate mode. In this mode, the probability of triggering a major compaction after a data dump is moderate.
• SUPER: Indicates super mode. In this mode, the probability of triggering a major compaction after a data dump is high.
• EXTREME: Indicates extreme mode. In this mode, the probability of triggering a major compaction after a data dump is high.

For more information about major compactions, see Adaptive major compaction.

dynamic_partition_policy_option

• ENABLE = {true | false}: An optional parameter indicating whether to enable dynamic partition management. It can be modified. Valid values:

  • true: The default value, indicating that dynamic partition management is enabled.
  • false: Indicates that dynamic partition management is disabled.

• TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}: A required parameter indicating the time unit for partitioning, i.e., the interval for automatically creating partition boundaries. It cannot be modified. Valid values:

  • hour: Partitions are created by hour.
  • day: Partitions are created by day.
  • week: Partitions are created by week.
  • month: Partitions are created by month.
  • year: Partitions are created by year.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: An optional parameter indicating the precreation time. When dynamic partition management is scheduled, partitions are precreated so that max_partition_upper_bound > now() + precreate_time. It can be modified. Valid values:

  • -1: The default value, indicating that no partitions are precreated.
  • 0: Indicates that only the current partition is precreated.
  • n {hour | day | week | month | year}: Indicates the time span for precreating partitions. For example, 3 hour indicates that partitions are precreated for the last 3 hours.

  Note

  • When multiple partitions are to be precreated, the partition boundary interval is specified by TIME_UNIT.
  • The boundary of the first precreated partition is the ceiling of the existing maximum partition boundary based on TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: An optional parameter indicating the partition expiration time. When dynamic partition management is scheduled, all partitions with upper bounds < now() - expire_time are deleted. It can be modified. Valid values:

  • -1: The default value, indicating that partitions never expire.
  • 0: Indicates that all partitions except the current one are expired.
  • n {hour | day | week | month | year}: Indicates the partition expiration time. For example, 1 day indicates that the partition expiration time is 1 day.

• TIME_ZONE = {'default' | 'time_zone'}: An optional parameter indicating the timezone information used to determine the current time and the size of the partition key of the date and timestamp types. It cannot be modified. Valid values:

  • default: The default value, indicating that no timezone is configured and the tenant's timezone is used. For types other than date and timestamp, the time_zone field must be default.
  • time_zone: Indicates a custom timezone offset. For example, +8:00 is a timezone offset.

• BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}: An optional parameter indicating the timestamp precision of the number type partition key. It cannot be modified. Valid values:

  • none: The default value, indicating no precision (the partition key is not of the number type).
  • us: Indicates microsecond precision.
  • ms: Indicates millisecond precision.
  • s: Indicates second precision.

For more information about creating a dynamic partition table, see Create a dynamic partition table.

Here is an example:

CREATE TABLE tbl2 (col1 INT, col2 TIMESTAMP)
    DYNAMIC_PARTITION_POLICY(
        ENABLE = true,
        TIME_UNIT = 'hour',
        PRECREATE_TIME = '3 hour',
        EXPIRE_TIME = '1 day',
        TIME_ZONE = '+8:00',
        BIGINT_PRECISION = 'none')
    PARTITION BY RANGE (col2)(
        PARTITION P0 VALUES LESS THAN (TIMESTAMP '2024-11-11 13:30:00')
        );

Examples

• Create a table TEST_TBL1.

  obclient> CREATE TABLE TEST_TBL1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
  

• Create a table TEST_TBL2 and create eight partitions of the hash type.

  obclient> CREATE TABLE TEST_TBL2 (col1 INT PRIMARY KEY, col2 INT) PARTITION BY HASH(col1) PARTITIONS 8;
  

• Create a table TEST_TBL3 with a range partitioned partition set and hash partitioned subpartitions.

  obclient> CREATE TABLE TEST_TBL3 (col1 INT, col2 INT, col3 INT)
                   PARTITION BY RANGE(col1)
                   SUBPARTITION BY HASH(col2)  SUBPARTITIONS  5
                   (PARTITION p0 VALUES LESS THAN(0),
                    PARTITION p1 VALUES LESS THAN(100));
  

• Create a table TEST_TBL4 with zstd compression and 5% macroblock reserve space.

  obclient> CREATE TABLE tbl6 (col1 INT, col2 INT, col3 VARCHAR(64))
                   COMPRESS FOR ARCHIVE
                   PCTFREE 5;
  

• Create a transaction-level temporary table TEST_TBL5.

  obclient> CREATE GLOBAL TEMPORARY TABLE TEST_TBL5(col1 INT) ON COMMIT DELETE ROWS;
  

• Create a table TEST_TBL6 with constraints.

  obclient> CREATE TABLE TEST_TBL6 (col1 INT, col2 INT, col3 INT,CONSTRAINT equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
  

• Specify a foreign key for table REF_T2. When the DELETE operation affects key values in the parent table that correspond to matching rows in the child table, execute the SET NULL operation.

  obclient> CREATE TABLE REF_T1(c1 INT PRIMARY KEY,C2 INT);
  

  obclient> CREATE TABLE REF_T2(c1 INT PRIMARY KEY,C2 INT,FOREIGN KEY(c2) REFERENCES ref_t1(c1) ON DELETE SET NULL);
  

• Create a non-templated range + range subpartitioned table TEST_TBL7.

  obclient> CREATE TABLE TEST_TBL7 (col1 INT, col2 INT, col3 INT)
                   PARTITION BY RANGE(col1)
                   SUBPARTITION BY RANGE(col2)
  (
    PARTITION p0 VALUES LESS THAN(100)
    (
      SUBPARTITION p0_r1 VALUES LESS THAN(2019),
      SUBPARTITION p0_r2 VALUES LESS THAN(2020),
      SUBPARTITION p0_r3 VALUES LESS THAN(2021)
    ),
    PARTITION p1 VALUES LESS THAN(200)
    (
      SUBPARTITION p1_r1 VALUES LESS THAN(2019),
      SUBPARTITION p1_r2 VALUES LESS THAN(2020),
      SUBPARTITION p1_r3 VALUES LESS THAN(2021)
    ),
    PARTITION p2 VALUES LESS THAN(300)
    (
      SUBPARTITION p2_r1 VALUES LESS THAN(2019),
      SUBPARTITION p2_r2 VALUES LESS THAN(2020),
      SUBPARTITION p2_r3 VALUES LESS THAN(2021)
    )
  );
  

• Specify a parallelism degree of 3 for table TEST_TBL8.

  obclient> CREATE TABLE TEST_TBL8(col1 INT PRIMARY KEY, col2 INT) PARALLEL 3;
  

• Use the function to specify the default value of a column.

  obclient> CREATE SEQUENCE SEQ_PERSONIPTVSEQ START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
  

  obclient> SELECT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;
  

  The following result is returned:

  +----------------------------------------------------------------------------+
  | LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')) |
  +----------------------------------------------------------------------------+
  | 2025-04-08 19:35:1                                                         |
  +----------------------------------------------------------------------------+
  

  obclient> CREATE TABLE FUNC_DEFAULT_TEST ( OID NUMBER(20,0) DEFAULT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) NOT NULL);
  

• Create a replicated table DUP_T1 at the cluster level. You can insert and read and write data from a replicated table, as you do from a regular table. If you use a Proxy to submit a read request, the request may be routed to any OBServer node. If you directly connect to an OBServer node and its local replica is readable, the request is processed on the connected node.

  obclient> CREATE TABLE DUP_T1(c1 int) DUPLICATE_SCOPE = 'cluster';
  

  obclient> INSERT INTO DUP_T1 VALUES(1);
  

  obclient> SELECT * FROM DUP_T1;
  

  The following result is returned:

  +------+
  | C1   |
  +------+
  |    1 |
  +------+
  

• Create a columnstore table TBL_CG.

  obclient> CREATE TABLE tbl1_cg (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(50)) WITH COLUMN GROUP(each column);
  

• Specify the Skip Index attribute for a column during table creation.

  obclient> CREATE TABLE TEST_INDEX(
    col1 NUMBER SKIP_INDEX(MIN_MAX, SUM),
    col2 FLOAT SKIP_INDEX(MIN_MAX),
    col3 VARCHAR2(1024) SKIP_INDEX(MIN_MAX),
    col4 CHAR(10)
  );
  

• Create a table TEST_TBL9 with a column col1 of the integer type. Specify that 5 parallel tasks be used for this operation and specify that the contents of the new table TEST_TBL8 will be sourced from the query results of table TEST_TBL8.

  obclient> CREATE /*+ parallel(5) */ TABLE TEST_TBL9 (col1 NUMBER) AS SELECT col1 FROM TEST_TBL8;
  

• Create a table TEST_TBL10 with a column col1 as an auto-incrementing column and specified as a primary key.

  obclient> CREATE TABLE TEST_TBL10 (
    col1 INT GENERATED BY DEFAULT AS IDENTITY,
    col2 VARCHAR2(50),
    PRIMARY KEY (col1)
  );
  

• Create a table tb with a persistent macroblock-level bloom filter enabled.

  obclient> CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) enable_macro_block_bloom_filter = True;
  

• Create a table tb with the new flat row-based storage format (version 2).

  obclient> CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) micro_block_format_version = 2;
  

• Create a table with a data expiration time of 7 days.

  obclient(SYS@oracle001)[SYS]> CREATE TABLE TTL_TBL1(
    ID INT PRIMARY KEY,
    VAL VARCHER2(100)
  ) MERGE_ENGINE = append_only TTL ora_rowscn + INTERVAL 7 DAY BY COMPACTION;
  

• Create a table tbl_e and specify the incremental data format as the new encoding-based incremental data format.

  obclient> CREATE TABLE tbl_e(col1 INT PRIMARY KEY, col2 INT) DELTA_FORMAT = 'encoding';
  

Limitations of global temporary tables in Oracle compatibility mode

• Temporary tables in Oracle compatibility mode are widely used in various business scenarios and provide basic correctness and functionality guarantees.
• The primary purpose of using temporary tables is to ensure compatibility and reduce the need for business modifications. They can be used in limited scenarios where performance requirements are not high. If the business scenario can be converted to a regular table, it is better to do so.

Performance and stability

• The SQL execution efficiency of temporary tables is generally similar to that of regular tables, with no significant advantages.
• Temporary tables require additional work to clean up data when transactions end or sessions are disconnected, which incurs additional overhead.
• The checks and cleanup actions performed on temporary tables during login may put pressure on the login thread, leading to longer login times, and in severe cases, login failures.

Creating temporary tables

When creating a temporary table, the system automatically modifies the create table statement:

• Adds a new SYS_SESSION_ID column as the primary key.
• Adds a new SYS_SESS_CREATE_TIME column.
• Creates a hash-partitioned table with SYS_SESSION_ID as the partitioning key, with a fixed number of 16 partitions.

For example:

obclient> CREATE GLOBAL TEMPORARY TABLE TEST_TBL11(
  c1 INT,
  c2 INT,
  PRIMARY KEY(c1)
);

is rewritten to the following form:

obclient> CREATE GLOBAL TEMPORARY TABLE TEST_TBL11(
  SYS_SESSION_ID INT,
  SYS_SESS_CREATE_TIME INT,
  c1 INT,
  c2 INT,
  PRIMARY KEY(SYS_SESSION_ID, c1)
)
PARTITION BY HASH(SYS_SESSION_ID) PARTITIONS 16;

DML and query statements on temporary tables

When executing an INSERT statement, the system automatically inserts the current session's session ID and session creation time into the SYS_SESSION_ID and SYS_SESS_CREATE_TIME columns, respectively.

When executing UPDATE, DELETE, or SELECT statements, the system automatically adds a filter condition to the statement containing the temporary table: "SYS_SESSION_ID = current session session_id". This allows the SQL optimizer to perform partition pruning and query range extraction.

Data cleanup for temporary tables

• For temporary tables with the ON COMMIT DELETE ROWS option (transactional temporary tables, which is the default option), a new transaction is started at the end of the transaction to delete the temporary table data using an DELETE statement.
• For temporary tables with the ON COMMIT PRESERVE ROWS option (session temporary tables), a new transaction is started when the session is disconnected to delete the temporary table data using an DELETE statement.
• Due to the possibility of session ID reuse, OceanBase Database V3.2.4 BP4 and earlier versions will check the data for the current session ID upon login. If additional cleanup is needed, it will be performed.
• The login checks and cleanup operations performed due to non-unique session IDs may lead to failures (such as cluster login failures).

Routing for temporary tables

• Transactional temporary tables (ON COMMIT DELETE ROWS) Temporary table access within a transaction can only be routed to the node where the transaction was initiated.

• Session temporary tables (ON COMMIT PRESERVE ROWS) After a session accesses a temporary table, the OBServer node notifies the Proxy, which then routes subsequent requests to the current session.

Dropping temporary tables

Like regular tables, you can successfully execute DROP statements while executing DML operations. All data in the temporary table will be deleted. This behavior differs from Oracle, where you must wait until all sessions no longer hold temporary table resources before you can execute DROP.

Support for cross-functional features

Workarounds for critical issues

Login failure

1. Stop the business related to temporary tables, delete the temporary tables, or perform a major compaction. This usually resolves the issue. If the issue persists, proceed to step 2.
2. Restart the machine that cannot log in.

PL Cache expansion due to plan reuse failure

For example, consider a PL/SQL block that includes a temporary table:

obclient> CREATE OR REPLACE PROCEDURE PRO_1
AS
  var1 VARCHAR2(100);
BEGIN
  -- Use dynamic SQL to create a temporary table
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_table (
    col1 VARCHAR2(100)
  ) ON COMMIT DROP';

  -- Dynamically insert data
  EXECUTE IMMEDIATE 'INSERT INTO temp_table VALUES (''xxx'')';

  -- Dynamically query
  EXECUTE IMMEDIATE 'SELECT col1 FROM temp_table WHERE ROWNUM = 1' INTO var1;

  DBMS_OUTPUT.PUT_LINE(var1);
END PRO_1;
/

Since plans cannot be shared across sessions for temporary tables, each session must compile the PRO_1 procedure to generate a corresponding cache, which may lead to stability issues. Changing the temporary table SQL to use dynamic SQL can resolve this issue.

Data not cleaned up during failures

Failures may result in data residue, but this generally does not affect usage. If excessive residual data remains, you can drop the temporary table and recreate it.

Read-only and read/write tables in an Oracle tenant

In an Oracle tenant, you can use the CREATE TABLE statement to create READ ONLY and READ WRITE tables. You can also use the ALTER TABLE statement to change the read/write attribute of a table.

Procedure:

1. Create a regular user:

   CREATE USER test1 IDENTIFIED BY "12345";
   

2. Grant the user the CONNECT and CREATE TABLE privileges:

   GRANT CREATE SESSION TO test1;
   GRANT CREATE TABLE TO test1;
   

3. Connect to OceanBase Database as the regular user:

   obclient -hxxx.xx.xxx.xxx -P2881 -utest1@oracle001  -A
   

4. Create a read-only table:

   CREATE TABLE tb_readonly1(id INT) READ ONLY;
   

5. Attempt to insert data into the read-only table (expected to fail):

   INSERT INTO tb_readonly1 VALUES (1);
   -- Expected error: ORA-00600: internal error code, arguments: -5235, The table 'TEST1.TB_READONLY1' is read only so it cannot execute this statement
   

6. Create a read/write table:

   CREATE TABLE tb_readwrite1(id INT) READ WRITE;
   

7. Insert data into the read/write table (expected to succeed):

   INSERT INTO tb_readwrite1 VALUES (99),(98);
   -- Expected result: Query OK, 2 rows affected (0.002 sec)
   --             Records: 2  Duplicates: 0  Warnings: 0
   

8. Convert the read/write table to a read-only table:

   ALTER TABLE tb_readwrite1 READ ONLY;
   

9. Attempt to insert data into the converted read-only table (expected to fail):

   INSERT INTO tb_readwrite1 VALUES (96),(97);
   -- Expected error: ORA-00600: internal error code, arguments: -5235, The table 'TEST1.TB_READWRITE1' is read only so it cannot execute this statement
   

References

• Change a table
• Create a table
• Truncate a table
• Drop a table