Oracle12c新特性大全（三）CDB的管理

管理CDB时，通常需要使用sys用户连接根容器数据库，在操作方式上与非CDB数据库同样。

当前连接容器的信息

1.

23:03:31 sys. woqu>show con_id con_name user

CON_ID

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

1

CON_NAME

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

CDB$ROOT

USER is "SYS"

23:03:33 sys. woqu>

2.

COL CON_ID FOR A10

COL CUR_CONTAINER FOR A25

COL CUR_USER FOR A25

SELECT

    sys_context('USERENV','CON_ID') con_id,

    sys_context('USERENV','CON_NAME') cur_container,

    sys_context('USERENV','session_user') cur_user

FROM dual;

CON_ID     CUR_CONTAINER             CUR_USER

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

1          CDB$ROOT                  SYS

启动和停止CDB

只有公用用户才可以连接根容器，并且启动和停止CDB，停止和启动与普通数据库的处理过程相同，当公用用户连接PDB时，无法启动和停止CDB。

sys. ora12c>startup

默认情况下启动CDB时不会自动启动PDBs，我们可以使用手工的方式启动PDB：

ALTER PLUGGABLE DATABASE [pdb_name] OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;      --打开所有PDB

sys. ora12c>shutdown immediate

在关闭CDB时，CDB中正在运行PDSs会也会关闭，连接CDB与PDBs的session均会断开。

查看CDB环境中表空间使用情况

with generator0 as

 (select cf.con_id, cf.tablespace_name, sum(cf.bytes) / 1024 / 1024 frm

    from cdb_free_space cf

   group by cf.con_id, cf.tablespace_name),

generator1 as

 (select cd.con_id, cd.tablespace_name, sum(cd.bytes) / 1024 / 1024 usm

    from cdb_data_files cd

   group by cd.con_id, cd.tablespace_name),

generator2 as(

select g0.con_id, c.name con_name, g0.tablespace_name, g0.frm, g1.usm

  from generator0 g0, generator1 g1, v$containers c

 where g0.con_id = g1.con_id

   and g0.tablespace_name = g1.tablespace_name

   and c.con_id = g1.con_id

union

select c.con_id,

       c.name,

       ct.tablespace_name,

       null,

       sum(ct.bytes) / 1024 / 1024

  from v$containers c, cdb_temp_files ct

 where c.con_id = ct.con_id

 group by c.con_id, c.name, ct.tablespace_name)

select con_id,

case when con_name = LAG(con_name, 1) OVER(PARTITION BY con_name ORDER BY tablespace_name) THEN null ELSE con_name END

con_name, tablespace_name, frm freemb, usm usemb

from generator2

order by con_id;

CON_ID CON_NAME     TABLESPACE_NAME     FREEMB      USEMB

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

     1 CDB$ROOT     SYSAUX                  28        470

     1              SYSTEM              6.5625        810

     1              TEMP                               33

     1              UNDOTBS1            1.9375         60

     1              USERS                    4          5

     3 ORA12CPDB    SYSAUX              18.125        360

     3              SYSTEM                1.25        250

     3              TEMP                               64

     3              UNDOTBS1                33        100

     3              USERS                    4          5

切换容器

使用公用用户连接CDB后可以使用alter session的方式切换不同的容器

alter session set container=pdb1;

alter session set container = cdb$root;

在切换容器时无需运行监听器和密码文件。只要公用用户拥有相关权限就可以切换到另外的容器中。