sp_addsegment

Defines a segment on a database device in a database.

Syntax

sp_addsegment segname, dbname, devname

Parameters

Examples

Usage

There are additional considerations when using sp_addsegment:
  • 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, the SAP ASE server returns an error message if you create a database using:

    create database dbname on devicename log on devicename with override

See alsoalter database, create index, create table, disk init in Reference Manual: Commands .

Permissions

The permission checks for sp_addsegment differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage database permission

Disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_dropsegment
sp_extendsegment
sp_helpdb
sp_helpdevice
sp_placeobject