Naming Database Objects

Naming Database Objects
• 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
• Nonquoted names cannot be Oracle reserved
words.
• Nonquoted names must begin with an alphabetic
character from your database character set.
Naming Database Objects
When you name an object in the database you have the option of enclosing names in double
quotation marks ("). If you do this then you can break several of the naming rules mentioned in the
slide. However this is not recommended, because if you name an object this way you must always
refer to it with the quotes around the name. For example, if you name a table "Local Temp" you
must do the following:
SQL> select * from "Local Temp";
TEMP_DATE LO_TEMP HI_TEMP
--------- ---------- ----------
01-DEC-03 30 41
If you mistype the case you will get:
SQL> select * from "local temp";
select * from "local temp"
*
ERROR at line 1:
ORA-00942: table or view does not exist
Nonquoted names are stored in uppercase and are not case sensitive. When a SQL statement is
processed, nonquoted names are converted to all uppercase.
Oracle Database 10g: Administration Workshop I 8-8
8-8 Copyright © 2004, Oracle. All rights reserved.
Naming Database Objects
• Nonquoted names can contain only
– Alphanumeric characters from your database
character set
– The underscore (_)
– Dollar sign ($)
– Pound sign (#)
• No two objects can have the same name within the
same namespace
Naming Database Objects (continued)
Nonquoted identifiers can contain only alphanumeric characters from your database character set
and the underscore (_), the dollar sign ($), and the pound sign (#). Database links can also contain
periods (.) and the “at” sign (@). You are strongly discouraged 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.
Oracle Database 10g: Administration Workshop I 8-9
8-9 Copyright © 2004, Oracle. All rights reserved.
Schema Object Namespaces
The following have their
own namespace:
• Indexes
• Constraints
• Clusters
• Database triggers
• Private database
links
• Dimensions
The following are in the
same namespace:
• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone
procedures
• Stand-alone stored
functions
• Packages
• Materialized views
• User-defined types
Schema Object Namespaces
The Oracle database uses namespaces to resolve schema object references. When you refer to an
object in a SQL statement, Oracle considers the context of the SQL statement and locates the
object in the appropriate namespace. After locating the object, Oracle performs the operation
specified by the statement on the object. If the named object cannot be found in the appropriate
namespace, then Oracle returns an error.
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[@more@]