Tablespaces are the most bigger logical storage structure in oracle ( Tablespace -> segment -> extent -> block) . Tablespaces gather segments (you can think of it as objects like tables, indexes, etc), when we create an object like a table , if we dont specify a tablespace in the CREATE TABLE command it is assigned to our user default tablespace ( in most cases the SYSTEM tablespace , which is recommended by oracle just for dictionary objects). A tablespace is composed of datafiles wich are part of the physical structure of the oracle database (datafiles, control files, redo log files) they are stored in the OS containing the data of the database.
Relationship between Database - Tablespace - Datafile - Object:
The graph shows us the relationship between this structures, a database is composed logically of tablespaces , each tablespace is composed physically of datafiles and each datafile stores data from the objects . The size of the database is total size of the tablespaces and the total size of the tablespaces is the total size all of his datafiles. You can add or remove space to a tablespace in two ways , adding datafiles to the tablespace or resizing the actual datafiles.
Why we need tablespaces
Tablespaces ease the management of the database, they are useful to separate objects and apply different actions to each of them, we can take one of the tablespaces offline, make it read only or backup it affecting just part of the database
Types of tablespaces
Permanent tablespaces: where usual objects are stored ( data tablespaces)
Temporary tablespaces: used for temporary segments (in sort operations)
Undo tablespaces: for rollback segments.
Space management
There are basically two states when oracle needs to use some space (extents) in a tablespace: used (allocated) and available, and Oracle manages that space in two ways :
- Dictionary Managed Tablespaces
- Locally Managed Tablespaces
Dictionary Managed Tablespaces
The information about used and available extents are stored in the data dictionary. This type of management is not the most optimal because of the SQL overhead in the database when performing a lot of operations and also it can't be done simultaneously they must be serialized increasing wait times. Since 9i, Oracle is deprecating this method and it will not let you create dictionary managed tablespaces if you have your system tablespace locally managed.
Locally Managed Tablespaces
In this way Oracle uses bitmaps stored in the datafile itself to know when an extent is used ( with 1) and available or free ( with 0) , this approach eases the space management and remove the overhead in the database.
For more information, this is the best oracle architecture book:
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
1 comment:
Thiss was lovely to read
Post a Comment