Enhance Skills Daily

October 03, 2026  ·   · 

Oracle DBA Lesson 54 Part 2 — Change the Intended PDB Setting

A useful parameter change starts with its container, supported modification scope, and intended lifetime. The same parameter name can have an inherited PDB value, a persistent local override, or, for a parameter that supports it, a session-specific value. A PDB rename also changes connection identity, so the service and client consequences belong in the plan.

Oracle DBA Lesson 54 Part 2 — Change the Intended PDB Setting

Check the current container

On Oracle Database 19c, a CDB uses one shared startup parameter file. ALTER SYSTEM in a PDB does not create a private SPFILE for that PDB. In SQL*Plus, check each container you intend to change:

SHOW CON_NAME

SELECT name, value, ispdb_modifiable, issys_modifiable
FROM v$system_parameter
WHERE name = 'open_cursors';

Oracle 19c documents the OPEN_CURSORS default as 50. An installation can already use a different configured value. The figures 300, 400, 500, and 600 below are example limits, not defaults or a tuning recommendation. An opening comparison can look like this:

Connected containerNAMEVALUE
CDB$ROOTopen_cursors300
APP_PDBopen_cursors300

OPEN_CURSORS limits how many open cursors one session can hold. A cursor is a handle to a private SQL area. The limit is a count per session, not a memory allocation. Investigate the application's cursor use, and any cursor errors, before you choose a value.

The same number in the root and the PDB does not prove inheritance. A local setting can equal the root value. Establish the configuration from the recorded local changes and the persistent-setting evidence for that environment.

A wider check of related parameters is:

SELECT name, value, ispdb_modifiable,
       issys_modifiable, isdefault
FROM v$system_parameter
WHERE name IN ('sessions', 'cursor_sharing', 'local_listener')
ORDER BY name;

Read each parameter on its own. Whether it allows a local change, and when that change takes effect, are separate properties. Changing CURSOR_SHARING can affect SQL matching and execution. It is not a general tuning remedy. LOCAL_LISTENER concerns listener registration. It does not create a private listener process for a PDB.

MetadataInterpretation
ISPDB_MODIFIABLE = TRUEThe parameter supports modification inside a PDB, subject to its documented constraints.
ISPDB_MODIFIABLE = FALSEReject a proposed independent local PDB modification.
ISSYS_MODIFIABLE = IMMEDIATEA supported ALTER SYSTEM change can take effect immediately. Scope still controls whether the running setting is changed.
ISSYS_MODIFIABLE = DEFERREDThe change applies to subsequent sessions. Use the required DEFERRED form. Existing sessions retain the earlier value.
ISSYS_MODIFIABLE = FALSEA running system change is unsupported. A supported static change needs persistent configuration and the later restart or reopening boundary that applies.
ISSES_MODIFIABLE = TRUEALTER SESSION is supported for that parameter.
ISDEFAULTDescribes default versus explicitly specified configuration under the view's documented semantics. It is not an inheritance flag.

A flag is one part of the decision. Also check the permitted range, dependencies, root and PDB limits, and the other restrictions for that parameter.

See V$SYSTEM_PARAMETER, OPEN_CURSORS, and CURSOR_SHARING.

Compare the session value with the system value

V$PARAMETER describes the value in effect for the current session. V$SYSTEM_PARAMETER describes system-level values. In a PDB, read the system value in the connected container. A new session starts from the system values. A supported session change can then make that session differ.

For a parameter that supports session changes:

SELECT name, value, isses_modifiable, ismodified
FROM v$parameter
WHERE name = 'cursor_sharing';

SELECT name, value, ispdb_modifiable,
       issys_modifiable, isdefault
FROM v$system_parameter
WHERE name = 'cursor_sharing';

When one connection behaves differently, compare its session evidence with the system evidence. A session override applies only to that session. It is separate from a persistent change for the whole PDB. The OPEN_CURSORS statements below are system changes. They do not set CURSOR_SHARING, and OPEN_CURSORS is not the session-modifiable case.

See V$PARAMETER.

A root change reaches inheriting PDBs

Start from this configuration. REPORT_PDB inherits OPEN_CURSORS. APP_PDB has a persistent local override.

ContainerOPEN_CURSORSConfiguration
CDB$ROOT300Root value
REPORT_PDB300Inherits the root value
APP_PDB400Persistent local override

Changing the root to 500 with CONTAINER=CURRENT updates the root and the PDB that inherits the parameter. The PDB with a persistent override keeps its local value.

ContainerResulting valueReason
CDB$ROOT500Root setting changed
REPORT_PDB500Inherits the changed root setting
APP_PDB400Persistent local override retained

What changes is the inherited value. The root keeps its new value. The statement does not move a database, a cursor, or a file.

Before the change, record the original running value, the persistent setting, whether the PDB inherits or overrides, the affected services and sessions, the intended result, and the restoration path. Schedule the interruption and account for unfinished work. This form applies to a supported dynamic parameter in an SPFILE-backed CDB:

-- Example only: connected to CDB$ROOT
ALTER SYSTEM SET open_cursors = 500 CONTAINER = CURRENT;

This is the root-impact form. It is not the way to change only one application PDB. CURRENT in the root includes PDBs that inherit the parameter. CONTAINER=ALL, issued from the appropriate root with the required privilege, applies the setting across containers and sets their inheritance property to true. Use ALL only when that broader result is intended. The statement above uses the documented SPFILE-backed default scope. State SCOPE explicitly in the plan.

