Forgive my limited knowledge of toad, as I just started using it. I have a question. If I compile a package (F5), do I have to commit after compile else I will lose changes or compile package includes commit also? I do see “package created” in output windows and I note that in the toad editor, autocommit is not enabled.
When Oracle executes a DDL statement it automatically does a commit. This included things like create table, create view, truncate and compile commands (to name a few). In fact, Oracle does two commits as in:
This is built in behavior and has nothing to do with Toad.
Hope this helps,
I senr the following by email, but it appears not to have arrived. Hmm.
The only time you should/must commit is when you are doing INSERT, DELETE or UPDATE statements, in other words, DML statements, or after using the deprecated DBMS_JOB package to create a job.
(DML = Data Manipulation Language)
Whenver you run a DDL statement, CREATE, CREATE OR REPLACE, DROP, ALTER etc, then those statements will run a COMMIT first, do whatever SQL they need to do to facilitate the command, then COMMIT again if it worked.
So, the answer is no, you need not commit because the DDL command to compile a package (ALTER PACKAGE xxxxx COMPILE [BODY] will commit for you.
Auto-commit is a bad thing. It forces a commit after every statement, you should only commit after the end of a transaction - which may include many statements.
Never mix DDL and DML in the same script unless you manually commit the DML before the DDL executes. If you do mix them, you might not be able to rollback any errors because the DDL has committed anybchanges.
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Just to make clear.
I think that oracle do:
and doesn’t perform last commit.
you can trace it if you like, but Oracle will COMMIT before running the recursive SQL to facilitate the DDL statement, This will update the dictionary tables - depending on what the DDL created, altered or dropped - and then COMMIT, the recursive SQL’s changes.
If Oracle did not COMMIT after running the DDL’s recursive SQL, then the object(s) created/altered/dropped by the DDL statement would not become permanent.
So, if the DDL statement(s) work ok, we have this:
If the DDL failed for some reason, we have this:
IMHO, what Oracle do after DDL is far from classical commit/rollback action.
DDL are different types of actions and they do not have transaction in a way that DML do.
They have a transaction in the data dictionary, that’s why the transaction needs to be isolated (commit before) and why they need to either commit or rollback after (to make the data dictionary change stick or become undone)
How can I see that “commit/rollback” oracle behavior?
Now I am really interested in that.
It commits if the DDL was successful. I was expecting to see a rollback on failure, but maybe I didn’t do the right kind of failure (I tried creating a table that already exists, and creating a PK that failed to due duplicate rows)
In a trace file, the XCTEND lines indicate transaction. rlbk=0 on that line indicates commit. I was looking for XCTEND rlbk=1 after DDL failure but didn’t see it. I’ll bet Norm has more info. He usually does.
You can see the behavior with a SQL trace.
I would also strongly recommend getting a copy of “Expert Oracle Database Architecture” by Tom Kyte. Every Oracle person should sleep with this under their pillow.
These links might help, start with Ask Tom (second link) then do thevOracle Docs one.
Thx for links.
From Ask Tom:
DDL is processed like this (conceptually)
COMMIT; – any outstanding work
COMMIT; – the DDL statement
when others then
ROLLBACK; – any work done by the DDL
RAISE; – reraise the exception back to the client
So, exactly what I was saying … it is something like commit/rollback, but far deeper placed inside kernel, and certainly not executed like code above.
Well, when it all boils diwn to it, everything is done deep in the kernel. One of the rules of a relational database us that it must use relational language to do it’s own work, or recursive SQL as we know it.
When you traceva COMMIT, or ROLLBACK, you don’t always see that statement in the trace, sometimes you do. You will always see an XCTEND instead of a PARSE, EXEC. That’s a COMMIT or ROLLBACK happening regardless.
What Tom is saying is that Oracle does call COMMIT before a DDL, and COMMIT or ROLLBACK afterwards. Run a trace and see what you get. Did you check out the other link where the Oracle docs explain what happens too? They use COMMIT and ROLLBACK as well.
On my DBA course, that’s exactly how it was described, COMMIT, DDL, COMMIT or ROLLBACK.
If you think Tom’s use of the word conceptually is meaning ‘this is what it does, just not like this’, then haveva look in a trace file for a COMMIT being parsed at some DEP greater than 0 - that’s Oracle being recursive and committing it’s own work, and, anything you had mid-transaction. There’s nothing cenceptual about it - it must do it that way to be a relational database system!
In other words, it is not ‘something like a commit’, it is a commit.
Ok, I had some time during coffee this morning and traced the following:
exec sys.dbms_system.ksdwrt(1, ‘*** CREATE SEQUENCE ***’);
alter session set events ‘10046 trace name context forever, level 12’;
create sequence norm_seq start with 1 increment by 1 minvalue 1 maxvalue 999 cache 5 cycle order;
exec sys.dbms_system.ksdwrt(1, ‘*** DROP SEQUENCE (WORKS) ***’);
drop sequence norm_seq;
exec sys.dbms_system.ksdwrt(1, ‘*** DROP SEQUENCE (FAILS) ***’);
drop sequence norm_seq;
The first message ‘*** CREATE SEQUENCE ***’ was logged at line 25 in the trace file. The first XCTEND (aka COMMIT) occurred at line 33 just before the parsing of the create sequence statement.
The next XCTEND (COMMIT) occurred at line 1,641 in the trace file, after a whole lot of recursive SQL.
So far, so good, we have COMMIT; DDL; COMMIT - as predicted.
The second message ‘*** DROP SEQUENCE (WORKS) ***’, was written to the trace file at line 1,835, followed by the XCTEND at line 1,842, a whole pile of recursive SQL to drop the sequence, and a second XCTEND at line 10,076. (There’s a lot of work involved in dropping something as “simple” as a sequence it seems.
The second message ‘*** DROP SEQUENCE (FAILS) ***’, was written to the trace file at line 10,089, followed by the XCTEND at line 10,096. Then there was some recursive SQL to do various checks, no changes were made at this point, and eventually, the checks failed and an ERROR was written to the trace file. No XCTEND rlbck=1 was written AS NO CHANGES HAD YET OCCURRED.
I seem to have problems actually getting Oracle to do some work as part of a DDL statement that I intend to fail, so I’m currently unable to show the actual ROLLBACK version of the XCTEND statement, unfortunately.
However, we can see clearly, from the DDL that worked, that there is nothing “conceptual” in the code executed by Oracle for a DDL statement, it simply runs an XCTEND to COMMIT outstanding work, runs whatever recursive SQL is necessary to facilitate the DDL statement, then executes another XCTEND to COMMIT the changes to the data dictionary. These statements are identical to those seen when tracing “normal” SQL statements - a COMMIT or ROLLBACk are still shown as XCTEND.
Hope this helps to clear up things.
Thx Norm for your time.
Perhaps the post-DDL rollback occurs only after ORA-0600 - or the like (unexpected errors). The other, errors, the specific ones (object already exists, invalid syntax, etc) are handled before Oracle begins to make changes to the data dictionary.
yes, when I traced a DROP SEQUENCE for a sequence that I had already dropped, and traced, the trace file had nothing in it other than a few SELECTs, so no work done to rollback.
I did try a few other “problems” but they all showed the same thing, nothing to rollback.