DELETE DATABASE Usage Notes - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

ft:locale

en-US

ft:lastEdition

2024-12-11

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

DELETE DATABASE Removes Privileges

The DELETE DATABASE statement removes all privileges to the objects that were dropped. Because the Dump and Restore utility does not automatically recover these privileges, they must be reestablished following the restore operation.

DELETE DATABASE and Locks

When a DELETE DATABASE statement is performed, Vantage places an EXCLUSIVE lock on the database from which objects are being deleted.

Referenced Objects

If the database you want to delete contains a table that is the subject table for one or more triggers defined in any other database or user, you must first drop those triggers. Otherwise, the statement aborts and the system returns an error message.

Journal Tables

The DELETE DATABASE statement does not delete a journal table. You must enter a MODIFY DATABASE request to remove a journal table.

Global Temporary or Volatile Tables

Rules for temporary and volatile tables are as follows:

Table Type	What Happens When You Delete a Database
Global temporary	If you specify ALL, then all objects, including the materialized global temporary tables within the target database, are dropped. If you do not specify ALL, the request aborts if there are any materialized global temporary tables; else all objects in the target database are dropped.
Volatile	All objects in the target database are dropped except volatile tables.

Queue Tables

If a transaction is delayed while waiting to perform a SELECT AND CONSUME operation on a queue table in the specified database, and you delete that database, then the system aborts the delayed transaction.

Java External Stored Procedures

When you delete a database, you must also delete all Java external stored procedures, Java UDFs, and JAR files.

When you delete the database, the system internally performs the following actions on Java external stored procedure-related dictionary tables. If there are Jars defined in the current database, the system:

Updates the corresponding row for the current database in DBC.Dbase with an incremented JarLibRevision number.
Deletes all rows in DBC.Jar_Jar_Usage where the JarDatabaseId matches the ID of the database to be deleted.
Deletes all rows in DBC. Routine_Jar_Usage and DBC.Jars where the DatabaseId matches the ID of the database to be deleted.

SQL UDFs

A DELETE DATABASE statement deletes all the database objects contained in the database. If database objects contained within other databases or users reference any of the deleted objects, the referencing objects are no longer valid.

An SQL UDF can reference different database objects in its definition. Such objects may be dropped individually, or collectively when you submit a DELETE DATABASE request, which can invalidate the UDF. Conversely, an SQL UDF can itself be referenced by database objects. If an SQL UDF in such a relationship is dropped, or if its containing database or user is dropped, those database objects then lose their validity.

For example, suppose you have database df2 that contains a table referenced by an SQL UDF in database pls, and you decide to delete the contents of df2. The next time a request references the affected SQL UDF, the system aborts the request and returns an error to the requestor.

Error Tables

When you delete a database that contains an error table, the system drops the error table regardless of the containing database for its data table. The system also drops the rows for the error table in the DBC.ErrorTbls system table. For more information, see X Views .

You cannot delete a database that contains a data table with an error table that is contained by another database.

The remedy for this situation is as follows:

Find all the error tables that are defined on the tables in the database being deleted by using the following SELECT statement:
```
SELECT  BaseTblDbName, BaseTblName, ErrTblDbName, ErrTblName
FROM    DBC.ErrorTblsV
WHERE   BaseTblDbName <> ErrTblDbName
AND     BaseTblDbName = CurrentDBName;
```
where CurrentDBName is the database name of the database being deleted.
Drop all error tables from the list produced by this query.
Resubmit the DELETE DATABASE statement.

Online Archive Logging

You can delete a database that has already initiated online archive logging on the entire database or on a subset of its tables, but not while an archive operation is active.

The system stops online archive logging on the database or the specified subset of its tables when the database is deleted and also deletes all of the associated log subtables at that time.

After DELETE DATABASE Structure Remains Intact

After all the data tables, views, triggers, stored procedures, user-defined functions, and macros have been deleted from a database, the structure of the emptied database remains intact, as if its CREATE statement had been performed, and it can be used in a subsequent restore operation. The DELETE DATABASE statements is used when a database has been only partially restored and is not in a usable state.

Statistics

When you delete a database, Vantage drops all of the objects in the database and all of the statistics that have been collected on those objects.