Changes the read/write mode, changes the size, or extends an existing dbspace.
ALTER DBSPACE dbspace-name
{ READWRITE | READONLY | RELOCATE
| SIZE dbspace-size [ KB | MB | GB | TB | PAGES ]
| ADD dbspace-size [ KB | MB | GB | TB | PAGES ] }
Change the mode of a dbspace called mydb_tmp_2 to relocate.
ALTER DBSPACE mydb_tmp_2 RELOCATE;
Specify the new size of 10MB for the dbspace IQ_SYSTEM_MAIN.
ALTER DBSPACE IQ_SYSTEM_MAIN SIZE 10MB
Increase the size of the dbspace IQ_SYSTEM_TEMP by 2GB.
ALTER DBSPACE IQ_SYSTEM_TEMP ADD 2 GB
Specify the new size of 4MB for the dbspace IQ_SYSTEM_TEMP. (SIZE defaults to megabytes.)
ALTER DBSPACE IQ_SYSTEM_TEMP SIZE 4
Increase the size of the dbspace IQ_SYSTEM_MAIN by 1000 pages. (ADD defaults to pages.)
ALTER DBSPACE IQ_SYSTEM_MAIN ADD 1000
The ALTER DBSPACE statement changes the read/write 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 no longer writes 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 is not 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. You can increase the size of the dbspace only if the free list (an allocation map) has sufficient room or if the dbspace has sufficient reserved space. You can decrease the size of the dbspace 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 increase dbspace size only if the dbspace has
sufficient reserved space.
You can also view and change the dbspace mode and size through the Sybase Central Dbspaces window.
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 read/write dbspaces.
Must have DBA authority.
“sp_iqdbspace procedure” in Chapter 10, “System Procedures”
“Working with dbspaces” in Chapter 5, “Working with Database Objects,” of the Sybase IQ System Administration Guide
