[Oracle 工程师手记]探究 Oracle PDB Application Container (二)

Application Container 中 application 的升级:

需要执行:

ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.0' TO '1.1';

对数据的增减、对结构的改动语句等

ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;

验证过程： 如同   [Oracle 工程师手记]探究 Oracle PDB Application Container (一)  的那样，

先来做出 application container 和 application pdb ，以及 application:

alter system set db_create_file_dest='/refresh/home/';
CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY Password1;

ALTER PLUGGABLE DATABASE appcon1 OPEN;
ALTER SESSION SET container = appcon1;

CREATE PLUGGABLE DATABASE apppdb1 ADMIN USER pdb_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE apppdb1 OPEN;


ALTER SESSION SET container = appcon1;
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN INSTALL '1.0';


CREATE TABLESPACE ref_app_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER ref_app_user IDENTIFIED BY ref_app_user
  DEFAULT TABLESPACE ref_app_ts
  QUOTA UNLIMITED ON ref_app_ts
  CONTAINER=ALL;

GRANT CREATE SESSION, CREATE TABLE TO ref_app_user;

CREATE TABLE ref_app_user.reference_data SHARING=DATA (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

INSERT INTO ref_app_user.reference_data
SELECT level,
       'Description of ' || level
FROM   dual
CONNECT by level <= 5;
COMMIT;


ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL;

 然后执行 application 的升级：

ALTER SESSION SET container = appcon1;

ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.0' TO '1.1';

alter table ref_app_user.reference_data drop column description;

ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;

 
回到 cdb$root;

可以发现: 除了这几个PDB 之外，还生成了一个特殊的 PDB:

SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 APPCON1                        READ WRITE NO
         5 APPPDB1                        READ WRITE NO
         6 F289887660_3_1                 READ ONLY  NO
SQL>

 
如果container 是 appcon1 ，则看不到这个 F289887660_3_1, 在 cdb$root 时，可以看到。

这个PDB 无法直接删除，可以这样删除:

先删除 APPPDB1， 再删除 APPCON1：

alter pluggable database appcon1 close;

drop pluggable database apppdb1 including datafiles;

drop pluggable database appcon1 including datafiles;

这是，可以看到 F289887660_3_1 也随着 application container 一起消失了。


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
SQL>

 补充一点， 这个特殊的 PDB 是何时生成的呢？

SQL> ALTER SESSION SET container = appcon1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.0' TO '1.1';

Pluggable database altered.

SQL>

 
从另外的一个窗口，执行 show pdbs, 就已经可以发现有一个特殊的 PDB (F103021535_3_1)被生成。
在 19c 也是这样的。

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB193                         MOUNTED
         4 APPCON1                        READ WRITE NO
         5 APPPDB1                        READ WRITE NO
         7 F103021535_3_1                 READ WRITE NO
SQL>

也就是 BEGIN UPGRADE 语句，就有这个作用。