编者按：
本文作者系大连健哥、 POSTGRESQL、ORACLE 数据库资深从业人员、IT 技术的深度爱好者。相信科学改变人类、技术创造未来。个人主页：https://www.cnblogs.com/gaojian/，经其本人授权发布。
【免责声明】本号文章仅代表个人观点，与任何公司无关。
Oracle数据库工程师手记：探究 Oracle PDB Application Container (一)
Oracle数据库工程师手记：探究 Oracle PDB Application Container (二)
继续来研究 application container 中的 application 升级问题。
首先，生成 application container 和 application pdb ，以及 application：
代码语言：javascript复制alter system set db_create_file_dest='/refresh/home/cpdest';
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 upgrade:
代码语言：javascript复制ALTER SESSION SET container = appcon1;
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.0' TO '1.1';
create table tab001(id integer, val varchar2(3));
ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;可以，看到生成了一个特殊的 PDB: F139230267_3_1
代码语言：javascript复制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 PDB193 MOUNTED
7 APPCON1 READ WRITE NO
9 APPPDB1 READ WRITE NO
11 F139230267_3_1 READ WRITE NO
SQL>第二次的 application upgrade:
代码语言：javascript复制ALTER SESSION SET container = appcon1;
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.1' TO '1.2';
create table tab002(id integer, val varchar2(3));
ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;又生成了 一个 特殊PDB: F139230267_3_2
代码语言：javascript复制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 PDB193 MOUNTED
6 F139230267_3_2 READ WRITE NO
7 APPCON1 READ WRITE NO
9 APPPDB1 READ WRITE NO
11 F139230267_3_1 READ WRITE NO
SQL>第三次的 application upgrade:
代码语言：javascript复制ALTER SESSION SET container = appcon1;
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.2' TO '1.3';
create table tab003(id integer, val varchar2(3));
ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;再次生成特殊的 PDB:
代码语言：javascript复制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 PDB193 MOUNTED
5 F139230267_3_3 READ WRITE NO
6 F139230267_3_2 READ WRITE NO
7 APPCON1 READ WRITE NO
9 APPPDB1 READ WRITE NO
11 F139230267_3_1 READ WRITE NO
SQL>每一次升级 application ，都生成一个新PDB，每次大约 0.7 GB， 对空间的浪费太大了。
代码语言：javascript复制SQL> select sum(bytes)/1024/1024/1024 from v$datafile where con_id=11;
SUM(BYTES)/1024/1024/1024
-------------------------
.684570313
SQL> select sum(bytes)/1024/1024/1024 from v$datafile where con_id=6;
SUM(BYTES)/1024/1024/1024
-------------------------
.694335938
SQL>
SQL> select sum(bytes)/1024/1024/1024 from v$datafile where con_id=5;
SUM(BYTES)/1024/1024/1024
-------------------------
.694335938
SQL>如何解决？
