Administering a CDB with SQL*Plus

（一）Administrative Tasks Common to CDBs and Non-CDBs

因为以下资源在CDB中是共享的，所以在CDB配置会影响所有pdbs，管理方法同non-cdb，详见alter database语法

1. Managing processes
2. Managing memory
3. Monitoring errors and alerts：A CDB has one alert log for the entire CDB
4. Managing diagnostic data
5. Managing control files
6. Managing the online redo log and the archived redo log files
7. Managing undo: 如果是share undo mode则只能cdb中更改

除了share undo mode，其它表空间在cdb与pdbs是独立配置的

（二）Managing the CDB Undo Mode

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE  PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

If the query returns TRUE for the PROPERTY_VALUE, then the CDB is in local undo mode. Otherwise, the CDB is in shared undo mode.

Local undo mode provides increased isolation for each container and improves the efficiency of some operations, such as unplugging the container or performing point-in-time recovery on the container. In addition, local undo mode is required for some operations to be supported, such as relocating a PDB or cloning a PDB that is in open read/write mode.

创建CDB后仍可以更改undo mode

Oracle Database automatically creates an undo tablespace in any container in the CDB that does not have one. If a PDB without an undo tablespace is cloned, relocated, or plugged into a CDB that is configured to use local undo mode, then Oracle Database automatically creates an undo tablespace for the PDB the first time it is opened.

1. local undo mode

Any user who has the appropriate privileges for the current container can create an undo tablespace for the container.

Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views in every container in the CDB.

1. Shared Undo Mode

Only a common user who has the appropriate privileges and whose current container is the CDB root can create an undo tablespace.

When the current container is not the CDB root, an attempt to create an undo tablespace fails and returns an error.

Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views when the current container is the CDB root. Undo tablespaces are visible only in dynamic performance views when the current container is a PDB, an application root, or an application PDB.

When you change the undo mode of a CDB, containers in the CDB cannot flash back to a time or SCN that is prior to the change.

1. Share-> local mode:

sqlplus / as sysdba

STARTUP UPGRADE

SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

ALTER DATABASE LOCAL UNDO ON;

Shutdown immediate

Startup

Optional: Manually create an undo tablespace in the CDB seed.

While Oracle Database creates an undo tablespace in the CDB seed automatically in local undo mode, you might want to control the size and configuration of the undo tablespace by creating an undo tablespace manually. To ensure the PDBs created from the CDB seed use the manually-created undo tablespace and not the automatically-created undo tablespace, you must set the UNDO_TABLESPACE initialization parameter to the manually-created undo tablespace, or drop the automatically-created undo tablespace.

上面说的undo_tablespace应该是下在第5步在pdb$seed中设置

1. In SQL*Plus, ensure that the current container is the root.
2. Place the CDB seed in open read/write mode:
3. ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;
4. Switch container to the CDB seed:

ALTER SESSION SET CONTAINER=PDB$SEED;

1. Create an undo tablespace in the CDB seed. For example:

CREATE UNDO TABLESPACE seedundots1

   DATAFILE 'seedundotbs_1a.dbf'

   SIZE 10M AUTOEXTEND ON

   RETENTION GUARANTEE;

1. Switch container to the root:

ALTER SESSION SET CONTAINER=CDB$ROOT;

1. Place the CDB seed in open read-only mode:

ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE;

1. local-> share mode:

过程相同，只是命令为ALTER DATABASE LOCAL UNDO OFF;

When it is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces.

（四）DML Using Containers clause

在application root可以通过应用来创建共享对象，在application pdbs同步应用后，就可以使用共享对象；cdb$root不能创建共享对象，即cdb$root中对象与它的pdbs中对象没关系。

而这个contianers子句是用在cdb$root或application root中，用于在cdb$root 或application root中对其下的pdbs对象直接操作的，这个对象可以是共享对象也可以不是。特殊的在application root下，可以通过containers()对metadata以及extended data共享对象操作。

限制：

1. 如果表中有以下列则在containers clause中不会显示数据：

The following user-defined types: object types, varrays, REFs, and nested tables

The following Oracle-supplied types: ANYTYPE, ANYDATASET, URI types, SDO_TOPO_GEOMETRY, SDO_GEORASTER, and Expression

1. 如果pdb处于关闭状态或open restricted，则此pdb数据会被containers()忽略

When a container is opened in restricted mode, it is ignored by the CONTAINERS clause.

1. 如果containers()操作在pdb中有错误信息，则不会返回此pdb数据以及相应错误信息

When the CONTAINERS clause is used and an error is returned by a container, the query does not return results from the container that raised the error, and the error is not returned. For example, you cannot select a BFILE column from a remote table into a local variable. If a query that does this uses the CONTAINERS clause and includes local and remote containers, then the query returns results for the local containers, but not the remote containers, and no error is returned.

1. 在cdb$root/application root中要有操作的schema.table,如cdb中的pdb1下的scott.t1表，但cdb$root的scott下没有t1表，则使用select * from containers(scott.t1)时会报错表不存在
2. containers子句需要开启TCP协议的监听

