question

OraLearner avatar image
OraLearner asked

Do we need a commit after DDL statements ?

Generally to save the transactions or query results in the database we have a command COMMIT, which we doesn't use generally after DDL statemnts. If anybody knows the reason then please share with me ? Thank you in advance.

oracleplsqlddlcommit
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Christian13467 avatar image
Christian13467 answered

DDL statements are "autocommited" by design on oracle.

Tools working on oracle like sql developer offer an autocommit mode. Switching off this mode effects only dml statements. DDL statements are always autocommited.

Internal the processing is something like that:

BEGIN
   COMMIT;
   ... DDL ....
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
     ROLLBACK;
     RAISE;
END;

Thats different to sql server.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AutoCommit is a nice feature, until it bites you in the rear. Use with extreme caution in production environments - regardless of the tool you use.
1 Like 1 ·
HillbillyToad avatar image
HillbillyToad answered

DDL CREATE/ALTER/DROP commands are implicitly committed.

In a session, if you do 100 inserts, 20 updates, and then 1 DROP at the end, all of that work will be committed whether you issue a COMMIT or not.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.