Tables and Constraints - Pro Oracle Database 12c Administration - page 184

If you want to bypass the RECYCLEBIN feature and permanently drop a table, use the PURGE option of the
DROP TABLE statement:

SQL> drop table inv purge;

You can't use the FLASHBACK TABLE statement to retrieve a table dropped with the PURGE option. All space used by
the table is released, and any associated indexes and triggers are also dropped.

Removing Data from a Table
You can use either the DELETE statement or the TRUNCATE statement to remove records from a table. You need to be
aware of some important differences between these two approaches. Table 7-3 summarizes the attributes of the
DELETE and TRUNCATE statements.

Table 7-3. Features of DELETE and TRUNCATE

DELETE TRUNCATE

Choice of COMMIT or ROLLBACK YES NO

Generates undo YES NO

Resets the high-water mark to 0 NO YES

Affected by referenced and enabled foreign key constraints NO YES

Performs well with large amounts of data NO YES

Using DELETE
One big difference is that the DELETE statement can be either committed or rolled back. Committing a DELETE
statement makes the changes permanent:

SQL> delete from inv;
SQL> commit;

If you issue a ROLLBACK statement instead of COMMIT , the table contains data as they were before the DELETE
was issued.

Using TRUNCATE
TRUNCATE is a DDL statement. This means that Oracle automatically commits the statement (and the current
transaction) after it runs, so there is no way to roll back a TRUNCATE statement. If you need the option of choosing
to roll back (instead of committing) when removing data, then you should use the DELETE statement. However, the
DELETE statement has the disadvantage of generating a great deal of undo and redo information. Thus, for large tables,
a TRUNCATE statement is usually the most efficient way to remove data.
This example uses a TRUNCATE statement to remove all data from the COMPUTER_SYSTEMS table:

SQL> truncate table computer_systems;