oracle 应用程序容器

创建应用程序容器
CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION
CONTAINER ADMIN USER app_admin IDENTIFIED BY aabb123;
ALTER PLUGGABLE DATABASE appcon1 OPEN;


连接 创建可插入数据库
ALTER SESSION SET container = appcon1;
CREATE PLUGGABLE DATABASE apppdb1
ADMIN USER pdb_admin IDENTIFIED BY aabb123;
ALTER PLUGGABLE DATABASE apppdb1 OPEN;



应用程序同步
ALTER SESSION SET container = apppdb1;
ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

删除PDB
ALTER SESSION SET container = appcon1;
ALTER PLUGGABLE DATABASE apppdb1 CLOSE;
DROP PLUGGABLE DATABASE apppdb1 INCLUDING DATAFILES;


启动新应用程序的安装，并为其提供一个字符串以标识该应用程序的版本
ALTER SESSION SET container = appcon1;
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN INSTALL ‘1.0’;


创建表空间和用户
show CON_NAME
CREATE TABLESPACE ref_app_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE USER lihao IDENTIFIED BY aabb123
DEFAULT TABLESPACE ref_app_ts
QUOTA UNLIMITED ON ref_app_ts
CONTAINER=ALL;


创建表，插入数据
GRANT CREATE SESSION, CREATE TABLE TO lihao;

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

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

结束安装
ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL;

SELECT app_name,
app_version,
app_status
FROM dba_applications
WHERE app_name = ‘REF_APP’;
DBA_APPLICATIONS 提供有关当前应用程序容器中的应用程序的信息。
APP_NAME：应用名称
app_version：应用程序版本
app_status：申请状态

同步与应用程序根容器关联的应用程序PDB
ALTER SESSION SET container = apppdb1;
SHOW CON_NAME

检查是否存在应用程序对象
DESC lihao.reference_data;

将应用程序与应用程序根目录同步，在查看
DESC lihao.reference_data;
ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;

ALTER SESSION SET container = appcon1;
SELECT c.name,
aps.con_uid,
aps.app_name,
aps.app_version,
aps.app_status
FROM dba_app_pdb_status aps
JOIN v$containers c ON c.con_uid = aps.con_uid
WHERE aps.app_name = ‘REF_APP’;

DBA_APP_PDB_STATUS：提供有关当前应用程序容器中所有应用程序PDB中的应用程序的信息。在应用程序根目录中查询时，它将提供此信息。
V$CONTAINERS 显示有关PDB和与当前实例关联的根的信息。
CON_UID：与PDB关联的唯一标识符

升级应用程序

连接到应用程序根容器
ALTER SESSION SET container = appcon1;

ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE ‘1.0’ TO ‘1.1’;

ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE ‘1.0’ TO ‘1.1’;

增加列
ALTER TABLE lihao.reference_data ADD (
created_date DATE DEFAULT SYSDATE
);

CREATE OR REPLACE FUNCTION lihao.get_ref_desc (p_id IN reference_data.id%TYPE)
RETURN reference_data.description%TYPE
AS
l_desc reference_data.description%TYPE;
BEGIN
SELECT description
INTO l_desc
FROM reference_data
WHERE id = p_id;

RETURN l_desc;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/

GRANT EXECUTE ON lihao.get_ref_desc TO PUBLIC;

结束升级
ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;

进行同步


ALTER SESSION SET container = apppdb1;
SHOW CON_NAME
DESC lihao.reference_data;

ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;
DESC lihao.reference_data;



SELECT lihao.get_ref_desc(1) FROM dual;


卸载应用程序
ALTER SESSION SET container = appcon1;
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UNINSTALL;
DROP USER lihao CASCADE;
DROP TABLESPACE ref_app_ts INCLUDING CONTENTS AND DATAFILES;
ALTER PLUGGABLE DATABASE APPLICATION ref_app END UNINSTALL;


ALTER SESSION SET container = apppdb1;
SHOW CON_NAME
DESC lihao.reference_data;

ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;
DESC lihao.reference_data;