SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-48
Examples—ALTER TABLE
accommodate database growth. Because each partition must have access to
the location and key range of other partitions, REUSE PARTITON updates the
label of all the partitions with the new key range.

After REUSE PARTITION is performed, the reused partition is empty. If an
error is encountered during REUSE, the data in the reused partition might not
be recoverable. It is recommended that you back up data before performing
REUSE PARTITION.

During the operation of reusing a partition, the partition becomes inaccessible
for read and write by DML statements until the operation is done.

Only a partition with key-sequenced file organization can be reused. A partition
with relative or entry-sequenced file organization cannot be specified in
REUSE.

REUSE PARTITION can only be performed against the secondary partition of
a partitioned table. It cannot be performed on a primary partition or any
partitioned indexes.

REUSE PARTITION cannot be executed within a user transaction. This
prevents a user from performing a rollback of the transaction during the
REUSE. Instead, it is executed within the transaction started by the catalog
manager.

A partitioned table with or without view dependent objects can be reused. A
table on which indexes are defined cannot be reused.

If records exist within the FIRST KEY specified, SQL returns an error. The
partition is not used and the partitions remain intact. The key ranges of each
partition cannot overlap.
Examples—ALTER TABLE

This example alters the security and owner for a table:
ALTER TABLE \SYS1.$VOL2.PERSNL.EMPLOYEE
SECURE "nunu" OWNER 12,101;

This example alters the date that a table can be purged:
ALTER TABLE SALES.ORDERS NOPURGEUNTIL JAN 01 2004;

This example turns on TMF auditing (perhaps after temporarily setting the NO
AUDIT attribute to perform a LOAD operation) for a table:
ALTER TABLE SALES.ORDERS AUDIT;

This example adds a column to a table:
ALTER TABLE CUSTOMER ADD COLUMN LAST_ORDER_DATE
NUMERIC(6) DEFAULT 860000 HEADING "Last Ordered";