|Oracle8i SQL Reference
Release 2 (8.1.6)
Basic Elements of Oracle SQL, 9 of 10
This section provides:
The following rules apply when naming schema objects:
If your database character set contains multibyte characters, Oracle recommends that each name for a user or a role contain at least one single-byte character.
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. For a list of a product's reserved words, see the manual for the specific product, such as PL/SQL User's Guide and Reference.
DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally. Therefore, if you use these words as names for objects and object parts, 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.
The following figure shows the namespaces for schema objects. Each box is a namespace. Tables and views are in the same namespace. Therefore, 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.
The following figure shows the namespaces for nonschema objects. Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.
If you give a schema object a name enclosed in double quotation marks, you must use double quotation marks whenever you refer to the object.
Enclosing a name in double quotes allows it to:
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:
If you give a user or password a quoted name, the name cannot contain lowercase letters.
Database link names cannot be quoted.
The following examples are valid schema object names:
Although column aliases, table aliases, usernames, and passwords are not objects or parts of objects, they must also follow these naming rules with these exceptions:
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 database with a name like PMDD instead of PAYMENT_DUE_DATE.
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 FIN_.
Use the same names to describe the same things across tables. For example, the department number columns of the sample EMP and DEPT tables are both named DEPTNO.