Read only tablespace requires no outstanding transactions...anywhere...

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"?

Do we need AWR?

A recent post on CDOS got me to thinking...do we need AWR? After all, AWR stands for "Automated Workload Repository". There are three words in that functionality title.

From dictionary.com:

Automated (automatic, actually): having the capability of starting, operating, moving, etc., independently
Workload: The amount of work that a machine produces or can produce in a specified time period.
Repository: A place where things may be put for safekeeping.

The normal reasons given for using AWR (or not) are as follows:

1. You can't duplicate the functionality
2. It is against your license terms to use AWR if you haven't paid for it.

If either is true, and depending on which one is, then you may or may not want to use it.

The first one indicates you can't do what it does. To answer this accurately, we have to document what it does. To be honest, I can't list every single piece of functionality, but I can say what I use it for (which is all that matters, anyway). I never use the Grid Control to view AWR information as a baseline. I have written my own queries to query the AWR (we *do* have it licensed). This is a piece of functionality I have always found lacking in AWR. If I want to see the historical change in db file sequential read, there is no Grid tool (of which I aam aware) that will do this for me. As a result, I wrote my own query to which I pass an event, and it will print out the historical time waited spent on this event. You can of course (and I do) apply this to OSSTAT, SYSSTAT, etc. In order to do this, of course, I must query the AWR views...or do I?

I could write my own SQL that would create tables as a copy of each v$view, and then periodically select everything from the current contents of these views into these repository tables. This is a simple solution. In fact, I wrote it this morning in 45 minutes.

But wait, is this legal? In other words, are the historical AWR views the ones that cannot be used, or is it the live v$view information that cannot be queried?

According to http://download.oracle.com/docs/cd/B19306_01/license.102/b14199.pdf, the only off limits view is v$active_session_history.

Command-Line APIs
Diagnostics Pack features can also be accessed by way of database server APIs and
command-line interfaces:

• The DBMS_WORKLOAD_REPOSITORY package is part of this pack.
• The DBMS_ADVISOR package is part of this pack if you specify ADDM as the value of the advisor_name parameter, or if you specify for the value of the task_name parameter any value starting with the ADDM prefix.
• The V$ACTIVE_SESSION_HISTORY dynamic performance view is part of this pack.
• All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.
• All data dictionary views with the prefix DBA_ADVISOR_ are part of this pack if queries to these views return rows with the value ADDM in the ADVISOR_NAME column or a value of ADDM* in the TASK_NAME column or the corresponding TASK_ID.
• The following reports found in the /rdbms/admin/ directory of the Oracle home directory are part of this pack: awrrpt.sql, awrrpti.sql, addmrtp.sql, addmrpti.sql, awrrpt.sql, awrrpti.sql, addmrpt.sql, addmrpti.sql, ashrpt.sql, ashrpti.sql, awrddrpt.sql, awrddrpi.sql, awrsqrpi.sql, awrsqrpt.sql.