Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajan.

Asked: May 04, 2017 - 4:12 am UTC

Last updated: May 04, 2017 - 12:01 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi,
I am currently writing some function that can output unified_audit_trail table and DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL and DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ... so that on next query the table is small and has great performance. Following is my code.

gives me.


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
________________________________________________________________________________________


and Connor said...

You cannot do transactional activity *within* a pipelined function that is being used in a select. So you need to separate that into a separate transaction, for example:

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


But I dont recommend the approach in general - because it becomes a query with side effects. If I just want to run the query ... I can't, because it will purge as well. I'd keep the two functions separate

- one for query
- one for cleanup


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

@Rajan

Sokrates, May 04, 2017 - 5:18 am UTC

what's the sense of
EXCEPTION

  WHEN OTHERS THEN
    RAISE;

?
Chris Saxon
May 04, 2017 - 8:40 am UTC

Indeed. There's little value in a catch and re-raise unless you're logging the error at the same time. Even then I'd defer this to the last possible moment.

Rajan Sharma, May 04, 2017 - 10:33 am UTC

well, my intent is to write the function that queries the "unified_audit_trail" and guaranteed unified trail purge for an extremely high volume database. this function should maintain the integrity of the data.

what is the best way to write it?

rajan
Connor McDonald
May 04, 2017 - 12:01 pm UTC

I have no issues with your code - I just wouldnt have *both* operations (the select and the purge) in the same function.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here