OceanBase 数据库预留关键字相关问题总结

CREATE TABLE test_quote (`key` VARCHAR(20));
SELECT `key` FROM test_quote;

CREATE TABLE test_comment ("COMMENT" VARCHAR2(20));
SELECT "COMMENT" FROM test_comment;

SQL> SELECT user FROM test_quote;

USER
--------------------------------------------------------------------------------
ALVIN

SQL> SELECT "USER" FROM test_quote;

USER
------------------------------------------------------------
TOM

CREATE TABLE test_quote (user VARCHAR2(20));
CREATE TABLE test_quote ("USER" VARCHAR2(20));
INSERT INTO test_quote ("USER")  VALUES ('TOM');
COMMIT;
SELECT user FROM test_quote;
SELECT "USER" FROM test_quote;

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 30 17:56:40 2025

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE test_quote (user VARCHAR2(20));
CREATE TABLE test_quote (user VARCHAR2(20))
                         *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> CREATE TABLE test_quote ("USER" VARCHAR2(20));

Table created.

SQL> INSERT INTO test_quote ("USER")  VALUES ('TOM');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT user FROM test_quote;

USER
--------------------------------------------------------------------------------
ALVIN

SQL> SELECT "USER" FROM test_quote;

USER
------------------------------------------------------------
TOM

SELECT "USER" FROM gv$ob_processlist WHERE rownum<=1;

Server version: 8.0.41 Homebrew

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE test_quote (key VARCHAR(20));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(20))' at line 1
mysql> CREATE TABLE test_quote (`key` VARCHAR(20));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test_quote (`key`)  VALUES ('MyKey');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT key FROM test_quote;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key FROM test_quote' at line 1
mysql> SELECT `key` FROM test_quote;
+-------+
| key   |
+-------+
| MyKey |
+-------+
1 row in set (0.00 sec)

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE test_comment (comment VARCHAR2(20));
CREATE TABLE test_comment (comment VARCHAR2(20))
                           *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> CREATE TABLE test_comment ("COMMENT" VARCHAR2(20));

Table created.

SQL> INSERT INTO test_comment ("COMMENT")  VALUES ('COMMENT');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT comment FROM test_comment;
SELECT comment FROM test_comment
       *
ERROR at line 1:
ORA-00936: missing expression


SQL> SELECT "comment" FROM test_comment;
SELECT "comment" FROM test_comment
       *
ERROR at line 1:
ORA-00904: "comment": invalid identifier


SQL> SELECT "COMMENT" FROM test_comment;

COMMENT
------------------------------------------------------------
COMMENT

CREATE TABLE test_case ("COMMENT" VARCHAR2(20));
SELECT "comment" FROM test_case;
SELECT "COMMENT" FROM test_case;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE test_case ("COMMENT" VARCHAR2(20));

Table created.

SQL> SELECT "comment" FROM test_case;
SELECT "comment" FROM test_case
       *
ERROR at line 1:
ORA-00904: "comment": invalid identifier


SQL> SELECT "COMMENT" FROM test_case;

no rows selected

CREATE TABLE test_case (`Key` VARCHAR(20));
SELECT `KEY` FROM test_case;
SELECT `key` FROM test_case;

Server version: 8.0.41 Homebrew

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE test_case (`Key` VARCHAR(20));
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT `KEY` FROM test_case;
Empty set (0.01 sec)

mysql> SELECT `key` FROM test_case;
Empty set (0.00 sec)

