Differences between drop, delete, and truncate in oracle: droptruncate

Differences between drop, delete, and truncate in oracle: droptruncate

Differences between drop, delete, and truncate in oracle you can use the drop, delete, and truncate commands to delete tables in the database, there are many articles and tutorials on the Internet that specifically explain the similarities and differences between them. I 'd like to make it simple:

-- Oracle10g. A table similar to BINnJ5JuP9cQmqPaArFei384g = nJ5JuP9cQmqPaArFei384g = 0 is displayed after you select and delete it in pl/SQL. -- 1. view the recycle bin select * from user_recyclebin; -- 2. Clear the recycle bin purge recyclebin; -- 3. Clear a table in the recycle bin. If you delete a table in the following way, the system will prompt that the SQL command has not ended correctly. Purge table BIN/UpBuh + rows =/UpBuh + LQ9yZGN95BFsk5Q = 0 -- the correct syntax is as follows: purge table "BIN/UpBuh + LQ9yZGN95BFsk5Q =/UpBuh + rows = "; -- the following message is displayed when you delete the SQL command. Drop table BIN/UpBuh + LQ9yZGN95BFsk5Q =/UpBuh + LQ9yZGN95BFsk5Q = 0 -- if you write as follows, the system will prompt that you cannot execute DDL/DML on objects in the recycle BIN. Drop table "BIN0iJ7/rWFQrSGdZexvGv3qQ = 0iJ7/rWFQrSGdZexvGv3qQ = 0" 4. restore the recycle bin flashback table "BIN0iJ7/rWFQrSGdZexvGv3qQ = 0iJ7/rWFQrSGdZexvGv3qQ = 0" to before drop -- this is a new 10g feature -- if the flash drop feature is enabled in 10, when you drop a table, the database does not directly delete it. Instead, it is placed in the recycle bin. When there is a shortage of space, this part of space will be gradually reclaimed. -- Bin $ indicates that the table is placed in the recycle bin. If you want to delete the table, you can find it again. If you want to delete the table, you can directly delete the delete tanle bin $ .....; -- another method is to use purge table table_name; -- drop table without generating them. Modify your drop statement to write it as: drop table TABLE_NAME PURGE; -- reprinted: -- I used to know that Windows only has a recycle bin. Today I heard that Oracle also has a recycle bin! -- Accidentally DROP a table in Oracle. If regular backup is not performed, it will cause a lot of trouble. In some cases, daily data is very important, and the regular backup cycle is a little longer. I am afraid the situation is not optimistic! -- But fortunately, Oracle has a recycle bin, which is exactly the same as the recycle bin in Windows. -- For example, the following error occurs: drop table drop_test; -- when you use the SELECT statement to query this TABLE, the system will prompt that the TABLE or view does not exist. However, you can use the following statement to query the table in the Oracle recycle bin: SELECT * FROM user_recyclebin WHERE original_name = 'drop _ test'; -- now you can use the following statement to restore the table: flashback table drop_test to before drop -- However, note that if you use the toad tool for mouse operations, that is, when you right-click the drop table operation, if you select the purge option, it is permanently deleted, the recycle bin does not exist in oracle, so do not select this option when deleting a table, unless you are sure that the table is no longer used.

-- Oracle10g. A table similar to BIN $ nJ5JuP9cQmqPaArFei384g ==$ 0 is displayed after you select and delete the table in pl/SQL. -- 1. view the recycle bin select * from user_recyclebin; -- 2. Clear the recycle bin purge recyclebin; -- 3. Clear a table in the recycle bin. If you delete a table in the following way, the system will prompt that the SQL command has not ended correctly. Purge table BIN $/UpBuh + LQ9yZGN95BFsk5Q = $0 -- the correct syntax is as follows: purge table "BIN $/UpBuh + LQ9yZGN95BFsk5Q = $0"; -- the following message is displayed when deleting: the SQL command is not completed correctly. Drop table BIN $/UpBuh + LQ9yZGN95BFsk5Q = $0 -- if you write as follows, the system will prompt that DDL/DML cannot be executed on objects in the recycle BIN. Drop table "BIN $0iJ7/rWFQrSGdZexvGv3qQ = $0" 4. restore the recycle bin flashback table "BIN $0iJ7/rWFQrSGdZexvGv3qQ = $0" to before drop -- this is a new 10g feature -- if the flash drop function is enabled in 10g, when you drop a table, the database does not directly delete it. Instead, it is placed in the recycle bin. When there is a shortage of space, this part of space will be gradually reclaimed. -- Bin $ indicates that the table is placed in the recycle bin. If you want to delete the table, you can find it again. If you want to delete the table, you can directly delete the delete tanle bin $ .....; -- another method is to use purge table table_name; -- drop table without generating them. Modify your drop statement to write it as: drop table TABLE_NAME PURGE; -- reprinted: -- I used to know that Windows only has a recycle bin. Today I heard that Oracle also has a recycle bin! -- Accidentally DROP a table in Oracle. If regular backup is not performed, it will cause a lot of trouble. In some cases, daily data is very important, and the regular backup cycle is a little longer. I am afraid the situation is not optimistic! -- But fortunately, Oracle has a recycle bin, which is exactly the same as the recycle bin in Windows. -- For example, the following error occurs: drop table drop_test; -- when you use the SELECT statement to query this TABLE, the system will prompt that the TABLE or view does not exist. However, you can use the following statement to query the table in the Oracle recycle bin: SELECT * FROM user_recyclebin WHERE original_name = 'drop _ test'; -- now you can use the following statement to restore the table: flashback table drop_test to before drop -- However, note that if you use the toad tool for mouse operations, that is, when you right-click the drop table operation, if you select the purge option, it is permanently deleted, the recycle bin does not exist in oracle, so do not select this option when deleting a table, unless you are sure that the table is no longer used.

1. Both delete and truncate delete the table content but cannot delete the table structure. drop deletes the table structure and content (the table will no longer exist );

2. delete can delete the data of the entire table or selectively delete one or more data records, while truncate deletes the data of the entire table at a time;

3. The table data deleted using delete is not deleted, and the tablespace occupied by the data exists. This is equivalent to deleting the data from Windows to the recycle bin and restoring the table data in the future;

4. Table data deleted using truncate will be directly deleted together with the occupied tablespace, which is equivalent to using del + shift to delete files in Windows and cannot be recovered.