Administering Application Containers with SQL*Plus

Note： application root的comm user只存在于application container中（包括application root, application pdbs, application seed）

（一）Managing Applications in an Application Container

Application表示通过用户连接数据库使用应用相关数据，而CDB中的application核心在于把它使用的连接用户(common user)与数据(common object)共享给多个application pdbs

你可以在应用外创建common user，方便管理application pdbs，但如果是连接应用的用户建议在应用中定义；而common object只能在应用中创建

一个application root可以创建多个不同applicaton，一个共享对象只能对应一个application：

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app BEGIN INSTALL '1.0';

CREATE TABLESPACE saas_sales_tbs DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

CREATE USER saas_sales_adm IDENTIFIED BY ****** CONTAINER=ALL;

GRANT CREATE SESSION, DBA TO saas_sales_adm;

ALTER USER saas_sales_adm DEFAULT TABLESPACE saas_sales_tbs;

CONNECT saas_sales_adm/******@saas_sales_ac

CREATE TABLE saas_sales_adm.sales_mlt sharing=metadata|data|extended data

(YEAR NUMBER(4), REGION VARCHAR2(10), QUARTER VARCHAR2(4));

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app END INSTALL '1.0';

查看对应application的对象:

select a.owner, a.object_name, a.OBJECT_TYPE, b.app_name, a.CREATED_VSNID, a.MODIFIED_VSNID from dba_objects a, dba_applications b where a.CREATED_APPID=b.app_id and b.app_name='APP1';

Note: application的创建及修改，以及pdb中的sync操作均需要dba权限

An application can be installed, upgraded, and patched in an application root. 

When an application is installed, upgraded, or patched, you must issue an ALTER PLUGGABLE DATABASE ... BEGIN statement to start the operation and an ALTER PLUGGABLE DATABASE ... END statement to end the operation. These statements can be issued in the same user session or in different user sessions(下面会说关于module问题).

When application changes are performed in this way, application PDBs can synchronize with the application in the application root to apply the changes.

APP PDB可以使用不同版本的application:

As the application evolves, the application container maintains all of the versions and patch changes you apply. You can also configure the application container so that different application PDBs use different versions of the application.

Note: SQL*Loader is the only supported utility for bulk inserts into tables during application install, upgrade, and patch operations.

Application Module Names

在执行APPLICATION BEGIN与APPLICATION BEGIN期间，与执行会话有相同module的语句都会被捕获，为此在执行此语句时先把当前module单独设置一个值。方便点的方法是直接用sysdba用户操作，这与一般用户的module默认就是不同的

The application module name is set by the DBMS_APPLICATION_INFO.SET_MODULE procedure or the equivalent OCI attribute setting.

The module name is necessary during application maintenance because of other activity that might be occurring in the database. For example, statements issued by background processes should not be captured in the application capture tables. Also, other users might execute statements that are unrelated to the application. A module name check distinguishes what should be captured from what should not be captured. Only sessions whose module name matches the module name of the session where APPLICATION BEGIN was issued are considered for capture.

Some clauses, such as the SHARING clause, are valid only when issued between an ALTER PLUGGABLE DATABASE ... BEGIN statement and an ALTER PLUGGABLE DATABASE ... END statement.

For these clauses, if the module name for a session does not match, then this session is not included in between the BEGIN and END statements, causing statements that include the clause to fail with ORA-65021 or other errors.

The most common cause for a module name mismatch is the default module name. For example, SQL*Plus sets a default module name when a connection is made to the database. A connection as a SYSDBA user results in one default module name (for example, sqlplus@host1 (TNS V1-V3)), whereas a connection as a non-SYSDBA user results in a different default module name (for example, SQL*Plus). When SYSDBA and non-SYSDBA users are both performing maintenance, you must explicitly set the module name in each session to the same value, and not rely the default settings in SQL*Plus.

SQL> CONNECT / AS SYSDBA

SQL> select module from v$session where audsid = SYS_CONTEXT('USERENV','sessionid');

MODULE

----------------------------------------------------------------

sqlplus@host1 (TNS V1-V3)

SQL> CONNECT dba1

SQL> select module from v$session where audsid = SYS_CONTEXT('USERENV','sessionid');

MODULE

----------------------------------------------------------------

SQL*Plus

1. Installing Applications in an Application Container

Show con_name

ALTER PLUGGABLE DATABASE APPLICATION salesapp BEGIN INSTALL '1.0';

创建comm metadata与comm data

ALTER PLUGGABLE DATABASE APPLICATION salesapp END INSTALL '1.0';

创建后不用sync, 会自动同步到application pdbs

1. Upgrading Applications in an Application Container

升级应用会产生多个版本，每个应用版本对应一个application root版本， 称之为application root clone，application root clone不论application root以何种模式打开都会以open read only方式打开；当application root关闭时application root clone也自动关闭

When an application is upgraded, Oracle Database automatically creates a clone of the application root. During the upgrade operation, application PDBs are pointed to the clone. Applications continue to run during the upgrade operation. Application PDBs can perform DML operations on metadata-linked and extended data-linked tables and views, and application PDBs can query metadata-linked objects, extended data-linked objects, and data-linked objects.

After the upgrade operation is complete, the application root clone remains and continues to support any application PDB that uses the version of the application in the clone. Application PDBs that upgrade are pointed to the upgraded application root. Application PDBs that do not upgrade might continue to use the clone, and application PDBs that are plugged into the application root might also use the same application version as the clone.

When the application root is in any open mode, the application root clone is in read-only mode. When the application root is closed, the application root clone is also closed.

执行update前提：

1. The CDB must be in local undo mode.
2. The application root must be in open read/write mode.

Show con_name

ALTER PLUGGABLE DATABASE APPLICATION salesapp BEGIN UPGRADE '1.0' to '1.1';

Modify comm metadata与comm data

ALTER PLUGGABLE DATABASE APPLICATION salesapp END UPGRADE to '1.1';

创建后不同sync, 会自动同步到application pdbs

Alter session set container=apphr1;

Alter pluggable database application sync;

1. Patching Applications in an Application Container

Patch只是轻量级别更改app, 如果执行像drop table操作会被提示：operation not supported in an application patch错误，这时就要用upgrade来替代patch操作

You patch the application in the application root only, and the application PDBs that synchronize with the application apply the changes.

You must indicate the start of the patch operation with an ALTER PLUGGABLE DATABASE APPLICATION BEGIN PATCH statement and the end of the patch operation with an ALTER PLUGGABLE DATABASE APPLICATION END PATCH statement.

Each patch must be associated with an application name, starting version number, and ending version number, which are specified in the ALTER PLUGGABLE DATABASE APPLICATION statements.

Note: The patch process is restricted to a small set of operations. In general, destructive operations, such as dropping a table, are not allowed in a patch. If you attempt to patch an application, and the operation raises an “operation not supported in an application patch” error, then upgrade the application instead of patching it to make the necessary changes.

The following prerequisites must be met:

1. The current user must have the ALTER PLUGGABLE DATABASE system privilege, and the privilege must be commonly granted in the application root.
2. The application root must be in open read/write mode.

Show con_name

ALTER PLUGGABLE DATABASE APPLICATION salesapp BEGIN PATCH patch_number MINIMUM VERSION 'minimum_application_version_number';

The minimum_application_version_number indicates the minimum application version at which an application installation must be before the patch can be applied to it.

Do something

ALTER PLUGGABLE DATABASE APPLICATION application_name END PATCH patch_number;

这里patch_number可与begin patch中的匹配

Synchronize all of the application PDBs that must patch the application by issuing an ALTER PLUGGABLE DATABASE APPLICATION statement with the SYNC clause.

1. Migrating an Existing Application to an Application Container

这里的场景就是在cdb中我有100个库均为pdb，均被相同应用程序使用，这时想迁移到application root中，先要把其中一个pdb迁移为application root，然后创建应用，把原pdb的user/roles/profiles以及object associate 到应用中，最后分别把这100个pdb迁移为application pdb

4.1 About Migrating an Existing Application to an Application Container

这里是把一般pdb迁移为application root（无法把application pdb迁移为application root）

在pdb迁移到application root时，可以使用dbms_pdb完成下面两件事：

1. 原pdb中的user/roles/profiles会自动变为common,  你可以按需要对它们设置是否为common
2. 原pdb对象仍为非共享对象，你可以创建新应用时，把这些原来的非共享对象转为应用使用的共享对象

操作过程：

1. In the Oracle Database 12c Release 2 (12.2) CDB, create the application root by cloning the existing PDB, relocating the existing PDB, or by unplugging and plugging in the existing PDB.
2. With the application root as the current container, start an application installation operation by issuing an ALTER PLUGGABLE DATABASE ... BEGIN INSTALL statement.
3. Optional: Query the COMMON column in the DBA_USERS, DBA_ROLES, and DBA_PROFILES views to determine which users, roles, and profiles are common.

Run the following procedures in the DBMS_PDB package to associate users, roles. and profiles with the application:

Run the SET_USER_EXPLICIT procedure to set application common users.

Run the SET_ROLE_EXPLICIT procedure to set application common roles.

Run the SET_PROFILE_EXPLICIT procedure to set application common profiles.

If you do not have EXECUTE privilege on the DBMS_PDB package, then you can run these procedures in the DBMS_PDB_ALTER_SHARING package.

1. Optional: With the application root as the current container, query the SHARING column in the DBA_OBJECTS view to determine which database objects are shared.

Run the following procedures in the DBMS_PDB package to associate database objects with the application:

Run the SET_DATA_LINKED procedure to set data-linked application common objects.

Run the SET_METADATA_LINKED procedure to set metadata-linked application common objects.

Run the SET_EXT_DATA_LINKED procedure to set extended data-linked application common objects.

If you do not have EXECUTE privilege on the DBMS_PDB package, then you can run these procedures in the DBMS_PDB_ALTER_SHARING package.

1. End the application installation operation by issuing an ALTER PLUGGABLE DATABASE ... END INSTALL statement.
2. Optional: Rerun the queries that you ran previously to ensure that the sharing properties of the database objects are correct and that the common properties of the users, roles, and profiles are correct.
3. Optional: If existing PDBs use the application, then create application PDBs using these existing PDBs.

4.2 Creating an Application PDB Using an Existing PDB

这里是把普通pdb迁移为application pdb

1. In the application root, create the application PDB by cloning the existing PDB, relocating the existing PDB, or by unplugging and plugging in the existing PDB.

Violations will be reported during PDB creation.

1. Connect to or switch to the new PDB as a user with the required privileges.
2. Run the pdb_to_apppdb.sql script in the ORACLE_HOME/rdbms/admin directory.

The script automatically synchronizes the application PDB with the application root.

1. Optional: Query the COMMON column in the DBA_USERS, DBA_ROLES, and DBA_PROFILES views to ensure that the common properties of the users, roles, and profiles are correct.
2. Optional: Query the SHARING column in the DBA_OBJECTS view to ensure that the sharing properties of the database objects are correct.

1. Synchronizing Applications in an Application PDB

When an application is installed, upgraded, patched, or uninstalled in an application root, an application PDB that belongs to the application root is not changed until it is synchronized. Synchronizing an application updates the application in the application PDB to the latest version and patch in the application root.

除了用户自定义的应用，还有一种隐式应用，它是在application root第一次打开时自动创建的，隐式应用名为app$guid, guid为application root的guid

隐式应用不是能过ALTER PLUGGABLE DATABASE BEGIN/END statement来修改，而是common user操作(如create|alter user)时使用container=all子句时隐式修改的，隐式应用没有多版本只有一个当前版本

In addition to user-created applications, application containers can also contain implicitly created applications. An application is created implicitly in an application root when an application common user operation is issued with a CONTAINER=ALL clause outside of ALTER PLUGGABLE DATABASE BEGIN/END statements.

Application common user operations include operations such as creating a common user with a CREATE USER statement or altering a common user with an ALTER USER statement. There are several other application common user operations. An implicit application is named APP$guid, where guid is the GUID of the application root. An implicit application is created when the application root is opened for the first time.

同步需要切到application pdb：

1. 同步一般application

ALTER PLUGGABLE DATABASE APPLICATION application_name SYNC;

ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

1. 同步隐式application

ALTER PLUGGABLE DATABASE APPLICATION APP$guid SYNC;

guid is the GUID of the application root.

ALTER PLUGGABLE DATABASE APPLICATION APP$CON SYNC;

APP$CON is a logical application name that Oracle Database automatically translates to the real name of the implicit application.

1. Synchronizing an Application Root Replica with a Proxy PDB

在application root master下创建引用application root replica的proxy pdb，这样在application root master中对应用的修改，proxy pdb只需要sync即可同步到application root replica

In this configuration, one application container has the master application root. The master application root is where the application is installed, upgraded, and patched. One or more application root replicas are copies of the master application root. Each application root replica will be referenced by a proxy PDB in the master application root.

Whenever a proxy PDB is synchronized with the application changes in the master application root, it propagates those changes to its referenced application root replica. After an application root replica is synchronized with the changes from the master application root, application PDBs that are plugged into the application root replica can synchronize with it to get the changes also.

另外在master application root中使用containers()查询proxy pdb会查看到application root replica及它的application pdbs数据

In addition, when an application root replica is configured and has its own application PDBs, a query that includes the CONTAINERS clause in the master application root can return data from the current application container and from the application container with the application root replica. The query can show results from the application root replica and from any open application PDBs plugged into it.

使用此架构在master application root中的dml尽量在begin/end中操作：

If the data-linked or extended data-linked application common object is part of a configuration that synchronizes an application root replica with a proxy PDB, then DML operations on a data-linked/extended data-linked object in the application root can be done outside of an application action, but the DML operation is not automatically propagated to the application root replication through the proxy PDB. If you want the DML operation to be propagated to the application root replica, then the DML operation on a data-linked/extended data-linked object in the application root must be done within an application installation, upgrade, or patch.

创建过程：

1. 在master application root创建application container
2. 在application root replica 创建application container，可以用以下方法

Create an empty application container using any supported method.

Clone the master application root.

1. In the master application root, create a proxy PDB that references the application root replica you created in the previous step.
2. Open and synchronize the proxy PDB.
3. Optional: In the master application root, modify the application by installing, upgrading, or patching it.
4. Optional: Synchronize the proxy PDB with the application changes in the master application root by running the ALTER PLUGGABLE DATABASE APPLICATION statement with the SYNC clause.

示例：

1) 创建master application root msappcon:

mkdir /u01/app/oracle/oradata/orcl/msappcon

show con_name

CREATE PLUGGABLE DATABASE msappcon AS APPLICATION CONTAINER

  ADMIN USER msappconadm IDENTIFIED BY oracle

  STORAGE (MAXSIZE 2G)

  DEFAULT TABLESPACE sales DATAFILE '/u01/app/oracle/oradata/orcl/msappcon/sales01.dbf' SIZE 250M AUTOEXTEND ON

  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdbseed/', '/u01/app/oracle/oradata/orcl/msappcon/');

ALTER PLUGGABLE DATABASE msappcon OPEN;

2) master application root下安装应用

alter session set container=msappcon;

ALTER PLUGGABLE DATABASE APPLICATION sampleapp BEGIN INSTALL '1.0';

CREATE TABLE apptb SHARING=METADATA  (id  NUMBER(6),  widget_name   VARCHAR2(20));

ALTER PLUGGABLE DATABASE APPLICATION sampleapp END INSTALL '1.0';

3) 创建application root replica

mkdir /u01/app/oracle/oradata/orcl/depappcon

show con_name

CREATE PLUGGABLE DATABASE depappcon AS APPLICATION CONTAINER

  ADMIN USER depappconadm IDENTIFIED BY oracle

  STORAGE (MAXSIZE 2G)

  DEFAULT TABLESPACE sales DATAFILE '/u01/app/oracle/oradata/orcl/depappcon/sales01.dbf' SIZE 250M AUTOEXTEND ON

  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdbseed/', '/u01/app/oracle/oradata/orcl/depappcon/');

ALTER PLUGGABLE DATABASE depappcon OPEN;

设置proxy使用的host与port

ALTER PLUGGABLE DATABASE CONTAINERS HOST='0.0.0.0';

ALTER PLUGGABLE DATABASE CONTAINERS PORT=1521;

4) 在master application root创建proxy PDB：

alter session set container=msappcon;

在master application下创建到replica application的dblink

CREATE PUBLIC DATABASE LINK rep_link CONNECT TO system IDENTIFIED BY oracle USING

    '(DESCRIPTION =

             (ADDRESS_LIST =

                (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)))

                (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = depappcon))

      )';

mkdir /u01/app/oracle/oradata/orcl/msappcon/prxy

CREATE PLUGGABLE DATABASE prxy AS PROXY FROM depappcon@rep_link

   host='0.0.0.0' port=1521

   FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/depappcon/', '/u01/app/oracle/oradata/orcl/msappcon/prxy/');

ALTER PLUGGABLE DATABASE prxy OPEN;

这里需要用dblink的用户名密码连接prxy:

conn system/oracle@0.0.0.0/prxy

ALTER PLUGGABLE DATABASE APPLICATION sampleapp SYNC;

ORA-65313: cannot synchronize application in a proxy pluggable database without being in a SYSDBA session  

在application root中授权：

conn / as sysdba

alter session set container=msappcon;

grant sysdba to system container=all;

以sysdba登陆：

conn system/oracle@0.0.0.0/prxy  as sysdba

ALTER PLUGGABLE DATABASE APPLICATION sampleapp SYNC;

5) 在replica application root创建pdbs

ALTER SESSION SET CONTAINER=depappcon;

mkdir /u01/app/oracle/oradata/orcl/depappcon/deppdb1

CREATE PLUGGABLE DATABASE deppdb1 ADMIN USER deppdb1admin IDENTIFIED BY oracle

  STORAGE (MAXSIZE 2G)

  DEFAULT TABLESPACE sales  DATAFILE '/u01/app/oracle/oradata/orcl/depappcon/deppdb1/deppdb101.dbf' SIZE 250M AUTOEXTEND ON

  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdbseed/', '/u01/app/oracle/oradata/orcl/depappcon/deppdb1/');

ALTER PLUGGABLE DATABASE deppdb1 OPEN;

ALTER SESSION SET CONTAINER=deppdb1;

ALTER PLUGGABLE DATABASE APPLICATION sampleapp SYNC;

desc apptb

6) 在master修改app并在proxy pdb应用

ALTER SESSION SET CONTAINER=msappcon;

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

ALTER TABLE apptb ADD (widget_type VARCHAR2(30));

ALTER PLUGGABLE DATABASE APPLICATION sampleapp END UPGRADE TO '1.1';

conn system/oracle@0.0.0.0/prxy  as sysdba

ALTER PLUGGABLE DATABASE APPLICATION sampleapp SYNC;

ALTER SESSION SET CONTAINER=deppdb1;

ALTER PLUGGABLE DATABASE APPLICATION sampleapp SYNC;

desc apptb

1. Uninstalling Applications from an Application Container

只能在application root中uninstall application, 同样需要在application pdbs同步

Uninstall并不是删除应用，但是让应用不再可以更改，它也去产生application root clone，可以看成是一种特殊的upgrade

Uninstalling an application does not remove the application from the data dictionary. It marks the application as UNINSTALLED so that upgrade, patch, and uninstall of the application is disallowed.

Destructive changes to application objects are allowed during application uninstallation. Applications running in an application PDB continue to function during uninstallation and after the application is uninstalled from the application root.

The application can continue to function in the application PDB because the ALTER PLUGGABLE DATABASE APPLICATION BEGIN UNINSTALL statement creates a clone of the application root called an application root clone.

An application root clone serves as a metadata repository for old versions of application objects, so that application PDBs that have not been synchronized with latest version of the application can continue to function.

The following prerequisites must be met:

The CDB must be in local undo mode.

The current user must have the ALTER PLUGGABLE DATABASE system privilege, and the privilege must be commonly granted in the application root.

The application root must be in open read/write mode.

1. In SQL*Plus, ensure that the current container is the application root.
2. ALTER PLUGGABLE DATABASE APPLICATION application_name BEGIN UNINSTALL;
3. Uninstall the application using scripts, SQL statements, or graphical user interface tools.
4. ALTER PLUGGABLE DATABASE APPLICATION application_name END UNINSTALL;
5. Synchronize all of the application PDBs that must uninstall the application.

1. Setting the Compatibility Version of an Application

指定compatibility version后，低于兼容版本的application root clones将被隐式drop

If there are application root clones that resulted from application upgrades, then all application root clones that correspond to versions earlier than the compatibility version are implicitly dropped.

You specify the compatibility version of an application by issuing one of the following SQL statements when the application root is the current container:

ALTER PLUGGABLE DATABASE APPLICATION application_name SET COMPATIBILITY VERSION 'application_version_number';  

application_version_number is the earliest compatible version.

ALTER PLUGGABLE DATABASE APPLICATION application_name SET COMPATIBILITY VERSION CURRENT;

The current version is the version of the application in the application root.

关于application root clones:

Application的install并不发生application clones, 但在application upgrade/patch/uninstall均发生clone，这时切到cdb$root执行cdb_pdbs可以看到：

col PDB_NAME format a40

col status format a10

select PDB_NAME,status, APPLICATION_CLONE from cdb_pdbs;

PDB_NAME                                 STATUS     APPLIC

---------------------------------------- ---------- ------

F1904620897_41_1                         NORMAL     YES

APPCON1                                  NORMAL     NO

APPCON1$SEED                             NORMAL     NO

F1904620897_21_1                         NORMAL     YES

查看磁盘空间占用，发现application root clone完全是把application root数据文件复制了一份，所以很占空间，不要创建过多application versions.

你可以通过设置compatibility version删除应用的application root clone, 但无法彻底删除掉一个应用：

alter session set container=appcon1;

select app_name, app_version from dba_APP_VERSIONS;

APP_NAME                                 APP_VERSION

---------------------------------------- ------------------------------------------------------------

APP$B33FEBB00F801F09E055EF115491F42F     1.0

APP1                                     1.0

APP2                                     1.0

APP2                                     1.1

SALESAPP                                 4.2

APP10                                    1.0

APP10   1.1

APP12                                    1.0

alter pluggable database application app2 set compatibility version current;

alter pluggable database application app10 set compatibility version current;

select PDB_NAME,status, APPLICATION_CLONE from cdb_pdbs;

PDB_NAME                                 STATUS     APPLIC

---------------------------------------- ---------- ------

APPCON1                                  NORMAL     NO

APPCON1$SEED                             NORMAL     NO

1. Performing Bulk Inserts During Application Install, Upgrade, and Patch Operations

SQL*Loader is the only supported utility for bulk inserts into tables during application install, upgrade, and patch operations. Only conventional path loads are supported for bulk inserts during application install, upgrade, and patch operations.

The correct SQL*Loader module name must be specified between the ALTER PLUGGABLE DATABASE APPLICATION BEGIN and theALTER PLUGGABLE DATABASE APPLICATION END statements. The module name is SQL Loader Conventional Path Load.

1. In SQL*Plus, ensure that the current container is the application root
2. Set the correct module by running the following procedure:

exec DBMS_APPLICATION_INFO.SET_MODULE('SQL Loader Conventional Path Load', '');

This module must remain set for the entire application install, upgrade, or patch operation.

1. Run the ALTER PLUGGABLE DATABASE APPLICATION BEGIN statement for beginning an application installation, upgrade, or patch.

ALTER PLUGGABLE DATABASE APPLICATION application_name BEGIN INSTALL 'version_number';

1. Perform the conventional path load with SQL*Loader.
2. Run the ALTER PLUGGABLE DATABASE APPLICATION END statement for ending an application installation, upgrade, or patch.

ALTER PLUGGABLE DATABASE APPLICATION application_name END INSTALL 'version_number';

Example 44-8 Performing a Conventional Path Load During an Application Installation

ALTER SESSION SET CONTAINER=cdb1_approot1;

exec DBMS_APPLICATION_INFO.SET_MODULE('SQL Loader Conventional Path Load', '');

ALTER PLUGGABLE DATABASE APPLICATION APP1 BEGIN INSTALL '1';

host sqlldr u1/u1@cdb1_approot1 control=my_bulk_load.ctl rows=3 log=my_bulk_load.log

ALTER PLUGGABLE DATABASE APPLICATION APP1 END INSTALL '1';

（二）Managing Application Common Objects

There are three types of application common object: metadata-linked, data-linked, and extended data-linked.

The following types of database objects can be application common objects:

Analytic views, Attribute dimensions, Directories, External procedure libraries,

Hierarchies, Java classes, Java resources, Java sources, Packages, Object tables,

Object types, Object views, Sequences, Stored functions, Stored procedures, Synonyms,

Tables (including temporary tables), Triggers, Views

在application root中创建以上对象时可以通过SHARING子句来指定共享类型，如果不指定则使用参数DEFAULT_SHARING设置值，它可以设置如下值：

1. METADATA: A metadata link shares the database object’s metadata, but its data is unique to each container. These database objects are referred to as metadata-linked application common objects. This setting is the default.
2. DATA: A data link shares the database object, and its data is the same for all containers in the application container. Its data is stored only in the application root. These database objects are referred to as data-linked application common objects.
3. EXTENDED DATA: An extended data link shares the database object, and its data in the application root is the same for all containers in the application container. However, each application PDB in the application container can store data that is unique to the application PDB. For this type of database object, data is stored in the application root and, optionally, in each application PDB. These database objects are referred to as extended data-linked application common objects. 
4. NONE: The database object is not shared.

不同对象类型支持不同类型的share, 但大多数对象类型只支持metadata与none的设置

For most types of application common objects, the only valid settings for the SHARING clause are METADATA and NONE.

For tables (excluding object tables), the SHARING clause can be set to METADATA, DATA, EXTENDED DATA, or NONE. For object tables, only METADATA or NONE is valid.

For views (excluding object views), the SHARING clause can be set to METADATA, DATA, EXTENDED DATA, or NONE. For object views, only METADATA or NONE is valid.

For sequences, the SHARING clause can be set to METADATA, DATA, or NONE.

With a metadata-linked sequence, each application PDB has its own sequence. When the metadata-linked sequence is incremented using the NEXTVAL pseudocolumn in one application PDB, it does not affect the value of the sequence in the other application PDBs in the application container.

With a data-linked sequence, each application PDB shares the same sequence in the application root. When the metadata-linked sequence is incremented using the NEXTVAL pseudocolumn in one application PDB, all of the other application PDBs in the same application container also see the change.

查看：select owner, object_name, object_type, sharing from dba_objects where object_name ='T1';

Application common objects can be created or changed only as part of an application installation, upgrade, or patch.

If an application PDB is closed when an application common object is created, dropped, or modified, then the appropriate changes are applied in the application PDB when it is opened and synchronized with the application.

The names of application common objects must not conflict with those of local database objects in any of the application PDBs that belong to the application root or Oracle-supplied common objects in the CDB root. If a newly opened application PDB contains a local database object whose name conflicts with that of an application common object, then the application PDB is opened in RESTRICTED mode. In this case, you must resolve the naming conflict before the application PDB can be opened in normal mode.

注：一旦对象创建了就不能更改它的sharing属性了

1. Restrictions for Application Common Objects

查询是可以访问非本地数据的，但有一些特殊的列类型在非本地查询中不会显示数据

Queries on application common objects can return data from a container that is not the current container. For example, when the current container is an application root, queries that include the CONTAINERS clause can return data from application PDBs for metadata-linked application common objects. Also, when the current container is an application PDB, queries on data-linked and extended data-linked application common objects return data that resides in the application root.

Columns of the following types return no data in queries that return data from a container other than the current container:

1. The following user-defined types: object types, varrays, REFs, and nested tables
2. The following Oracle-supplied types: ANYTYPE, ANYDATASET, URI types, SDO_TOPO_GEOMETRY, SDO_GEORASTER, and Expression
3. In addition, queries on object tables and object views return no data from containers other than the current container.

1. Issuing DML Statements on Application Common Objects

测试环境：

alter session set container=appcon1;

ALTER PLUGGABLE DATABASE APPLICATION app4 BEGIN INSTALL '1.0';

create table metadata4 sharing=metadata (id number);

create table data4 sharing=data(id number);

create table extended4 sharing =extended data(id number);

insert into metadata4 values(11111);

insert into data4 values(2222);

insert into extended4 values(3333);

commit;

ALTER PLUGGABLE DATABASE APPLICATION app4 END INSTALL '1.0';

alter session set container=app1;

ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

Metadata-linked application common objects

Metadata-linked的common object，数据完全私有化，只能在application pdb中DML各自数据，application root执行ddl或dml需要在application root的begin/end statement中

如果application root在begin/end statement中dml(如insert, application root只对自己的数据可见)，则application pdbs中会看到此数据， 并把它当作本地数据，即可以dml此数据，更改不会影响application自己数据：

如上面的metadata4表有一条记录，在app1中是见且可DML的，如果在app1中删除它，从application root看此记录还是存在的

Data-linked application common objects:

Data-linked的common object，数据完全公有化，application pdb只能查询，application root可以dml. 但执行ddl仍需在application root的begin/end statement中

Extended data-linked application common objects: 

Extended-linked的common object，application pdb可以DML自己的数据，对公有数据只能查询，application root可以dml公有数据也只可见公有数据. 执行ddl仍需在application root的begin/end statement中

（三）Partitioning by PDB with Container Maps

Container maps是application container中metadata linked table的一种属性，有此属性的表在application root查询时会通过application root的map object找到对应数据在哪个pdb。

Map object是一个非共享的分区表，可以使用的分区策略包括：range, list与hash. 它的每个分区名与对应的application pdb名相同。

具有container map属性的表并非物理分区表，而是application pdb level的逻辑分区。即它把相关application pdbs组合成了一个数据集合。每个application root只能指定一个mapper table，即所有开启container map的表都是按同一列相同规则分区的。

原来通过使用containers()加个con_id的过滤来查询或更改指定pdb数据，使用container map后可以通过逻辑分区列来找到pdb

限制：

Container maps cannot be created in the CDB root.

There is a limit of one container map for each application container. 只能有一个map object

Data must be loaded into the PDBs’ tables in a manner that is consistent with the partitions defined in map object. 因为是逻辑分区，在导入数据时一定要符合map object设定

When there are changes to the application PDBs in an application container, the map object is not synchronized automatically to account for these changes. For example, an application PDB that is referenced in a map object can be unplugged, renamed, or dropped. The map object must be updated manually to account for such changes.

图中application container有3个pdbs(amer, euro, asia), 它们使用同一应用，应用中包含一个metadata-linked table(oe.cmtb)，这个表有country列; 在application root中存储着map object，此表定义了对country列使用list分区，其中分区名即为application pdb名

操作过程：

1. In SQL*Plus, ensure that the current container is the application root.
2. Create the map object.

The container map must be created by a common user with ALTER DATABASE system privilege.

CREATE TABLE salesadm.conmap (

    country VARCHAR2(30) NOT NULL)

PARTITION BY LIST (country) (

PARTITION AMER VALUES ('US','MEXICO','CANADA'),

PARTITION EURO VALUES ('UK','FRANCE','GERMANY'),

PARTITION ASIA VALUES ('INDIA','CHINA','JAPAN'));

1. Set the CONTAINER_MAP database property to the map object.

ALTER PLUGGABLE DATABASE SET CONTAINER_MAP='salesadm.conmap';

1. Begin an application installation.

ALTER PLUGGABLE DATABASE APPLICATION salesapp BEGIN INSTALL '1.0';

1. Create a metadata-linked table that will be queried using the container map.

CREATE TABLE oe.cmtb SHARING=METADATA (

   value   VARCHAR2(30),

   country VARCHAR2(30));

1. Enable the container map for the table to be queried.

ALTER TABLE oe.cmtb ENABLE CONTAINER_MAP;

1. Ensure that the table to be queried is enabled for the CONTAINERS clause.

ALTER TABLE oe.cmtb ENABLE CONTAINERS_DEFAULT;

1. End the application installation.

ALTER PLUGGABLE DATABASE APPLICATION salesapp END INSTALL '1.0';

1. Switch session into each application PDB and synchronize it.

ALTER SESSION SET CONTAINER=amer;

ALTER PLUGGABLE DATABASE APPLICATION salesapp SYNC;

ALTER SESSION SET CONTAINER=euro;

ALTER PLUGGABLE DATABASE APPLICATION salesapp SYNC;

ALTER SESSION SET CONTAINER=asia;

ALTER PLUGGABLE DATABASE APPLICATION salesapp SYNC;

Insert values into the oe.cmtb table in each application PDB based on the partitioning strategy.

ALTER SESSION SET CONTAINER=amer;

INSERT INTO oe.cmtb VALUES ('AMER VALUE','US');

INSERT INTO oe.cmtb VALUES ('AMER VALUE','MEXICO');

INSERT INTO oe.cmtb VALUES ('AMER VALUE','CANADA');

COMMIT;

ALTER SESSION SET CONTAINER=euro;

INSERT INTO oe.cmtb VALUES ('EURO VALUE','UK');

INSERT INTO oe.cmtb VALUES ('EURO VALUE','FRANCE');

INSERT INTO oe.cmtb VALUES ('EURO VALUE','GERMANY');

COMMIT;

ALTER SESSION SET CONTAINER=asia;

INSERT INTO oe.cmtb VALUES ('ASIA VALUE','INDIA');

INSERT INTO oe.cmtb VALUES ('ASIA VALUE','CHINA');

INSERT INTO oe.cmtb VALUES ('ASIA VALUE','JAPAN');

COMMIT;

1. Switch session into the application root and query the data using the container map.

ALTER SESSION SET CONTAINER=sales;    --application root

SELECT value FROM oe.cmtb WHERE country='MEXICO';

SELECT value FROM oe.cmtb WHERE country='GERMANY';

SELECT value FROM oe.cmtb WHERE country='JAPAN';

比较使用containers()与container map区别：

SELECT value FROM oe.cmtb WHERE country='MEXICO';

Select value from containers(oe.cmtb) where country='MEXICO' and con_id=10;

使用container map就不用再使用container()了，而且查询更改效率