When you create a database, Adaptive Server creates three segments in the database, as described in Table 9-1.
Segment |
Function |
|---|---|
system |
Stores the database’s system tables |
logsegment |
Stores the database’s transaction log |
default |
Stores all other database objects—unless you create additional segments and store tables or indexes on the new segments by using create table...on segment_name or create index...on segment_name |
If you create a database on a single database device, the system, default, and logsegment segments are created on the same device. If you use the log on clause to place the transaction log on a separate device, the segments resemble those shown in Figure 9-1.
Figure 9-1: System-defined segments
Although you can add and drop user-defined segments, you cannot drop the default, system, or log segments from a database. A database must have at least one of each type of system-defined segment: system, logsegment, and default.
These are the commands and system procedures for managing segments:
sp_addsegment – defines a segment in a database.
create table and create index – creates a database object on a segment.
sp_dropsegment – removes a segment from a database or removes a single device from the scope of a segment.
sp_extendsegment – adds devices to an existing segment.
sp_placeobject – assigns future space allocations for a table or an index partition to a specific segmen.t
sp_helpsegment – displays the segment allocation for a database or data on a particular segment.
sp_helpdb – displays the segments on each database device. See Chapter 6, “Creating and Managing User Databases,” for examples
sp_help – displays information about a table, including the segment where the table resides.
sp_helpindex – displays information about a table’s indexes, including the segments where the indexes reside.
