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!