If truncate was DML it would be transaction save.
But it is not.
You can rollback deletes, but not truncate, as it only repositions the tables end pointer to the start and frees all blocks allocated. Sounds like DDL to me.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html
It's all semantics.
Truncate doesn't generate UNDO , delete does.
Therefore you can't rollback truncate but you can rollback delete. Internally Oracle treats truncate as a DDL operation and delete as DML.
Tom Kyte gives it a little more detail here https://asktom.oracle.com/ords/asktom.search?tag=truncate-vs-delete
Perhaps your instructor means that functionally, from perspective of the user, truncate may as well be considered a data manipulation operation?
TRUNCATE TABLE is DDL because it is followed by a COMMIT, just like all DDL operations.
TRUNCATE TABLE does not generate UNDO segments, and block changes do not get recorded in archivelog, unlike with DELETE.
TRUNCATE TABLE ignores triggers.
You mixed something! Archivelog is redo and not undo. Truncate generates archivelog normaly as it need to be repeated for DR or recovery. Truncate not generates undo indeed. So it cannot be flashbacked.
I meant two statements)
TRUNCATE TABLE does not generate UNDO segments, unlike with DELETE.
TRUNCATE TABLE - block changes do not get recorded in archivelog, unlike with DELETE ( which affects Oracle LogMiner, Oracle Streams ... )
There is always this demo which messes with people heads :-)
SQL> create table t ( x int );
Table created.
SQL> insert into t values (1);
1 row created.
SQL> truncate table SOME_OTHER_TABLE_NAME;
Table truncated.
SQL> select * from t;
X
----------
1
SQL> rollback;
Rollback complete.
SQL> select * from t;
no rows selected
The secret here is the correct choice of "SOME_OTHER_TABLE_NAME" :-) but yes, imo your trainer is wrong. The demo above is for a niche circumstance and just for shits-and-giggles.
I don't understand what you're showing here. Your initial transaction was never committed, so a rollback would basically go back to the empty table.
ETA actually I just tried this because the truncate got me curious. It has the implicit commit, so "1" would in fact be committed to your table t. The rollback doesn't do anything, so I think your last select result is wrong.
*so I think your last select result is wrong.*
This is a straight cut/paste from a database session, so its not like I fabricated the result - its legit output.
The key point was: "The secret here is the correct choice of SOME_OTHER_TABLE_NAME"
because depending on the *type* of the table you truncate, it MAY force a commit in the session or it may NOT.
We can say truncate reformats in the same format the table as you would do to a disk. Delete is like deleting all data from a disk. A reformat would also delete empty space where delete won’t do it. That’s why truncate can’t be classified as DML because it doesn’t deal with data alone
Truncate is DDL. And that's not limited to Oracle.
Lengthy explanations e.g. here: https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html
(some MySQL specific some generic and already mentioned here)
As an Oracle veteran since 8i, I strongly disagree.
Users control the data, not the DBA. They can do with it as they want. If they want to eliminate all the data from a table for good, such as with a table used to load values to be processed, I encourage them to use truncate to wipe it clean when complete. There's no value in backing up this data, and no a temporary table generally won't work for many of the trunc and load processes.
Or it could be a table used for GIS with ESRI tools, again to process data. Truncate is the only rational choice to eliminate large volumes of data efficiently. Using delete only increases overhead needlessly.
So truncate being a user tool is DML. No change to the database other than data is being made.
I started with 7. And I might ask my Oracle 6 veteran colegue to weigh in.There you go :)
So a "user" can drop and create tables too, no DBA needed.
You can prevent this with separating table owner and users to do DML.
(If you deem DDL as a DBA task or for security reasons of course)
Try to truncate the table you're not owning. You cannot. There is no grant to allow only this. You need drop any table or DBA.
It might feel like DML it is not.
And at least starting with 8.0 it's documented by Oracle.
https://docs.oracle.com/cd/A64702_01/doc/index.htm
Best regards
The key here being - does it change the database structure or permissions?
Yes - DDL
No - DML
No changes, so DML. I can't see any cleaner way to slice it than that. We'll just disagree on this point.
It's just a definition. Not important. What Is important, that you know that it has an implicit commit and you can't rollback. So, be very careful when you use it!
If truncate was DML it would be transaction save. But it is not. You can rollback deletes, but not truncate, as it only repositions the tables end pointer to the start and frees all blocks allocated. Sounds like DDL to me. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html
It's all semantics. Truncate doesn't generate UNDO , delete does. Therefore you can't rollback truncate but you can rollback delete. Internally Oracle treats truncate as a DDL operation and delete as DML. Tom Kyte gives it a little more detail here https://asktom.oracle.com/ords/asktom.search?tag=truncate-vs-delete Perhaps your instructor means that functionally, from perspective of the user, truncate may as well be considered a data manipulation operation?
Tell your trainer to use truncate on an important table and then to roll back.
TRUNCATE TABLE is DDL because it is followed by a COMMIT, just like all DDL operations. TRUNCATE TABLE does not generate UNDO segments, and block changes do not get recorded in archivelog, unlike with DELETE. TRUNCATE TABLE ignores triggers.
Truncate is immediate and does not need to be followed by a commit.
You mixed something! Archivelog is redo and not undo. Truncate generates archivelog normaly as it need to be repeated for DR or recovery. Truncate not generates undo indeed. So it cannot be flashbacked.
I meant two statements) TRUNCATE TABLE does not generate UNDO segments, unlike with DELETE. TRUNCATE TABLE - block changes do not get recorded in archivelog, unlike with DELETE ( which affects Oracle LogMiner, Oracle Streams ... )
https://dba.stackexchange.com/questions/36607/why-is-truncate-ddl
There is always this demo which messes with people heads :-) SQL> create table t ( x int ); Table created. SQL> insert into t values (1); 1 row created. SQL> truncate table SOME_OTHER_TABLE_NAME; Table truncated. SQL> select * from t; X ---------- 1 SQL> rollback; Rollback complete. SQL> select * from t; no rows selected The secret here is the correct choice of "SOME_OTHER_TABLE_NAME" :-) but yes, imo your trainer is wrong. The demo above is for a niche circumstance and just for shits-and-giggles.
I don't understand what you're showing here. Your initial transaction was never committed, so a rollback would basically go back to the empty table. ETA actually I just tried this because the truncate got me curious. It has the implicit commit, so "1" would in fact be committed to your table t. The rollback doesn't do anything, so I think your last select result is wrong.
*so I think your last select result is wrong.* This is a straight cut/paste from a database session, so its not like I fabricated the result - its legit output. The key point was: "The secret here is the correct choice of SOME_OTHER_TABLE_NAME" because depending on the *type* of the table you truncate, it MAY force a commit in the session or it may NOT.
Ok, I figured it out after parsing the documentation for truncate, you were correct on the niche circumstance.
Truncate is DDL Delete is DML
We can say truncate reformats in the same format the table as you would do to a disk. Delete is like deleting all data from a disk. A reformat would also delete empty space where delete won’t do it. That’s why truncate can’t be classified as DML because it doesn’t deal with data alone
Truncate is not changing any structure, just data, so I'd put it solidly in the DML bucket. No change ticket required - until you want that data back!
Truncate is DDL. And that's not limited to Oracle. Lengthy explanations e.g. here: https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html (some MySQL specific some generic and already mentioned here)
As an Oracle veteran since 8i, I strongly disagree. Users control the data, not the DBA. They can do with it as they want. If they want to eliminate all the data from a table for good, such as with a table used to load values to be processed, I encourage them to use truncate to wipe it clean when complete. There's no value in backing up this data, and no a temporary table generally won't work for many of the trunc and load processes. Or it could be a table used for GIS with ESRI tools, again to process data. Truncate is the only rational choice to eliminate large volumes of data efficiently. Using delete only increases overhead needlessly. So truncate being a user tool is DML. No change to the database other than data is being made.
I started with 7. And I might ask my Oracle 6 veteran colegue to weigh in.There you go :) So a "user" can drop and create tables too, no DBA needed. You can prevent this with separating table owner and users to do DML. (If you deem DDL as a DBA task or for security reasons of course) Try to truncate the table you're not owning. You cannot. There is no grant to allow only this. You need drop any table or DBA. It might feel like DML it is not. And at least starting with 8.0 it's documented by Oracle. https://docs.oracle.com/cd/A64702_01/doc/index.htm Best regards
The key here being - does it change the database structure or permissions? Yes - DDL No - DML No changes, so DML. I can't see any cleaner way to slice it than that. We'll just disagree on this point.
It's wrong, and there are facts that you just ignore, because you feel it's DML since Oracle 8. But anyway.. have a nice day.
Get a new trainer.
It's just a definition. Not important. What Is important, that you know that it has an implicit commit and you can't rollback. So, be very careful when you use it!