Drops a segment from a database or unmaps a segment from a particular database device.
sp_dropsegment segname, dbname [, device]
is the name of the segment to be dropped.
is the name of the database from which the segment is to be dropped.
is the name of the database device from which the segment segname is to be dropped. This parameter is optional, except when the system segment system, default, or logsegment is being dropped from a database device.
This command drops the segment indexes from the pubs2 database.
sp_dropsegment indexes, pubs2
This command unmaps the segment indexes from the database device dev1:
sp_dropsegment indexes, pubs2, dev1
You can drop a segment if it is not referenced by any table, index, or partition in the specified database.
If you do not supply the optional argument device, the segment is dropped from the specified database. If you do supply a device name, the segment is no longer mapped to the named database device, but the segment is not dropped.
Dropping a segment drops all thresholds associated with that segment.
You can only execute sp_dropsegment for the logsegment system segment in single-user mode.
This command may take a long time to complete in in
very large databases.
When you unmap a segment from one or more devices, Adaptive Server drops any thresholds that exceed the total space on the segment. When you unmap the logsegment from one or more devices, Adaptive Server recalculates the last-chance threshold.
sp_placeobject changes future space allocations for a table or index from one segment to another, and removes the references from the original segment. After using sp_placeobject, you can drop the original segment name with sp_dropsegment.
For the system segments system, default, and logsegment, you must specify the device name from which you want the segments dropped.
The permission checks for sp_dropsegment differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage database privilege. |
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 |
|
System procedures sp_addsegment, sp_addthreshold, sp_helpsegment, sp_helpthreshold, sp_placeobject
