Segments (Continued)

We all know that a database will always have users of it. Further more a user also has his or her objects e.g. tables, indexes, views etc. In database a user and his/her objects are collectively known as Schema. For each user in the database there must be a tablespace (although a user can have more than one tablespaces but we will consider only one because tablespace is not the topic here). Within each tablespace there are segments. Each segment corresponds to an object that a user owns. For example there may be a table segment for the tables, an index segment for the index etc. Almost all the user objects are stored inside segments except the one thing and that is programs. Stored procedures, triggers and all other type of programs are stored inside the system tablespace.
Oracle database provides different types of segments for storing different types of objects. They are:

1) Table Segment: It is used to store the tables created by the user.

2) Index Segment: It is used to store the indexes created by the user.

3) Cluster Segment: Sometimes for performance reasons two or more tables are stored together on the disc. It is known as clustering. Cluster segment holds the clustered tables.

4) Table partition Segment: When your table is too large then it is partitioned into one or more components. Table partition segment holds the partitioned table.

5) Index Partition Segment: Similarly when your index is too large it is also partitioned and index partition segment is used to store it. It is only present in 8i and above versions.

6) LOB Partition Segment: LOB stands for large objects. By large objects we mean video data, audio data, images etc. There are three data types for storing large objects. BLOB (Binary Large Objects), CLOB (Character Large Objects), NCLOB (Natural Character Large Objects). When any column in a table has a data type from any of above mentioned data types; then that column is not stored in the segment in which the actual table is being stored. There will be separate segment for that particular column and that segment is called LOB Partition Segment.

7) Index Organized Table Segment: Sometimes the table and the index of it are stored in the same segment. That segment is known as index organized table segment.

8) Undo/Rollback Segment: Suppose you have made a change in a table and you gave the command of rollback. The previous value will be restored. Oracle stores all of its old values in the rollback segment. Before 9i it was known as rollback segment and in and after 9i it is known as undo segment.

9) Temporary Segment: Temporary segment is used to hold the temporary objects that the user needs. For example when you are sorting you need an object to temporarily hold a value.

10) Nested Table Segment: When you store a table inside a table nested table segment is used to store a table inside a table.