I found out this afternoon that a transaction waiting to receive all required latches is subordinate to a ddl statement. Regard the following:
[ZZXJFF@OCP04:W2ZZXJFF02] SQL> select * from blaat; A B - ---------- b 1 b 2
[ZZXJFF@OCP04:W2ZZXJFF02] SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE FLOW_1 ONLINE 7 rows selected. [ZZXJFF@OCP04:W2ZZXJFF02] SQL> SELECT SQL_TEXT, SADDR 2 FROM V$SQLAREA,V$SESSION 3 WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS 4 AND SQL_TEXT LIKE 'update%'; SQL_TEXT SADDR ---------------------------------------------------------------- -------- update blaat set b=3 6CF21C9C [ZZXJFF@OCP04:W2ZZXJFF02] SQL> SELECT SES_ADDR, START_SCNB 2 FROM V$TRANSACTION 3 ORDER BY START_SCNB; SES_ADDR START_SCNB -------- ---------- 6CF267BC 0 6CF28D4C 1650137 [ZZXJFF@OCP04:W2ZZXJFF02] SQL> @ses % % % Username OSUser SADDR Stmnt Object -------------------------------- ----------- -------- ---------------------- ----------------------- ZZXJFF '138 ,26' EMEA\zzxjff 6CF267BC @q9 1029988163 138 ZZXJFF '140 ,8' EMEA\zzxjff 6CF28D4C @qc 2981482239 140 ZZXJFF '134 ,9' EMEA\zzxjff 6CF21C9C @qc 3856233622 134 TABLE ZZXJFF.BLAAT * ZZXJFF '130 ,5' EMEA\zzxjff 6CF1D17C TABLE SYSTEM.SQLPLUS_PRODUCT_PROFILE
In session 1 (130) i query the lot, session 2 (138) gives the first update, session3 the third (134) and session 4 (140) alters the tablespace ‘USERS’ read only.
On commit in session 2 (138), the alter tablespace statement takes precedence over the second update statement. As you can see, the second update hasn’t got SCN, so it isn’t really even a transaction yet (?, have to look that one up !), it fails with the following EM:
[ZZXJFF@OCP04:W2ZZXJFF02] SQL> update blaat set b=3; update blaat set b=3 * ERROR at line 1: ORA-00372: file 4 cannot be modified at this time ORA-01110: data file 4: 'D:\DATA\ORACLE\ORADATA\OCP04\USERS01.DBF'
And of course the tablespace is read only:
[ZZXJFF@OCP04:W2ZZXJFF02] SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMP ONLINE USERS READ ONLY EXAMPLE ONLINE FLOW_1 ONLINE 7 rows selected.
Below the (excerpt) of the alert log, as you can see, the message that the tablespace was to be altered to READ ONLY was written at the time the statement was entered, the statement completed when i committed in session 2 (138) -the first update-.
Tue Dec 23 00:31:18 2008 alter tablespace users read only Tue Dec 23 00:31:56 2008 Shutting down archive processes Tue Dec 23 00:32:01 2008 ARCH shutting down ARC2: Archival stopped Tue Dec 23 00:33:49 2008 Converting block 0 to version 10 format Completed: alter tablespace users read only
Will have to dive into the whole latching business again, ’cause i do want to know what and why the differences are between the DDL/DML latching acquirements.