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. 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
command.
- 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
will fail.
To drop a user table space using the command line,
enter:
DROP TABLESPACE <name>
The
following SQL statement drops the table space ACCOUNTING:
DROP TABLESPACE ACCOUNTING
- Dropping user temporary table spaces
- You can only drop a user temporary table space
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 were using DMS, you could add
a container without having to drop and recreate 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 could also be 8 KB, 16
KB, or 32 KB).
This is the statement to create a system temporary
table space:
CREATE SYSTEM TEMPORARY TABLESPACE <name>
MANAGED BY SYSTEM USING ('<directories>')
Then,
to drop a system table space using the command line, enter:
DROP TABLESPACE <name>
The
following SQL statement creates a new system temporary table space
called TEMPSPACE2:
CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2
MANAGED BY SYSTEM USING ('d:\systemp2')
Once
TEMPSPACE2 is created, you can then drop the original system temporary
table space TEMPSPACE1 with the command:
DROP TABLESPACE TEMPSPACE1
DB2 Version 9.7 for Linux, UNIX, and Windows