Dropping table spaces

When you drop a table space, you delete all the data in that table space, free the containers, remove the catalog entries, and cause all objects defined in the table space to be either dropped or marked as invalid.

About this task

You can reuse the containers in an empty table space by dropping the table space, but you must commit the DROP TABLESPACE statement before attempting to reuse the containers.

Note: You cannot drop a table space without dropping all table spaces that are associated with it. For example, if you have a table in one table space and its index created in another table space, you must drop both index and data table spaces in one DROP TABLESPACE statement.

Procedure

Dropping user table spaces:
You can drop a user table space that contains all of the table data including index and LOB data within that single user table space. You can also drop a user table space that might have tables spanned across several table spaces. That is, you might have table data in one table space, indexes in another, and any LOBs in a third table space. You must drop all three table spaces at the same time in a single statement. All of the table spaces that contain tables that are spanned must be part of this single statement or the drop request fails.
The following SQL statement drops the table space ACCOUNTING:
```
    DROP TABLESPACE ACCOUNTING
```
Dropping user temporary table spaces:
You can drop a user temporary table space only if there are no declared or created temporary tables currently defined in that table space. When you drop the table space, no attempt is made to drop all of the declared or created temporary tables in the table space.
Note: A declared or created temporary table is implicitly dropped when the application that declared it disconnects from the database.
Dropping system temporary table spaces:
You cannot drop a system temporary table space that has a page size of 4 KB without first creating another system temporary table space. The new system temporary table space must have a page size of 4 KB because the database must always have at least one system temporary table space that has a page size of 4 KB. For example, if you have a single system temporary table space with a page size of 4 KB, and you want to add a container to it, and it is an SMS table space, you must first add a new 4 KB page size system temporary table space with the proper number of containers, and then drop the old system temporary table space. (If you are using DMS, you can add a container without needing to drop and re-create the table space.)

The default table space page size is the page size that the database was created with (which is 4 KB by default, but can also be 8 KB, 16 KB, or 32 KB).
1. To create a system temporary table space, issue the statement:
```
    CREATE SYSTEM TEMPORARY TABLESPACE name 
      MANAGED BY SYSTEM USING ('directories')
```
2. Then, to drop a system table space using the command line, enter:
```
    DROP TABLESPACE name
```
3. The following SQL statement creates a system temporary table space called TEMPSPACE2:
```
    CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2
      MANAGED BY SYSTEM USING ('d:\systemp2')
```
4. After TEMPSPACE2 is created, you can drop the original system temporary table space TEMPSPACE1 with the statement:
```
    DROP TABLESPACE TEMPSPACE1
```