Imagine a massive library, but instead of books, it holds information organized in different ways. That's essentially what happens in a relational database. Each piece of information lives within a specific "database object," which acts like a labeled container keeping things tidy and accessible.
In an RDBMS, a database object is a structural element that stores or references data. It serves as a foundational unit for organizing, managing, and overseeing information within a database. This blog post delves into some of the essential objects that form the foundation of any functional application. We'll explore their roles and how they work together to store, manage, and manipulate your data efficiently. Let's get started!
A Brief Overview of Database Objects
While many more database objects are available in Oracle, some of the most essential ones are listed below:
- TABLE: This is a tabular structure designed for data storage, enforcing a rigid grid with explicit data types assigned to each column. This ensures consistency and predictability in data representation.
- INDEX: It accelerates data retrieval from tables by referencing specific values and their location, just like a phone book that organizes names alphabetically for faster searching.
- VIEW: This is a virtual table created from query expressions. Unlike a table object, a VIEW object does not physically store data; instead, it provides a dynamic window into one or more underlying tables, presenting a customized perspective based on a pre-defined query.
- SEQUENCE: A SEQUENCE object works as a counter commonly used to generate unique identifiers for new rows as they are added to a table.
- SYNONYM: It serves as an alias for another database object, often used to provide an alternative name for a table or view.
- CONSTRAINT: This establishes custom validation rules within a table to safeguard data quality and maintain referential integrity.
- USERS: The USERS object can create and own schema objects, such as tables, views, or indexes.
- ROLES: This is a set of one or more privileges that can be granted to a user, defining their access and capability within the database.
The complete list of the Oracle Database objects is available in the official reference.
What is a Schema?
A schema is a collection of certain database objects, such as tables, indexes, and views, all of which are owned by a user account. It acts as an organizational unit within a database, grouping related database objects together and separating them from other schemas. Schema might appear conceptually similar to user accounts, but there is a subtle distinction: a user account contains the database objects owned by a user, whereas a schema is the set of objects housed therein.
Schema and Non-schema Objects
A database object is either a schema object or non-schema object:
- Schema Objects
- Tables
- Constraints
- Indexes
- Views
- Sequences
- Private synonyms
- Non-schema Objects
- Users
- Roles
- Public synonyms
Roughly speaking, schema objects are those that can be directly owned by a specific user account, whereas non-schema objects are owned by database itself and cannot be attributed to individual users. For example, consider a user account (USERS object). It cannot be owned by itself or any other user account, making it a non-schema object and property of the database as a whole. Similarly, ROLES objects, which encapsulate collections of privileges, inherently exist at a level beyond individual user accounts. They are, therefore, classified as non-schema objects.
A PUBLIC SYNONYM cannot be owned by any specific user account. Rather, it belongs to a special PUBLIC user, reserved by Oracle. Thus, it is also a non-schema object. On the other hand, a PRIVATE SYNONYM, which is an alias for an object given by a specific user, is a schema object, as it is owned by the user.
Naming Rules for Database Objects
The following naming rules apply to tables, views, indexes, and all the other database objects, including a table's constraints and columns:
- Length: Names must be between 1 and 30 characters in length.
- First Character: The first character must be a letter.
- Subsequent Characters: Allowed characters include letters, numbers, $, _, and #. No other special characters can be used.
- Case Sensitivity: Names are not case-sensitive.
- Reserved Words: Reserved words used in SQL statements (e.g., SELECT, CREATE) are prohibited. Refer to the Oracle SQL Language Reference Manual for the complete list of reserved words.
To override the rules described above, it is possible to create database objects with double quotations. Quoted names vary from non-quoted names in several ways; they can include any character and are case-sensitive. For example:
To retrieve data from a quote named table, you have to use the double quotations.
While it is technically possible to override the naming rules, Oracle strongly discourages using double quotations for object names. Using quoted names may result in compatibility issues, as not all database tools, applications, and programming languages seamlessly support quoted identifiers. Additionally, manually typing double-quoted names for each reference increases the risk of typos and syntax errors, which could potentially lead to query failures.
Unique Names and Namespaces
A namespace is a conceptual boundary within the database that encapsulates a specific group of database objects. Multiple namespaces operate concurrently, contingent upon the context in which you are working.
- USER and ROLE objects are in their own collective namespace.
- PUBLIC SYNONYM objects maintain a separate namespace, exclusive to themselves.
- TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM, and user-defined TYPE objects collectively exist within a unique namespace specific to a given schema.
- INDEX objects occupy their own namespace within a given schema.
- CONSTRAINT objects have an exclusive namespace within the confines of a given schema.
In essence, you must assign distinct names to each object within its dedicated namespace. Objects existing in the same namespace necessitate unique names within that specific namespace. However, objects residing in separate namespaces are permitted to bear identical names.
Additional Tips
When assigning names to objects, choose descriptive names for the purpose of the object, and be consistent. It is also advisable to use a consistent prefix for all database objects associated with a particular application. For example, for a user management application, prefix each table with USER_. However, refrain from using prefix already designated by Oracle Corporation for system-defined objects, such as SYS_, ALL_, GV$, NLS_, ROLE_, USER_, and V$.
0 Comments