This is more a note to myself in case I’ll encounter a similar environment. But maybe it helps others – at least my search results weren’t suitable to Windows in the first place.
C:\> set ORACLE_HOME=C:\path\to\grid\home
C:\> set ORACLE_SID=+ASM1
C:\> asmcmd
connected to an idle instance.
Some troubles — especially those happening only sporadically — are not so easy to shoot and call for a deeper understanding of the matter. In the following real-world example this means: SQL*Net Tracing and some knowledge about the inner workings of the server’s operating system, particularly random number generation.
This case was suited well to demonstrate an approach to trouble-shoot connections to Oracle databases.
Up to Oracle 11.2 it was possible to display archived SQL plans from Statspack using DBMS_XPLAN. I make use of this in some of my scripts and SQL Developer Reports since I first saw this in Christian Antognini’s Book “Troubleshooting Oracle Performance“.
But in 12c (here: 12.1.0.1 on Linux), there’s a piece missing now:
select * from table(dbms_xplan.display( table_name => 'perfstat.stats$sql_plan', statement_id => null, format => 'ALL -predicate -note', filter_preds => 'plan_hash_value = '|| &&phv ); ERROR: an uncaught error in function display has happened; please contact Oracle support Please provide also a DMP file of the used plan table perfstat.stats$sql_plan ORA-00904: "TIMESTAMP": invalid identifier
So it looks like STATS$SQL_PLAN wasn’t synchronized to the changes in 12c’s PLAN_TABLE. Maybe because the timestamp wouldn’t make much sense there, anyway, maybe simply because Oracle forgot.
==> Quick and most certainly unsupported workaround:
ALTER TABLE perfstat.stats$sql_plan ADD timestamp INVISIBLE AS (cast(NULL AS DATE));
Another workaround could be to create a separate view with an additional dummy timestamp column an reference the view. I chose to stick with the invisible column solution so I won’t have to create new objects in the DB and change scripts to use these objects.
Hopefully, this will be solved in 12.2 – at least, I had filed an SR / Enhancement Request with Oracle Support.
Oraculix (en) 