InterBase Quick Start: Part II - Creating Tablespaces

Go Up to InterBase Quick Start: Part II - Data Definition

For more information on tablespaces refer to the TABLESPACE documentation. This tutorial shows how to create a table space and assign tables and indexes to it.

Creating a tablespace

This section shows how to create a employee_tblspc tablespace for the EMPLOYEE table using the CREATE TABLESPACE statement. CREATE TABLESPACE has the following structure:

CREATE TABLESPACE <tablespace_name> FILE '<file>'

Using WISQL

1. Open the Interactive SQL window and enter the following statement:

   CREATE TABLESPACE employee_tblspc FILE 'c:\dbstore\employee_tblspc.its'
   

   Note:
   The statement uses .its as a file extension for the employee_tblspc tablespace, but you can use any file extension you want or no file extension at all.
2. Execute the statement
3. Close the Interactive SQL window.
4. Select Tablespaces on the server tree, the newly created tablespace should be visible on the tablespaces tab.

Using IBConsole Tablespace Editor

Alternatively you can create tablespaces using the Tablespace Editor. To create a tablespace with the Tablespace Editor follow these instructions:

1. Expand the Tutorial database and select Tablespaces.
2. Right click on the Database pane and choose Create....
3. On the Tablespace Name field enter "employee_tblspc".
   
4. On the File Location field enter a location for the tablespace file or click to set a path.
5. You can optionally set a description on the Description field.
6. Click Ok.
7. The newly created tablespace should be visible on the Tablespaces tab.

Assigning tables to a tablespace

This section shows how to assign a table to the recently created employee_tblspc tablespace using the ALTER TABLE and ALTER TABLESPACE statements.

ALTER TABLE <table_name> 
    [ALTER TABLESPACE {<tablespace_name> | PRIMARY}]

Note:
By default all tables belong to the Primary tablespace. In the context of InterBase tablespaces when you assign a table to a tablespace you move it from the Primary to another existing tablespace.

Using WISQL

1. Open the Interactive SQL window and enter the following statement:

   ALTER TABLE employee
   ALTER TABLESPACE employee_tblspc
   

2. Execute the statement
3. Close the Interactive SQL window.
4. Verify the table is listed on the employee_tblspc tablespace properties window.

Using IBConsole

1. Right click on the EMPLOYEE table and select Alter....
2. On the Tablespace field choose the employee_tblspc tablespace.
3. Click OK.
4. Verify the EMPLOYEE table is listed on the employee_tblspc tablespace properties window.

   Removing tables from a tablespace

   This section shows how to remove a table from a tablespace

   Note:
   In the context of InterBase tablespaces, when you remove a table from a tablespace you move it to the Primary or to another existing tablespace.

   ALTER TABLE <table_name> 
       [ALTER TABLESPACE {<tablespace_name> | PRIMARY}]
   

   First use ALTER TABLE to modify the employee table, then use ALTER TABLESPACE to move the employee table to the employee_tblspc tablespace.

   ALTER TABLE employee 
   ALTER TABLESPACE primary
   

   
   

   Dropping a tablespace

   This section shows how to drop a tablespace from a database. Use DROP TABLESPACE to drop a tablespace.

   DROP TABLESPACE <tablespace_name>
   

   Note:
   Before dropping a tablespace make sure remove any tables or indices assigned to it.

   /* Dropping the tablespace */
   DROP TABLESPACE employee_tblspc
   

   
   

   Advance To:

   • Creating Views