Oracle Data cleanup method

Oracle Data-Cleansing methods

Delete all database objects under a user

One way to do this is to delete the user and then rebuild it, requiring administrator action:

Drop user Wangyi cascade; --Delete User
SELECT * from dba_users where username = ' Wangyi '--Query default table space
Drop tablespace Wangyi_dtbs including contents and datafiles; --Remove Tablespace

Rebuild User:

Create user Wangyi identified by Wangyi;
Grant Create session, create view, create no index, imp_full_database to Wangyi;
Alter user Wangyi default tablespace wangyi temporary tablespace temp;
Alter user Wangyi quota unlimited on Wangyi;

Second, clear the contents of the entire table

Delete from table name;
TRUNCATE TABLE name;

The difference between delete and truncate:
Delete deleted data can be restored, truncate cannot be restored
Delete high watermark does not drop, truncate high watermark will drop (free tablespace)

Third, delete the table

The drop table table name;

IV. Recycling Station

Oracle 10g begins, the table is deleted after the deletion is not a direct deletion but went to the Recycle Bin.

Delete the specified table in the Recycle Bin

Purge table name;

Empty the Recycle Bin

Purge RecycleBin;

Delete directly

drop table table name purge;

will not be deleted directly in the Recycle Bin.

Another kind of article

Deletes the name of a table (record and structure) Delete ———— truncate ———— drop

Delete (delete the statement that is logged in the datasheet)
DELETE from table name WHERE condition;

Note: Deleting records does not release the data block tablespace that is occupied in Oracle. It only marks those deleted chunks of data as unused.
If you are sure you want to delete all records in a large table, you can use the TRUNCATE command, which frees up the data block tablespaces that occupy
TRUNCATE table name;
This operation cannot be rolled back.
Same point

Truncate and delete with no WHERE clause, and drop deletes data in the table

Attention:

1. Delete here refers to a DELETE statement without a WHERE clause
2. TRUNCATE TABLE TableName operations are not allowed by default in stored procedures, so use the
Execute immediate ' TRUNCATE TABLE TableName ';

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 Deletes only the data does not delete the table's structure (definition)
Drop statement deletes the table's structure-dependent constraints (constrain), triggers (trigger), Indexes (index); Stored procedures/functions that depend on the table are preserved, but become invalid states. The
2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and when the corresponding trigger is executed, it is triggered.
Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in rollback segment and cannot be rolled back. The operation does not trigger the trigger. The
3.delete statement does not affect the extent that the table occupies, and high watermark keeps the original position.
It is obvious that the drop statement frees up all the space occupied by the table
Truncate statement by default see space released to Minextents a extent, unless using reuse storage;   truncate will reset the high watermark (back to the beginning).
4. Speed, in general: drop>; truncate >; Delete
5. Security: Use Drop and truncate with care, especially when there is no backup. Otherwise, it's too late.
use, want to delete some data rows with delete , note the WHERE clause is taken. The rollback segment should be large enough.
To delete a table, of course, drop
wants to preserve the table and delete all data. If it has nothing to do with the transaction, use truncate. If it is related to a transaction, or if you want to trigger a trigger, or delete.
If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage, and then re-import/insert the data
Delete a column of statements in the table
ALTER TABLE name drop colum column name