AUD$UNIFIED 基表及 分区键创建索引 复制代码

create index audsys.idx_unified  on audsys.AUD$UNIFIED(EVENT_TIMESTAMP) local  tablespace sysaux;    

创建本地组合前缀索引：  EVENT_TIMESTAMP 是分区健    
      而在用视图UNIFIED_AUDIT_TRAIL查询时 ：   条件需用 EVENT_TIMESTAMP_UTC    
                     
  create index sys.idx_act_name  on sys.all_unified_audit_actions(name)    tablespace sysaux;    
复制

#注意,12C基表个别字段 与19C有区别UNIFIED_AUDIT_TRAIL 创建语句 ：more /oracle/dbsoft/product/12cr2/rdbms/admin/catuat.sql

create or replace view AUDSYS.UNIFIED_AUDIT_TRAIL
        (
         AUDIT_TYPE,
         SESSIONID,
         PROXY_SESSIONID,
         OS_USERNAME,
         USERHOST,
         TERMINAL,
         INSTANCE_ID,
         DBID,
         AUTHENTICATION_TYPE,
         DBUSERNAME,
         DBPROXY_USERNAME,
         EXTERNAL_USERID,
         GLOBAL_USERID,
         CLIENT_PROGRAM_NAME,
         DBLINK_INFO,
         XS_USER_NAME,
         XS_SESSIONID,
         ENTRY_ID,
         STATEMENT_ID,
         EVENT_TIMESTAMP,
         EVENT_TIMESTAMP_UTC,
         ACTION_NAME,
         RETURN_CODE,
         OS_PROCESS,
         TRANSACTION_ID,
         SCN,
         EXECUTION_ID,
         OBJECT_SCHEMA,
         OBJECT_NAME,
         SQL_TEXT,
         SQL_BINDS,
         APPLICATION_CONTEXTS,
         CLIENT_IDENTIFIER,
         NEW_SCHEMA,
         NEW_NAME,
         OBJECT_EDITION,
         SYSTEM_PRIVILEGE_USED,
         SYSTEM_PRIVILEGE,
         AUDIT_OPTION,
         OBJECT_PRIVILEGES,
         ROLE,
         TARGET_USER,
         EXCLUDED_USER,
         EXCLUDED_SCHEMA,
         EXCLUDED_OBJECT,
         CURRENT_USER,
         ADDITIONAL_INFO,
         UNIFIED_AUDIT_POLICIES,
         FGA_POLICY_NAME,
         XS_INACTIVITY_TIMEOUT,
         XS_ENTITY_TYPE,
         XS_TARGET_PRINCIPAL_NAME,
         XS_PROXY_USER_NAME,
         XS_DATASEC_POLICY_NAME,
         XS_SCHEMA_NAME,
         XS_CALLBACK_EVENT_TYPE,
         XS_PACKAGE_NAME,
         XS_PROCEDURE_NAME,
         XS_ENABLED_ROLE,
         XS_COOKIE,
         XS_NS_NAME,
         XS_NS_ATTRIBUTE,
         XS_NS_ATTRIBUTE_OLD_VAL,
         XS_NS_ATTRIBUTE_NEW_VAL,
         DV_ACTION_CODE,
         DV_ACTION_NAME,
         DV_EXTENDED_ACTION_CODE,
         DV_GRANTEE,
         DV_RETURN_CODE,
         DV_ACTION_OBJECT_NAME,
         DV_RULE_SET_NAME,
         DV_COMMENT,
         DV_FACTOR_CONTEXT,
         DV_OBJECT_STATUS,
         OLS_POLICY_NAME,
         OLS_GRANTEE,
         OLS_MAX_READ_LABEL,
         OLS_MAX_WRITE_LABEL,
         OLS_MIN_WRITE_LABEL,
         OLS_PRIVILEGES_GRANTED,
         OLS_PROGRAM_UNIT_NAME,
         OLS_PRIVILEGES_USED,
         OLS_STRING_LABEL,
         OLS_LABEL_COMPONENT_TYPE,
         OLS_LABEL_COMPONENT_NAME,
         OLS_PARENT_GROUP_NAME,
         OLS_OLD_VALUE,
         OLS_NEW_VALUE,
         RMAN_SESSION_RECID,
         RMAN_SESSION_STAMP,
         RMAN_OPERATION,
         RMAN_OBJECT_TYPE,
         RMAN_DEVICE_TYPE,
         DP_TEXT_PARAMETERS1,
         DP_BOOLEAN_PARAMETERS1,
         DIRECT_PATH_NUM_COLUMNS_LOADED,
         RLS_INFO,
         KSACL_USER_NAME,
         KSACL_SERVICE_NAME,
         KSACL_SOURCE_LOCATION,
         PROTOCOL_SESSION_ID,
         PROTOCOL_RETURN_CODE,
         PROTOCOL_ACTION_NAME,
         PROTOCOL_USERHOST,
         PROTOCOL_MESSAGE
         )
