Oracle several ways to erase data that are useful when the platform is initialized with a different hospital database

Delete the name of the table (record and structure) Delete ———— truncate ———— drop
Delete (deletes the statement recorded in the data table)
DELETE from table name WHERE condition;
Note: Deleting a record does not release the block table space that is used in Oracle. It only marks the deleted chunks of data as unused.
If you are sure you want to delete all the records from a large table, you can use the TRUNCATE command, which frees up the data block table space that is occupied
TRUNCATE table name;
This action is not fallback.
same Point
truncate and delete without a WHERE clause, and drop deletes the data in the table
Note:
1. Delete refers to the DELETE statement without a WHERE clause
2. The TRUNCATE TABLE TableName operation is not allowed by default in the stored procedure, so use the
execute immediate ' TRUNCATE TABLE tablename ';
For example:

CREATE OR REPLACE PROCEDURE Proc_delete_all_data is BEGIN execute immediate ' truncate TABLE T_flow_account ';  Execute immediate ' TRUNCATE TABLE t_flow_merchant '; END Proc_delete_all_data;

different points:
1. Truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table that is dependent on the constraint (constrain), the trigger (trigger), index, or the stored procedure/function that depends on the table will remain, but becomes invalid state.
The 2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.
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.
The 3.delete statement does not affect the extent occupied by the table, and the high waterline (watermark) remains in its original position.
apparently the drop statement frees all the space occupied by the table.
TRUNCATE statement by default see space released to minextents extent unless reuse storage is used; Truncate will reset the high watermark (back to the beginning).
4. Speed, in general: drop>; truncate >; Delete
5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.
on use, to delete some data rows with delete, note the WHERE clause. The rollback segment is large enough.
want to delete the table, of course with drop
You want to keep the table and delete all the data. If it is unrelated to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.
If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/Insert Data
Delete a column of statements from a table in Oracle
ALTER TABLE name drop colum column name

Original address: http://bbs.delit.cn/thread-164-1-1.html

Reprint please specify the source:

Author: metric technology www. Delit . CN

Oracle several ways of purging data are useful when the platform is initialized with a different hospital database