|Oracle® Database SQL Reference
10g Release 2 (10.2)
|PDF · Mobi · ePub|
Some schema objects are made up of parts that you can or must name, such as the columns in a table or view, index and table partitions and subpartitions, integrity constraints on a table, and objects that are stored within a package, including procedures and stored functions. This section provides:
Rules for naming schema objects and schema object location qualifiers
Guidelines for naming schema objects and qualifiers
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.
A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.
A nonquoted identifier is not surrounded by any punctuation.
You can use either quoted or nonquoted identifiers to name any database object. However, database names, global database names, and database link names are always case insensitive and are stored as uppercase. If you specify such names as quoted identifiers, then the quotation marks are silently ignored. Please refer to 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:
Names must be from 1 to 30 bytes long with these exceptions:
Names of databases are limited to 8 bytes.
Names of database links can be as long as 128 bytes.
If an identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long. Each period separator, as well as any surrounding double quotation marks, counts as one byte. For example, suppose you identify a column like this:
The schema name can be 30 bytes, the table name can by 30 bytes, and the column name can be 30 bytes. Each of the quotation marks and periods is a single-byte character, so the total length of the identifier in this example can be up to 98 bytes.
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.
Note:The reserved word
ROWIDis an exception to this rule. You cannot use the uppercase word
ROWID, either quoted or nonquoted, as a column name. However, you can use the uppercase word as a quoted identifier that is not a column name, and you can use the word with one or more lowercase letters (for example, "
Rowid" or "
rowid") as any quoted identifier, including a column name.
The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table
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.
You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems.
Note:Oracle recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform. Please refer to Oracle Database Administrator's Guide for more information about this recommendation.
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.
Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (
The following schema objects share one namespace:
Stand-alone stored functions
Each of the following schema objects has its own namespace:
Private database links
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.
Public database links
Parameter files (
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:
employees "employees" "Employees" "EMPLOYEES"
Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:
employees EMPLOYEES "EMPLOYEES"
Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.
Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.
The following examples are valid schema object names:
last_name horse hr.hire_date "EVEN THIS & THAT!" a_very_long_and_valid_name
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:
Use full, descriptive, pronounceable names (or well-known abbreviations).
Use consistent naming rules.
Use the same name to describe the same entity or attribute across tables.
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