The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes.
Oracle clearly classifies TRUNCATE as DDL in the Concepts Guide, but DELETE as DML.
The main points that put TRUNCATE in the DDL camp on Oracle, as I understand it, are:
TRUNCATE can change storage parameters (the NEXT parameter), and those are part of the object definition - that's in the DDL camp.TRUNCATE does an implicit commit, and cannot be rolled back (flashback aside) - most (all?) DDL operations in Oracle do this, no DML does.
The fact that TRUNCATE doesn't run ON DELETE triggers also sets it apart from normal DML operations (but some direct path DML operations also skip triggers, so that's not a clear indicator).
That same documentation notes that DELETE generates UNDO, but TRUNCATE doesn't, so your statement is correct in this respects. (Note that TRUNCATE does generate some REDO so that the truncation can be replayed in case of restore/recovery.) But some NOLOGGING operations can also produce reduced UNDO (not sure about none at all), so that's not a clear indicator either in my opinion.
So I'd sum it up as:
truncate is not "transactional" in the sense that it commits and can't be rolled back, and can modify object storage attributes. So it's not ordinary DML - Oracle classifies it as DDL.delete is an ordinary DML statement.
Apparently you dropped the object already. Now to calm you down - corrupted blocks now most probably are in free space and do no harm. They just annoy you during backups. To check that:
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
from dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1
and f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
If it really shows here you have two options. You can simply ignore it. Once the block will be assigned to some object it will be reformated and corruption will go away. If you want to fix corrupted blocks then you will have to create the object which would occupy your corrupted blocks.
Let's say your corrupted block exists in tablespace USERS and datafile /ora/users01.dbf. First you have to make datafiles of tablespace not autoextendable. That is not to inflate datafile size. And then you'll create filler table.
create table filler (
padding varchar2(1000)
) tablespace users nologging pctfree 90 pctused 10;
Then you have to extend table's segment so that it would incorporate corrupt blocks:
alter table filler allocate extent
(datafile '/ora/users01.dbf' size 100m);
In such way you can add several extents adjusting their sizes. After you'll check that corrupt blocks now longer belong to free space you can fill the table:
insert /*+append */ into filler select rpad('x',1000) from
dba_objects
-- filler
where rownum <= 50000;
First insert/select goes from dba_objects then it is faster to insert/select from filler table itself.
Now you just have to check datafile using RMAN:
RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 333;
And drop filler table:
DROP TABLE filler PURGE;
Easy. :)
Best Answer
We need to understand how DB performs both the operations. Database in itself maintains record for each table. For simplicity, you can consider a database as a table, and all the tables information of database as data of that table.
So now when you DROP a table (which actually deletes the data and structure of table), Database doesn't delete the entry of that table and it maintains an entry of that table (in other words, sort of a soft delete operation). When you PURGE, then only database start looking for those table entries which are marked as soft delete and start deleting them permanently. Hence when it is purge (or permanently deleted) then you cannot recover it.
OTOH when you execute a TRUNCATION query, then it deletes the data but it is also combined with PURGE command. So when you TRUNCATE then it removes the data permanently.
In simple words, you can relate this with example of DEL and Shift+Del. When you DEL a file, then it goes to RecycleBin and you can recover it from there. But when you delete a file with Shift+Del then it permanently deletes. I hope it helps.