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

上一回:  [Oracle 工程师手记]探究 Oracle PDB Application Container (四)

在使用 Application Container 时,当我们定义Application 中的一个共通对象为 extended data 的时候,Application 中的共通数据,和 Application PDB 中的独立数据,会一起出现在查询结果中,导致一些特殊的问题(比如数据重复)。

下面是测试的过程,首先,构造测试用的 Application container 、Application、Application PDB。

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;

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


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

CREATE TABLESPACE app_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE USER app_user IDENTIFIED BY app_user
DEFAULT TABLESPACE app_ts
QUOTA UNLIMITED ON app_ts
CONTAINER=ALL;
GRANT CREATE SESSION, CREATE TABLE TO app_user;
CREATE TABLE app_user.reference_data SHARING= EXTENDED DATA (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT t1_pk PRIMARY KEY (id)
);

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

COMMIT;

ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL;D

 

这里,我定义 table app_user.reference_data 时,使用了 SHARING= EXTENDED DATA。我给了它5条数据。我先试试看能否插入同样ID(例如:ID=5)的数据:

SQL>  alter session set container=apppdb1;

Session altered.

SQL>
SQL> ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

Pluggable database altered.

SQL> select * from app_user.reference_data;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description of 1
         2 Description of 2
         3 Description of 3
         4 Description of 4
         5 Description of 5


SQL> insert into app_user.reference_data(id,description) values(5,'Desp5');
insert into app_user.reference_data(id,description) values(5,'Desp5')
*
ERROR at line 1:
ORA-00001: unique constraint (APP_USER.T1_PK) violated


SQL>

 还不错,因为 Application 中的这个表,有主键约束,在已经有 ID=5 的数据的同时,我在 Appcation PDB 中,是无法再插入 ID =5 的数据的。

 

但是,如果我在 Application PDB 中插入 ID=6 的新数据,然后回到 Application 进行升级,在 Application 中,也插入 ID=6 的数据,会如何呢?

SQL>  alter session set container=apppdb1;

Session altered.

SQL>
SQL> ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

SQL> insert into app_user.reference_data(id, description) values(6,'Desp6');

1 row created.

SQL> commit;

Commit complete.


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> insert into app_user.reference_data(id, description) values(6,'Descrption 6');

1 row created.

SQL> commit;

Commit complete.

SQL> ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;

Pluggable database altered.

SQL>

 

可以看到,在Application 的升级过程里,插入同样 ID=6 的数据,成功了,Application 无视了 Application PDB 中的内容。

那么,我再回到 Application PDB中,会看到怎样的景象呢?

SQL> alter session set container=apppdb1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

Pluggable database altered.

SQL> select * from app_user.reference_data;

       ID DESCRIPTION
---------- --------------------------------------------------
        1 Description of 1
        2 Description of 2
        3 Description of 3
        4 Description of 4
        5 Description of 5
        6 Descrption 6
        6 Desp6

7 rows selected.

SQL> 

可以看到, 在 Application PDB 中,看到了ID=6 的重复数据。看来,使用 Application container、Application 时,还是要从业务层面制定好规则,确定什么样的数据可以在 Application 中共享。

posted @ 2021-04-10 09:51  健哥的数据花园  阅读(62)  评论(0编辑  收藏  举报