映射表指定元数据链接的公共表中的一列,并使用分区将不同的应用程序PDBs与不同的列值关联起来。通过这种方式,当数据没有在表级别物理分区时,容器映射支持在PDB级别分区数据。
下面进行一下实践活动,昨天匆匆测试,发现oracle18c的online doc中好多用到的用户、表等都是不存在的,得自己尝试的创建后,测能测试通过,但是这样会有个问题,万一某些小细节与oracle18c的官方测试环境不一致,结果就肯能出现差异,本文就出现差异了,大家实践后帮忙分析回复(大家看到的加个关注吧,我也好回访你们的csdn主页,互相学习),
实例:
--删除之前的实验application container saas_sales_ac
DROP PLUGGABLE DATABASE saas_sales_ac INCLUDING DATAFILES;
-- Create the application container called saas_sales_ac
-- Create the application container called saas_sales_ac
CREATE PLUGGABLE DATABASE saas_sales_ac AS APPLICATION CONTAINER
ADMIN USER saas_sales_ac_adm IDENTIFIED BY manager;
-- Open the application root
ALTER PLUGGABLE DATABASE saas_sales_ac OPEN;
ALTER SESSION SET CONTAINER = saas_sales_ac;
--Create the application pdbs 并打开
SQL> CREATE PLUGGABLE DATABASE amer_pdb ADMIN USER amer_pdbadm IDENTIFIED BY amer_pdbadm;
Done
SQL> CREATE PLUGGABLE DATABASE euro_pdb ADMIN USER euro_pdbadm IDENTIFIED BY euro_pdbadm;
Done
SQL> CREATE PLUGGABLE DATABASE asia_pdb ADMIN USER asia_pdbadm IDENTIFIED BY asia_pdbadm;
Done
SQL> alter pluggable database amer_pdb open;
插接式数据库已变更。
SQL> alter pluggable database euro_pdb open;
插接式数据库已变更。
SQL> alter pluggable database asia_pdb open;
-----Example 2-16 Creating a Metadata-Linked Table, Map Table, and Container Map: Part 1
ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app BEGIN INSTALL '1.0';
CREATE USER saas_sales_adm IDENTIFIED BY manager CONTAINER=ALL;
GRANT CREATE SESSION, DBA TO saas_sales_adm;
CREATE USER oe IDENTIFIED BY manager CONTAINER=ALL;
GRANT CREATE SESSION, DBA TO oe;
CREATE TABLE oe.countries_mlt SHARING=METADATA (
region VARCHAR2(30),
cname VARCHAR2(30));
SQL> CREATE TABLE saas_sales_adm.pdb_map_tbl (cname VARCHAR2(30) NOT NULL)
2 PARTITION BY LIST (cname) (
3 PARTITION amer_pdb VALUES ('US','MEXICO','CANADA'),
4 PARTITION euro_pdb VALUES ('UK','FRANCE','GERMANY'),
5 PARTITION asia_pdb VALUES ('INDIA','CHINA','JAPAN'));
Table created
ALTER PLUGGABLE DATABASE SET CONTAINER_MAP='saas_sales_adm.pdb_map_tbl';
SQL> ALTER TABLE oe.countries_mlt ENABLE CONTAINER_MAP;
Table altered
SQL> ALTER TABLE oe.countries_mlt ENABLE CONTAINERS_DEFAULT;
Table altered
SQL> ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app END INSTALL '1.0';
Done
---Example 2-17 Synchronizing the Application, and Adding Data: Part 2
SQL> ALTER SESSION SET CONTAINER=amer_pdb;
Session altered
SQL> ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;
Done
SQL>
SQL> INSERT INTO oe.countries_mlt VALUES ('AMER','US');
1 row inserted
SQL> INSERT INTO oe.countries_mlt VALUES ('AMER','MEXICO');
1 row inserted
SQL> INSERT INTO oe.countries_mlt VALUES ('AMER','CANADA');
1 row inserted
SQL> COMMIT;
Commit complete
SQL>
SQL> ALTER SESSION SET CONTAINER=euro_pdb;
Session altered
SQL> ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;
Done
SQL> INSERT INTO oe.countries_mlt VALUES ('EURO','UK');
1 row inserted
SQL> INSERT INTO oe.countries_mlt VALUES ('EURO','FRANCE');
1 row inserted
SQL> INSERT INTO oe.countries_mlt VALUES ('EURO','GERMANY');
1 row inserted
SQL> COMMIT;
Commit complete
SQL>
SQL> ALTER SESSION SET CONTAINER=asia_pdb;
Session altered
SQL> ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;
Done
SQL> INSERT INTO oe.countries_mlt VALUES ('ASIA','INDIA');
1 row inserted
SQL> INSERT INTO oe.countries_mlt VALUES ('ASIA','CHINA');
1 row inserted
SQL> INSERT INTO oe.countries_mlt VALUES ('ASIA','JAPAN');
1 row inserted
SQL> COMMIT;
Commit complete
结果测试
SQL> ALTER SESSION SET CONTAINER=asia_pdb;
Session altered
SQL> SELECT region FROM oe.countries_mlt;
REGION
------------------------------
ASIA
ASIA
ASIA
SQL> ALTER SESSION SET CONTAINER=euro_pdb;
Session altered
SQL> SELECT region FROM oe.countries_mlt;
REGION
------------------------------
EURO
EURO
EURO
SQL> ALTER SESSION SET CONTAINER=saas_sales_ac;
Session altered
SQL> SELECT region FROM oe.countries_mlt;
REGION
------------------------------
SQL> SELECT region FROM oe.countries_mlt WHERE cname='MEXICO';
REGION
------------------------------
SQL> SELECT region FROM containers(oe.countries_mlt);
REGION
------------------------------
发现有问题:
1、application root中查询怎么是空记录,与文章预期不符。
大家帮忙分析一下,你们有出来结果的本文回复一下,谢谢
一些错误代码的分析:
1、ALTER PLUGGABLE DATABASE SET CONTAINER_MAP='saas_sales_adm.pdb_map_tbl'
ORA-65291: 无效的容器映射对象
原因:你没创建application pdbs造成的,因为map内部的pdbs不存在,所以肯定是无效的
2、CREATE TABLE appadm.countries_mlt SHARING=METADATA (
region VARCHAR2(30),
cname VARCHAR2(30))
ORA-65293: 隐式用户不能拥有公用对象。
分析:appadm是在application root下创建的common用户,在
ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app BEGIN INSTALL '1.0';
内部创建application user就可以了
3、CREATE TABLE appadm.countries_mlt SHARING=METADATA (
region VARCHAR2(30),
cname VARCHAR2(30))
ORA-65021: SHARING 子句的使用非法
分析:
在ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app BEGIN INSTALL '1.0';内部创建即可
4、CREATE PLUGGABLE DATABASE app01 ADMIN USER appadm IDENTIFIED BY manager
ORA-65001: 缺少管理用户名或管理用户名无效
原因:appadm是已经存在的common用户