-------------------------------------------------------------------------------------------------------
- --Lerning Content :Oracle 12C PL/SQL新特性（下半部分）
- --Author :600团队
- --Description :PL/SQL Lanaguage Reference 关于Change in This Release For Oracle Database PLSQL
Language Reference有具体描述的特性清单
- --Remark :
-------------------------------------------------------------------------------------------------------
1.对于部分12c的PL/SQL新特性改进如下：
1.1调用者权限函数结果集缓存技术
1.2Oracle PL/SQL特有的更多数据类型可以跨越PL/SQL到SQL的接口子句
1.3ACCESSIBLE BY新特性
1.4FETCH FIRST新特性
1.5可将角色授予PL/SQL包和独立子程序
1.6更多的数据类型在SQL和PL/SQL中具有相同的最大大小
1.7可插拔数据库上的触发器设置
1.8library可定义为directory对象，并可带有credential子句
1.9隐式语句结果集
1.10BEQUEATH CURRENT_USER视图
1.11INHERIT PRIVILEGES 和INHERIT ANY PRIVILEGES
1.12不可见列
1.13如何在sql中运行PL/SQL
1.14预定义的查询命令：$$PLSQL_UNIT_OWNER和$$PLSQL_UNIT_TYPE
1.15编译参数PLSQL_DEBUG弃用
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
8.基于Oracle 12c pdb 创建事件触发器：
8.1pdb是oracle多租户框架的组成部分之一，通常它是可移植的模式和其他数据库对象的一个集合，从Oracle12c
开始，可以在pdb上创建基于事件的触发器，所谓事件通常分为以下两个指标选项：
8.1.1系统事件：比如启动或者关闭数据库，基于trigger的记录范围
8.1.2用户事件：比如current session登录数据库或者退出数据库，利用trigger实现数据库的粗略审计，记录
登录时间，session sid等，如下代码体系展示了如何基于trigger实现session级别审计
--Author :如人饮水冷暖自知
CREATE TABLE T_LOGIN_MONITOR(
COL_TERMINAL VARCHAR2(50),
COL_LANGUAGE VARCHAR2(150),
COL_SESSION VARCHAR2(20),
COL_INSTANCE INTEGER,
COL_ENTRYID VARCHAR2(50),
COL_ISDBA VARCHAR2(20),
COL_NLS_CALENDAR VARCHAR2(50),
COL_NLS_DATE_FORMAT VARCHAR2(50),
COL_CURRENT_USER VARCHAR2(50),
COL_CURRENT_USERID VARCHAR2(20),
COL_SESSION_USER VARCHAR2(20),
COL_DB_NAME VARCHAR2(20),
COL_HOST VARCHAR2(100),
COL_OS_USER VARCHAR2(20),
COL_ADDRESS VARCHAR2(50),
COL_NETWORK VARCHAR2(50),
COL_LAST_MODULE VARCHAR2(50),
COL_LOGON_DAY DATE,
COL_LOGON_DIFF DATE,
COL_LOGIN_DATE DATE
)
SEGMENT CREATION IMMEDIATE
MONITORING
ROWDEPENDENCIES
NOLOGGING
NOPARALLEL
TABLESPACE ETL
;
/
--创建登录监控触发器
CREATE OR REPLACE TRIGGER TRI_LOGIN_MONITOR
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO T_LOGIN_MONITOR
VALUES
(SYS_CONTEXT('USERENV', 'TERMINAL'),
SYS_CONTEXT('USERENV', 'LANGUAGE'),
SYS_CONTEXT('USERENV', 'SESSIONID'),
SYS_CONTEXT('USERENV', 'INSTANCE'),
SYS_CONTEXT('USERENV', 'ENTRYID'),
SYS_CONTEXT('USERENV', 'ISDBA'),
SYS_CONTEXT('USERENV', 'NLS_CALENDAR'),
SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT'),
SYS_CONTEXT('USERENV', 'CURRENT_USER'),
SYS_CONTEXT('USERENV', 'CURRENT_USERID'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'DB_NAME'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL'),
NULL,
NULL,
NULL,
SYSDATE);
COMMIT;
END TRI_LOGIN_MONITOR;
/
------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER TRI_LOGON_DIFFERENCE
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE T_LOGIN_MONITOR
SET COL_LAST_MODULE =
(SELECT MODULE
FROM V$SESSION
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = AUDSID)
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = COL_SESSION;
UPDATE T_LOGIN_MONITOR
SET COL_LOGON_DIFF = SYSDATE
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = COL_SESSION;
END TRI_LOGON_DIFFERENCE;
------------------------------------------------------------------------------------
ALTER TRIGGER TRI_LOGIN_MONITOR ENABLE;
ALTER TRIGGER TRI_LOGON_DIFFERENCE ENABLE;
------------------------------------------------------------------------------------
SELECT *
FROM (SELECT TO_CHAR(SAMPLE_TIME, 'yyyy-mm-dd hh24:mi:ss.ff'),
SESSION_ID,
EVENT_NAME,
SQL_ID,
BLOCKING_SESSION,
ASH.BLOCKING_INST_ID,
CHR(BITAND(P1, -16777216) 16777215) ||
CHR(BITAND(P1, 16711680) 65535) "Name",
(BITAND(P1, 65535)) "Mode",
ASH.P1,
ASH.P2,
ASH.P3,
ASH.SQL_EXEC_START,
ASH.MODULE,
ASH.MACHINE,
ASH.ACTION,
ASH.PORT
FROM WRH$_ACTIVE_SESSION_HISTORY ASH, WRH$_EVENT_NAME EN
WHERE ASH.EVENT_ID = EN.EVENT_ID(+)
AND EVENT_NAME LIKE '%enq: TX - row lock contention%'
AND SAMPLE_TIME <= TO_TIMESTAMP('20170926', 'yyyymmdd')
AND SAMPLE_TIME >= TO_TIMESTAMP('20170925', 'yyyymmdd')
ORDER BY 1)
WHERE ROWNUM <= 100;
------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE PKG_GET_FGA IS
---------------------------------------------------------------------
--
-- Description :完成数据库审计相关工作
-- Author : 如人饮水冷暖自知
-- Created : 2016/07/01 08:15:36
---------------------------------------------------------------------
---------------------------------------------------------------------
*
*process P_EM_UNITNODEDATA
*执行细粒度审计包
*/
PROCEDURE ADD_FGA;
---------------------------------------------------------------------
---------------------------------------------------------------------
*
*process P_EM_UNITNODEDATA
*删除细粒度审计包
*/
PROCEDURE DELETE_FGA;
---------------------------------------------------------------------
---------------------------------------------------------------------
*
*process P_EM_UNITNODEDATA
*数据库审计计划统计表单
*/
--PROCEDURE GET_RECORDS;
---------------------------------------------------------------------
END PKG_GET_FGA;
/
CREATE OR REPLACE PACKAGE BODY PKG_GET_FGA IS
---------------------------------------------------------------------
*
*process P_EM_UNITNODEDATA
*执行细粒度审计包
*/
---------------------------------------------------------------------
PROCEDURE ADD_FGA IS
V_TABLE USER_TABLES.TABLE_NAME%TYPE;
V_RANDOM VARCHAR2(10);
CURSOR CUR_TABLE_NAME IS
SELECT TABLE_NAME FROM USER_TABLES;
BEGIN
OPEN CUR_TABLE_NAME;
LOOP
FETCH CUR_TABLE_NAME
INTO V_TABLE;
V_RANDOM := DBMS_RANDOM.STRING('X', 3);
BEGIN
SYS.DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'WMS_IC',
OBJECT_NAME => V_TABLE,
POLICY_NAME => 'TABLE_AUDIT',
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => TRUE,
STATEMENT_TYPES => 'INSERT,UPDATE,DELETE',
AUDIT_TRAIL => SYS.DBMS_FGA.DB +
SYS.DBMS_FGA.EXTENDED);
END;
EXIT WHEN CUR_TABLE_NAME%NOTFOUND;
END LOOP;
CLOSE CUR_TABLE_NAME;
END ADD_FGA;
---------------------------------------------------------------------
*
*process P_EM_UNITNODEDATA
*删除细粒度审计包
*/
PROCEDURE DELETE_FGA IS
V_POLICY_NAME DBA_FGA_AUDIT_TRAIL.POLICY_NAME%TYPE;
CURSOR CUR_TABLE_NAME IS
SELECT POLICY_NAME FROM DBA_FGA_AUDIT_TRAIL;
BEGIN
OPEN CUR_TABLE_NAME;
LOOP
FETCH CUR_TABLE_NAME
INTO V_POLICY_NAME;
BEGIN
SYS.DBMS_FGA.DROP_POLICY(OBJECT_SCHEMA => 'WMS_IC',
OBJECT_NAME => V_POLICY_NAME,
POLICY_NAME => 'FGA_');
END;
EXIT WHEN CUR_TABLE_NAME%NOTFOUND;
END LOOP;
CLOSE CUR_TABLE_NAME;
END DELETE_FGA;
---------------------------------------------------------------------
*process P_EM_UNITNODEDATA
*数据库审计结果表，用于监控数据库物理表单DML操作，及其闪回数据库使用
PROCEDURE GET_RECORDS IS
G_BEFTIME BINARY_INTEGER;
INVT_TIME NUMBER;
L_ERR_TEXT VARCHAR2(100);
V_SQL_TEXT VARCHAR2(1024);
V_SQL_DELETE VARCHAR2(1024);
BEGIN
G_BEFTIME := DBMS_UTILITY.GET_TIME();
ODSMAIN.PKG_GET_STATISTICS_INFORMATION.GET_STATISTICS_TABLE;
V_SQL_DELETE := 'TRUNCATE TABLE RECORD_TMP';
EXECUTE IMMEDIATE V_SQL_DELETE;
V_SQL_TEXT := 'ALTER SESSION ENABLE PARALLEL DML';
EXECUTE IMMEDIATE V_SQL_TEXT;
INSERT *+ APPEND */
INTO RECORD_TMP NOLOGGING
SELECT *+ CHOOST*/
SEQ_RECORD.NEXTVAL,
T.OWNER,
T.TABLE_NAME,
TO_CHAR(ROUND(T2.CAPACITY, 2),'fm9999990.0099')||'M' "CAPACITY",
T.STATUS,
T.NUM_ROWS,
COUNT(T1.TIMESTAMP) OVER(PARTITION BY TIMESTAMP ORDER BY TIMESTAMP ASC) "DIFFERENCE_NUMBER",
T1.TIMESTAMP,
T1.STATEMENT_TYPE,
T1.COMPUTER_USER,
TO_NUMBER(T1.FLASHBACK_SCN) SCN,
T1.SQL_TEXT
FROM DBA_TABLES T,
(SELECT *+ CHOOST */
TO_CHAR(TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') "TIMESTAMP",
DB_USER"USERS",
OBJECT_NAME"OBJECT_NAME",
OBJECT_SCHEMA"OBJECT_SCHEMA",
USERHOST "COMPUTER_USER",
OBJECT_NAME"TABLE_NAME",
POLICY_NAME"FGA_NAME",
SCN"FLASHBACK_SCN",
STATEMENT_TYPE"STATEMENT_TYPE",
SQL_TEXT || SQL_BIND"SQL_TEXT"
FROM SYS.DBA_FGA_AUDIT_TRAIL) T1,
(SELECT *+ CHOOST */
SUM(BYTES) 1024 1024"CAPACITY",
OWNER"OWNER",
SEGMENT_NAME"SEGMENT_NAME"
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY OWNER, SEGMENT_NAME) T2
WHERE T.OWNER = T1.OBJECT_SCHEMA
AND T.TABLE_NAME = T1.OBJECT_NAME
AND T1.USERS = T2.OWNER
AND T1.OBJECT_NAME = T2.SEGMENT_NAME
AND T1.STATEMENT_TYPE IN ('UPDATE', 'DELETE', 'INSERT');
COMMIT;
INVT_TIME := DBMS_UTILITY.GET_TIME() - G_BEFTIME;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
L_ERR_TEXT := SQLERRM;
INVT_TIME := DBMS_UTILITY.GET_TIME() - G_BEFTIME;
END GET_RECORDS;
END PKG_GET_FGA;
/
8.1.3基于审计不在本次文档讨论范围之内，不过可以为各位提供一种基于fga的审计标准，如下代码可以帮助实现
细粒度审计，非标准审计代码，只为提供参考。
1.登录触发器审计功能
CREATE TABLE T_LOGIN_MONITOR(
COL_TERMINAL VARCHAR2(50),
COL_LANGUAGE VARCHAR2(150),
COL_SESSION VARCHAR2(20),
COL_INSTANCE INTEGER,
COL_ENTRYID VARCHAR2(50),
COL_ISDBA VARCHAR2(20),
COL_NLS_CALENDAR VARCHAR2(50),
COL_NLS_DATE_FORMAT VARCHAR2(50),
COL_CURRENT_USER VARCHAR2(50),
COL_CURRENT_USERID VARCHAR2(20),
COL_SESSION_USER VARCHAR2(20),
COL_DB_NAME VARCHAR2(20),
COL_HOST VARCHAR2(100),
COL_OS_USER VARCHAR2(20),
COL_ADDRESS VARCHAR2(50),
COL_NETWORK VARCHAR2(50),
COL_LAST_MODULE VARCHAR2(50),
COL_LOGON_DAY DATE,
COL_LOGON_DIFF DATE,
COL_LOGIN_DATE DATE
)
SEGMENT CREATION IMMEDIATE
MONITORING
ROWDEPENDENCIES
NOLOGGING
NOPARALLEL
TABLESPACE ETL
;
/
--创建登录监控触发器
CREATE OR REPLACE TRIGGER TRI_LOGIN_MONITOR
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO T_LOGIN_MONITOR
VALUES
(SYS_CONTEXT('USERENV', 'TERMINAL'),
SYS_CONTEXT('USERENV', 'LANGUAGE'),
SYS_CONTEXT('USERENV', 'SESSIONID'),
SYS_CONTEXT('USERENV', 'INSTANCE'),
SYS_CONTEXT('USERENV', 'ENTRYID'),
SYS_CONTEXT('USERENV', 'ISDBA'),
SYS_CONTEXT('USERENV', 'NLS_CALENDAR'),
SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT'),
SYS_CONTEXT('USERENV', 'CURRENT_USER'),
SYS_CONTEXT('USERENV', 'CURRENT_USERID'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'DB_NAME'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL'),
NULL,
NULL,
NULL,
SYSDATE);
COMMIT;
END TRI_LOGIN_MONITOR;
/
CREATE OR REPLACE TRIGGER TRI_LOGON_DIFFERENCE
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE T_LOGIN_MONITOR
SET COL_LAST_MODULE =
(SELECT MODULE
FROM V$SESSION
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = AUDSID)
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = COL_SESSION;
UPDATE T_LOGIN_MONITOR
SET COL_LOGON_DIFF = SYSDATE
WHERE SYS_CONTEXT('USERENV', 'SESSIONID') = COL_SESSION;
END TRI_LOGON_DIFFERENCE;
/
ALTER TRIGGER TRI_LOGIN_MONITOR ENABLE;
ALTER TRIGGER TRI_LOGON_DIFFERENCE ENABLE;
2.细粒度审计添加策略
BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'NEWDAAS',
OBJECT_NAME => 'S_EN_BASEINFO',
POLICY_NAME => 'ben_audit_fga',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => TRUE,
STATEMENT_TYPES => 'select',
AUDIT_TRAIL => DBMS_FGA.DB_EXTENDED,
AUDIT_COLUMN_OPTS => DBMS_FGA.ANY_COLUMNS);
END;
/
BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'NEWDAAS',
OBJECT_NAME => 'S_EN_PERSONINFO',
POLICY_NAME => 'ben_audit_fga_personinfo',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => TRUE,
STATEMENT_TYPES => 'select',
AUDIT_TRAIL => DBMS_FGA.DB_EXTENDED,
AUDIT_COLUMN_OPTS => DBMS_FGA.ANY_COLUMNS);
END;
/
BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'NEWDAAS',
OBJECT_NAME => 'S_EN_INVINFO',
POLICY_NAME => 'ben_audit_fga_invinfo',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => TRUE,
STATEMENT_TYPES => 'select',
AUDIT_TRAIL => DBMS_FGA.DB_EXTENDED,
AUDIT_COLUMN_OPTS => DBMS_FGA.ANY_COLUMNS);
END;
/
BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'NEWDAAS',
OBJECT_NAME => 'S_EN_GTPERSONINFO',
POLICY_NAME => 'ben_audit_fga_gtpersoninfo',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => TRUE,
STATEMENT_TYPES => 'select',
AUDIT_TRAIL => DBMS_FGA.DB_EXTENDED,
AUDIT_COLUMN_OPTS => DBMS_FGA.ANY_COLUMNS);
END;
/
BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'NEWDAAS',
OBJECT_NAME => 'S_EN_GTBASEINFO',
POLICY_NAME => 'ben_audit_fga_gtbaseinfo',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => TRUE,
STATEMENT_TYPES => 'select',
AUDIT_TRAIL => DBMS_FGA.DB_EXTENDED,
AUDIT_COLUMN_OPTS => DBMS_FGA.ANY_COLUMNS);
END;
/
BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'NEWDAAS',
OBJECT_NAME => 'S_EN_ALTERRECORD',
POLICY_NAME => 'ben_audit_fga_alterecode',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
HANDLER_SCHEMA => NULL,
HANDLER_MODULE => NULL,
ENABLE => TRUE,
STATEMENT_TYPES => 'select',
AUDIT_TRAIL => DBMS_FGA.DB_EXTENDED,
AUDIT_COLUMN_OPTS => DBMS_FGA.ANY_COLUMNS);
END;
3.细粒度审计合并视图
CREATE OR REPLACE VIEW V_FGA_AUDIT_TRAIL_CRY_TEST
(session_id, timestamp, db_user, os_user, userhost, client_id, econtext_id, ext_name, object_schema, object_name, policy_name, scn, sql_text, sql_bind, comment$text, statement_type, extended_timestamp, proxy_sessionid, global_uid, instance_number, os_process, transactionid, statementid, entryid, obj_edition_name, dbid, client_ip, program)
AS
SELECT SESSIONID,
CAST((FROM_TZ(NTIMESTAMP#, '00:00') AT LOCAL) AS DATE),
DBUID,
OSUID,
OSHST,
CLIENTID,
AUDITID,
EXTID,
OBJ$SCHEMA,
OBJ$NAME,
POLICYNAME,
SCN,
TO_NCHAR(SUBSTR(LSQLTEXT, 1, 2000)),
TO_NCHAR(SUBSTR(LSQLBIND, 1, 2000)),
COMMENT$TEXT,
DECODE(STMT_TYPE,
1,
'SELECT',
2,
'INSERT',
4,
'UPDATE',
8,
'DELETE',
'INVALID'),
FROM_TZ(NTIMESTAMP#, '00:00') AT LOCAL,
PROXY$SID,
USER$GUID,
INSTANCE#,
PROCESS#,
XID,
STATEMENT,
ENTRYID,
OBJ$EDITION,
DBID,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'), --获得IP地址
(SELECT PROGRAM
FROM V$SESSION
WHERE SYS.FGA_LOG$.SESSIONID = V$SESSION.AUDSID
AND ROWNUM = 1) --获得客户端连接工具的信息(SQLPLUS/PLSQL/JDBC等等)
FROM SYS.FGA_LOG$
WHERE SYS.FGA_LOG$.POLICYNAME = 'BEN_AUDIT_FGA'
;
4.审计策略数据字典及其审计日志表
SELECT * FROM DBA_AUDIT_POLICIES;
SELECT * FROM NEWDAAS.V_FGA_AUDIT_TRAIL_CRY_TEST
5.删除审计策略方式方法：
BEGIN
DBMS_FGA.DROP_POLICY(OBJECT_SCHEMA => 'NEWDAAS',
OBJECT_NAME => 'S_EN_PERSONINFO',
POLICY_NAME => 'BEN_AUDIT_FGA');
END;
/
BEGIN
DBMS_FGA.DROP_POLICY(OBJECT_SCHEMA => 'NEWDAAS',
OBJECT_NAME => 'S_EN_BASEINFO',
POLICY_NAME => 'POLICY_FIRST1');
END;
6.最终结果集查询视图
SELECT * FROM NEWDAAS.V_FGA_AUDIT_TRAIL_CRY_TEST
8.1.4接下来继续讨论基于12c的系统事件触发器，要在CDB中的数据库事件上创建触发器，需要将与CDB的连接作为
具有管理数据库触发器系统特权的公共用户，例如如下代码：
从 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开
C:\WINDOWS\system32>sqlplus nolog
SQL*Plus: Release 12.1.0.2.0 Production on 星期二 7月 24 13:58:09 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn as sysdba;
已连接。
SQL> show user;
USER 为 "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL>CREATE OR REPLACE TRIGGER tri_after_startup
AFTER STARTUP ON DATABASE
BEGIN
NULL;
END;
8.1.5要在PDB中的数据库事件上创建触发器，需要将到PDB的连接作为一个公共或本地用户，并在PDB上下文中具有
管理数据库触发器系统特权。ON数据库和ON PLUGGABLE DATABASE子句在PDB中功能上是等效的，但是有些事件需要
显式地使用ON PLUGGABLE DATABASE子句
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show user;
USER 为 "SCOTT"
SQL> show con_name;
CON_NAME
------------------------------
PDBORACLE
SQL> set serveroutput on;
SQL> set linesize 150;
SQL> create or replace trigger tri_after_startup
2 after startup on pluggable database
3 begin
4 null;
5 end;
6 /
触发器已创建
SQL> create or replace trigger tri_pdboracle
2 after startup on database
3 begin
4 null;
5 end;
6 /
触发器已创建
注：以下为cdb/pdb模式下对应的event事件
Event Availability
The following database events are available at both the CDB and PDB level.
AFTER STARTUP : Trigger fires after the CDB or PDB opens.
BEFORE SHUTDOWN : Trigger fires before the CDB shuts down or before the PDB closes.
AFTER SERVERERROR : Trigger fires when a server error message is logged and it is safe to fire error triggers. Available at [PLUGGABLE] DATABASE or SCHEMA level.
AFTER LOGON : Trigger fires when a client logs into the CDB or PDB. Available at [PLUGGABLE] DATABASE or SCHEMA level.
BEFORE LOGOFF : Trigger fires when a client logs out of the CDB or PDB. Available at [PLUGGABLE] DATABASE or SCHEMA level.
AFTER SUSPEND : Trigger fires when a server error causes a transaction to be suspended. Available at [PLUGGABLE] DATABASE or SCHEMA level.
BEFORE SET CONTAINER : Trigger fires before the SET CONTAINER command executes. Available at [PLUGGABLE] DATABASE or SCHEMA level.
AFTER SET CONTAINER : Trigger fires after the SET CONTAINER command executes. Available at [PLUGGABLE] DATABASE or SCHEMA level.
The following database event is only available at the CDB level.
AFTER DB_ROLE_CHANGE : Fires when the database role switches from primary to standby or from standby to primary in a Data Guard configuration.
The following database events are only available at the PDB level and require the ON PLUGGABLE DATABASE clause explicitly. Using the ON DATABASE clause results in an error.
AFTER CLONE : After a clone operation, the trigger fires in the new PDB and then the trigger is deleted. If the trigger fails, the clone operation fails.
BEFORE UNPLUG : Before an unplug operation, the trigger fires in the PDB and then the trigger is deleted. If the trigger fails, the unplug operation fails.
-------------------------------------------------------------------------------------------------------
9.简要oracle 12c library 说明
9.1使用CREATE LIBRARY语句创建与操作系统共享库相关联的模式对象。然后，可以在CREATE函数或CREATE PROCEDURE语句
的call_spec中使用该模式对象的名称，或者在包或类型中声明函数或过程时使用该模式对象的名称，以便SQL和PL/SQL
可以调用第三代语言(3GL)函数和过程
9.2先决条件
9.2.1CREATE LIBRARY语句仅在支持共享库和动态链接的平台上有效。
9.2.2要在自己的模式中创建库，必须具有create library system特权。要在另一个用户的模式中创建库，
必须具有create ANY library system特权。
要在CREATE FUNCTION语句的call_spec中使用库，或者在包或类型中声明函数时，必须在库中拥有EXECUTE
对象特权，并创建函数系统特权。
9.3语法规则：
CREATE [ OR REPLACE ] LIBRARY [ schema. ] libname
{ IS | AS } 'filename' [ AGENT 'agent_dblink' ] ;
注意：
OR REPLACE
如果库已经存在，则指定或替换以重新创建它。使用此子句更改an的定义
不删除、重新创建和重新授予在其上授予的模式对象特权的现有库。
以前在重新定义的库上被授予特权的用户仍然可以访问该库，而不被重新授予特权
libname
当用户使用call_spec声明函数或过程时，指定表示该库的名称
filename
指定字符串文字，用单引号括起来。该字符串应该是对应操作系统确认为共享库命名的路径或文件名
在执行CREATE LIBRARY语句时，不会解释文件名。直到有了库文件的存在
AGENT Clause
如果希望从服务器以外的数据库链接运行外部过程，请指定代理子句。
Oracle数据库将使用agent_dblink指定的数据库链接来运行外部过程。如果你
省略此子句，服务器上的默认代理(extproc)将运行外部过程。
9.4示例说明
9.4.1普通共享库脚本
CREATE LIBRARY ext_lib AS '/OR/lib/ext_lib.so';
9.4.2指定外部过程代理:下面的示例创建一个库app_lib，并指定将从公共数据库600demo.acme.com运行外部过程
CREATE LIBRARY app_lib as '${ORACLE_HOME}/lib/app_lib.s
AGENT '600demo.acme.example.com';
-------------------------------------------------------------------------------------------------------
总结：整体的12c PL/SQL新特性就介绍到这里，部分功能后期会通过单独详细的文档，案例去描述，后期关注600团队更多
文章，帮助学习、实践。
-------------------------------------------------------------------------------------------------------
