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] [[MERGE_ENGINE = {delete_insert | partial_update}] 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) //Specify the constraint after creating all columns.
        | [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 ]       //Adds 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] [COMMENT string_value] [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}

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-based 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 secondary partition*/
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 non-template secondary partitions can be defined.*/]

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 non-template secondary partitions can be defined.*/]
    [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 non-template secondary partitions can be defined.*/]
    [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. In this mode, the probability of triggering a major compaction after a minor compaction is extremely low.
• QUEUING: In this mode, the probability of triggering a major compaction after a minor compaction is low.
• MODERATE: Indicates moderate. In this mode, the probability of triggering a major compaction after a minor compaction is medium.
• SUPER: Indicates super. In this mode, the probability of triggering a major compaction after a minor compaction is high.
• EXTREME: Indicates extreme. In this mode, the probability of triggering a major compaction after a minor compaction is relatively high.

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

dynamic_partition_policy_option

• ENABLE = {true | false}: Optional. Specifies whether to enable dynamic partition management. Modifiable. Valid values:

  • true: The default value, indicating to enable dynamic partition management.
  • false: Indicates to disable dynamic partition management.

• TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}: Required. Specifies the time unit for partitioning, i.e., the interval at which partition boundaries are automatically created. Not modifiable. Valid values:

  • hour: Partitions are created hourly.
  • day: Partitions are created daily.
  • week: Partitions are created weekly (by week).
  • month: Partitions are created monthly.
  • year: Partitions are created annually.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the precreation time. When scheduling dynamic partition management, partitions are precreated such that maximum partition upper bound > now() + precreate_time. Modifiable. Valid values:

  • -1: The default value, indicating not to precreate partitions.
  • 0: Indicates to precreate only the current partition.
  • n {hour | day | week | month | year}: Indicates to precreate partitions for the corresponding time span. For example, 3 hour indicates to precreate partitions for the next 3 hours.

  Note

  • If multiple partitions need to be pre-created, the interval between partition boundaries is TIME_UNIT.
  • The boundary of the first pre-created partition is the existing maximum partition boundary rounded up by TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the partition expiration time. When dynamic partition management is scheduled, all partitions that meet the condition partition upper_bound < now() - expire_time are deleted. This parameter can be modified. Valid values:

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

• TIME_ZONE = {'default' | 'time_zone'}: Optional. Specifies the time zone to use when comparing the current time with the partitioning key of a time or timestamp type. The value cannot be modified. Valid values:

  • default: The default value, which indicates that no additional time zone is configured and the tenant's time zone is used. For all types other than the above, the time_zone field must be set to default.
  • time_zone: The custom time zone offset. For example, a time zone offset such as +8:00.

• BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}: Optional. Specifies the timestamp precision for the partitioning key of the number type. This parameter cannot be modified. Valid values:

  • none: The default value, indicating no precision (the partitioning key is not of the number type).
  • us: microseconds.
  • ms: millisecond precision.
  • s: seconds.

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

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 the table TEST_TBL1.

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

• Create a table named TEST_TBL2 with eight hash partitions.

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

• Create a table named TEST_TBL3 with a range partition as the primary partition and a hash partition as the subpartition.

  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 the table TEST_TBL4, enable encoding, use zstd for compression, and set the macroblock reserved space to 5%.

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

• Create a transactional temporary table named TEST_TBL5.

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

• Create a table named 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 and execute a SET NULL operation when a DELETE operation affects the key value in the parent table that matches a row in the subtable.

  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 named 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)
    )
  );
  

• Create table TEST_TBL8 with a degree of parallelism of 3.

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

• Use a function to define 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 return result is as follows:

  +----------------------------------------------------------------------------+
  | 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 cluster-level replicated table named DUP_T1. Insert data into and read from this replicated table as you would with a regular table. For a read request, if a proxy is used, the request may be routed to any OBServer node. If connecting directly to an OBServer node, the read request will be executed on that node if the local replica is readable.

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

  obclient> INSERT INTO DUP_T1 VALUES(1);
  

  obclient> SELECT * FROM DUP_T1;
  

  The return result is as follows:

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

• Create a columnar storage table named TBL_CG.

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

• Set the skip index attribute for columns 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 the table TEST_TBL9, which has an integer column named col1. Specify that this operation should be executed with a degree of parallelism of 5, and specify that the data for the new table TEST_TBL8 will come 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 named TEST_TBL10, set column col1 as an auto-increment column, and specify it as the primary key.

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

• Create a table named tb with the macroblock-level Bloom filter persistence feature enabled.

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

• Create table tb and enable the new Flat row storage format (version 2).

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

