InterBase Quick Start: Part II - Creating Tablespaces

From InterBase
Jump to: navigation, search

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.

Image 025.jpg 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:\dbs\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, The newly created tablespace should be visible on the Database tab.
    Tablespace created.png

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....
    Tablespacecreate.png
  3. On the Tablespace Name field enter "employee_tblspc".
    Addtablespace.png
  4. On the File Location field enter a location for the tablespace file or click ExploreBTN.png 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 Database tab.
    Tablespace created.png

Image 025.jpgAssigning 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.
    Tblspcprop.png

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.
    Tblspcprop.png

      Image 025.jpgRemoving 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
      


      Image 025.jpgDropping 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: