The segmap column is a bitmask linked to the segment column in the user database’s syssegments table. Since the logsegment in each user database is segment 2, and these user databases have their logs on separate devices, segmap contains 4 (22) for the devices named in the log on statement and 3 for the data segment that holds the system segment (20 = 1) + default segment (21 = 2).
Some possible values for segments containing data or logs are:
Value |
Segment |
|---|---|
3 |
Data only (system and default segments) |
4 |
Log only |
7 |
Data and log |
Values higher than 7 indicate user-defined segments. The segmap column is explained more fully in the segments tutorial section in Chapter 8, “Creating and Using Segments.”
The query below illustrates the connection between segments in syssegments and segmap in master..sysusages. The query lists the segment mappings for the current database, showing that each segment number in syssegments becomes a bit number in master..sysusages:
select dbid, lstart, segmap, name as 'segment name' from syssegments s, master..sysusages u where u.segmap & power(2,s.segment) != 0 and dbid = db_id() order by 1,2
dbid lstart segmap segment name ----- ------ -------- -------------- 4 0 3 system 4 0 3 default 4 5120 4 logsegment 4 7680 3 system 4 7680 3 default
This example shows that disk fragment for lstart value 0 and the fragment for lstart value 7680 use segments system number 0 and default number 1, while the fragment for lstart value 5120 uses segment logsegment number 2. This database was created using both the on and log on clauses of create database, and was then extended once using the on clause of alter database.
Because the sysusages segmap uses an int datatype, it can contain only 32 bits, so no database can hold more than 32 segments (numbered 0 - 31). Because segmap is a signed quantity (that is, it can display both positive and negative numbers), segment 31 is perceived as a very large negative number, so the query above generates an arithmetic overflow when you use it in a database that uses segment 31.