DML statements using the CONTAINERS clause require that the database listener is configured using TCP (instead of IPC) and that the PORT and HOST values are specified for each target PDB using the PORT and HOST clauses, respectively.

否则会报错：ORA-12541: TNS:no listener

它是通过监听访问的，相当于又开启一个会话，所以不能看到未提交的事务，示例中有说明

1. Insert as select以及多表插入不支持使用containers()
2. 没法对sys下面对象使用containers()进行DML，但可以查询
3. 可以对Oracle系统表或视图使用，如select * from containers(v$instance);
4. CONTAINERS_DEFAULT_TARGET

如果没通过con_id进行过滤，则会对数据库属性CONTAINERS_DEFAULT_TARGET设置值的容器上操作，此属性只能设置为一个容器，如果为none，默认为当前cdb$root或application root, 并非全部container.

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'CONTAINERS_DEFAULT_TARGET';

ALTER DATABASE CONTAINERS DEFAULT TARGET = (app1);

ALTER DATABASE CONTAINERS DEFAULT TARGET = none;   

如果是application root要用此命令：

ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (app1);

此时操作：

select * from containers(system.t1);        --仍然是全部容器均显示

Update containers(system.t1) set id=2222; --没有where con_id即对默认容器的system.t1更改

Insert into containers(system.t1) values(111);   

ORA-00947: not enough values     --插入时仍要指定con_id值

1. Application root可以对共享对象开启CONTAINERS_DEFAULT属性，表示默认使用containers()子句，即开启后select * from system.share_tab1 where con_id=5 等同于select * from containers(system.share_tab1) where con_id=5. 同样在不使用where con_id时使用CONTAINERS_DEFAULT_TARGET属性设置值。

The CONTAINERS_DEFAULT column in the DBA_TABLES and DBA_VIEWS views shows whether the database object is enabled for the CONTAINERS clause by default.

注更改必须要在begin/end statement中执行

ALTER PLUGGABLE DATABASE APPLICATION app7 BEGIN INSTALL '1.0';

create table app7_t1 sharing=metadata (id number);

ALTER TABLE sales.customers ENABLE CONTAINERS_DEFAULT;

ALTER PLUGGABLE DATABASE APPLICATION app7 end install '1.0';

Alter session set container=app1;   --con_id=4

alter pluggable database application all sync;

这时在application root可以直接插入到app1中：

insert into app7_t1(con_id, id) values(4,1111);

commit;

select * from app7_t1;

        ID     CON_ID

---------- ----------

      1111         4

Containers clause使用方法

用containers(schema.table)替代DML中表位置(不指定schema则为当前schema)，并在where条件中使用con_id进行过滤，即可对指定容器中的表进行DML

下面为方法比较均创建的是system.t1表:

Cdb$root: conid=1

create table t1(id number);

insert into t1 values(111);

Commit;

Pdb: pdbnew, conid=3

create table t1(id number);

insert into t1 values(111);

Commit;

Application root: appcon1,conid=4

create table t1(id number);

insert into t1 values(111);

Commit;

Application pdb: app1 ,conid=5

create table t1(id number);

insert into t1 values(111);

Commit;

cdb$root:

select * from containers(system.t1);

        ID     CON_ID

---------- ----------

       111          1

       111          3

       111          4

       111          5

select * from containers(system.t1) where con_id in (4,5);

        ID     CON_ID

---------- ----------

       111          4

       111          5

Update containers(system.t1) set id=2222 where con_id=5;

select * from containers(system.t1);   --还是原来结果

        ID     CON_ID

---------- ----------

       111          1

       111          3

       111          4

       111          5

Commit;

select * from containers(system.t1);

        ID     CON_ID

---------- ----------

       111          1

       111          3

       111          4

      2222          5

insert into containers(system.t1)(id,con_id) values (2222,5); 

Delete from containers(system.t1) where con_id=1;

另外试了下对于sys下对象查询可以但DML不行：

update containers(sys.t1) set id=2222 where con_id=3;

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from PDBNEW

关于CONTAINERS_PARALLEL_DEGREE

The CONTAINERS_PARALLEL_DEGREE initialization parameter can control the degree of parallelism of a query involving the CONTAINERS clause. If the value of CONTAINERS_PARALLEL_DEGREE is lower than 65535 (the default), then the specified value is used.

When the CONTAINERS_PARALLEL_DEGREE initialization parameter is set to the default value (65535), queries that use the CONTAINERS clause are parallel by default. The default degree of parallelism is calculated with the following formula:

max(min(cpu_count,number_of_open_containers),#instances)

In addition, you can pass a DEFAULT_PDB_HINT hint in the CONTAINERS clause. The hint is passed in the query that is run in each container.

You can force the recursive SQL that results from a query that includes the CONTAINERS clause to be parallel by using the DEFAULT_PDB_HINT clause of a CONTAINERS hint or by using automatic degree of parallelism. However, parallel statement queuing is not possible for recursive SQL that results from a query that includes the CONTAINERS clause.