If a database has filled its allocated storage space, you cannot add new data or updates to it. Existing data is always preserved. If the space allocated for a database proves to be too small, the database owner can use the alter database command to increase it. alter database permission defaults to the database owner, and cannot be transferred. You must be using the master database to use alter database.
The default increase is 2MB from the default pool of space. This statement adds 2MB to newpubs on the default database device:
alter database newpubs
See the Reference Manual: Commands.
The on clause in the alter database command is just like the on clause in create database. The for load clause is just like the for load clause in create database and can be used only on a database created with the for load clause.
To increase the space allocated for newpubs by 2MB on the database device pubsdata, and by 3MB on the database device newdata, type:
alter database newpubs on pubsdata = 2, newdata = 3
When you use alter database to allocate more space on a device already in use by the database, all of the segments already on that device use the added space fragment. All the objects already mapped to the existing segments can now grow into the added space. The maximum number of segments for any database is 32.
When you use alter database to allocate space on a device that is not yet in use by a database, the system and default segments are mapped to the new device. To change this segment mapping, use sp_dropsegment to drop the unwanted segments from the device. See the Reference Manual: Procedures.
Using sp_extendsegment automatically
unmaps the system and default segments.
For information about with override, see Chapter 6, “Creating and Managing User Databases,” in the System Administration Guide: Volume 2.