as
(select  act.component,
         sessionid,
         proxy_sessionid,
         os_user,
         host_name,
         terminal,
         instance_id,
         dbid,
         authentication_type,
         userid,
         proxy_userid,
         external_userid,
         global_userid,
         client_program_name,
         dblink_info,
         xs_user_name,
         xs_sessionid,
         entry_id,
         statement_id,
         cast((from_tz(event_timestamp, '00:00') at local) as timestamp),
         event_timestamp,
         act.name,
         return_code,
         os_process,
         transaction_id,
         scn,
         execution_id,
         obj_owner,
         obj_name,
         sql_text,
         sql_binds,
         application_contexts,
         client_identifier,
         new_owner,
         new_name,
         object_edition,
         system_privilege_used,
         spx.name,
         aom.name,
         object_privileges,
         role,
         target_user,
         excluded_user,
         excluded_schema,
         excluded_object,
         current_user,
         additional_info,
         unified_audit_policies,
         fga_policy_name,
         xs_inactivity_timeout,
         xs_entity_type,
         xs_target_principal_name,
         xs_proxy_user_name,
         xs_datasec_policy_name,
         xs_schema_name,
         xs_callback_event_type,
         xs_package_name,
         xs_procedure_name,
         xs_enabled_role,
         xs_cookie,
         xs_ns_name,
         xs_ns_attribute,
         xs_ns_attribute_old_val,
         xs_ns_attribute_new_val,
         dv_action_code,
         dv_action_name,
         dv_extended_action_code,
         dv_grantee,
         dv_return_code,
         dv_action_object_name,
         dv_rule_set_name,
         dv_comment,
         dv_factor_context,
         dv_object_status,
         ols_policy_name,
         ols_grantee,
         ols_max_read_label,
         ols_max_write_label,
         ols_min_write_label,
         ols_privileges_granted,
         ols_program_unit_name,
         ols_privileges_used,
         ols_string_label,
         ols_label_component_type,
         ols_label_component_name,
         ols_parent_group_name,
         ols_old_value,
         ols_new_value,
         rman_session_recid,
         rman_session_stamp,
         rman_operation,
         rman_object_type,
         rman_device_type,
         dp_text_parameters1,
         dp_boolean_parameters1,
         direct_path_num_columns_loaded,
         rls_info,
         ksacl_user_name,
         ksacl_service_name,
         ksacl_source_location,
         protocol_session_id,
         protocol_return_code,
         protocol_action_name,
         protocol_userhost,
         protocol_message
from sys.gv_$unified_audit_trail uview, sys.all_unified_audit_actions act,
     sys.system_privilege_map spx, sys.stmt_audit_option_map aom
where   uview.action = act.action   (+)
  and - uview.system_privilege = spx.privilege (+)
  and   uview.audit_option = aom.option#   (+)
  and   uview.audit_type = act.type
  
UNION ALL
select  act1.component,
         sessionid,
         proxy_sessionid,
         os_user,
         host_name,
         terminal,
         instance_id,
         dbid,
         authentication_type,
         userid,
         proxy_userid,
         external_userid,
         global_userid,
         client_program_name,
         dblink_info,
         xs_user_name,
         xs_sessionid,
         entry_id,
         statement_id,
         cast((from_tz(event_timestamp, '00:00') at local) as timestamp),
         event_timestamp,
         act1.name,
         return_code,
         os_process,
         transaction_id,
         scn,
         execution_id,
         obj_owner,
         obj_name,
         sql_text,
         sql_binds,
         application_contexts,
         client_identifier,
         new_owner,
         new_name,
         object_edition,
         system_privilege_used,
         spx1.name,
         aom1.name,
         object_privileges,
         role,
         target_user,
         excluded_user,
         excluded_schema,
         excluded_object,
         current_user,
         additional_info,
         unified_audit_policies,
         fga_policy_name,
         xs_inactivity_timeout,
         xs_entity_type,
         xs_target_principal_name,
         xs_proxy_user_name,
         xs_datasec_policy_name,
         xs_schema_name,
         xs_callback_event_type,
         xs_package_name,
         xs_procedure_name,
         xs_enabled_role,
         xs_cookie,
         xs_ns_name,
         xs_ns_attribute,
         xs_ns_attribute_old_val,
         xs_ns_attribute_new_val,
         dv_action_code,
         dv_action_name,
         dv_extended_action_code,
         dv_grantee,
         dv_return_code,
         dv_action_object_name,
         dv_rule_set_name,
         dv_comment,
         dv_factor_context,
         dv_object_status,
         ols_policy_name,
         ols_grantee,
         ols_max_read_label,
         ols_max_write_label,
         ols_min_write_label,
         ols_privileges_granted,
         ols_program_unit_name,
         ols_privileges_used,
         ols_string_label,
         ols_label_component_type,
         ols_label_component_name,
         ols_parent_group_name,
         ols_old_value,
         ols_new_value,
         rman_session_recid,
         rman_session_stamp,
         rman_operation,
         rman_object_type,
         rman_device_type,
         dp_text_parameters1,
         dp_boolean_parameters1,
         direct_path_num_columns_loaded,
         rls_info,
         ksacl_user_name,
         ksacl_service_name,
         ksacl_source_location,
         protocol_session_id,
         protocol_return_code,
         protocol_action_name,
         protocol_userhost,
         protocol_message
from audsys.aud$unified auduni, sys.all_unified_audit_actions act1,
     sys.system_privilege_map spx1, sys.stmt_audit_option_map aom1
where   auduni.action = act1.action   (+)
  and - auduni.system_privilege = spx1.privilege (+)
  and   auduni.audit_option = aom1.option#   (+)
  and   auduni.audit_type = act1.type)
/
复制