Oracle DBA Lesson 54B — Plan a PDB Setting or Rename

A parameter proposal should identify the container, the current value, when a change would apply, and how the original configuration would be restored. A PDB rename also changes identities used by services and connections. These checks make the affected workload visible before a change is scheduled.

Oracle DBA Lesson 54B — Plan a PDB Setting or Rename

Read the setting in the intended container

Use SQL*Plus with the instructor's read-only COURSE_DBA connection to LABPDB:

SHOW USER
SHOW CON_NAME

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

SHOW CON_NAME reports the session's current container. V$SYSTEM_PARAMETER supplies system parameter values and modification metadata in that reporting context. Keep the container name beside the result. An authorized common observer can run the same query in CDB$ROOT for comparison. A local PDB user needs a separate connection supplied by the instructor.

Field	Interpretation
VALUE	Current system value; retain its exact returned representation, including an empty or null value
ISPDB_MODIFIABLE=TRUE	A PDB may set the parameter, subject to its documented rules
ISPDB_MODIFIABLE=FALSE	Reject a proposed independent PDB setting
ISSYS_MODIFIABLE=IMMEDIATE	A supported live system change takes effect immediately
ISSYS_MODIFIABLE=DEFERRED	A system change applies to subsequent sessions; existing sessions retain their values
ISSYS_MODIFIABLE=FALSE	Plan a static persistent setting and the relevant restart; at root, the instance must restart
ISDEFAULT	Default or parameter-file status, interpreted with configuration evidence

These are interpretations of actual returned metadata, rather than assumed output. See V$SYSTEM_PARAMETER.

The three selected parameters illustrate different purposes:

• SESSIONS limits sessions. Oracle 19c permits a PDB-local value, bounded by the CDB's value, and gives the root a different modification rule. Inspect both scopes. This exercise proposes no capacity adjustment. See SESSIONS.
• CURSOR_SHARING controls which statements can share a cursor. EXACT and FORCE are supported values. Choosing between them needs workload evidence and performance testing. Reading its row supplies no tuning recommendation. See CURSOR_SHARING.
• LOCAL_LISTENER identifies a network name resolving to local listener addresses. A parameter value describes registration configuration. The listener process and its actual endpoint require their own inspection. See LOCAL_LISTENER.

Separate a session value from system configuration

Read the current session's evidence:

SELECT name, value, isses_modifiable, ismodified
FROM v$parameter
WHERE name IN ('sessions','cursor_sharing','local_listener')
ORDER BY name;

V$PARAMETER.VALUE is the value effective in the current session. ISSES_MODIFIABLE reports whether ALTER SESSION is supported. In this view, ISMODIFIED=MODIFIED records a session modification, SYSTEM_MOD records a system modification affecting sessions, and FALSE indicates no modification since instance startup. A session override can explain a difference from the system value. See V$PARAMETER.

Add the system modification evidence in both the PDB and root connections:

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

In this system view, ISMODIFIED=MODIFIED records an ALTER SYSTEM modification. The fields describe modification and default status. Neither field alone certifies the PDB's inheritance policy. Equal root and PDB values can arise from inheritance or from a local setting equal to the root value.

Compare the two containers' actual results and review the instructor's recorded local configuration and parameter-change history. Mark the origin confirmed inherited, confirmed local, or unresolved, with the supporting evidence. A documented local SET or subsequent RESET, with its scope and reopening history, helps establish the configuration. A pending persistent change may differ from today's live value. Preserve uncertainty when the records are incomplete.

The documented inheritance model applies the root value when the PDB has no independent setting. See CDB parameter inheritance.

Choose container scope and persistence separately

Proposed container scope	Affected configuration
PDB + CONTAINER=CURRENT	That PDB's supported local setting
Root + CONTAINER=CURRENT	Root and PDBs inheriting that parameter
Root + CONTAINER=ALL	All containers; PDB inheritance is set to true

CURRENT is the default, so a root connection can still affect inheriting PDBs. Record the affected container set explicitly. See ALTER SYSTEM container semantics.

From the authorized root observer, inspect the startup mode:

SHOW PARAMETERS SPFILE