See administering a CDB.

Set a local PDB value

After you confirm APP_PDB, parameter support, the required privilege, and the value:

SHOW CON_NAME
-- Expected target: APP_PDB

ALTER SYSTEM SET open_cursors = 600
  SCOPE = BOTH
  CONTAINER = CURRENT;

The earlier persistent local value was 400. This statement changes it to 600. In the connected PDB, CONTAINER=CURRENT targets that PDB. SCOPE=BOTH changes the running value and stores a persistent PDB-specific setting. Justify 600 from the workload. It is not a recommendation.

Check the system value in the target container and test the application behavior that depends on it. When the parameter is deferred, use a new session. A query of the current value does not prove restart behavior. Verify persistence when the PDB is closed and reopened, or when the CDB restarts.

Choose the lifetime of a PDB setting

SCOPERunning PDBAfter close/reopen or CDB restart
MEMORYSupported dynamic running value changes.The temporary local value is lost. Root inheritance resumes under the documented conditions.
SPFILEThe current running value is unchanged.The stored PDB setting takes effect at the later reopening boundary.
BOTHSupported dynamic running value changes.The local setting persists across reopening and restart.

A traditional text PFILE cannot contain PDB-specific parameter values. Check the startup mode and the supported scope before you plan persistence. For a root-level static parameter, the required boundary can be a full instance restart. Reopening a PDB does not apply every root or static change.

See administering PDBs and ALTER SYSTEM.

Remove a local override

If the parameter was inherited, setting the same number locally does not restore inheritance. That statement creates a local override. Reset the PDB-specific persistent setting:

-- Run in the intended PDB:
ALTER SYSTEM RESET open_cursors SCOPE = SPFILE;

Close and reopen the PDB before you rely on the restored inherited running value. Verify the value and the inheritance against the root configuration. Restoring a stored local value and removing a local override are different tasks. Use the reset scope that parameter supports.

Rename the PDB and its default service

Record the old and proposed global names. Inventory default-service users, user-defined application services, connection strings, connection pools, jobs, and other name references. The proposed global name must differ from the other container global names and meet the documented naming rules.

Application containers, proxy PDBs, and managed services have additional conditions. For an ordinary PDB on a single instance, the rename workflow is:

  1. Open the target PDB READ WRITE RESTRICTED. Arrange the interruption, and confirm sessions and unfinished work, before that open.
  2. Connect to that PDB with ALTER DATABASE, granted commonly or locally in the PDB, and with restricted-session access.
  3. Rename the global name.
  4. Close the PDB and reopen it READ WRITE so the new service name is integrated.

Issue the rename in the target PDB:

ALTER PLUGGABLE DATABASE
  RENAME GLOBAL_NAME TO app_new.example.com;

The PDB name becomes APP_NEW, the first component of the new global name. Oracle changes the default service name and sets the PDB property of every service in that PDB to the new global name. Inspect the network, service, and domain configuration in the environment.

User-defined application service names and external client references need their own review. When clients used the old default service name, update their route. After reopening, check service registration, the ordinary application account, SHOW CON_NAME or SYS_CONTEXT('USERENV','CON_NAME'), and the required application work.

On RAC, the PDB must be open on the current instance only and closed on the other instances.

To return to the previous global name, repeat that restricted rename, close and reopen, and restore the affected service and client references. Restore changed parameter values, inheritance, and the opening policy as well. A rename back to the old global name leaves external client configuration and application behavior to be checked on their own.

See ALTER PLUGGABLE DATABASE.

Read a proxy HOST and PORT as an endpoint

A proxy PDB connects to a referenced PDB through listener endpoint information. The documented HOST and PORT values say how the proxy reaches that listener. Updating them changes the reference destination. It does not start a listener process for every ordinary PDB. Configure and verify listener processes, endpoints, and service registration through network administration. Several PDB services can share one listener.

See creating a proxy PDB.

Recap

Start with the container, the modification flags, and the lifetime. V$PARAMETER is the current session. V$SYSTEM_PARAMETER is the system value in the connected container. ISPDB_MODIFIABLE = FALSE means an independent local PDB change is unsupported. CONTAINER=CURRENT in the root also updates PDBs that inherit the parameter, and a persistent local override stays. SCOPE=BOTH changes the running value and stores a PDB-specific setting. A text PFILE cannot hold that PDB setting. Setting the inherited number locally does not restore inheritance: reset the PDB-specific setting, then close and reopen. RENAME GLOBAL_NAME changes the PDB name and the default service. Close and reopen read/write to integrate the new service name, then check services and clients. A proxy HOST and PORT update the referenced endpoint. They do not create a listener for each PDB.

Quiz

1. ISPDB_MODIFIABLE is FALSE. What should happen to a proposed independent PDB-local parameter change?

2. A root change uses CONTAINER=CURRENT. Which PDB can follow the root value?

3. For a supported dynamic PDB parameter in an SPFILE-backed database, what does SCOPE=BOTH mean?

4. What completes integration of a new PDB service name after RENAME GLOBAL_NAME?

5. Does changing a proxy reference HOST and PORT create an isolated listener process for every ordinary PDB?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...