Skip to content
Home » Oracle » Segments vs Schema Objects

Segments vs Schema Objects

Segments vs Schema Objects

Not only showing all tables in Oracle, for better understanding the nature of table, you also need to know the definitions of segment and schema object in Oracle world.

Segments

A segment in an Oracle database is a set of extents that contains all the data for a specific logical storage structure within a tablespace. Which means segments contain not only metadata but real data.

Typically, the types of segment are limited, you can query DBA_SEGMENTS for all types of segments. Since segments contains real data, they usually reside in their owner's default tablespace and consequently take most of disk space. I talked about a way that can list the biggest segments in the whole database.

An ordinary table can have only one segment. For tables containing LOB, nested table or partitions can have multiple segments, but they cannot live without their hosts (tables).

Schema Objects

A schema in an Oracle database is a collection of database objects and owned by a database user. The schema name shares the same name as the user. Generally speaking, they are equivalent.

A Schema = An User

Which means, a schema object can only belong to a specific user.

Various types of schema object in a database can be found by querying DBA_OBJECTS. Schema objects with no data are usually stored in SYSTEM tablespace and take very little disk space. For example, programming units (stored procedures) are typical schema objects that have no data.

Differences

Normally, a table or an index with single segment is of course a schema object, but a schema object may not contain a segment, that's the difference. Therefore, if you have ever heard someone said that "a table is a segment", the speaking is not very accurate, but it isn't wrong.

We can say that a table is a segment and also a schema object in conventional ways. Strictly speaking, a segment is just an independent storage piece which belongs to a schema object, e.g. table or index. Which means, one table have its own data and can only belongs to one schema (user). This is why we want to show a list on all Oracle tables, because they are the most important objects in the entire Oracle database.

Schema objects like procedure, view, database link and synonym containing no data shall not have segments.

Leave a Reply

Your email address will not be published. Required fields are marked *