Defines a segment on a database device in a database.
sp_addsegment segname, dbname, devname
is the name of the new segment to add to the syssegments table of the database. Segment names are unique in each database.
specifies the name of the database in which to define the segment. dbname must be the name of the current database or match the database name qualifying sp_addsegment.
is the name of the database device in which to locate segname. A database device can have more than one segment associated with it.
Creates a segment named indexes for the database pubs2 on the database device named dev1:
sp_addsegment indexes, pubs2, dev1
Creates a segment named indexes for the pubs2 database on the database device named pubs2_dev:
disk init name = "pubs2_dev", physname = "/dev/pubs_2_dev", vdevno = 9, size = 5120 go alter database pubs2 on pubs2_dev = 2 go pubs2..sp_addsegment indexes, pubs2, dev1
You cannot create a segment on a device that already has an exclusive segment. If you attempt to do so, you see an error message similar to:
A segment with a virtually hashed table exists on device orders_dat.
sp_addsegment defines segment names for database devices created with disk init and assigned to a specific database with an alter database or create database command.
After defining a segment, use it in create table and create index commands and in the sp_placeobject procedure to place a table or index on the segment.
When a table or index is created on a particular segment, all subsequent data for the table or index is located on the segment.
Use the system procedure sp_extendsegment to extend the range of a segment to another database device used by the same database.
If a database is extended with alter database on a device used by that database, the segments mapped to that device are also extended.
The system and default segments are mapped to each database device included in a create database or alter database command. The logsegment is also mapped to each device, unless you place it on a separate device with the log on extension to create database or with sp_logdevice. See the System Administration Guide for more information.
Although you can use sp_addsegment in a database that has both data and the log on the same device, such as when the database is created without the log on option, Adaptive Server returns an error message if you create a database using:
create database dbname on devicename log on devicename with override
The permission checks for sp_addsegment differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage database permission |
Granular permissions disabled |
With granular permissions disabled, you must be the database owner or a user with sa_role. |
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
Commands alter database, create index, create table, disk init
System procedures sp_dropsegment, sp_extendsegment, sp_helpdb, sp_helpdevice, sp_placeobject