A returned SPFILE name identifies the server parameter file in use. An empty value calls for checking the recorded PFILE startup procedure. At root, MEMORY changes the running instance, SPFILE stores a future-startup value, and BOTH combines live and persistent changes where supported. With a PFILE, ALTER SYSTEM supports memory changes. Persistent text-file edits are a separate planned operation. See Initialization-file administration.

For a supported PDB setting under the documented SPFILE workflow:

Scope	PDB behavior
MEMORY	Live override; it ends when the PDB closes and reopens
SPFILE	Stored local value; applies at PDB reopening or CDB restart
BOTH	Live and stored local value; persists after reopening or restart

Respect ISSYS_MODIFIABLE as well. A deferred change needs DEFERRED, and static settings require persistent staging. A PFILE cannot hold PDB-specific settings. Confirm the actual startup mode and parameter-specific support before drafting executable SQL. See PDB parameter persistence.

Restoring a previously inherited configuration means removing the introduced local override using the documented PDB RESET scope and activation procedure. Simply setting a local value equal to the root preserves a local setting. An originally explicit local configuration instead needs its captured value and persistence policy restored. These are proposal choices here. No ALTER SYSTEM execution is part of this exercise.

Practice: complete a proposed change sheet

Use Oracle Database 19c, a single-instance Linux CDB and PDB, and SQL*Plus. Record the exact RU, edition, platform, database, and container. The instructor provisions CREATE SESSION plus delegated access to SYS.V_$SYSTEM_PARAMETER and SYS.V_$PARAMETER in the selected container. COURSE_DBA is an identity, not an assumed role grant. Root comparison requires an appropriately privileged common observer.

Choose one of the three parameters and fill in this sheet from actual results:

Proposal field	Evidence or decision to record
Parameter / reason	Exact name and the requirement motivating review
Reporting context	Database, instance, account, container, and sample time
Current values	Exact PDB system, root system, and session values
Original configuration	Inherited, local, or unresolved; persistent setting versus live override; supporting records
Modification support	Actual PDB, system, and session flags, plus parameter-specific conditions
Proposed value	Requirement-based candidate; leave pending if no requirement exists
Intended scope	Exact container set, CONTAINER choice, and persistence scope
Activation	Immediate, new-session, or scheduled reopening and restart checks
Verification	Re-query the system value, test the relevant session or workload, and verify persistence when applicable
Exact restoration	Captured original value, quoting and null handling, original local or inherited policy, and activation steps
Decision	Accept for further review, reject an unsupported scope, or hold for missing evidence

Do not execute a change. Test your reasoning with an additional instructor-provided parameter whose observed ISPDB_MODIFIABLE is FALSE: reject its local-change proposal before drafting SQL. Pass when the sheet distinguishes the session and system values, identifies scope, timing, and persistence, preserves unresolved origin evidence, and gives an exact restoration plan. Read-only queries require no cleanup. Close only your exercise connections.

Design-only rename exercise

Prepare this workflow on paper unless the instructor separately supplies a disposable LABPDB_RENAME, a recovery plan, and an isolated window. Keep the shared LABPDB outside the rename exercise. The following SQL is a design example. It has not been executed.

Record the old complete global name, PDB identity, open mode and restriction, saved-state policy, all service definitions and their manager, active client pools, database links, jobs, scripts, and monitoring references. Verify that the proposed name is unused throughout the CDB. Require a tested recovery or reset point, available administrator access after the rename, an agreed session drain and transaction disposition, the outage duration, and clear acceptance and reversal criteria. Closing immediately can disconnect work and roll back transactions. Renaming is persistent DDL and needs a reversal procedure.

For this paper example only, assume the old global name is LABPDB_RENAME.EXAMPLE.TEST, the new name is LABPDB_RENAMED.EXAMPLE.TEST, and the original state is READ WRITE, unrestricted. Replace these assumptions with the recorded lab state before any separately authorized execution.

The root administrator needs the applicable open and close administrative privilege, exercised at connection. The rename session needs ALTER DATABASE and RESTRICTED SESSION in the target. A delegated common account also needs the applicable SET CONTAINER access. RAC is outside this example. Oracle's RAC rename workflow requires the target open on the current instance only. See ALTER PLUGGABLE DATABASE prerequisites.

Capture identity and service references while connected to the disposable PDB with the necessary dictionary access:

SHOW CON_NAME
SELECT global_name FROM global_name;
SELECT name, network_name, pdb FROM dba_services;

