|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
Basic Elements of Oracle SQL, 9 of 10
This section provides:
Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.
You can use either quoted or nonquoted identifiers to name any database object, with one exception: database links must be named with nonquoted identifiers. In addition, Oracle Corporation strongly recommends that you not use quotation marks to make usernames and passwords case sensitive.
CREATE USER for additional rules for naming users and passwords
The following list of rules applies to both quoted and nonquoted identifiers unless otherwise indicated:
Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words.
The reserved word
DUAL, and keywords (the uppercase words in SQL statements, such as
DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results.
In particular, do not use words beginning with "
SYS_" as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions.
Oracle Corporation recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform. Please refer to Oracle9i Database Administrator's Guide for more information about this recommendation.
Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks.
The following schema objects share one namespace:
Each of the following schema objects has its own namespace:
Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.
Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.
Each of the following nonschema objects also has its own namespace:
PFILEs) and server parameter files (
Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.
By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:
Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:
The following examples are valid schema object names:
All of these examples adhere to the rules listed in "Schema Object Naming Rules". The following example is not valid, because it exceeds 30 characters:
Although column aliases, table aliases, usernames, and passwords are not objects or parts of objects, they must also follow these naming rules unless otherwise specified in the rules themselves.
Here are several helpful guidelines for naming objects and their parts:
When naming objects, balance the objective of keeping names short and easy to use with the objective of making names as descriptive as possible. When in doubt, choose the more descriptive name, because the objects in the database may be used by many people over a period of time. Your counterpart ten years from now may have difficulty understanding a table column with a name like
pmdd instead of
Using consistent naming rules helps users understand the part that each table plays in your application. One such rule might be to begin the names of all tables belonging to the
FINANCE application with
Use the same names to describe the same things across tables. For example, the department number columns of the sample
departments tables are both named