The TRUNCATE TABLE statement is a DDL command, so it includes an implicit COMMIT, meaning that there is no way to issue a ROLLBACK if you decide you don't want to delete the rows. This statement is used to delete all records from a table in Oracle, performing the same function as a DELETE statement without a WHERE clause. It is much more efficient than the DELETE statement if you want to delete all the rows in a table. When the user changes the search criteria, it is necessary to confirm the previous search transaction so that the data is truncated.
This means that your data exists on the data pages, but the extensions have been marked as empty for reuse. Deleting rows with the TRUNCATE TABLE statement can be faster and more efficient than deleting and recreating a table. To truncate a table or cluster, the table or cluster must be in the schema or you must have the DROP ANY TABLE system privilege. If a regular or indexed table contains LOB columns, all LOB data and LOB index segments are also truncated.
The MATERIALIZED VIEW LOG clause specifies whether a materialized view record defined in the table should be preserved or purged when the table is truncated. If you specify CASCADE, Oracle Database will also truncate all child tables that reference the table with an ON DELETE CASCADE referential constraint enabled. This is a recursive operation that will truncate all child tables, grandchild tables, etc., using the specified options. To preserve any materialized view records when the master table is truncated, specify PRESERVE.
When truncating a cluster, the database automatically removes all data from the indexes in the grouping tables. Yes, of course, if you confirm the transaction before reversing it, you won't be able to recover the truncated data. Therefore, it is important to be sure before confirming any transaction that involves truncating data.
Leave Reply