About deleting data from the Oracle database

1. Delete table drop

1.1 execute drop table table_name statement

The drop tables are placed in the user Recycle Bin (user_recyclebin) without being deleted directly, and the tables in the Recycle Bin can be restored. Get the deleted table information by querying the Recycle Bin user_recyclebin, and then use the following statement:

Flashback table user_recyclebin.object_name [user_recyclebin.original_name] to before drop [rename to new_table_name]

Restore the table in the Recycle Bin to its original name or specify a new name.

1.2 to delete a table completely, use the statement: drop table table_name purge

The statement says: do not put into the Recycle Bin, delete directly.

1.3 drop tables with foreign keys

Drop table table_name cascade constraints

-also known as cascading deletion, where related views and integrity constraints are deleted.

2. Delete the data in the table

2.1 delete

The delete operation applies to deleting some of the data in the table, but not all of the data. If you delete all rows in the table using delete, although the data in the table is deleted, the high water mark alone will not be released.

2.2 the truncate operation deletes all the information in the table, sets the high water mark to 0, and truncates the relevant indexes on the table.

Truncate table table_name

Truncate does not support rollback

You cannot truncate a table with a foreign key. If you want to delete it, cancel the foreign key first, and then delete it.

After truncate table, it is possible that the tablespace is still not freed.

You can release it using the following statement:

  alter table table_name deallocate UNUSED KEEP 0

Alter table emp deallocate UNUSED KEEP 0

Or when you clear the data, reclaim the storage space as well:

TRUNCATE TABLE table_name DROP (REUSE) STORAGE

For example: truncate table test1 DROP STORAGE

Note: this blog is a summary of a number of blog posts and Oracle professional documents, and after my verification and summary, if there is any infringement, please contact me to indicate.