问题描述
嗨，
我目前正在编写一些函数，可以输出unified_audit_trail表和DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL和DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP...因此，在下一个查询时，该表很小，并且具有很好的性能。以下是我的代码。
给我。
我目前正在编写一些函数，可以输出unified_audit_trail表和DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL和DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP...因此，在下一个查询时，该表很小，并且具有很好的性能。以下是我的代码。
给我。
ERROR:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "SYS.GET_LASTEST_UNIFIED_AUDITS", line 132
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 5355
ORA-06512: at "SYS.DBMS_LOCK", line 306
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 5328
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1358
ORA-06512: at "SYS.GET_LASTEST_UNIFIED_AUDITS", line 115
How can i write the procedure to achieve, what i need?
_________________________________________________________________________________________________________________
DROP TYPE UNIFIED_AUDIT_TAB_TYPE
/
DROP TYPE UNIFIED_AUDIT_TRAIL_REC_TYPE
/
CREATE OR REPLACE TYPE UNIFIED_AUDIT_TRAIL_REC_TYPE AS OBJECT
(
AUDIT_TYPE VARCHAR2(64)
,SESSIONID NUMBER
,PROXY_SESSIONID NUMBER
,OS_USERNAME VARCHAR2(128)
,USERHOST VARCHAR2(128)
,TERMINAL VARCHAR2(30)
,INSTANCE_ID NUMBER
,DBID NUMBER
,AUTHENTICATION_TYPE VARCHAR2(1024)
,DBUSERNAME VARCHAR2(128)
,DBPROXY_USERNAME VARCHAR2(128)
,EXTERNAL_USERID VARCHAR2(1024)
,GLOBAL_USERID VARCHAR2(32)
,CLIENT_PROGRAM_NAME VARCHAR2(48)
,DBLINK_INFO VARCHAR2(4000)
,XS_USER_NAME VARCHAR2(128)
,XS_SESSIONID RAW(33)
,ENTRY_ID NUMBER
,STATEMENT_ID NUMBER
,EVENT_TIMESTAMP TIMESTAMP(6) WITH LOCAL TIME ZONE
,ACTION_NAME VARCHAR2(64)
,RETURN_CODE NUMBER
,OS_PROCESS VARCHAR2(16)
,TRANSACTION_ID RAW(8)
,SCN NUMBER
,EXECUTION_ID VARCHAR2(64)
,OBJECT_SCHEMA VARCHAR2(128)
,OBJECT_NAME VARCHAR2(128)
,SQL_TEXT CLOB
,SQL_BINDS CLOB
,APPLICATION_CONTEXTS VARCHAR2(4000)
,CLIENT_IDENTIFIER VARCHAR2(64)
,NEW_SCHEMA VARCHAR2(128)
,NEW_NAME VARCHAR2(128)
,OBJECT_EDITION VARCHAR2(128)
,SYSTEM_PRIVILEGE_USED VARCHAR2(1024)
,SYSTEM_PRIVILEGE VARCHAR2(40)
,AUDIT_OPTION VARCHAR2(40)
,OBJECT_PRIVILEGES VARCHAR2(35)
,ROLE VARCHAR2(128)
,TARGET_USER VARCHAR2(128)
,EXCLUDED_USER VARCHAR2(128)
,EXCLUDED_SCHEMA VARCHAR2(128)
,EXCLUDED_OBJECT VARCHAR2(128)
,CURRENT_USER VARCHAR2(128)
,ADDITIONAL_INFO VARCHAR2(4000)
,UNIFIED_AUDIT_POLICIES VARCHAR2(4000)
,FGA_POLICY_NAME VARCHAR2(128)
,XS_INACTIVITY_TIMEOUT NUMBER
,XS_ENTITY_TYPE VARCHAR2(32)
,XS_TARGET_PRINCIPAL_NAME VARCHAR2(128)
,XS_PROXY_USER_NAME VARCHAR2(128)
,XS_DATASEC_POLICY_NAME VARCHAR2(128)
,XS_SCHEMA_NAME VARCHAR2(128)
,XS_CALLBACK_EVENT_TYPE VARCHAR2(32)
,XS_PACKAGE_NAME VARCHAR2(128)
,XS_PROCEDURE_NAME VARCHAR2(128)
,XS_ENABLED_ROLE VARCHAR2(128)
,XS_COOKIE VARCHAR2(1024)
,XS_NS_NAME VARCHAR2(128)
,XS_NS_ATTRIBUTE VARCHAR2(4000)
,XS_NS_ATTRIBUTE_OLD_VAL VARCHAR2(4000)
,XS_NS_ATTRIBUTE_NEW_VAL VARCHAR2(4000)
,DV_ACTION_CODE NUMBER
,DV_ACTION_NAME VARCHAR2(30)
,DV_EXTENDED_ACTION_CODE NUMBER
,DV_GRANTEE VARCHAR2(128)
,DV_RETURN_CODE NUMBER
,DV_ACTION_OBJECT_NAME VARCHAR2(128)
,DV_RULE_SET_NAME VARCHAR2(128)
,DV_COMMENT VARCHAR2(4000)
,DV_FACTOR_CONTEXT VARCHAR2(4000)
,DV_OBJECT_STATUS VARCHAR2(1)
,OLS_POLICY_NAME VARCHAR2(128)
,OLS_GRANTEE VARCHAR2(1024)
,OLS_MAX_READ_LABEL VARCHAR2(4000)
,OLS_MAX_WRITE_LABEL VARCHAR2(4000)
,OLS_MIN_WRITE_LABEL VARCHAR2(4000)
,OLS_PRIVILEGES_GRANTED VARCHAR2(128)
,OLS_PROGRAM_UNIT_NAME VARCHAR2(128)
,OLS_PRIVILEGES_USED VARCHAR2(128)
,OLS_STRING_LABEL VARCHAR2(4000)
,OLS_LABEL_COMPONENT_TYPE VARCHAR2(12)
,OLS_LABEL_COMPONENT_NAME VARCHAR2(30)
,OLS_PARENT_GROUP_NAME VARCHAR2(30)
,OLS_OLD_VALUE VARCHAR2(4000)
,OLS_NEW_VALUE VARCHAR2(4000)
,RMAN_SESSION_RECID NUMBER
,RMAN_SESSION_STAMP NUMBER
,RMAN_OPERATION VARCHAR2(20)
,RMAN_OBJECT_TYPE VARCHAR2(20)
,RMAN_DEVICE_TYPE VARCHAR2(5)
,DP_TEXT_PARAMETERS1 VARCHAR2(512)
,DP_BOOLEAN_PARAMETERS1 VARCHAR2(512)
,DIRECT_PATH_NUM_COLUMNS_LOADED NUMBER
,RLS_INFO CLOB
,KSACL_USER_NAME VARCHAR2(128)
,KSACL_SERVICE_NAME VARCHAR2(512)
,KSACL_SOURCE_LOCATION VARCHAR2(48)
)
/
CREATE OR REPLACE TYPE UNIFIED_AUDIT_TAB_TYPE AS TABLE OF UNIFIED_AUDIT_TRAIL_REC_TYPE
/
CREATE OR REPLACE FUNCTION GET_LASTEST_UNIFIED_AUDITS RETURN UNIFIED_AUDIT_TAB_TYPE PIPELINED IS
REC UNIFIED_AUDIT_TRAIL%ROWTYPE;
LAST_TIMESTAMP TIMESTAMP;
BEGIN
FOR REC IN ( SELECT * FROM UNIFIED_AUDIT_TRAIL )
LOOP
PIPE ROW
(
UNIFIED_AUDIT_TRAIL_REC_TYPE(
REC.AUDIT_TYPE
,REC.SESSIONID
,REC.PROXY_SESSIONID
,REC.OS_USERNAME
,REC.USERHOST
,REC.TERMINAL
,REC.INSTANCE_ID
,REC.DBID
,REC.AUTHENTICATION_TYPE
,REC.DBUSERNAME
,REC.DBPROXY_USERNAME
,REC.EXTERNAL_USERID
,REC.GLOBAL_USERID
,REC.CLIENT_PROGRAM_NAME
,REC.DBLINK_INFO
,REC.XS_USER_NAME
,REC.XS_SESSIONID
,REC.ENTRY_ID
,REC.STATEMENT_ID
,REC.EVENT_TIMESTAMP
,REC.ACTION_NAME
,REC.RETURN_CODE
,REC.OS_PROCESS
,REC.TRANSACTION_ID
,REC.SCN
,REC.EXECUTION_ID
,REC.OBJECT_SCHEMA
,REC.OBJECT_NAME
,REC.SQL_TEXT
,REC.SQL_BINDS
,REC.APPLICATION_CONTEXTS
,REC.CLIENT_IDENTIFIER
,REC.NEW_SCHEMA
,REC.NEW_NAME
,REC.OBJECT_EDITION
,REC.SYSTEM_PRIVILEGE_USED
,REC.SYSTEM_PRIVILEGE
,REC.AUDIT_OPTION
,REC.OBJECT_PRIVILEGES
,REC.ROLE
,REC.TARGET_USER
,REC.EXCLUDED_USER
,REC.EXCLUDED_SCHEMA
,REC.EXCLUDED_OBJECT
,REC.CURRENT_USER
,REC.ADDITIONAL_INFO
,REC.UNIFIED_AUDIT_POLICIES
,REC.FGA_POLICY_NAME
,REC.XS_INACTIVITY_TIMEOUT
,REC.XS_ENTITY_TYPE
,REC.XS_TARGET_PRINCIPAL_NAME
,REC.XS_PROXY_USER_NAME
,REC.XS_DATASEC_POLICY_NAME
,REC.XS_SCHEMA_NAME
,REC.XS_CALLBACK_EVENT_TYPE
,REC.XS_PACKAGE_NAME
,REC.XS_PROCEDURE_NAME
,REC.XS_ENABLED_ROLE
,REC.XS_COOKIE
,REC.XS_NS_NAME
,REC.XS_NS_ATTRIBUTE
,REC.XS_NS_ATTRIBUTE_OLD_VAL
,REC.XS_NS_ATTRIBUTE_NEW_VAL
,REC.DV_ACTION_CODE
,REC.DV_ACTION_NAME
,REC.DV_EXTENDED_ACTION_CODE
,REC.DV_GRANTEE
,REC.DV_RETURN_CODE
,REC.DV_ACTION_OBJECT_NAME
,REC.DV_RULE_SET_NAME
,REC.DV_COMMENT
,REC.DV_FACTOR_CONTEXT
,REC.DV_OBJECT_STATUS
,REC.OLS_POLICY_NAME
,REC.OLS_GRANTEE
,REC.OLS_MAX_READ_LABEL
,REC.OLS_MAX_WRITE_LABEL
,REC.OLS_MIN_WRITE_LABEL
,REC.OLS_PRIVILEGES_GRANTED
,REC.OLS_PROGRAM_UNIT_NAME
,REC.OLS_PRIVILEGES_USED
,REC.OLS_STRING_LABEL
,REC.OLS_LABEL_COMPONENT_TYPE
,REC.OLS_LABEL_COMPONENT_NAME
,REC.OLS_PARENT_GROUP_NAME
,REC.OLS_OLD_VALUE
,REC.OLS_NEW_VALUE
,REC.RMAN_SESSION_RECID
,REC.RMAN_SESSION_STAMP
,REC.RMAN_OPERATION
,REC.RMAN_OBJECT_TYPE
,REC.RMAN_DEVICE_TYPE
,REC.DP_TEXT_PARAMETERS1
,REC.DP_BOOLEAN_PARAMETERS1
,REC.DIRECT_PATH_NUM_COLUMNS_LOADED
,REC.RLS_INFO
,REC.KSACL_USER_NAME
,REC.KSACL_SERVICE_NAME
,REC.KSACL_SOURCE_LOCATION
)
);
END LOOP;
SELECT SYSTIMESTAMP INTO LAST_TIMESTAMP FROM DUAL;
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => TRUE
);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME => LAST_TIMESTAMP
);
RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
SHOW ERR
________________________________________________________________________________________
专家解答
您不能在select中使用的流水线函数内进行事务活动。所以你需要把它分成一个单独的事务，例如:
但我不建议一般的方法-因为它成为一个有副作用的查询。如果我只想运行查询...我不能，因为它也会清除。我会把这两个函数分开
-一个用于查询
-一个用于清理
SQL> create table t ( x int ); Table created. SQL> insert into t values (1); 1 row created. SQL> commit; Commit complete. SQL> SQL> create or replace 2 function pipe_plus_commit return sys.odcivarchar2list pipelined is 3 begin 4 pipe row ( 'Some text '); 5 pipe row ( 'Some more text '); 6 7 delete from t; 8 commit; 9 end; 10 / Function created. SQL> SQL> select * from table(pipe_plus_commit); ERROR: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "MCDONAC.PIPE_PLUS_COMMIT", line 6 no rows selected SQL> SQL> SQL> SQL> create or replace 2 function pipe_plus_commit return sys.odcivarchar2list pipelined is 3 pragma autonomous_transaction; 4 begin 5 pipe row ( 'Some text '); 6 pipe row ( 'Some more text '); 7 8 delete from t; 9 commit; 10 end; 11 / Function created. SQL> SQL> select * from table(pipe_plus_commit); COLUMN_VALUE -------------------------------------------------------------------------------- Some text Some more text
但我不建议一般的方法-因为它成为一个有副作用的查询。如果我只想运行查询...我不能，因为它也会清除。我会把这两个函数分开
-一个用于查询
-一个用于清理
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
