Other Implementation-Specific Objects

In this section you'll learn how to change and destroy some implementation-specific objects discussed in Chapter 4.

Tablespaces

Both Oracle and DB2 allow you to change or remove existing tablespaces.

ALTER TABLESPACE statement

Several options are available to modify an existing tablespace.

Oracle 9i

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.

DB2 UDB 8.1

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.

DROP TABLESPACE statement

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.

Oracle 9i

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.

DB2 UDB 8.1

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

Sequences can be modified or dropped both in Oracle and DB2; as you know from Chapter 4, MS SQL Server has no sequence objects.

ALTER SEQUENCE statement

Almost all options for sequences that you can use with the CREATE SEQUENCE statement (Chapter 4) can also be used with ALTER SEQUENCE.

Oracle 9i

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 UDB 8.1

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; 

DROP SEQUENCE statement

Sequences can be dropped with the DROP SEQUENCE statement.

Oracle 9i

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>; 

DB2 UDB 8.1

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