• Create a table t_udt with a UDT column.

  Note

  The UDT COLUMN values written by the current version of OceanBase Database must be processed by CDC in online schema mode.

  obclient> CREATE TABLE t_udt (c1 int, c2 type_obj1, c3 type_array);
  

  You can use the following command to view UDT columns:

  obclient> SELECT C1, C2, C3 FROM t_udt;
  

  View UDT column attributes:

  Note

  You must specify a table alias to query the attributes of a UDT column.

  obclient> SELECT T.C2.A, T.C2.B FROM t_udt T;
  

• Specify column comments when creating the table_create_comment table.

  obclient> CREATE TABLE table_create_comment (col1 int comment 'CREATE TABLE COL1 COMMENT', col2 varchar2(100) comment 'CREATE TABLE COL2 COMMENT');
  

Limitations on using global temporary tables in Oracle compatible mode

• Temporary tables in Oracle compatible mode have practical applications in various business scenarios and are guaranteed to be basically correct and functional.
• Generally, the purpose of using temporary tables is mostly for compatibility and to reduce business transformation. They can be used when the business scenario is limited and the performance requirements for temporary tables are not high. If the business scenario can be changed to use regular tables, it is better to do so.

Performance and stability

• The SQL execution efficiency of temporary tables is basically the same as that of regular tables, with no particular advantages.
• Temporary tables require additional cleanup work when a transaction ends or a session is disconnected, which incurs extra overhead.
• The check and cleanup operations performed on temporary tables during login may put pressure on the login thread, causing login times to increase or, in severe cases, preventing login.

Create a temporary table

When creating a temporary table, the table creation statement is rewritten by default:

• Added the SYS_SESSION_ID column as the primary key.
• Added SYS_SESS_CREATE_TIME as a normal column.
• Create a HASH partitioned table with SYS_SESSION_ID as the partitioning key, and set the number of partitions to 16.

For example:

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

It will be rewritten in 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 current session ID and session creation time are inserted into the SYS_SESSION_ID column and SYS_SESS_CREATE_TIME column by default.

When executing UPDATE, DELETE, or SELECT statements, SQL rewrite adds a default filter condition "SYS_SESSION_ID= current sessionsession_id" to statements containing temporary tables. This condition enables the SQL optimizer to perform partition pruning and narrow down the query scope.

Temporary table data cleanup

• For temporary tables created with the ON COMMIT DELETE ROWS option (transactional temporary tables, which is also the default option), when a transaction ends, a new transaction is started to execute the DELETE statement for deleting the temporary table data.
• For temporary tables created with the ON COMMIT PRESERVE ROWS option (session-level temporary tables), the data in these tables is deleted by executing a DELETE statement when the session is disconnected.
• Due to the possibility of session ID reuse, OceanBase Database V3.2.4 BP4 and earlier versions perform a check on the data of the current session ID during login. If any exist, an additional cleanup is required.
• Log-in checks and clean-ups performed due to non-unique session IDs may cause failures (the cluster cannot be logged in to).

Routing of temporary tables

• Transaction temporary tables (ON COMMIT DELETE ROWS) Access to temporary tables 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 that subsequent requests from the proxy can only be sent to the current session.

Drop a temporary table

Similar to regular tables, you can successfully execute a DROP statement while DML operations are still in progress, causing all data in the temporary table to be deleted. This behavior differs from Oracle, which waits until no sessions hold resources on the temporary table before allowing a DROP.

Supported cross-feature operations

Mitigation strategies for critical issues

Unable to log in

1. Stop the business related to the temporary tables, delete them, or perform a major compaction. The system should recover on its own in most cases. If recovery is still not possible, proceed to step 2.
2. Restart the server that cannot be logged in to.

PL Cache Expansion Due to Unreusable Plans

For example, statements that contain temporary tables in the PL definition:

obclient> CREATE OR REPLACE PROCEDURE PRO_1
AS
  var1 VARCHAR2(100);
BEGIN
  -- Create a temporary table using dynamic SQL
  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'')';

  -- Dynamic Query
  EXECUTE IMMEDIATE 'SELECT col1 FROM temp_table WHERE ROWNUM = 1' INTO var1;

  DBMS_OUTPUT.PUT_LINE(var1);
END PRO_1;
/

Because different sessions cannot share execution plans for accessing temporary tables, each session must compile the PRO_1 procedure to generate its own corresponding cache, which may lead to stability issues. Changing the temporary table SQL to dynamic SQL can circumvent this problem.

Data not cleaned up in case of failure

The failure may result in data remnants. Currently, there is no automatic cleanup method, but this generally does not affect usage. If excessive residual data exists, you can resolve the issue by dropping the temporary table with a DROP statement and then rebuilding it.

Overview

In an Oracle-compatible tenant, you can use the CREATE TABLE statement to create tables that are read-only or read/write. 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 connection and table creation 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

• Modify a table
• Create a table
• Truncate a table
• Delete a table