In this section you'll learn how to change and destroy some implementation-specific objects discussed in Chapter 4.
Both Oracle and DB2 allow you to change or remove existing tablespaces.
Several options are available to modify an existing tablespace.
The most common action on a tablespace is to add a new file to an existing tablespace:
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <file_path_and_name> SIZE size K|M;
The following command adds a new data file, C:\oracle\ora92\oradata\acme\data01.dbf ,of size 1 megabyte to tablespace DATA01:
ALTER TABLESPACE DATA01 ADD DATAFILE C:\oracle\ora92\oradata\acme\data01.dbf SIZE 1M;
Note |
Many more options are available with the ALTER TABLESPACE statement in Oracle, but they are mostly for database administrators' use and are not covered in this book. |
Similar to Oracle, the ALTER TABLESPACE statement in DB2 is primarily for DBAs. For example, you can add a container to a tablespace created with the MANAGED BY DATABASE option or increase its size.
Note |
You already know from Chapter 4 that MS SQL Server 2000 uses filegroups in a way similar to how Oracle and DB2 use tablespaces. The ALTER DATABASE ... ADD FILE command is covered in Chapter 4. |
Again, usually only database administrators have the necessary privileges to drop existing tablespaces. Please keep in mind that dropping a tablespace drops all objects defined in the tablespace; proceed with caution.
In Oracle you can specify several options with the DROP TABLESPACE statement:
DROP TABLESPACE <tablespace_name> [INCLUDING CONTENTS [AND DATAFILES]] [CASCADE CONSTRAINTS];
If you want to drop a tablespace that contains objects, you would have to specify INCLUDING CONTENTS or Oracle generates an error. By default the actual operating files are not deleted; you have to specify an AND DATAFILES clause unless you want to remove them manually. Specify CASCADE CONSTRAINTS to drop all referential integrity constraints from tables outside tablespace that refer to primary and unique keys of tables inside tablespace.
Note |
If the tablespace is Oracle-managed, you don't need the AND DATAFILES clause; the OS files will be deleted automatically. |
The syntax for DB2 is
DROP TABLESPACE[S] <tablespace_name>,...
You can delete multiple tablespaces within one DROP TABLESPACE statement. All OS files for the tablespace(s) managed by the system will be removed; containers created by users are not deleted.
Sequences can be modified or dropped both in Oracle and DB2; as you know from Chapter 4, MS SQL Server has no sequence objects.
Almost all options for sequences that you can use with the CREATE SEQUENCE statement (Chapter 4) can also be used with ALTER SEQUENCE.
You can change the increment, minimum, and maximum values, cached numbers, and behavior of an existing sequence. Only the future sequence numbers are affected. The only clause you cannot modify for existing sequences is START WITH.
ALTER SEQUENCE [<qualifier>.]<sequence_name> [INCREMENT BY <increment_value>] [MAXVALUE <max_value> | NOMAXVALUE] [MINVALUE <min_value> | NOMINVALUE] [CYCLE | NOCYCLE ] [CACHE <value> | NOCACHE] [ORDER | NOORDER]
The following statement changes MY_SEQUENCE4 in such a way that it no longer has a maximum value and does not cycle:
CREATE SEQUENCE my_sequence4 INCREMENT BY 1 NOMAXVALUE NOCYCLE;
DB2 allows you to restart the sequence, to change its increment (for future values only), to set or eliminate the minimum or maximum values, to change the number of cached values, and more:
ALTER SEQUENCE <sequence_name> [RESTART WITH <start_value>] [INCREMENT BY <increment_value>] [MAXVALUE <max_value> | NOMAXVALUE] [MINVALUE <min_value> | NOMINVALUE] [CYCLE | NOCYCLE ] [CACHE <value> | NOCACHE] [ORDER | NOORDER]
For example
CREATE SEQUENCE my_sequence4 RESTART WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;
Sequences can be dropped with the DROP SEQUENCE statement.
DROP SEQUENCE [<qualifier>.]<sequence_name>;
No dependencies exist (i.e., no database objects would prevent a sequence from being dropped; also, no objects would be invalidated). The statement below removes sequence MY_SEQUENCE1:
DROP SEQUENCE <my_sequence1>;
DROP SEQUENCE <sequence_name> RESTRICT -- restrict is a required keyword
You cannot drop a sequence used in a trigger. This example is an equivalent to Oracle's syntax from the previous example:
DROP SEQUENCE my_sequence1 RESTRICT