Database Object Names and Qualifiers (original) (raw)

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. However, database names, global database names, database link names, disk group names, and pluggable database (PDB) 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.

The following list of rules applies to both quoted and nonquoted identifiers unless otherwise indicated:

• The maximum length of identifier names depends on the value of the COMPATIBLE initialization parameter.
• If COMPATIBLE is set to a value of 12.2 or higher, then names must be from 1 to 128 bytes long with these exceptions:
  • Names of databases are limited to 8 bytes.
  • Names of disk groups, pluggable databases (PDBs), rollback segments, tablespaces, and tablespace sets are limited to 30 bytes.
    If an identifier includes multiple parts separated by periods, then each attribute can be up to 128 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:
    "schema"."table"."column"
    The schema name can be 128 bytes, the table name can be 128 bytes, and the column name can be 128 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 392 bytes.
• If COMPATIBLE is set to a value lower than 12.2, then 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:
    "schema"."table"."column"
    The schema name can be 30 bytes, the table name can be 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.
• Nonquoted identifiers cannot be Oracle SQL reserved words. Quoted identifiers can be reserved words, although this is not recommended.
  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 ROWID is 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.
  See Also:
• Oracle SQL Reserved Words for a listing of all Oracle SQL reserved words
• The manual for a specific product, such as Oracle Database PL/SQL Language Reference, for a list of the reserved words of that product
• The Oracle SQL language contains other words that have special meanings. These words include data types, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, 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_ or ORA_ 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.
  See Also:
• Oracle SQL Keywords for information how to obtain a list of keywords
• Data Types, About SQL Functions, and Selecting from the DUAL Table
• You should use characters from the ASCII repertoire in database names, global database names, and database link names, because these characters provide optimal compatibility across different platforms and operating systems. You must use only characters from the ASCII repertoire in names of common users and common roles in a multitenant container database (CDB).
• You can include multibyte characters in passwords.
• Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
• Nonquoted identifiers can only contain 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 (\0).
• Within a namespace, no two objects can have the same name.
  The following schema objects share one namespace:
• Packages
• Private synonyms
• Sequences
• Stand-alone procedures
• Stand-alone stored functions
• Tables
• User-defined operators
• User-defined types
• Views
  Each of the following schema objects has its own namespace:
• Clusters
• Constraints
• Database triggers
• Dimensions
• Indexes
• Materialized views (When you create a materialized view, the database creates an internal table of the same name. This table has the same namespace as the other tables in the schema. Therefore, a schema cannot contain a table and a materialized view of the same name.)
• Private database links
  Because tables and sequences are in the same namespace, a table and a sequence 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:
• Editions
• Parameter files (PFILEs) and server parameter files (SPFILEs)
• Profiles
• Public database links
• Public synonyms
• Tablespaces
• User roles
  Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.
• Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive.
  By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:
  "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"
• When Oracle stores or compares identifiers in uppercase, the uppercase form of each character in the identifiers is determined by applying the uppercasing rules of the database character set. Language-specific rules determined by the session setting NLS_SORT are not considered. This behavior corresponds to applying the SQL function UPPER to the identifier rather than the function NLS_UPPER.
  The database character set uppercasing rules can yield results that are incorrect when viewed as being in a certain natural language. For example, small letter sharp s ("ß"), used in German, does not have an uppercase form according to the database character set uppercasing rules. It is not modified when an identifier is converted into uppercase, while the expected uppercase form in German is the sequence of two characters capital letter S ("SS"). Similarly, the uppercase form of small letter i, according to the database character set uppercasing rules, is capital letter I. However, the expected uppercase form in Turkish and Azerbaijani is capital letter I with dot above.
  The database character set uppercasing rules ensure that identifiers are interpreted the same in any linguistic configuration of a session. If you want an identifier to look correctly in a certain natural language, then you can quote it to preserve the lowercase form or you can use the linguistically correct uppercase form whenever you use that identifier.
• 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 data types. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.