Shulou(Shulou.com)06/01 Report--

In database operation and maintenance, delete drop truncate operation is often encountered, so how to grasp their usage and difference?

For example, when the database space is full, it has grown to the maximum of 32G of storage space for a single storage file. You need some way to free up or expand the table space to solve the problem.

Generally, when the system uses a large number of partition tables, and clearing data for partition tables does not release table space, the partition must be dropped to release space.

Let's take a look at these three commands:

1. delete

1. delete is DML. When deleting, delete a row from the table at a time, and record the deletion operation of the row in the redo and undo tablespaces for rollback and redo operations. However, note that the tablespaces must be large enough to take effect manually. You can undo the operation through rollback.

Delete can delete the data that meets the conditions in the table according to the conditions. If the where clause is not specified, all records in the table are deleted.

The delete statement does not affect the extent occupied by the table, and the high watermark remains unchanged.

Note: delete can be flashback restored.

II. Truncate

Truncate is DDL, it will be implicitly committed, so it cannot be rolled back and will not trigger. The truncate operation is very similar to delete without the where condition, except that all the information in the table is deleted, but the table still exists.

truncate deletes all records in the table and resets the watermark and all indexes, freeing space to minextents by default unless reuse storage is used. Log will not be recorded, so the execution speed is very fast, but you can not undo the operation through rollback (if you accidentally truncate a table, you can also recover, but you cannot recover through rollback).

For tables referenced by foreignkey constraints, you cannot use truncate tables, but delete statements without where clauses.

truncatetable cannot be used for tables that participate in indexed views.

For example, after truncating the table, it is possible that the table space is still not released. You can use the following statement:

alter table name deallocate UNUSED KEEP 0;

Note that if KEEP 0 is not added, the table space will not be released.

Or:

TRUNCATE TABLE (schema)table_name DROP(REUSE) STORAGE to free the table space.

For example: truncate table test1 DROP STORAGE;

III. Drop

Drop is DDL and commits implicitly, so it cannot be rolled back and triggers are not triggered.

The drop statement deletes the table structure and all data, and releases all the space occupied by the table.

The drop statement will remove constraints, triggers, indexes, and stored procedures/functions that depend on the structure of the table will remain, but become invalid.

Note: The dropped tables are placed in the recycle bin (user_recyclbin) rather than deleted. In this way, the table information in the recycle bin can be restored or completely removed. Get the deleted table information by querying the recycle bin user_recyclbin, and then use the statement

flashback table to before drop [rename to ];

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

To delete a table completely, use the statement: drop table purge;

Oracle command delete truncate drop difference

1. delete/truncate deletes only data without deleting the structure of tables, indexes. drop will delete the table structure and dependent index/constraint/trigger, procedure/function dependent on the table will remain, but become invalid;

2. delete is dml, write rollback segmentation, rollback, slow speed, transaction committed before taking effect. Flashback recovery can be used. Delete of a large batch of data at one time may cause the rollback segment to expand sharply and affect the database. Trigger should be used with caution. truncate/drop is ddl, implicit commit, no rollback segment, no rollback, fast.

3. delete does not affect the extent occupied by the table, HWM remains in place, even if the data deleted is closest to HWM. Delete can actually free up space, but without lowering HWM, the free space of the block after delete reaches pct_used, and can be reused. Truncate frees space (tables and indexes) to minextents by default, unless reuse storage is used. Truncate will reset the high waterline (back to the beginning). drop releases all the space occupied by the table, segment does not exist, and there is no concept of HWM;

Oracle High Water Mark (HWM) Interpretation

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

4. The object of truncate/drop must be in this mode or be granted drop any table permission, but drop any table permission cannot truncate/drop sys tables. delete object must be in this mode, or be granted delete on SCHEMA.table or delete any table permission, but delete any table permission cannot delete sys table;

5. You cannot truncate a table with an enable foreign key, regardless of whether there is data in the table. If you want to truncate, you must first disable the foreign key or delete the foreign key (the table that drops the foreign key must delete the 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, you must first delete the foreign key, or directly use drop table_NAMEcascade constraints; to delete the foreign key. Delete can.

Summary:

In general, drop> truncate > delete.

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

3. If you want to delete part of the data, use delete, pay attention to the where clause, and the rollback segment should be large enough; if you want to delete the table, of course, use drop; if you want to keep the table and delete all the data, if it has nothing to do with the transaction, use truncate; if it is related to the transaction, or you want to trigger, use delete; if it is to sort out the fragments inside the table, you can use truncate to keep up with reuse stroage, and then re-import/insert the data.

Additional things to note:

1, alter table table name move is to eliminate row migration, clear space debris, delete free space, to reduce the occupied space, but will cause the index on this table invalid (because ROWID changed, can not be found), so the execution of move requires rebuilding the index.

Find the index for the table.

select index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner='SCOTT' ;

According to the status value, it is ok to reconstruct the invalid one.

sql='alter index '||index_name||' rebuild'; executed using stored procedures, slightly comforting.

Also note that locks will be generated during the alter table move process, and should be avoided during peak business hours!

2. Supplement some PURGE knowledge

Purge operation:

1). Purge tablespace_name : Recycle Bin used to purge tablespaces

2). Purge tablespace_name user user_name: Clears the object of the specified user in Recycle Bin of the specified tablespace

3). Purge recycle bin: Delete objects from Recycle Bin of current user

4). Purge dba_recyclbin: Delete objects in Recycle Bin of all users, this command requires sysdba permission

5). Drop table_name purge: Delete the object and do not put it in Recycle Bin, i.e. permanent deletion, cannot be restored with Flashback.

6). Purge index recycle_bin_object_name: When you want to free up space for Recycle bin and want to be able to restore the table, you can relieve the space pressure by releasing the space occupied by the index of the object. Because indexes can be rebuilt.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.