Oracle Database 11g: Tablespace management

Oracle 11g Tablespace management is one of the important tasks of a database administrator.Before learning how to manage tablespaces lets understand what is tablespace.

A tablespace is one of the logical structure of the database. The database’s data is collectively stored in the database’s tablespaces. Tablespace contains one or more datafiles.

In 10g Oracle as introduced bigfile table which contains only one datafile which can grow up to terabytes.

It is recommended to create a big file tablespace if ASM or third-party volume manager is in place.

• if the block size is 8k, a datafile can grow up to 32GB.
• If the block size is 16k, a datafile can grow up to 64GB.

Advantages of having separate tablespaces:

1. we can separate the metadata with the business data
2. In the event of loss of a datafile only that application users are going to face the downtime.
3. we can make an individual tablespace read-only.

The minimum and mandatory tablespaces that are required to create a database are

1. system
2. sysaux

In Oracle 10g, Oracle introduced sysaux tablespace which is considered as an auxiliary tablespace for system tablespace.Some of the components of metadata have been moved from system to sysaux.

we cannot offline system tablespace but we can make sysaux tablesapce offline.

we cannot rename system and sysaux tablespaces.

Types of tablespaces

There are two types of tablespaces

1) dictionary managed tablespaces

2) locally managed tablespaces.

In case of dictionary managed tablespace, information about free extents will be maintained within the data dictionary.

In case of a locally managed tablespace, information about free extents will be maintained within the tablespace.

By default, every tablespace will be created in logging mode.

No logging means only minimal information will be recorded into online redo log files.

generally, we enable nologging for index tablespaces and during bulk load operations.

once the bulk load operations are completed, we enable logging and we take the backup of the database.

extent size can be uniform or auto allocate.

Segment space management

segment space management is of two types.

1 ) manual

2 ) auto

manual: Oracle uses free lists and free list groups to manage the space(but we are not using now)

Auto:  This uses bitmaps to manage free space within segments.

o   A bitmap describes the status of each data block within a segment with regard to the data block’s ability to have additional rows inserted.

o   Bitmaps allow Oracle to manage free space automatically.

o   Specify automatic segment-space management only for permanent, locally managed tablespaces.

o   Automatic generally delivers better space utilization than manual, and it is self-tuning.

From 10g, users will be assigned to default permanent tablespace.

A database will have one default permanent tablespace and one default temporary tablespace and one default undo tablespace.

In Oracle 11g, Oracle introduced differed extent allocation which means Oracle will not allocate an extent whenever we create a segment. Oracle delays extent allocation until we insert the data.

 

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.

Also read, How to create and manage a tablespace – Activity