ALTER DBSPACE statement

Description

Changes the readwrite mode, changes the size, or extends an existing dbspace.

Syntax

ALTER DBSPACE dbspace-name 
{ READWRITE | READONLY | RELOCATE
| SIZE dbspace-size [ KB | MB | GB | TB | PAGES ]
| ADD dbspace-size [ KB | MB | GB | TB | PAGES ] }

Examples

Example 1

Change the mode of a dbspace called mydb_tmp_2 to relocate.

ALTER DBSPACE mydb_tmp_2 RELOCATE;

Example 2

Specify the new size of 10MB for the dbspace IQ_SYSTEM_MAIN.

ALTER DBSPACE IQ_SYSTEM_MAIN SIZE 10MB

Example 3

Increase the size of the dbspace IQ_SYSTEM_TEMP by 2GB.

ALTER DBSPACE IQ_SYSTEM_TEMP ADD 2 GB

Example 4

Specify the new size of 4MB for the dbspace IQ_SYSTEM_TEMP. (SIZE defaults to megabytes.)

ALTER DBSPACE IQ_SYSTEM_TEMP SIZE 4

Example 5

Increase the size of the dbspace IQ_SYSTEM_MAIN by 1000 pages. (ADD defaults to pages.)

ALTER DBSPACE IQ_SYSTEM_MAIN ADD 1000

Usage

The ALTER DBSPACE statement changes the readwrite mode, changes the size, or extends an existing dbspace. The sp_iqdbspace system stored procedure displays the mode and size of the dbspace. Dbspace names are case sensitive for databases created with CASE RESPECT.

READWRITE clause Specifies that allocations can be made from the dbspace. The mode of a newly created dbspace is readwrite.

READONLY clause Specifies that the server will no longer write to the dbspace. You can still modify objects on the dbspace, but new versions are placed on the remaining readwrite dbspaces.

RELOCATE clause Specifies that space will not be allocated from the dbspace and that the objects on the dbspace are subject to relocation. The server does not write to an IQ Main dbspace in relocate mode.

SIZE clause Specifies the new size of the dbspace in units of pages, kilobytes (KB), megabytes (MB), gigabytes (GB), or terabytes (TB). The default is megabytes. The size of the dbspace can be increased only if the free list has sufficient room or if the dbspace has sufficient reserved space. The size of the dbspace can be decreased only if the truncated portion is not in use.

ADD clause ALTER DBSPACE ADD extends the dbspace by the specified dbspace-size in units of pages, kilobytes (KB), megabytes (MB), gigabytes (GB), or terabytes (TB). The default is PAGES. The page size of a database is fixed when the database is created.

You can also view and change the dbspace mode and size through the Sybase Central Dbspaces window.

Side effects

Automatic commit
Automatic checkpoint
A mode change to readonly or relocate causes immediate relocation of the internal database structures on the dbspace to one of the readwrite dbspaces.

Standards

SQL/92 Vendor extension.

Sybase Not supported by Adaptive Server Enterprise.

Permissions

Must have DBA authority.