Oracle Database uses several storage structures to store and manage data. The primary storage structures include:
1. Datafiles: A datafile is a physical file on disk that stores the actual data of an Oracle database. Each tablespace in an Oracle database consists of one or more datafiles. Datafiles are used to store database objects such as tables, indexes, and stored procedures.
2. Redo log files: A redo log file is a circular buffer that records all changes made to the database. The buffer is used to recover the database in case of a system failure. Redo log files are stored on disk and are used to ensure transactional consistency in the database.
3. Control files: A control file is a small binary file that is used to manage the metadata of the database. The control file contains information about the database name, location of datafiles, redo log files, and other important details about the database. There are usually multiple control files kept in different locations to help prevent data loss in case of a failure.
4. Tablespaces: A tablespace is a logical storage structure made up of one or more datafiles. Objects in the database like tables, indexes, stored procedures, etc., are stored in tablespaces. Tablespaces help manage the physical storage of objects.
5. Segments: A segment is a logical storage structure that represents a database object, such as a table, index, or stored procedure. Segments are composed of one or more extents.
6. Extents: An extent is a logical storage unit made up of a set of contiguous data blocks in a datafile. An extent corresponds to a set of data blocks that are allocated to a specific segment.
The primary functions of these storage structures are:
1. Storage and retrieval of data: Datafiles are used to store the actual data of an Oracle database. Segments and extents help manage the physical storage of database objects.
2. Maintaining database consistency: Redo log files are used to maintain transactional consistency in a database. Control files contain metadata about the database and help prevent data loss in case of a failure.
3. Performance optimization: Tablespaces and segments can be optimized to improve the performance of the database.
Examples of how these structures are used in an Oracle database:
1. Datafiles: Let’s say, for example, that you have a table called "employees" that you want to add to an Oracle database. To do this, you would create a tablespace to store the table and then create a datafile within that tablespace to hold the data for the "employees" table.
2. Redo log files: When a user updates a record in the "employees" table, the change is recorded in the redo log files. If a system failure occurs, the redo log files can be used to recover the changes made to the database.
3. Control files: Control files contain metadata about the database, such as the database name, the location of the datafiles, and information about the redo log files. Control files help maintain database integrity and prevent data loss in case of a failure.
4. Tablespaces: Tablespaces can be created or altered to improve the performance of the database. For example, you might create a new tablespace that is stored on high-performance disk drives to improve the speed of data access.
5. Segments: If you have a table called "employees" that contains millions of records, you might create separate segments for each department to improve the performance of queries that relate to specific departments.
6. Extents: Extents are allocated to segments as needed. For example, if you add more data to the "employees" table, Oracle Database might allocate a new extent to the table to store the additional data.