Learnitweb

Introduction to database objects

—

by

Editorial Team
in

1. Introduction

Database objects are the fundamental components of a database that are used to store, organize, and manage data. Understanding these objects is crucial for designing, implementing, and interacting with a database.

Oracle database has many database objects categorized as Schema objects and non-schema objects.

2. Schema objects

In Oracle Database, a schema is a logical container that holds a collection of database objects. These objects can include tables, views, indexes, sequences, stored procedures, functions, and more. The schema essentially provides a way to organize and manage these objects, facilitating better data management and access control.

A database user has only one schema with the same name of it’s user name.

Following are the important database objects:

  • Tables: In Oracle Database, a table is a fundamental database object that stores data in a structured format. Each table consists of rows and columns, where each row represents a record, and each column represents a specific attribute of that record.
  • Synonym: In Oracle Database, a synonym is an alias or alternative name for a database object, such as a table, view, sequence, or another synonym. Synonyms simplify access to these objects by providing a shorter or more meaningful name, and they can help manage security and accessibility within the database. We can query other user’s objects without writing it’s schema name using synonym.
  • Views: In Oracle Database, a view is a virtual table that provides a way to present data from one or more underlying tables. Unlike a physical table, a view does not store data itself but derives it from the tables upon which it is based. Views are used to simplify complex queries, enhance security, and present data in a specific format. Views act like a real table but do not take any physical space for the data.
  • Materialized views: A materialized view in Oracle Database is a database object that contains the results of a query and stores this data physically on disk. Unlike regular views, which are virtual and always reflect the latest data from the underlying tables, materialized views store a snapshot of the data, allowing for faster access and improved performance in certain scenarios.
  • Constraints: In Oracle Database, constraints are rules to enforce data integrity and ensure that the data entered into the database meets specific criteria. Constraints help maintain the accuracy and reliability of the data within the database by preventing invalid data entries.
  • Database link: In Oracle Database, a database link is a schema object that enables users to access objects in a remote database as if they were located in the local database. Database links facilitate distributed database systems, allowing for seamless data sharing and operations across different Oracle databases. Database links are connections between two physical database servers. If we use more than one database in the same network, we can reach another database object with using database links.
  • Indexes: An index in Oracle Database is a database object that improves the speed of data retrieval operations on a table or a view. Indexes create a data structure that allows the database to find and access data more efficiently, much like an index in a book helps you locate information quickly. Indexes work automatically and need not to run manually.
  • Functions and procedures: In Oracle Database, functions and procedures are both types of stored program units that allow you to encapsulate business logic and SQL statements in reusable blocks of code. They enhance modularity, maintainability, and performance by reducing the amount of repetitive code and improving code organization.
  • Packages: An Oracle package is a schema object that groups related PL/SQL types, objects, variables, and subprograms (functions and procedures) into a single unit. Packages enhance modularity, encapsulation, and reusability in PL/SQL programming, making it easier to manage and organize code.
  • Trigger: An Oracle trigger is a stored PL/SQL block that is automatically executed or fired in response to specific events on a particular table or view. Triggers are used to enforce business rules, maintain data integrity, and automate system tasks within the database.
  • Sequences: An Oracle sequence is a database object that generates a series of unique numeric values, typically used for generating primary key values for tables. Sequences are particularly useful in scenarios where unique identifiers are required for new records. Sequences can be used by multiple users. Sequences can not be rolled back. Once you call a sequence, it’s number is increased, whether you use it or not.

3. Non-schema objects

Other types of objects that are stored in the database are called non-schema objects. These objects are not part of a schema. Few examples are: directories, roles, tablespaces etc.