CREATE VIEW|V4.2.5| docs|Distributed Database

CREATE VIEW

Last Updated：2026-02-11 07:43:39 Updated

Purpose

This statement is used to create a view.

Limitations and considerations

If the OR REPLACE clause is specified, this statement can replace an existing view.
A view does not actually exist as a table in the database. Instead, it is derived from the SELECT statement specified in the CREATE VIEW statement. Each time the view is used, it is derived.
If two or more tables or views are referenced in the FROM clause, the view is called a join view. An updatable join view, also known as a modifiable join view, involves two or more base tables or views and allows DML operations. Updatable views are not restricted by the WITH READ ONLY clause.
To be updatable, a view must meet several conditions. One common rule is that for a join view, an INSERT, UPDATE, or DELETE operation can only modify one base table at a time.

Privilege requirements

To execute the CREATE VIEW statement, the current user must have the CREATE VIEW privilege. For more information about privileges in OceanBase Database, see Privilege classification in Oracle mode.

Syntax

CREATE [OR REPLACE] [{NO FORCE} | FORCE] VIEW view_name [(column_name_list)]
    AS select_stmt
    [view_check_option];

column_name_list:
    column_name [, column_name ...]

view_check_option:
    WITH READ ONLY
    | WITH CHECK OPTION

Parameters

Parameter	Description
OR REPLACE	Optional. Specifies that if a view with the specified name already exists, it should be recreated with the new definition.
{NO FORCE} \| FORCE	Optional. Controls whether the view creation is forced. `NO FORCE`: The default value. Specifies that the view is created only if the base tables exist and the owner of the schema where the view is created has the necessary privileges. `FORCE`: Specifies that the view is created regardless of whether the base tables or referenced objects exist, and whether the owner of the schema where the view is created has the necessary privileges.
view_name	The name of the view.
column_name_list	Optional. Specifies the list of column names for the view. If not specified, the column names retrieved by the `SELECT` statement are used as the view column names. The `SELECT` statement can retrieve columns from tables, or it can use expressions involving functions, constants, operators, etc. The view column names must meet the following requirements: Each column name must be unique and not repeated. The number of names in `column_name_list` must equal the number of columns retrieved by the `SELECT` statement.
select_stmt	The `SELECT` statement used to define the view. It specifies the view's definition, allowing data to be selected from base tables or other views. For more information about the structure and options of the `SELECT` statement, see SELECT statement.
view_check_option	Optional. Specifies additional properties for the view. For more information, see view_check_option below.

view_check_option

WITH READ ONLY: An option for creating or replacing a view. It specifies that the view can only be used for reading data and not for DML operations (INSERT, UPDATE, DELETE). This prevents inserting, updating, or deleting data through the view.
WITH CHECK OPTION: A syntax option for creating a view. It ensures that any data inserted or updated through the view meets the view's definition conditions. This allows DML operations through the view, but the data must satisfy the view's definition conditions after the operation.

Note

In Oracle mode, the WITH CHECK OPTION syntax does not support specifying LOCAL or CASCADED. The default is CASCADED.

Starting from V4.2.5 BP7, views with filtering conditions (where_clause) that include subqueries support the WITH CHECK OPTION clause.

Notice

When using the WITH CHECK OPTION clause in filtering conditions, the JOIN operation is not supported in the view definition.

Examples

Create test tables tbl1 and tbl2.

obclient> CREATE TABLE tbl1 (col1 INT, col2 VARCHAR(50));

obclient> CREATE TABLE tbl2 (col1 INT, col2 INT);

Create a view v1_t1 by selecting the col1 and col2 columns from table tbl1.

obclient> CREATE OR REPLACE FORCE VIEW v1_t1(vcol1, vcol2)
    AS SELECT col1, col2
       FROM tbl1;

Create a view v2_t2 based on the col1 and col2 columns from table tbl2, containing all data that meets the condition tbl2.col2 > 1.
```
obclient> CREATE VIEW v2_t2
    AS SELECT *
       FROM tbl2
       WHERE tbl2.col2 > 1 WITH CHECK OPTION;
```

Create a view v3_t3 based on the col1 and col2 columns from table tbl2 and add comments to the view v3_t3.

Create the view v3_t3.

obclient> CREATE VIEW v3_t3
    AS SELECT 
        col1,   --test.col1
        col2    --test.col2
    FROM tbl2;

Query the view comments using SHOW CREATE VIEW or DBMS_METADATA.GET_DDL.

Query the view comments using SHOW CREATE VIEW.

obclient> SHOW CREATE VIEW v3_t3;

The returned result is as follows:

+-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| VIEW  | CREATE VIEW                                                                                                                              | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| V3_T3 | CREATE VIEW "V3_T3" ("COL1", "COL2")  AS SELECT
            col1,   --test.col1
            col2    --test.col2
        FROM tbl2 | utf8mb4              | utf8mb4_bin          |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set

Query the view comments using DBMS_METADATA.GET_DDL.

obclient> SELECT DBMS_METADATA.GET_DDL('VIEW', 'V3_T3', 'SYS') FROM DUAL;

The returned result is as follows:

+-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| VIEW  | CREATE VIEW                                                                                                                              | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| V3_T3 | CREATE VIEW "V3_T3" ("COL1", "COL2")  AS SELECT
            col1,   --test.col1
            col2    --test.col2
        FROM tbl2 | utf8mb4              | utf8mb4_bin          |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.028 sec)

obclient(SYS@oracle001)[SYS]> SELECT DBMS_METADATA.GET_DDL('VIEW', 'V3_T3', 'SYS') FROM DUAL;
+------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_METADATA.GET_DDL('VIEW','V3_T3','SYS')                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE VIEW "V3_T3" ("COL1", "COL2")  AS SELECT
            col1,   --test.col1
            col2    --test.col2
        FROM tbl2 |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Specify the WITH CHECK OPTION clause after the filtering condition subquery.

obclient> CREATE OR REPLACE VIEW v4_t1
    AS SELECT *
    FROM tbl1 t1
    WHERE EXISTS (SELECT 1 FROM tbl2 t2 WHERE t2.col1 = t1.col1) WITH CHECK OPTION;