Tablespace Management in Oracle.
A Tablespace is a logical storage unit within an Oracle database. It is logical because a table space is not visible in the file system of the machine on which the database resides.
A table space, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server.
A datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace.
The tablespace builds the bridge between the Oracle database and the file system in which the table’s or index’ data is stored.
Comparison of TABLESPACE with FILES(o/s):
Tablespace options in a simple diagram :
Based on Extent management there is two types of tablespaces . They are :
1.Dictionary Managed Tablespace
2.Locally Managed Tablespace
Dictionary Managed Tablespace
Tablespaces that record extent allocation in the dictionary are called Dictionary Managed Tablespaces (DMT).
With this approach the data dictionary contains tables that store information that is used to manage extent allocation and deallocation manually.
Extents are managed in the data dictionary Each segment stored in the tablespace can have different storage clause Coalescing is required.
Example
|
1 |
SQL>CREATE TABLESPACEuser_data DATAFILE ‘/disk1/oradata/primary/userdata01.dbf’ SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0); |
The DEFAULT STORAGE clause enables you to customize the allocation of extents. This provides increased flexibility, but less efficiency than locally managed tablespaces
The tablespace will be in a single, 50M datafile. The EXTENT MANAGEMENT DICTIONARY clause specifies the management.
All segments created in the tablespace will inherit the default storage parameters unless their storage parameters are specified explicitly to override the default.
The storage parameters specify the following:
INITIAL – size in bytes of the first extent in a segment.
NEXT – size in bytes of second and subsequent segment extents.
PCTINCREASE – percent by which each extent after the second extent grows.
SMON periodically coalesces free space in a dictionary-managed tablespace, but only if the PCTINCREASE setting is NOT zero. Use ALTER TABLESPACE <tablespacename> COALESCE to manually coalesce adjacent free extents.
MINEXTENTS – number of extents allocated at a minimum to each segment upon creation of a segment.
MAXEXTENTS – number of extents allocated at a maximum to a segment – you can specify UNLIMITED.
Locally Managed Tablespace
Tablespaces that record extent allocation in the tablespace header are called Locally Managed Tablespaces (LMT). The extents allocated to a locally managed tablespace are managed through the use of bitmaps.
Locally Managed Tablespace (LMT) is one of the key features in Oracle database. These have been made available since Oracle 8i.
It is worth using LMTS considering the benefits in doing so. Each bit corresponds to a block or group of blocks (an extent). The bitmap value (on or off) corresponds to whether or not an extent is allocated or free for reuse.
Reduced contention on data dictionary tables No undo generated when space allocation or deallocation occurs
Benefits of using LMTs include:
- Locally managed tablespaces do not record free space in the data dictionary, it reduces contention on these tables.
- Local management of extents automatically tracksadjacent free space, eliminating the need to coalesce free extents.
- Avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
- Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
- Changes to the extent bitmaps do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
Reduced fragmentation No coalescing required.
Based on Contents there are three types of tablespaces. They are :
1.Permanent
2.Temporary
3.Undo
Permanent tablespaces will contains actual data or metadata .
Ex: System,Sysaux, Userdata etc…,
A TEMPORARY tablespace is used to manage space for sort operations.
Ex : Temp tablespace
Undo tablespace is used to store before image of DML tx’s.
Ex: UNDO
Based on Sizes there are two types of tablespaces. They are :
1.Small file
2.Big file
PLUGGED_IN :
It indicates whether the tablespace is plugged in ( If we created tablespace the default Plugged_in value is NO. If we perform copied tablespace from one database to another database using Transportable tablespace(T-Ts) then the plugged_in value will be YES.
STATUS : We can keep tablespace either Offline or Online. OFFLINE : If we keep tablespace Offline then any user cannot perform select ,insert,update and delete operations. To keep in offline we need follow below syntax
ONLINE : If we keep tablespace Online then only users can perform select ,insert,update and delete operations. To keep in Online we need follow below syntax
MODES : There are two modes of tablespace . 1.Read only. 2.Read write READ ONLY : If we keep tablespace Read Only then users can perform select operations but not insert,update and delete operations. To keep in Read Only we need follow below syntax
READ WRITE : If we keep tablespace Read WRITE then users can perform select ,insert,update and delete operations. To keep in Read Write we need follow below syntax
Data Dictionary Views for Tablespaces. DBA_TABLESPACES DBA_TABLESPACE_GROUPS DBA_DATA_FILES DBA_FREE_SPACE V$TABLESPACE V$DATAFILE DATABASE_PROPERTIES DBA_TEMP_FILES
|
|
Loading...
