Administering PDBs with SQL*Plus

使用alter pluggable database管理pdb详见SQL语法

1. Using the ALTER SYSTEM Statement to Modify a PDB

除下面alter system可在pdb中执行，其它alter system需要在root使用comm user执行，它将更改所有pdbs:

ALTER SYSTEM FLUSH SHARED_POOL

ALTER SYSTEM FLUSH BUFFER_CACHE

ALTER SYSTEM FLUSH FLASH_CACHE

ALTER SYSTEM ENABLE RESTRICTED SESSION

ALTER SYSTEM DISABLE RESTRICTED SESSION

ALTER SYSTEM SET USE_STORED_OUTLINES

ALTER SYSTEM SUSPEND

ALTER SYSTEM RESUME

ALTER SYSTEM CHECKPOINT

ALTER SYSTEM CHECK DATAFILES

ALTER SYSTEM REGISTER

ALTER SYSTEM KILL SESSION

ALTER SYSTEM DISCONNECT SESSION

ALTER SYSTEM SET initialization_parameter (for a subset of initialization parameters)

PDB中只能更改部分参数，PDB中未设置的参数使用CDB中参数设置值

查看pdb可更改参数：

SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE='TRUE' ORDER BY NAME;

PDB设置参数也可使用SCOPE=MEMORY|SPFILE|BOTH，但不能使用pfile

When a PDB is unplugged from a CDB, the values of the initialization parameters that were specified for the PDB with SCOPE=BOTH or SCOPE=SPFILE are added to the PDB's XML metadata file. These values are restored for the PDB when it is plugged in to a CDB.

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET STATISTICS_LEVEL = ALL SCOPE = MEMORY;

sessions参数：

The setting for the SESSIONS initialization parameter limits the total number of sessions available in a CDB, including the sessions connected to PDBs. If the limit is reached for the CDB, then users cannot connect to PDBs. To ensure that one PDB does not use too many sessions, you can limit the number of sessions available to a PDB by setting the SESSIONS initialization parameter in the PDB.

1. Connecting to a Container

ALTER SESSION SET CONTAINER = container_name [SERVICE = service_name]

其中container_name可以为CDB$ROOT, PDB$SEED以及PDB name

不设置service选项则默认使用默认的service(即pdb同名的默认service), 如果pdb没有启用的service(包括默认service)是无法切换的，这时可以先关闭pdb再切换，然后启用

By default, when you switch to a container, the session uses the default service for the container. However, the default PDB service does not support all of the service attributes and features such as service metrics, Fast Application Notification (FAN), load balancing, Resource Manager, Transaction Guard, Application Continuity, and so on. It is best practice to use a non-default service for the container by specifying SERVICE = service_name, where service_name is the name of the service.

使用时指定service为non-default server常用于应用端的universal connection pools(UCPs)中，即应用使用此语句来切换pdb及service，关于应用的UCP有时间可以了解下

With this new capability, connection pools can switch the service, and, when needed the PDB, on a connection when a connection is borrowed from the pool. Starting with Oracle Database 12c Release 2 (12.2.0.1), connection pools support more than one database service with universal connection pools (UCPs). It can also be used standalone.

When switching to a service, applications are able to consolidate to a CDB, while keeping the database services identified, prioritized, measured, and highly available.

Switching to a non-default service provides the following benefits:

1. It preserves the service attributes and features.
2. It eliminates too many connection pools with too many connections serving these tenants.
3. It allows applications to use more database services for workload control without consuming too many connection pools. Customers are able to identify and prioritize workloads using services without over sizing the database connections.

The following are considerations for using the ALTER SESSION SET CONTAINER statement:

1. After the statement completes successfully, the current schema of the session is set to the schema owned by the common user in the specified container.
2. After the statement completes successfully, the security context is reset to that of the schema owned by the common user in the specified container.
3. After the statement completes successfully, login triggers for the specified container do not fire. If you require a trigger, then you can define a before or after SET CONTAINER trigger in a PDB to fire before or after the ALTER SESSION SETCONTAINER statement is executed.
4. Package states are not shared across containers.
5. A transaction cannot span multiple containers. If you start a transaction and use ALTER SESSION SET CONTAINER to switch to a different container, then you cannot issue DML, DDL, COMMIT, or ROLLBACK statements until you switch back to the container in which you started the transaction.
6. If you open a cursor and use ALTER SESSION SET CONTAINER to switch to different container, then you cannot fetch data from that cursor until you switch back to the container in which the cursor was opened.

The following prerequisites must be met to use the ALTER SESSION SET CONTAINER statement:

1. The current user must be a common user. The initial connection must be made using the SQL*Plus CONNECT command.
2. When altering a session to switch to a PDB as a common user that was not supplied with Oracle Database, the current user must be granted the SET CONTAINER privilege commonly or must be granted this privilege locally in the PDB.

Before issuing an ALTER SESSION SET CONTAINER statement with the SERVICE clause, the following prerequisites must be met:

1. The service switched to must be active. You cannot switch to a service that is not running.

COL NAME FORMAT A30

COL CON_NAME FORMAT A20

SELECT NAME,CON_NAME, CON_ID FROM V$ACTIVE_SERVICES;

If the service is not running, then use the SRVCTL utility or the DBMS_SERVICE package to start the service.

1. When switching between services, the service attributes of the service being switched from and the service being switched to must match. For example, the services switched from and to must all have TAF, or must all use Application Continuity, or must all have drain_timeout set.