Archicture

1. Explain the Oracle Database Architecture Oracle Database Architecture comprises two main components: the Oracle Instance, which includes memory structures and background processes, and the Oracle Database, which stores data on disk.

2. What is the difference between Oracle instance and Oracle database? The Oracle instance is the combination of memory structures (SGA and PGA) and background processes, while the Oracle database is the physical storage of data on disk.

3. Describe the components of the Oracle Instance. The Oracle Instance consists of the System Global Area (SGA), Program Global Area (PGA), and background processes. SGA includes the shared memory structures, and PGA contains data and control information for individual user sessions.

4. Explain the purpose of the System Global Area (SGA). The SGA is a shared memory region that holds data and control information for the Oracle instance. It includes the buffer cache, shared pool, and other structures essential for database operation.

5. What is the role of the Program Global Area (PGA) in Oracle? PGA is a memory region for each individual user process and holds data and control information specific to that session, such as sorts and session variables.

6. What is the background Process? Background processes in Oracle perform various tasks, including managing memory structures, handling user connections, and performing I/O operations. Examples include the PMON (Process Monitor) and SMON (System Monitor) processes.

7. What is the function of the Oracle Data Dictionary? The Data Dictionary is a collection of database metadata that stores information about database objects, user accounts, and their privileges. It acts as a central repository for the database's structural information.

8. What is the Oracle Shared Pool? The Shared Pool is a part of the SGA that stores shared SQL and PL/SQL code, reducing parsing overhead by allowing frequently executed statements to be reused.

9. Explain the concept of Oracle Tablespaces. Tablespaces are logical storage units that group database objects together. They provide a way to manage and allocate space for database storage, allowing for better organization and maintenance.

10. What is the difference between a data block and a datafile in Oracle? A data block is the smallest unit of storage in the database, containing a set number of bytes. A datafile is a physical file on disk that stores the actual data, comprising one or more data blocks.

11. How does Oracle ensure transaction consistency and durability? Oracle ensures transaction consistency through the use of a redo log, undo segments, and the transaction control mechanism. Durability is achieved by persisting changes in the database through redo logs.

12. Describe the difference between a table and a view in Oracle. The main difference between a table and a view in Oracle is that a table is a physical storage structure for data, while a view is a virtual, query-based representation of data. Tables store data persistently, allow direct data manipulation, and have constraints, while views do not store data, are read-only by default, and provide a dynamic, abstracted view of data from one or more tables.

13. What is the purpose of the Oracle Data Dictionary, and how can it be accessed?

The Oracle Data Dictionary is a set of tables and views that store metadata about the Oracle Database. It serves as a centralised repository of information about the database's structure, objects, users, privileges, and other essential details. The data dictionary provides a way for users, database administrators, and various database tools to access and manage information about the database.

.............

Suppose you want to find information about a table named "employees" in the Oracle Data Dictionary. You can use the following SQL query:

SELECT * FROM all_tables WHERE table_name = 'EMPLOYEES';

In this example, the all_tables view in the Data Dictionary is queried to retrieve details about the table named "employees." The result may include information such as the table's owner, tablespace, number of rows, and other attributes.