Usage and differences of DELETE drop truncate in Oracle

In the operation of the database, often encounter delete drop truncate operations, then how to grasp their usage and differences?

For example, when database space is full, it has grown to the maximum value of a single storage file in storage space 32G . You need to solve the problem by releasing the table space or expanding the table space by some means.

in general, when the partition table is used extensively in the system and the data is purged against the partition table, the table space is not freed and the partition must be Drop the space will be released.

Let's take a look at these three commands:

First, delete

1, delete is DML, when you perform a delete operation, each time a row is deleted from the table, and the delete operation of the row is recorded in the Redo and undo table spaces for rollback (rollback) and redo operations, but be aware that the tablespace is large enough A manual commit (commit) action is required to take effect, and you can undo the operation by rollback.

2. Delete can delete the data in the table that satisfies the criteria, or delete all records in the table if the WHERE clause is not specified.

3. The DELETE statement does not affect the extent occupied by the table, and the high waterline (higher watermark) keeps the original position unchanged.

Note: A flash back recovery for delete.

Second, truncate

1, Truncate is DDL, will be implicitly committed, so cannot rollback, does not trigger trigger. The truncate operation is very similar to the delete operation without a where condition, except that all the information in the table is deleted, but the tables still exist.

2. Truncate will delete all records in the table and will reset the high watermark and all indexes, freeing the space to minextents extent by default, unless reuse storage is used. Logs are not logged, so the execution is fast, but cannot be undone by rollback (if a table is accidentally truncate off, it can be restored, but cannot be recovered by rollback).

3. For a table referenced by a foreign KEY (ForeignKey) constraint, you cannot use TRUNCATE TABLE, and you should use a DELETE statement without a WHERE clause.

4. Truncatetable cannot be used for tables that participate in an indexed view.

For example: After TRUNCATE TABLE, it is possible that the tablespace is still not released and can use the following statement:

ALTER TABLE name deallocate UNUSED KEEP 0;

Note that if you do not add keep 0, the tablespace will not be freed.

Or:

TRUNCATE Table (Schema) table_name DROP (reuse) storage to release the tablespace.

For example: Truncate TABLE test1 DROP STORAGE;

Three, drop

1. Drop is DDL and is implicitly committed, so it cannot be rolled back and triggers will not be triggered.

2. Drop statement deletes the table structure and all data, and frees all the space occupied by the table.

3. The drop statement will delete the constraints that the table's structure relies on, triggers, indexes, stored procedures/functions that depend on the table will be preserved, but become invalid states.

Note: The drop table is placed in the Recycle Bin (user_recyclebin) instead of being deleted directly. In this way, the table information in the Recycle Bin can be restored or completely erased. Retrieve the deleted table information by querying the Recycle Bin User_recyclebin, and then use the statement

Flashback table <user_recyclebin.object_name or user_recyclebin.original_name> to before drop [rename to <new_ table_name>];

Restore the table in the Recycle Bin to the original name or specify a new name, and the data in the table is not lost.

To delete a table completely, use the statement: drop table <table_name> Purge;

The difference between the Oracle command delete truncate drop

1. delete/truncate only delete data does not delete the structure of the table, index. Drop will delete the structure of the table and the dependent Index/constrain/trigger, and the procedure/function that depend on the table will remain, but become invalid state;

2. Delete is DML, write rollback segement, rollback, slow, and the transaction is submitted before it takes effect. You can use flashback flash back recovery. Delete of a one-time mass data may cause the rollback segment to expand dramatically to affect the database, with the use of triggering trigger. Truncate/drop is DDL, implicit commit, do not write rollback segment, cannot rollback, fast.

3. Delete does not affect the EXTENT,HWM occupied by the table to keep the original position, even if the data closest to HWM is deleted. Delete can also free up space, but it can be reused without reducing the block's free space to pct_used after Hwm,delete. Truncate frees space (tables and indexes) to minextents extent by default, unless reuse storage is used. Truncate will reset the high watermark (back to the beginning). Drop all the space occupied by the table is released, segment does not exist, does not matter the concept of HWM;

Oracle High Water level (HWM) interpretation

Http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4707900.aspx

4. The Truncate/drop object must be in this mode or be granted the permission to drop any table, but the drop any table permission cannot truncate/drop the SYS table. The Delete object must be in this mode or be granted the delete on schema.table or delete any table permission, but the delete any table permission cannot delete the SYS table;

5. Can not truncate a table with an enable foreign key, regardless of whether there is data, if you want to truncate, the first to disable foreign key or delete foreign key (drop foreign key table must be deleted foreign key). You cannot drop a table with an enable foreign key, regardless of whether there is data in the table, if you want to drop, first delete the foreign key, or directly with the drop table table_namecascade constraints; Cascade Delete foreign keys. Delete Yes.

Summarize:

1, in the speed, generally,drop> truncate > delete.

2, in the use of drop and truncate must pay attention, although can be restored, but in order to reduce the trouble, still need to be cautious.

3, if you want to delete some of the data with delete, pay attention to the WHERE clause, the rollback segment is large enough, if you want to delete the table, of course, drop, if you want to keep the table and all the data is deleted, if it is not related to the transaction, with the truncate, if the transaction, or want to trigger trigger, or delete, or if you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

Add to note:

1, ALTER TABLE name move is to eliminate the row migration, clear space debris, delete free space, to reduce the space occupied, but will cause the index on this table is not valid (because ROWID changed, cannot be found), so the execution of the move will need to rebuild the index.
Find the index corresponding to the table.
Select Index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner= ' SCOTT ';
Depending on the status value, rebuilding the invalid is OK.
Sql= ' alter index ' | | index_name| | ' rebuild '; Use stored procedure execution, a little consolation.

Also note that the ALTER TABLE move process generates locks that should be avoided during peak business operations!

2. Supplement some purge knowledge

Purge Operation:
1). Purge tablespace tablespace_name: Recycle Bin for empty table space
2). Purge tablespace tablespace_name User user_name: Empties the specified user's object in the Recycle Bin of the specified table space
3). Purge RecycleBin: Delete objects in Recycle Bin of the current user
4). Purge Dba_recyclebin: Delete all users Recycle Bin objects, the command to SYSDBA permissions
5). DROP TABLE table_name Purge: Deletes the object and does not place it in Recycle Bin, permanent deletion, and cannot be restored with flashback.

6). Purge Index Recycle_bin_object_name: When you want to release the space in the Recycle Bin and want to be able to restore the table, you can relieve the space pressure by releasing the space occupied by the object's index. Because the index can be rebuilt.

This article is from the "Hollows Jie Sun" blog, be sure to keep this source http://xjsunjie.blog.51cto.com/999372/1847337

Usage and differences of DELETE drop truncate in Oracle