Basically when I run the below query or any like it (actually pulling data) I am getting the ORA-30927 error, I can run this query without the UNION and it will not give this error, however trying to run multiple SELECT with UNION (in order to display different data results from the same 'master' select ) results in this error. We were recently running on a different DB Server for the same instance, there are 6 servers for this DB and on the previous server this was never an issue and this error was never appearing for these types of queries, however now on a different server we are having these errors and in general very low performance. I engaged a DBA however they only proceeded to tell me what the error was doing ( which I already knew ) without explaining a root cause as to why it is happening on this server but not the other, what could be causing this behavior / errors?

This same simple SQL Code block runs with no issues or errors within one DB Server and is similar to some actual SQL Code being used within our Dashboards in relation to structure but actually pulling DB Data. Without the proper access (eg. to run select * from v$sgastat order by pool, bytes desc; or other system queries) and my limited knowledge about Oracle SGA it seems there might be an issue within the Shared Pool within the SGA or SGA optimization all together within this other server.

Any input would be appreciated. Thank you!

Code sample below


WITH DUAL_TEST AS (

SELECT '1' "Number_Test" FROM DUAL
UNION
SELECT '2' "Number_Test" FROM DUAL
UNION
SELECT '3' "Number_Test" FROM DUAL
)

--SELECT * FROM DUAL_TEST

SELECT *
FROM DUAL_TEST
WHERE "Number_Test" = 1

UNION

SELECT *
FROM DUAL_TEST
WHERE "Number_Test" = 2

Bug 14143632  ora-30927 on active data guard

 This note gives a brief overview of bug 14143632. 
 The content was last updated on: 20-DEC-2013
 Click here for details of each of the sections below.
Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected 
11.2.0.3 Bundle Patch 8 for Exadata Database
11.2.0.3 Bundle Patch 7 for Exadata Database
11.2.0.3
Platforms affected Generic (all / most platforms affected)