What is the Oracle delete table statement?

Oracle delete table statement is [DELETE (statement to delete records in the data table) DELETE FROM table name WHERE condition;], to delete all records in a large table, you can use the statement [TRUNCATE TABLE table name; 】.

oracle delete table statement

Delete table (record and structure) language name

delete ———— truncate ———— drop

DELETE (删除数据表里记录的语句)
　　DELETE FROM表名 WHERE 条件;

Note: Deleting records does not release the occupied data block table space in ORACLE. It only marks the deleted data blocks as unused.

If you really want to delete all records in a large table, you can use the TRUNCATE command, which can release the occupied data block table space

　　TRUNCATE TABLE 表名;

This operation cannot be rolled back.

Same points

Truncate, delete without where clause, and drop will delete the data in the table

Note: The delete mentioned here is Refers to the delete statement without where clause

Differences:

1. Truncate and delete only delete data but not the structure (definition) of the table

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

2. The delete statement is dml. This operation will be placed in the rollback segment and will take effect after the transaction is submitted. If there is a corresponding trigger, it will be triggered during execution.

truncate, drop is ddl, the operation takes effect immediately. The original data is not placed in the rollback segment and cannot be rolled back. The operation does not trigger the trigger.

3. The delete statement does not affect the extent occupied by the table. The high water mark (high watermark) remains in its original position

Obviously the drop statement will release all the space occupied by the table

The truncate statement will release the space to minextents extents by default. Unless reuse storage is used; truncate will reset the high waterline (back to the beginning).

4. Speed, generally speaking: drop>; truncate >; delete

5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.

In use, if you want to delete some data rows, use delete. Pay attention to the where clause. The rollback segment must be large enough.

• If you want to delete the table, of course use drop

• If you want to keep the table, use All data is deleted. If it has nothing to do with the transaction, just use truncate. If it is related to the transaction, or you want to trigger a trigger, use delete.

• If you want to defragment the table, you can use Truncate follows reuse stroage, and then re-import/insert the data

Delete a column statement in the table in oracle

　　alter table 表名 drop colum 列名

Related learning Recommended: oracle database learning tutorial

The above is the detailed content of What is the Oracle delete table statement?. For more information, please follow other related articles on the PHP Chinese website!