Types of Segments

Database contains 3 types of segments :

1.permanent segments(emp,dept etc)

2.temporary segments

3.undo segments(for DML operations)

Temporary segments are created during sorting operations. Once the sorting operations are completed, temporary segments will be dropped automatically.

Note : A segment will never span across tablespaces but segments spans across datafiles belongs to the same tablespace.

In the industry, there should be proper monitoring mechanism and proper alerting mechanism.

In some customer environments we use the third party monitoring tools or Enterprise Manager Grid  Control or Shell Scripts submitted as cronjobs. The monitoring tool or shell script sends a mail alert to the DBA_DL(DBA_Distribution_List). If the space in the datafile is 85% utilised, it sends a threshold alert. If 95% of the space is utilised, it sends a critical alert to the mail box.

Generally in the industry, we recommended to have small sized datafiles for better performance.

Extents size could be uniform or auto allocate. By default it is auto allocate.

In 10g, oracle has introduced temporary tablespace group which contains multiple temporary tablespaces.

Note : temporary segment created in the temporary tablespace group spans across multiple temporary tablespaces.

In 11g, oracle has introduced deffered extent allocation, which means oracle will not allocate the extent once we create a segment/table. Oracle delays the extent allocation until we insert a record into the segment. Deffered extent allocation is more useful in case of new application development environment(we can successfully create tables scripts even there is no space in the datafile.