What is a Database in Oracle? & Why is Oracle called RDBMS?

Database in Oracle

An electronic collection of data created to satisfy particular requirements is what an Oracle database is basically. Whether the data is related to sales, inventory, finance, or human resources, it serves as a single location for all of your data storage needs. Nowadays, data quantities in the hundreds of gigabytes are typical, but the database can handle any amount of data, no matter how small or huge.

A database’s capacity to offer fast data retrieval procedures as applications interact with its contents is an essential feature. Additionally, it facilitates the exchange of business data, exemplifying the maxim “write once, read many,” in which fundamental data, such as names and addresses, are kept in a single location and accessible by multiple systems.

A collection of data in one or more files with both logical and physical structures is specifically referred to as an Oracle database. In order to store rows and speed up their retrieval, structures like tables and indexes are established as applications are developed. Allowing data input, securely storing that data, and facilitating data retrieval and manipulation are the three fundamental acts that comprise Oracle’s core function.

Relational Database Management Systems (RDBMS)

Originally developed as a relational database, Oracle functions as an object-relational database management system (ORDBMS). A relational database, which is essentially a collection of tables, is a simple method of organising and storing corporate data that was first proposed by Dr. E. F. Codd in a 1970 study. In 1979, the first commercially available implementation of SQL (Structured Query Language), based on Codd’s concept, was released by Oracle Corporation.

Tables in relational databases are similar to spreadsheets in that they contain rows (records of information) and columns (headings). An example of a weather report might be a table with a row for each city and columns for City, Temperature, Humidity, and Condition. Oracle uses commonplace words for these components, such as “tables,” “columns,” and “rows,” instead of specialised ones like “attribute” or “tuple,” in order to make the product more user-friendly.

The “relational” component comes from the ability to connect separate tables using shared columns. A common city name field, for instance, may link a WEATHER table to a LOCATION table. This straightforward yet effective concept serves as the foundation for the relational model. These capabilities are expanded by Oracle, an object-relational database, which also supports object-oriented ideas and functionalities.

Oracle Database Architecture (High-Level)

Knowing the difference between a database and an instance is essential to comprehending Oracle’s architecture. They are separate parts, even though DBAs frequently use them interchangeably.

1. Data files, online redo log files, and control files are among the physical disc files that make up an Oracle database.
2. To enable access to those database files, a computer’s memory contains a collection of background processes and memory structures known as an Oracle instance, or more precisely, the System Global Area, or SGA.

Using the STARTUP NOMOUNT line, which just launches the memory structures and background processes, you can establish an instance without a database. Multiple instances can access a single database in an Oracle Real Application Cluster (RAC) environment, but an instance can only be linked to one database at a time.

Three separate stages are involved in the initialisation of an Oracle database:

• Nomount: The parameter file is read as the instance (memory and background processes) launches.
• Mount: Opens the control files for the database. Important details on the names, locations, and sizes of the other files in the database are contained in these binary files. Although Oracle requires a minimum of one control file, it is frequently advised to have several copies for failure tolerance.
• Open: The database is prepared for usage by opening the data files and online redo log files. You must have at least two online redo logs.

Important elements of the architecture include:

1. Tablespaces: Groups of data files are managed by these logical divisions within the database. UNDO (for rollback information), TEMP (for temporary objects used in query processing and sorting), USERS (for user objects), SYSTEM (for Oracle’s data dictionary), and SYSAUX (for auxiliary internal objects and tools like Enterprise Manager) are examples of common tablespaces. A datafile can only be a part of one tablespace.
2. Background Processes: Background processes are those that help the instance run. Some examples are as follows:
   • Database Writer (DBW0): Writing the contents of database buffers from memory to disc is the responsibility of the Database Writer (DBW0).
   • Log Writer (LGWR): Information is written from the log buffer to the online redo logs under the control of the Log Writer (LGWR).

Foundations of Database in Oracle

• Tables: The most basic data storage containers, arranged in rows and columns. Object-relational tables (for type inheritance), partitioned tables (split into smaller, more manageable pieces), relational tables (using Oracle’s built-in datatypes), and clustered tables (physically stored together for frequent searches) are among the table types supported by Oracle Database 10g.
• Columns: A table’s columns, each with a name specific to that table, describe the kinds of data it contains. These specify the characteristics of the data.
• Rows: Distinct records or sets of information in a table.
• Fields: One data value is represented by a field, which is the intersection of a row and a column.
• Datatypes: Which include NUMBER, CHAR (fixed-length character string), VARCHAR2 (variable-length character string), DATE (date and time), CLOB (character large object for very large text), and BLOB (binary large object for unstructured binary data like images or videos), define the properties of the data stored in the database.
  • VARCHAR2, for instance, can only contain up to 4000 characters or bytes.
• Schema: A database user’s logical structure (named after the user) that includes items like tables, views, indexes, procedures, functions, packages, triggers, user-defined objects, collection types, sequences, synonyms, and database linkages.

Here are some basic SQL and PL/SQL examples:

Creating a Table and Inserting Data (SQL):

CREATE TABLE NEWSPAPER (
    City        VARCHAR2(20),
    Temperature NUMBER(3,1),
    Humidity    NUMBER(3,1),
    Condition   VARCHAR2(20)
);
INSERT INTO NEWSPAPER (City, Temperature, Humidity, Condition)
VALUES ('Sydney', 25.5, 70.2, 'Sunny');

Describing a Table Structure (SQL*Plus):

SQL> DESCRIBE NEWSPAPER;
 Column Name    Type         Nullable
 ------------   ------------ --------
 CITY           VARCHAR2(20) YES
 TEMPERATURE    NUMBER(3,1)  YES
 HUMIDITY       NUMBER(3,1)  YES
 CONDITION      VARCHAR2(20) YES

This output shows the columns, their datatypes, and nullability.

Retrieving Data (SQL):

SELECT City, Temperature FROM NEWSPAPER WHERE Condition = 'Sunny';

This statement uses SELECT to specify columns, FROM to specify the table, and WHERE to filter results.

“Hello World” in PL/SQL:

DECLARE
    message VARCHAR2(20) := 'Hello World from PL/SQL!';
BEGIN
    DBMS_OUTPUT.PUT_LINE(message);
END;
/

Output: Hello World from PL/SQL!

An extremely well-organised and effective library is what the Oracle database functions as. Books (data files), card catalogues (control files), and daily transaction logs (redo logs) are all part of the database itself, which is kept on the shelves. The memory and running processes that enable you to search the catalogue, get volumes, and log new checkouts make the collection accessible and controllable, much like a librarian and their desk.

Oracle called RDBMS

Oracle is an RDBMS because it uses the simple relational paradigm. According to this paradigm, data is arranged as a group of tables. Like regular tables like stock charts or weather reports, these tables have rows, columns, and titles.

Each distinct collection of data in an Oracle database is given its own row, and the data is described by the column headers in the tables. The name “relational database” is based on the fact that tables can be related to one another through shared columns.

The first business to introduce a product using the English-based Structured Query Language (SQL) was Oracle. By specifying requirements to retrieve rows, SQL enables users to engage with data logically without having to worry about the specifics of physical storage. SQL queries frequently resemble English sentences and extract and modify data from relational tables using keywords like select, from, where, and order by.

Oracle is an object-relational database management system that supports both relational and object-oriented concepts and functionalities, despite its primary relational nature. This implies that you can take advantage of Oracle’s object-oriented features or use it only as an RDBMS.