In ANSI mode, the COMMIT statement must follow the DELETE statement. The last line of the above example would read:

UserName = 'Oper' ; DELETE FROM DBC.LogOnOff ALL ; COMMIT;

8-124

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DELETE DATABASE, DELETE USER

DELETE DATABASE, DELETE USER

Function

The DELETE DATABASE or DELETE USER statements delete all data tables, views, and macros from a database or user. The database or user remains on the Teradata RDBMS as a named space of a particular size.

DELETE DATABASE, DELETE USER are flagged as non-ANSI, when the SQL flagger is enabled.

Syntax

DELETE -| DEL where: T- DATABASE —³ name I USER 1 CThh FF07R018
Syntax Element Specifies . . .
name the name of the database or user from which all tables, views, and macros are to be deleted.

Locks

When a DELETE DATABASE, DELETE USER statement is executed, an EXCLUSIVE lock is placed on the database or user from which objects are being deleted.

Privileges Required

To delete objects from a database or user space via DELETE DATABASE or DELETE USER, the user must have the DROP privilege on the referenced database/user.

DELETE USER can delete a database, and DELETE DATABASE can delete a user.

Access Rights Are Removed With DELETE

The DELETE DATABASE/USER statement also removes all access rights to the objects that were deleted. Because the Dump and Restore utility does not automatically recover these access rights, they must be reestablished following the restore operation.

Teradata RDBMS for UNIX SQL Reference

8-125
Teradata SQL Syntax Guide

DELETE DATABASE, DELETE USER

The DELETE DATABASE/USER statement will not delete a journal DELETE DATABASE/ table. The user must enter a MODIFY DATABASE/MODIFY USER

USER Does Not Delete statement to remove a journal table.

Journal Tables

After all the data tables, views, and macros have been deleted from After DELETE Structure a database/user, the structure remains intact (as though its CREATE

Remains Intact statement had just been executed) and can be used in a subsequent

restore operation. The DELETE DATABASE/USER statement is used when a database or user has been only partially restored and is not in a usable state.

The following statement can be used to delete all tables, views, and Example macros from the UsedCars database:

DELETE DATABASE UsedCars;

8-126

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DROP DATABASE, DROP USER

DROP DATABASE, DROP USER

The DROP DATABASE or DROP USER statements can drop empty databases or users only; all objects associated with that database or user must have already been dropped.

When a database or user is dropped, the disk space is freed.

DROP DATABASE, DROP USER are flagged as not ANSI, when the SQL flagger is enabled.

Syntax

DROP-

DATABASE-— USER —

name

where: FF07A019
Syntax Element... Specifies . . .
name the name of the database or user that is to be dropped.

To drop a database or user, a user must have the DROP DATABASE Privileges or DROP USER privilege on the database or user. The database or

user that is being dropped cannot own other databases or users, and must be at the bottom of the database hierarchy.

The object type is not checked; DROP DATABASE and DROP USER have the same effect.

If a database or user contains a journal table in their disk space, then Journal Tables the database/user cannot be dropped until the journal table is

removed from the system. A MoDiFY DATABASE /USER statement must be used to drop the journal table.

The drop operation performs the following functions:

DROP Functions

• Sets an exclusive lock on the database or user.

• Adds the disk space that the drop makes available to that of the immediate owner database.

After a database is dropped, it cannot be recovered by using the Dump and Restore utility unless the database is restored.