mysql> SHOW CREATE TABLE test_case;
+-----------+------------------------------------------------------+
| Table     | Create Table                                                                                                                   |
+-----------+------------------------------------------------------+
| test_case | CREATE TABLE `test_case` (
  `Key` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT table_schema,table_name,column_name,data_type FROM information_schema.columns WHERE table_name = 'test_case' AND column_name='key';
+--------------+------------+-------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE |
+--------------+------------+-------------+-----------+
| test         | test_case  | Key         | varchar   |
+--------------+------------+-------------+-----------+
1 row in set (0.00 sec)

SELECT table_schema,table_name,column_name FROM information_schema.columns WHERE column_name IN ('ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT','BETWEEN','BY','CHAR','CHECK','CLUSTER','COLUMN','COMMENT','COMPRESS','CONNECT','CREATE','CURRENT','CASE','CONNECT_BY_ROOT','DATE','DECIMAL','DEFAULT','DELETE','DESC','DISTINCT','DROP','DUAL','ELSE','EXCLUSIVE','EXISTS','FILE','FLOAT','FOR','FROM','GRANT','GROUP','HAVING','IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX','INITIAL','INSERT','INTEGER','INTERSECT','INTO','IS','LEVEL','LIKE','LOCK','LONG','MAXEXTENTS','MINUS','MODE','MODIFY','NOAUDIT','NOCOMPRESS','NOT','NOTFOUND','NOWAIT','NULL','NUMBER','OF','OFFLINE','ON','ONLINE','OPTION','OR','ORDER','PCTFREE','PRIOR','PRIVILEGES','PUBLIC','RAW','RENAME','RESOURCE','REVOKE','ROW','ROWID','ROWLABEL','ROWNUM','ROWS','START','SELECT','SESSION','SET','SHARE','SIZE','SMALLINT','SUCCESSFUL','SYNONYM','SYSDATE','SQL_CALC_FOUND_ROWS','TABLE','THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE','USER','VALIDATE','VALUES','VARCHAR','VARCHAR2','VIEW','WHENEVER','WHERE','WITH') 
AND table_schema NOT IN ('performance_schema','mysql','sys','information_schema')
ORDER BY column_name,table_schema;

SELECT owner,table_name,column_name FROM dba_tab_columns WHERE column_name IN (
'accessible','add','alter','analyze','as','asc','asensitive','before','between','bigint','binary','blob','call','cascade','case','change','char','character','check','collate','column','condition','constraint','continue','convert','create','cross','current_date','current_time','current_timestamp','current_user','cursor','database','databases','day_hour','day_microsecond','day_minute','day_second','declare','decimal','default','delayed','delete','desc','describe','deterministic','distinctrow','div','double','drop','dual','each','else','elseif','enclosed','escaped','exists','exit','explain','fetch','float','float4','float8','for','force','foreign','fulltext','generated','get','grant','group','having','high_priority','hour_microsecond','hour_minute','hour_second','if','ignore','in','index','infile','inner','inout','insensitive','insert','int','int1','int2','int3','int4','int8','integer','interval','into','io_after_gtids','io_before_gtids','is','iterate','join','key','keys','kill','leave','left','like','limit','linear','lines','load','localtime','localtimestamp','long','longblob','longtext','loop','low_priority','master_bind','master_ssl_verify_server_cert','match','maxvalue','mediumblob','mediumint','mediumtext','middleint','minute_microsecond','minute_second','mod','modifies','natural','not','no_write_to_binlog','numeric','on','optimize','option','optionally','or','order','out','outer','outfile','partition','precision','primary','procedure','purge','range','read','reads','read_write','real','references','regexp','release','rename','repeat','replace','require','resignal','restrict','return','revoke','right','rlike','schema','schemas','second_microsecond','sensitive','separator','set','show','signal','smallint','spatial','specific','sql','sqlexception','sqlstate','sqlwarning','sql_big_result','sql_small_result','ssl','skip','starting','stored','table','terminated','then','tinyblob','tinyint','tinytext','to','trigger','undo','union','unlock','unsigned','update','usage','use','using','utc_date','utc_time','utc_timestamp','values','varbinary','varchar','varcharacter','varying','virtual','where','while','with','write','xor','year_month','zerofill'
) ORDER BY owner,table_name,column_name;

SELECT table_schema,table_name,column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name IN ('accessible','add','alter','analyze','as','asc','asensitive','before','between','bigint','binary','blob','call','cascade','case','change','char','character','check','collate','column','condition','constraint','continue','convert','create','cross','current_date','current_time','current_timestamp','current_user','cursor','database','databases','day_hour','day_microsecond','day_minute','day_second','declare','decimal','default','delayed','delete','desc','describe','deterministic','distinctrow','div','double','drop','dual','each','else','elseif','enclosed','escaped','exists','exit','explain','fetch','float','float4','float8','for','force','foreign','fulltext','generated','get','grant','group','having','high_priority','hour_microsecond','hour_minute','hour_second','if','ignore','in','index','infile','inner','inout','insensitive','insert','int','int1','int2','int3','int4','int8','integer','interval','into','io_after_gtids','io_before_gtids','is','iterate','join','key','keys','kill','leave','left','like','limit','linear','lines','load','localtime','localtimestamp','long','longblob','longtext','loop','low_priority','master_bind','master_ssl_verify_server_cert','match','maxvalue','mediumblob','mediumint','mediumtext','middleint','minute_microsecond','minute_second','mod','modifies','natural','not','no_write_to_binlog','numeric','on','optimize','option','optionally','or','order','out','outer','outfile','partition','precision','primary','procedure','purge','range','read','reads','read_write','real','references','regexp','release','rename','repeat','replace','require','resignal','restrict','return','revoke','right','rlike','schema','schemas','second_microsecond','sensitive','separator','set','show','signal','smallint','spatial','specific','sql','sqlexception','sqlstate','sqlwarning','sql_big_result','sql_small_result','ssl','skip','starting','stored','table','terminated','then','tinyblob','tinyint','tinytext','to','trigger','undo','union','unlock','unsigned','update','usage','use','using','utc_date','utc_time','utc_timestamp','values','varbinary','varchar','varcharacter','varying','virtual','where','while','with','write','xor','year_month','zerofill') 
ORDER BY column_name,table_schema;

+--------------+------------------------------------+-------------+
| table_schema | table_name                         | column_name |
+--------------+------------------------------------+-------------+
| oceanbase    | __tenant_virtual_table_column      | default     |
| oceanbase    | DBA_OBJECTS                        | GENERATED   |
| oceanbase    | __all_virtual_obrpc_stat           | index       |
| oceanbase    | DBA_IND_PARTITIONS                 | INTERVAL    |
| oceanbase    | DBA_PART_INDEXES                   | INTERVAL    |
| oceanbase    | DBA_IND_SUBPARTITIONS              | INTERVAL    |
| oceanbase    | DBA_TAB_SUBPARTITIONS              | INTERVAL    |
| oceanbase    | DBA_PART_TABLES                    | INTERVAL    |
| oceanbase    | DBA_TAB_PARTITIONS                 | INTERVAL    |
| oceanbase    | V$SQL_PLAN_MONITOR                 | KEY         |
| oceanbase    | GV$SQL_PLAN_MONITOR                | KEY         |
| oceanbase    | __all_dummy                        | key         |
| oceanbase    | __tenant_virtual_table_column      | key         |
| oceanbase    | __all_virtual_tenant_memory_info   | limit       |
| oceanbase    | __tenant_virtual_object_definition | schema      |
| oceanbase    | __tenant_virtual_table_index       | table       |
+--------------+------------------------------------+-------------+
16 rows in set (0.056 sec)

obclient [oceanbase]> SELECT key FROM oceanbase.v$sql_plan_monitor LIMIT 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'FROM oceanbase.v$sql_plan_monitor LIMIT 1' at line 1
obclient [oceanbase]> SELECT `key` FROM oceanbase.v$sql_plan_monitor LIMIT 1;
Empty set (0.050 sec)

obclient [oceanbase]> SELECT interval FROM oceanbase.dba_part_tables LIMIT 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'FROM oceanbase.dba_part_tables LIMIT 1' at line 1
obclient [oceanbase]> SELECT `interval` FROM oceanbase.dba_part_tables LIMIT 1;
+----------+
| interval |
+----------+
| NULL     |
+----------+
1 row in set (0.087 sec)

SELECT owner,table_name,column_name FROM dba_tab_columns WHERE column_name IN (
'ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT','BETWEEN','BY','CHAR','CHECK','CLUSTER','COLUMN','COMMENT','COMPRESS','CONNECT','CREATE','CURRENT','CASE','CONNECT_BY_ROOT','DATE','DECIMAL','DEFAULT','DELETE','DESC','DISTINCT','DROP','DUAL','ELSE','EXCLUSIVE','EXISTS','FILE','FLOAT','FOR','FROM','GRANT','GROUP','HAVING','IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX','INITIAL','INSERT','INTEGER','INTERSECT','INTO','IS','LEVEL','LIKE','LOCK','LONG','MAXEXTENTS','MINUS','MODE','MODIFY','NOAUDIT','NOCOMPRESS','NOT','NOTFOUND','NOWAIT','NULL','NUMBER','OF','OFFLINE','ON','ONLINE','OPTION','OR','ORDER','PCTFREE','PRIOR','PRIVILEGES','PUBLIC','RAW','RENAME','RESOURCE','REVOKE','ROW','ROWID','ROWLABEL','ROWNUM','ROWS','START','SELECT','SESSION','SET','SHARE','SIZE','SMALLINT','SUCCESSFUL','SYNONYM','SYSDATE','SQL_CALC_FOUND_ROWS','TABLE','THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE','USER','VALIDATE','VALUES','VARCHAR','VARCHAR2','VIEW','WHENEVER','WHERE','WITH'
) AND owner = 'SYS' ORDER BY owner,table_name,column_name;

+-------+----------------------------------------+-------------+
| OWNER | TABLE_NAME                             | COLUMN_NAME |
+-------+----------------------------------------+-------------+
| SYS   | DBA_OB_ARCHIVELOG                      | COMMENT     |
| SYS   | DBA_OB_ARCHIVELOG_SUMMARY              | COMMENT     |
| SYS   | DBA_OB_BACKUP_DELETE_JOBS              | COMMENT     |
| SYS   | DBA_OB_BACKUP_DELETE_JOB_HISTORY       | COMMENT     |
| SYS   | DBA_OB_BACKUP_DELETE_TASKS             | COMMENT     |
| SYS   | DBA_OB_BACKUP_DELETE_TASK_HISTORY      | COMMENT     |
| SYS   | DBA_OB_BACKUP_JOBS                     | COMMENT     |
| SYS   | DBA_OB_BACKUP_JOB_HISTORY              | COMMENT     |
| SYS   | DBA_OB_BACKUP_SET_FILES                | COMMENT     |
| SYS   | DBA_OB_BACKUP_TASKS                    | COMMENT     |
| SYS   | DBA_OB_BACKUP_TASK_HISTORY             | COMMENT     |
| SYS   | DBA_OB_BALANCE_JOBS                    | COMMENT     |
| SYS   | DBA_OB_BALANCE_JOB_HISTORY             | COMMENT     |
| SYS   | DBA_OB_BALANCE_TASKS                   | COMMENT     |
| SYS   | DBA_OB_BALANCE_TASK_HISTORY            | COMMENT     |
| SYS   | DBA_OB_DATABASES                       | COMMENT     |
| SYS   | DBA_OB_IMPORT_TABLE_JOBS               | COMMENT     |
| SYS   | DBA_OB_IMPORT_TABLE_JOB_HISTORY        | COMMENT     |
| SYS   | DBA_OB_IMPORT_TABLE_TASKS              | COMMENT     |
| SYS   | DBA_OB_IMPORT_TABLE_TASK_HISTORY       | COMMENT     |
| SYS   | DBA_OB_LS_ARB_REPLICA_TASKS            | COMMENT     |
| SYS   | DBA_OB_LS_ARB_REPLICA_TASK_HISTORY     | COMMENT     |
| SYS   | DBA_OB_LS_REPLICA_TASKS                | COMMENT     |
| SYS   | DBA_OB_LS_REPLICA_TASK_HISTORY         | COMMENT     |
| SYS   | DBA_OB_RECOVER_TABLE_JOBS              | COMMENT     |
| SYS   | DBA_OB_RECOVER_TABLE_JOB_HISTORY       | COMMENT     |
| SYS   | DBA_OB_RESTORE_HISTORY                 | COMMENT     |
| SYS   | DBA_OB_TRANSFER_PARTITION_TASKS        | COMMENT     |
| SYS   | DBA_OB_TRANSFER_PARTITION_TASK_HISTORY | COMMENT     |
| SYS   | DBA_OB_TRANSFER_TASKS                  | COMMENT     |
| SYS   | DBA_OB_TRANSFER_TASK_HISTORY           | COMMENT     |
| SYS   | GV$OB_FLT_TRACE_CONFIG                 | LEVEL       |
| SYS   | GV$OB_FUNCTION_IO_STAT                 | MODE        |
| SYS   | GV$OB_FUNCTION_IO_STAT                 | SIZE        |
| SYS   | GV$OB_GROUP_IO_STAT                    | MODE        |
| SYS   | GV$OB_PLAN_CACHE_PLAN_EXPLAIN          | ROWS        |
| SYS   | GV$OB_PROCESSLIST                      | LEVEL       |
| SYS   | GV$OB_PROCESSLIST                      | USER        |
| SYS   | GV$OB_SESSION                          | USER        |
| SYS   | GV$OB_SQL_AUDIT                        | LEVEL       |
| SYS   | GV$OB_SSTABLES                         | SIZE        |
| SYS   | V$OB_ARCHIVE_DEST_STATUS               | COMMENT     |
| SYS   | V$OB_FUNCTION_IO_STAT                  | MODE        |
| SYS   | V$OB_FUNCTION_IO_STAT                  | SIZE        |
| SYS   | V$OB_GROUP_IO_STAT                     | MODE        |
| SYS   | V$OB_LS_LOG_RESTORE_STATUS             | COMMENT     |
| SYS   | V$OB_LS_REPLICA_TASK_PLAN              | COMMENT     |
| SYS   | V$OB_PLAN_CACHE_PLAN_EXPLAIN           | ROWS        |
| SYS   | V$OB_PROCESSLIST                       | LEVEL       |
| SYS   | V$OB_PROCESSLIST                       | USER        |
| SYS   | V$OB_SESSION                           | USER        |
| SYS   | V$OB_SQL_AUDIT                         | LEVEL       |
| SYS   | V$OB_SSTABLES                          | SIZE        |
+-------+----------------------------------------+-------------+
53 rows in set (0.158 sec)

obclient [ALVIN]> SELECT USER FROM gv$ob_processlist WHERE rownum<=1;
+------+
| USER |
+------+
| ALVIN  |
+------+
1 row in set (1.218 sec)

obclient [ALVIN]> SELECT "USER" FROM gv$ob_processlist WHERE rownum<=1;
+------+
| USER |
+------+
| SYS  |
+------+
1 row in set (0.408 sec)
obclient [ALVIN]> SELECT COMMENT FROM dba_ob_databases WHERE rownum<=1;
OBE-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'COMMENT FROM dba_ob_databases WHERE rownum<=1' at line 1
obclient [ALVIN]> SELECT "COMMENT" FROM dba_ob_databases WHERE rownum<=1;
+-----------------+
| COMMENT         |
+-----------------+
| system database |
+-----------------+
1 row in set (0.008 sec)

OWNER     TABLE_NAME                    COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SYS       GV_$ARCHIVE                    CURRENT
SYS       GV_$LATCH_MISSES               WHERE
SYS       GV_$RECOVER_FILE               ONLINE
SYS       GV_$SORT_USAGE                 USER
SYS       V_$ARCHIVE                     CURRENT
SYS       V_$DIAG_DDE_USR_ACT_PARAM_DEF  DEFAULT
SYS       V_$DIAG_DIR_EXT                LEVEL
SYS       V_$DIAG_HM_RUN                 MODE
SYS       V_$DIAG_IPS_PACKAGE_HISTORY    MODE
SYS       V_$DIAG_IPS_PKG_UNPACK_HIST    MODE
SYS       V_$DIAG_VHM_RUN                MODE
SYS       V_$DIAG_VIPS_PACKAGE_HISTORY   MODE
SYS       V_$LATCH_MISSES                WHERE
SYS       V_$RECOVER_FILE                ONLINE
SYS       V_$SORT_USAGE                  USER

15 rows selected.