The NONTEMPORAL privilege is required to use the NONTEMPORAL prefix with ALTER TABLE, CREATE TABLE AS, DELETE, INSERT, and UPDATE statements.
These nontemporal operations allow modifications to closed rows, which are normally prohibited on tables that have transaction time. For example, they allow direct modification of the transaction time column values, even for rows that are closed in transaction time. They also allow physical deletion of rows that are closed in transaction time. Closed rows are normally inaccessible to modifications. They provide a history of prior row modifications and deletions, and are saved indefinitely in the table.
Because nontemporal operations can be used to circumvent the normal processing of tables with transaction time, the use of nontemporal operations is discouraged. However, nontemporal operations may be required for certain kinds of database maintenance, as when very old history rows must be archived and deleted for space considerations, and for correcting problems or corruptions in temporal tables.
The availability of nontemporal operations at the system level is controlled by the NONTEMPORAL privilege and by the EnabNonTempoOp setting in DBS Control. By default, nontemporal operations are not allowed, regardless of the NONTEMPORAL privilege. To enable nontemporal operations for users with the NONTEMPORAL privilege, use the DBS Control utility. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .
Usage Notes
A NONTEMPORAL privilege is a table and view level privilege and can also be granted at the database or user level. This privilege is not an automatic privilege and must be explicitly granted by user DBC or another user who has sufficient privileges.
The NONTEMPORAL privilege is granted implicitly to DBC with GRANT OPTION. The system does not grant the NONTEMPORAL privilege implicitly to any other users or databases.
The NONTEMPORAL privilege follows the system convention that it can be granted by an owner on any owned objects. Note that an owner must explicitly grant the NONTEMPORAL privilege to itself in order to use the NONTEMPORAL qualifier on owned objects.
For example, assume user john123 has created a table, MyTemporalTable, and is logged in to the database. In order to perform nontemporal operations to this table, john123 would first need to send the following request:
GRANT NONTEMPORAL ON mytemporaltable TO john123;
The privilege is recorded as 'NT' in the AccessRight column in the DBC.AccessRights table.
Any use of the NONTEMPORAL privilege, whether successful or denied by the system, is automatically logged in the access logging tables. Nontemporal logging requires no explicit BEGIN LOGGING statement, and cannot be disabled.
For statements that include the NONTEMPORAL prefix, the system checks the NONTEMPORAL privilege in addition to those that the statement normally requires. For example, to execute NONTEMPORAL DELETE requires both NONTEMPORAL and DELETE privileges.