Database object names and qualifiers

Some database objects can or must be named by the user, such as columns in tables and views, index and table partitions and subpartitions, integrity constraints on tables, and objects stored in a package, including stored procedures and stored functions.

Database object naming rules

Unless otherwise specified, the following rules apply to both quoted and nonquoted identifiers.

Name length

The following table lists the name lengths for general database objects.

If the identifier contains multiple parts separated with periods, each period separator, as well as any surrounding double quotation marks, counts as one byte. For example, in "schema"."table"."column", each double quotation mark (") and period (.) is a single-byte character.

Use of reserved words as identifiers

Nonquoted identifiers cannot be reserved words of OceanBase Database. Quoted identifiers can be reserved words, but this is not recommended. For more information about reserved keywords of OceanBase Database, see the "Reserved keywords" topic in Reference Guide (Oracle mode).

Notice

The reserved word ROWID is an exception to this rule. You cannot use ROWID in all uppercase as a column name, regardless of whether it is quoted or nonquoted.

Use of words with special meanings as identifiers

The SQL language contains some words with special meanings. These words include data types, schema names, function names, dummy system table DUAL, and keywords. These keywords are uppercase words in SQL statements, such SEGMENT, ALLOCATE, and DISABLE. These words are not reserved, but the database uses them internally in specific ways. Therefore, we recommend that you do not use these words as object names.

In particular, do not use words that begin with SYS_ or ORA_ as schema names, or use the names of SQL built-in functions as the names of schema objects or user-defined functions.

Use of ASCII characters as identifiers

We recommend that you use ASCII characters in database names, global database names, and database link names, because ASCII characters provide the best compatibility across different platforms and operating systems.

Characters in passwords

A password can contain multi-byte characters, such as Chinese characters and Chinese punctuation marks.

Characters in identifiers

A nonquoted identifier must begin with a letter in the database character set. A nonquoted identifier can contain only letters and digits in the database character set, underscores (_), dollar signs ($), and number signs (#). Database links can also contain periods (.) and at signs (@). We recommend that you do not use dollar signs ($) or number signs (#) in nonquoted identifiers.

A quoted identifier can begin with any character. Quoted identifiers can contain any characters, punctuation marks, and spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks ('' '') or empty characters (\0).

Limits on object names in namespaces

Within the same namespace, no two objects can have the same name. For example, tables and sequences are in the same namespace. Therefore, a table and a sequence in the same schema cannot have the same name. However, tables and indexes are not in the same namespace. Therefore, a table and an index in the same schema can have the same name.

Each schema in the database has its own namespace for objects it contains. This means that two tables in different schemas can have the same name.

The following non-schema objects also have their own namespaces. Objects in these namespaces are not included in the schema. These namespaces work across the entire database.

• Configuration files

• Public database links

• Public synonyms

• Tablespaces

• User roles

Case of identifiers

Nonquoted identifiers are not case-sensitive. OceanBase Database automatically interprets them as uppercase. Quoted identifiers are case-sensitive. By enclosing a name in double quotation marks, you can specify the name for one of the following objects in the same namespace:

"oceanbase"
"Oceanbase"
"OCEANBASE"

Note that the following names are interpreted the same. Therefore, they cannot be used for different objects in the same namespace:

oceanbase
OCEANBASE
"OCEANBASE"

Column names

Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.

Procedure names and function names

Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and data types. Creating multiple procedures or functions with the same name in the same package with different arguments is known as overloading the procedure or function.

Example for naming schema objects

The following example shows how to name schema objects:

first_name
apple
hr.hire_date
"Welcome to OceanBase!"
a_very_long_and_valid_name