Nice little gotcha I received yesterday morning. Our intention is to rebuild two large tables from range partitioning to hash partitioning on another server. One of the datafiles for the tablespace that houses one of the tables in question is 1.4TB in size. When we tried to put it in read only mode
(we are using transport tablespace to segment the rebuild on another box), it hung on the enq – TX contention wait event. I found (and never knew before) that putting *any* tablespace in read only requires that no outstanding (uncommitted or rolled back) transactions exist, even in a tablespace that you are not putting into read only mode. Unfortunately, a long running transaction was about six hour from completing, so we had to wait it out.
It wasn’t enormously inconvenient, but caused our rebuild to be pushed back a bit. I wonder why *no* transactions can exist? Does the kernel not “know” that there are no transactions that would affect the tablespace in question? Maybe it’s just to complex to track, and its such a rare occurrence, the kernel designers just said “to be safe, let’s ensure there is nothing happening when we go into read only mode”?