GLOBAL_NAME identifies the current database's complete global name. NETWORK_NAME is the client-facing service name. PDB records the associated container. Keep the actual rows rather than guessing service names. See GLOBAL_NAME and service fields.

The proposed sequence is:

-- Root administrative connection, after the isolated-target checks:
SHOW CON_NAME
ALTER PLUGGABLE DATABASE labpdb_rename CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE labpdb_rename OPEN READ WRITE RESTRICTED;

-- Common rename connection, with the required target privileges:
ALTER SESSION SET CONTAINER=labpdb_rename;
SHOW CON_NAME
ALTER PLUGGABLE DATABASE
  RENAME GLOBAL_NAME TO labpdb_renamed.example.test;

-- Verified root administrative connection:
SHOW CON_NAME
ALTER PLUGGABLE DATABASE labpdb_renamed CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE labpdb_renamed OPEN READ WRITE;

The global-name change determines the PDB name from the portion before its first period. Oracle renames the default service and documents updating service PDB properties. Close and reopen READ WRITE completes integration. Verify the actual custom-service associations and the service manager's configuration, then test reconnects using each intended client and service path. Review any client alias, pool, database link, job, or script containing the old identity. Such external references require their own planned updates. Keep application services' intended names and properties with their owners. See Documented PDB rename workflow.

The paper reversal for the stated old name is:

-- Verified root administrative connection during the reversal window:
ALTER PLUGGABLE DATABASE labpdb_renamed CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE labpdb_renamed OPEN READ WRITE RESTRICTED;

-- Verified common rename connection:
ALTER SESSION SET CONTAINER=labpdb_renamed;
SHOW CON_NAME
ALTER PLUGGABLE DATABASE
  RENAME GLOBAL_NAME TO labpdb_rename.example.test;

-- Verified root administrative connection:
ALTER PLUGGABLE DATABASE labpdb_rename CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE labpdb_rename OPEN READ WRITE;

Restore the captured service-manager configuration and any changed client or job references. Re-query the global name, container identity, and services. Verify actual listener registration and reconnects, and restore the recorded mode, restriction, and saved-state policy if they differ from this example. Preserve only the intended lab target. A name reversal does not restore application data changed during a test. Use the tested recovery procedure if broader restoration is required. Design-only work needs no cleanup. A later executed exercise is complete only when the original identity, services, and access are verified restored.

Proxy endpoint reminder

HOST and PORT in the proxy-reference workflow identify the referenced listener endpoint. Ordinary PDB services use the CDB instance's shared listener infrastructure. Changing reference metadata configures proxy access. Creating or configuring a listener process is a separate operation. No proxy or listener change is performed here. See Proxy PDB listener settings.

Recap

Confirm the container, read eligibility, timing, and persistence, establish the value's origin and restoration policy, trace a rename through services, clients, and jobs, and validate identity and connections after reopening.

Quiz

1. A proposed local parameter has ISPDB_MODIFIABLE=FALSE. What should the change sheet say?

Run the PDB change with a larger value. Reject the proposed independent PDB setting and review supported scope. Rename the parameter. Add CONTAINER=ALL from the PDB.

2. What does ISSYS_MODIFIABLE=DEFERRED tell you?

All existing sessions immediately acquire the new value. The parameter is always read-only. A supported deferred system change applies to subsequent sessions. The SPFILE is automatically deleted.

3. Root and PDB values match, and ISDEFAULT=TRUE is reported. How should you establish origin?

Declare inheritance confirmed solely from those fields. Assume the session owns every system setting. Force a root change to see what happens. Compare contexts and review local configuration and change evidence, retaining unresolved status if needed.

4. Why does the documented rename workflow close and reopen READ WRITE afterward?

To complete integration of the changed PDB service identity. To delete all application services. To reverse the rename automatically. To update every external client and job automatically.

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

Yes, each metadata update starts a new process. Yes, every PDB gets a private network stack. No. It describes the proxy's referenced endpoint, while ordinary services share instance listener infrastructure. No. Oracle supports no listener connections to PDB services.

The commands and names are documentation-based teaching examples. No database connection, parameter change, rename, or listener operation was executed in preparing these notes. Use actual designated-lab results for your evidence, and leave unavailable root or configuration evidence explicitly pending.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro