Database structure and space Management Database Structure An

Database structure and space Management

Database Structure • An ORACLE database has both a physical and logical structure. By separating physical and logical database structure, the physical storage of data can be managed without affecting the access to logical storage structures.

Database structures Logical Physical

Logical Database Structure 1. 2. 3. 4. Tablespace - stores related database objects Segments - stores an individual database object, such as a table or an index Extent - a contiguous unit of storage space within a segment Data Block - smallest storage unit that the database can address. Extents consist of data blocks

Logical Database Structure Tablespace Segments Extents Data blocks Extents

Tablespace • Each Database is logically divided into one or more table spaces • Table space can be online (accessible) {default} or offline (Not accessible) • You can create a new tablespace to increase the size of a database • The database Administrator can bring any tablespace in an oracle online or offline • Oracle Database automatically switches a tablespace from online to offline when certain errors are encountered. For example, Oracle Database switches a tablespace from online to offline when the database writer process, DBWn, fails in several attempts to write to a datafile of the tablespace

Tablespace • Every Oracle database contains at least two tablespaces named SYSTEM and SYSUAX which Oracle Database creates automatically when the database is created. • The SYSTEM tablespace contains Data Dictionary and it’s always online when the database is open. • Data Dictionary contain Metadata which is data about data – Who created the table? – What columns are there in the table? – When the table is created? • The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace

Temporary tablespace • Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory • All operations that use sorts, including joins, union, index builds, ordering (ORDER BY) and computing aggregates (GROUP BY), benefit from temporary tablespaces. • Temporary tablespace is named TEMP. It is optional and permanent in nature but their segment are temporary in nature.

Read-only tablespace • The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. • Oracle Database never updates the files of a read-only tablespace • Because read-only tablespaces cannot be modified, and as long as they have not been made read/write at any point, they do not need repeated backup.

Database, Tablespaces, and data files • Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

Database, Tablespaces, and data files • The relationship among databases, tablespaces, and data files : 1. Each database is logically divided into one or more tablespaces. 2. One or more data files are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. 3. The combined size of a tablespace's data files in the total storage capacity of the tablespace. 4. The combined storage capacity of a database's tablespaces is the total storage capacity of the database

Allocate More Space for a Database • The size of a tablespace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces that constitute the database. • You can enlarge a database in three ways: – Add a datafile to a tablespace – Add a new tablespace – Increase the size of a datafile • When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace

Create table space • CREATE TABLESPACE tablespace_name file_name [SIZE integer M] [REUSE] DEFAULT STORAGE ( INITIAL integer M NEXT integer M MINEXTENTS integer MAXEXTENTS integer PCTINCREASE integer) ONLINE or OFFLINE PERMANENT or TEMPORARY; DATAFILE

Create table space • TABLESPACE : Tablespace in which you want the table to reside. • INITIAL SIZE: The size for the initial extent of the table. • NEXT SIZE: The value for any additional extents the table may take through growth. • MINEXTENTS and MAXEXTENTS: Identify the minimum and maximum extents allowed for the table. • PCTINCREASE: Identifies the percentage the next extent will be increased each time the table grows, or takes another extent.

Example • CREATE TABLESPACE tp DATAFILE 'df. ora' SIZE 10 M DEFAULT STORAGE( INITIAL 10 K NEXT 50 K MINEXTENTS 1 MAXEXTENTS 999 PCTINCREASE 10) ONLINE permanent;

Create Table • SQL Statement: CREATE TABLE table_name (column_name data_type [DEFAULT exp] [CONSTRAINT]) TABLESPACE tablespace_name STORAGE (INITIAL size K or M NEXT size K or M MINEXTENTS value MAXEXTENTS value PCTINCREASE value);

Example • CREATE TABLE maha ( id NUMBER CONSTRAINT co_id PRIMARY KEY, name varchar(20)) TABLESPACE tp STORAGE ( INITIAL 7000 NEXT 7000 MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 5);

Table space • Most major RDBMSs have default settings for table sizes and table locations. • If you do not specify table size and location, then the table will take the defaults. • The defaults may be very undesirable, especially for large tables.

Segments • The level of logical database storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure. • the different types of segments include: 1. Data segments • Every table in an Oracle database has a single data segment 2. Index segments • Every index in an Oracle database has a single index segment holds all of its data

3. Temporary segments – Temporary segments are created by ORACLE. When a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segments extents are returned to the system for future use. 4. Rollback segments It records old values of data that was changed by each transaction. . “Undo Information”

Rollback segments are areas in your database which are used to temporarily save the previous values when some updates occurred • have two main purposes : 1. If for one reason or another the user wants to cancel his/her update with a ROLLBACK statement, the former values are restored. This is possible only during the life of the transaction. If the user executes COMMIT instead, the values in the rollback segment are marked as invalidated and the change becomes permanent.

2. This is where other, concurrent sessions read the data when they access the changed tables before the transactions are committed. Note that if a SELECT starts on a table while a transaction is modifying the same table, the old value of the data will be read from a rollback segment - some queries take a pretty long time to run

Advantages of COMMIT and ROLLBACK Statements • With COMMIT and ROLLBACK statements, you can: • Ensure data consistency • Preview data changes before making changes permanent

State of the Data After ROLLBACK • Discard all pending changes by using the ROLLBACK statement: • Data changes are undone. • Previous state of the data is restored. • Locks on the affected rows are released. DELETE FROM copy_emp; 22 rows deleted. ROLLBACK; Rollback complete

statement description COMMIT Ends the current transaction by making all pending data changes permanent SAVEPOINT name Marks a savepoint within the current transaction ROLLBACK ends the current transaction by discarding all pending data changes ROLLBACK TO SAVEPOINT name ROLLBACK TO SAVEPOINT rolls back the current transaction to the specified savepoint, thereby discarding any changes created after the savepoint to which you are rolling back. If you omit the TO SAVEPOINT clause, the ROLLBACK statement rolls back the entire transaction. As savepoints are logical, there is no way to list the savepoints you have created.

Rolling Back Changes to a Marker • Create a marker in a current transaction by using the SAVEPOINT statement. • Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement. • Example: UPDATE. . . SAVEPOINT update_done; Savepoint created. INSERT. . . ROLLBACK TO update_done; Rollback complete.

State of the Data After COMMIT • Data changes are made permanent in the database. • The previous state of the data is permanently lost. • All users can view the results. • All savepoints are erased.

Committing Data • Make the changes. DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); 1 row inserted. • Commit the changes. COMMIT; Commit complete.

Data blocks • An ORACLE database's data is stored in data blocks. • One data block corresponds to a specific number of bytes of physical database space on disk. ‘ • A data block size is specified for each ORACLE database when the database is created. • A database uses and allocates free database space in ORACLE data blocks

Data blocks • Each Data Block consists of: header, free space and row data – Header - contains information about the data block contents, and is made up of three separate subsections: the block header, the table directory, and the row directory – Free space - is empty space that the block retains in case users update the data within the data block, and the updated data occupies more storage space than the original data – Row Data – stores actual data values

Data Block Components

Physical Database Structure • An ORACLE database's physical structure is determined by the operating system files that constitute the database. • The files of a database provide the actual physical storage for database information

Physical Database Structure 1. Datafiles – contain the actual data values 2. Redo log files - record all changes made to data, including both uncommitted and committed changes. 3. Control files - contain information about the database tablespaces, datafiles, redo log files, and the current state of the database Ex. Database name

Data Files • Every ORACLE database has one or more physical data files. • A database's data files contain all the database data. • The data of the logical database structures such as tables and indexes is physically stored in the data files allocated for a database.

Data files • A Data file can be associated with only one database • One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures

Redo log files • The primary function of the redo log is to record all changes made to data. • The information in a redo log file is used only to recover the database from a system or media failure that prevents database from being written to database's data files.

Control Files • • Every ORACLE database has a control file. A control file records the physical structure of the database. For example, it contains the following types of information: - database name - names and locations of a database's data files and redo log files - time stamp of database creation You should create two or more copies of the control file during database creation.