Oracle parameters

Everything on this page is a setting on your database, not a setting in MCPDBWizard. The values Oracle ships with are sized for an ordinary application, and a generated MCP server is an ordinary application in every respect but one: it can expose a great many operations at once. Where that runs into a default, the fix is on the database side and takes a minute.

`open_cursors`

A cursor is Oracle’s handle on one parsed SQL statement in one session. open_cursors caps how many a single session may hold at a time. Oracle’s shipped default is 300.

SELECT value FROM v$parameter WHERE name = 'open_cursors';

Why a generated server uses them

A generated DAO prepares each statement once and keeps the handle, so the second call to a tool costs no parse. That is deliberate, and it is why these servers are fast under load.

The consequence is the thing to know:

A session’s open-cursor count grows with the number of distinct tools it has been asked for, and not with how many times they are called.

Once every tool has been used once, the count stops rising. A million calls through four tools opens four cursors. Calling four hundred different tools once each opens at least four hundred.

Most tools cost one cursor. A tool that returns a REF CURSOR, or that moves a CLOB or BLOB, costs about three. So a server publishing 237 tools was measured needing roughly 300 — the count of tools alone is a floor, not the figure to compare.

What it looks like when you hit it

ORA-01000: maximum open cursors for session exceeded

Two things make this misleading, and they are worth knowing before you go looking in the wrong place:

It clears itself and comes back. Hitting the cap raises the error, which releases the cursors, and the count climbs again from zero. The server does not stop. It fails roughly one call in every open_cursors, on a different tool each time.
So it reads as flakiness, not as a limit. In one measured run that was five failures in 1,536 calls — 0.3%, scattered across unrelated tools. That looks like a transient database blip rather than a server that has outgrown a parameter, and a deployment can sit in that state for a long time before anyone connects the two.

Load testing will not find it, for the same reason as above: the cost is per distinct tool, so hammering a handful of tools proves nothing about it. Only breadth reaches it.

The start-up warning

Because it is so easy to misread, a generated server checks at start-up and says so in its log.

When it can read open_cursors:

This server publishes 237 tools and open_cursors is 300. A generated DAO holds ONE OPEN
CURSOR PER DISTINCT TOOL for the life of the session … Either raise open_cursors on the
database, or split this config into smaller ones.

It warns before the counts are equal, because tools cost more than one cursor each. It says nothing at all for an ordinary config.

When the DAO account may not read V$PARAMETER, it does not guess:

This server publishes 539 tools, and open_cursors could not be read from V$PARAMETER on this
connection (the account needs SELECT_CATALOG_ROLE or a grant on it), so it is UNKNOWN and MAY
BE EXCEEDED.

That message is not a diagnosis, it is an admission — the limit is genuinely not visible from that connection. Grant the privilege if you want the precise version, or check the parameter yourself:

GRANT SELECT ON v_$parameter TO <your_dao_user>;

(Note v_$parameter — the underscore form — is the view a grant can name. V$PARAMETER is a public synonym for it.)

The check only ever warns. It will not stop a server starting, because a server whose callers touch a handful of its tools will never come near the limit.

Fixing it

Raise the parameter. This is the usual answer and it is cheap — a cursor is a small amount of session memory, and values in the low thousands are unremarkable on production systems.

ALTER SYSTEM SET open_cursors = 2000 SCOPE=BOTH;

SCOPE=BOTH applies it now and after the next restart. In a multitenant database, run it against the PDB your server connects to. You will need DBA rights, so this may be a request to whoever owns the database rather than something you run yourself.

Give yourself room above the tool count rather than matching it: the REF CURSOR and LOB tools cost about three each, and anything else using that account needs cursors too.

Or split the config. Two smaller configs are two servers with two sessions, each holding its own cursors — and smaller, more focused tool sets are easier for an agent to use well, so this is often worth doing on its own merits. See Configs.

Checking a running server

SELECT s.sid, s.module, st.value AS open_cursors
FROM   v$session s, v$sesstat st, v$statname sn
WHERE  s.sid = st.sid
AND    st.statistic# = sn.statistic#
AND    sn.name = 'opened cursors current'
AND    s.username = '<your_dao_user>'
ORDER  BY st.value DESC;

A generated server’s session reports its DAO factory class as MODULE — see Session management. Watch that figure while an agent works: if it climbs towards the limit as new tools are used and then plateaus, that is the behaviour described above working exactly as designed.

`processes` and `sessions`

Each running config is a separate server holding its own Oracle sessions, and connection pooling can give one server several. A host running many configs at once therefore needs enough of both.

SELECT name, value FROM v$parameter WHERE name IN ('processes', 'sessions');
SELECT COUNT(*) FROM v$session;

Running out shows up as ORA-00020: maximum number of processes exceeded or ORA-12516: TNS:listener could not find available handler, on connect rather than mid-call.

ALTER SYSTEM SET processes = <n> SCOPE=SPFILE; and restart — unlike open_cursors, this one cannot be changed in a running instance.

One thing to watch for, because it turns a small problem into a big one: a tool that fails on every call can retry hard enough to open sessions faster than they are released, and take the whole database’s process limit with it. If you see connection errors alongside a tool that is failing consistently, stop that server first — the database will not recover while it is still trying.