dbms_pdb & dbms_pdb_alter_sharing

DBMS_PDB:
注：dbms_pdb_alter_sharing是dbms_pdb的子集，只有下面第2，3部分功能
出于安全考虑，只完成 2，3部分功能时可以用dbms_pdb_alter_sharing替代dbms_pdb，即授予execute on dbms_pdb_alter_sharing即可
dbms_pdb_alter_sharing是dbms_pdb的方法参数完全与dbms_pdb相同，把包名替换即可

##########################################################
##########################################################
(一) 相关plug in/out

1. 生成pdb的xml描述文件
create database link link_orcl2 connect to system identified by oracle using 
    '(DESCRIPTION =
             (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1522)))
                (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl2))
      )';
BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/u01/app/oracle/orclpdb2.xml',
    pdb_name       => 'pdborcl2@link_orcl2');       ----如果不是远程不用使用db link
END;
/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2. 检查pdb的xml文件是否可插入cdb
SET SERVEROUTPUT ON
DECLARE
    compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/u01/app/oracle/orclpdb2.xml', pdb_name=>'newpdbname')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
如果不符合查看视图PDB_PLUG_IN_VIOLATIONS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3. 在xml文件丢失且pdb已被unplug，可以用数据文件产生xml，用于plug in     ----试了好象不行
BEGIN
DBMS_PDB.RECOVER (
   pdb_descr_file =>'/u01/app/oracle/pdb1.xml', 
   pdb_name =>'pdb1',
   filenames => '/u01/app/oracle/oradata/orcl/pdb1/');     ---可以用逗号分隔列出所有文件，或指定目录，也可同时指定
END;
/

############################################################
############################################################
(二) application common user/role/profiles转化
只能将local转为comm, 需要在对应用中执行：
ALTER PLUGGABLE DATABASE APPLICATION APP1 BEGIN INSTALL '1.0';
exec DBMS_PDB.SET_USER_EXPLICIT (user_name=>'XXX');
exec DBMS_PDB.SET_ROLE_EXPLICIT (role_name=>'XXX');
exec DBMS_PDB.SET_PROFILE_EXPLICIT (profile_name=>'XXX');
ALTER PLUGGABLE DATABASE APPLICATION APP1 END INSTALL '1.0';

查看：
select username, common from dba_users;
select role, common from dba_roles;
select profile, common from dba_profiles;

############################################################
############################################################
(三) application sharing object 转化
将sharing=none 转为sharing=metadata/data/extended data, 需要在对应用中执行
ALTER PLUGGABLE DATABASE APPLICATION APP1 BEGIN INSTALL '1.0';
---查看对应对象的namespace:  
select object_name, namespace from dba_objects where object_name like '%_TEST'
exec DBMS_PDB.SET_METADATA_LINKED (schema_name=>'SYS',  object_name=>'METADATA_TEST',  namespace=>1);
exec DBMS_PDB.SET_DATA_LINKED (schema_name=>'SYS',  object_name=>'DATA_TEST',  namespace=>1);
exec DBMS_PDB.SET_EXT_DATA_LINKED (schema_name=>'SYS',  object_name=>'EXTENDED_TEST',  namespace=>1);
--将sharing转为no sharing
exec DBMS_PDB.REMOVE_LINK (schema_name=>'SYS',  object_name=>'SHARING_TAB',  namespace=>1);
ALTER PLUGGABLE DATABASE APPLICATION APP1 END INSTALL '1.0';

查看：
select owner, object_name, object_type, sharing from DBA_OBJECTS where object_name like '%_TEST'; 
select a.object_name, b.app_name from  dba_objects a, dba_applications b where a.CREATED_APPID=b.app_id and a.object_name like '%_TEST%';