Table of Contents
1) What is oracle database?
Oracle Database is developed on the basis of relational database management system (RDBMS) It can store and retrieve the large amounts of data. It has both physical and logical structures which work independently of each other.
2) What is schema?
A schema is a user who logs into the DB with his own credentials. A schema / User will have access to database, and he will have his own tables, views, indexes, triggers, clusters, procedures, functions, packages, and DB links. He will also have access to other schema / user objects. In short, a schema is a session of DB where he will have his own DB objects. SYS and SYSTEM are default schemas. One can create their own schema and delete them too.
3) What is a Tablespace?
A tablespace can be considered as folders in windows. Tablespaces are used for logical data storage. That means when we create table or view in DB, the actual data will be stored in the memory location as datafiles. Each datafile will have data about one or more tables. But when we see this data in the DB, it will be represented in a tabular form. This tabular representation of data is known as logical representation of the data. This logical data will also be stored in logical storage areas known as tablespace. This is similar to folders or datafiles in memory. But this tablespace will be linked to the datafiles.
A tablespace can have multiple datafiles from same schema or different schema; it can have DB objects from multiple schemas. Similarly, one schema can have multiple table spaces. By default, DB creates “SYSTEM” tablespace. User can create any new tablespace in their schema. All details about tablespace and its schema will be available in data dictionary.
4) What is a Control File?
Control file is a small binary file required for a DB to start and operate successfully. It will be updated continuously as DB is used by the user. It will not be accessible by user; but it will be read and modified by DB alone. It contains useful informations for normal functioning of DB. The list of information that are available in control files are:
- DB name
- Timestamp of DV creation
- Names and locations of the associated Datafiles and redo log files.
- Tablespace details
- Log history
- Archived log details
- Backup set and backup piece details
- Current log sequence number
- Copy of datafile details
- Checkpoint details.
5) Define data blocks?
Data block is smallest unit of database space. It is at the finest granularity. All the data in tables are stored in data blocks. Each data block will have specific bytes allocated in the physical memory for data storage.
6) What is an Extent?
Extent is a logical storage space allocated for data storage. It is at next level of data blocks i.e.; it is made up of contiguous set of data blocks. It is used for storing specific type of information.
7) What is a Segment?
A segment is a collection of extents, used for storing specific data structure. It is allocated in the same tablespace.
8) What is Rollback Segment?
Rollback segment is the one where roll back information about any DB transaction is stored. It will be used when any transaction has to be rolled back.
9) What are the different types of Segments?
There are mainly four types of segments.
- Data Segment: – Used for storing the data in the database.
- Index Segment: – Used for storing the table indexes.
- Rollback Segment: – It has information required for any transaction’s to roll back.
- Temporary Segment: – This segment is used when SQL query is fired where it requires to store some temporary data e.g.:- sorting list of numbers.
10) What is a Redo Log?
This is the memory area in SGA which is used to maintain the redo log entries. It is a circular buffer cache where entries made whenever there is DDL or DML changes in the database. This is useful to rebuild the database in case of any failure or crash. The recovery system of DB uses the entries in this buffer cache to recover the lost data changes.
Whenever user performs some changes to DB, they are copied to redo log buffer in SGA. These are stored in the contiguous memory locations in redo log buffer. This is the only buffer where entries are stored in contiguous memory locations. The background process LGWR – log writer is responsible for writing these entries in redo log buffer into online redo buffer in the disk or database.
11) What is a table Cluster?
Table Cluster is a collection of tables which share columns of those tables and store the column data in the same data block. When a table is clustered, single data block can contain data from different tables that are clustered.
For example, consider two tables EMPLOYEES and DEPARTMENTS are clustered. Then datas from both these tables are stored in the same data block and they share common column – DEPARTMENT_ID. This common column is known as known as cluster key column.