探究 Oracle PDB Application Container (三)
编者按：
本文作者系大连健哥、 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：
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_userDEFAULT TABLESPACE ref_app_tsQUOTA UNLIMITED ON ref_app_tsCONTAINER=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_dataSELECT level,'Description of ' || levelFROM dualCONNECT by level <= 5;COMMIT;ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL;
第一次的 application upgrade:
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
alter session set container=cdb$root;Session altered.show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB193 MOUNTED7 APPCON1 READ WRITE NO9 APPPDB1 READ WRITE NO11 F139230267_3_1 READ WRITE NOSQL>
第二次的 application upgrade:
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
alter session set container=cdb$root;Session altered.show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB193 MOUNTED6 F139230267_3_2 READ WRITE NO7 APPCON1 READ WRITE NO9 APPPDB1 READ WRITE NO11 F139230267_3_1 READ WRITE NOSQL>
第三次的 application upgrade:
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:
alter session set container=cdb$root;Session altered.show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB193 MOUNTED5 F139230267_3_3 READ WRITE NO6 F139230267_3_2 READ WRITE NO7 APPCON1 READ WRITE NO9 APPPDB1 READ WRITE NO11 F139230267_3_1 READ WRITE NOSQL>
每一次升级 application ，都生成一个新PDB，每次大约 0.7 GB， 对空间的浪费太大了。
SQL> select sum(bytes)/1024/1024/1024 from v$datafile where con_id=11;SUM(BYTES)/1024/1024/1024-------------------------.684570313SQL> select sum(bytes)/1024/1024/1024 from v$datafile where con_id=6;SUM(BYTES)/1024/1024/1024-------------------------.694335938SQL>SQL> select sum(bytes)/1024/1024/1024 from v$datafile where con_id=5;SUM(BYTES)/1024/1024/1024-------------------------.694335938SQL>
如何解决？
后续文章更加精彩，欢迎关注本公众号或访问【阅读原文】。
——End——
专注于技术不限于技术！
用碎片化的时间，一点一滴地提高数据库技术和个人能力。
欢迎关注！
备份恢复系列：
原文作者：SQL和数据库技术；如有侵权，请联系管理员删除。
