SQL> conn sysrm@dbs11p2 Enter password: Connected. SQL> --$Id: ora-audit.sql 92319 2007-07-30 21:50:22Z LASERFICHE\gfinger $ SQL> --Copyright 2007 Compulink Management Center, Inc. SQL> -- used in audit configuration only. Not part of audit log schema. SQL> create table rtd_dms8.audit_date_range 2 ( 3 date_range_guid varchar(36), 4 log_file_guid varchar(36) not null, 5 load_status number(10) not null, 6 constraint audit_date_range_unq unique(date_range_guid,log_file_guid) 7 ) 8 / Table created. SQL> SQL> -- used in audit configuration only. Not part of audit log schema. SQL> create table rtd_dms8.audit_file_info 2 ( 3 log_file_guid varchar(36), 4 begin_date timestamp not null, 5 end_date timestamp not null, 6 start_event_id number(20) not null, 7 end_event_id number(20) not null, 8 file_name nvarchar2(236) not null 9 ) 10 / Table created. SQL> SQL> -- Note: Some tables or views are broken up to make the schema consistent with that SQL> -- of MSSQL, which enforces the 8kb row size limit for sql2k. SQL> SQL> -- This table records event headers. Note that every event has headers SQL> -- (i.e. this table will contain a row for every event loaded into the database) SQL> create table rtd_dms8.audit_event 2 ( 3 event_id number(20), 4 event_time timestamp not null, 5 event_type number(5) not null, 6 succeeded number(1) not null, 7 error_code number(10) not null, 8 session_id number(10) not null, 9 hostname nvarchar2(128), 10 appname nvarchar2(128), 11 trustee nvarchar2(47), -- should be not null, but allow for errors 12 event_trustee_sid raw(85), 13 constraint auditevent_pk primary key (event_id) 14 ) 15 / Table created. SQL> SQL> Create index rtd_dms8.ix_audit_event_event_time on rtd_dms8.audit_event(event_time) 2 / Index created. SQL> SQL> Create index rtd_dms8.ix_audit_event_event_type on rtd_dms8.audit_event(event_type) 2 / Index created. SQL> SQL> Create index rtd_dms8.ix_audit_event_event_trustee on rtd_dms8.audit_event(trustee) 2 / Index created. SQL> SQL> Create index rtd_dms8.ix_audit_event_event_host on rtd_dms8.audit_event(hostname) 2 / Index created. SQL> SQL> Create index rtd_dms8.ix_audit_event_event_app on rtd_dms8.audit_event(appname) 2 / Index created. SQL> SQL> -- This table records the base information of custom audit events SQL> create table rtd_dms8.audit_custom 2 ( 3 event_id number(20), 4 custom_reason_id int, 5 custom_reason_label nvarchar2(64), 6 -- Used NCLOB as the alternative to nvarchar(max) in mssql, 7 -- http://msdn.microsoft.com/en-us/library/ms151817.aspx 8 custom_reason_data NCLOB, 9 constraint audit_custom_pk primary key (event_id), 10 constraint event_id_custom_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 11 ) 12 / Table created. SQL> SQL> -- This table records the properties that were set for custom audit events SQL> create table rtd_dms8.audit_custom_prop 2 ( 3 event_id number(20), 4 prop_index int, 5 data_label nvarchar2(256) not null, 6 constraint audit_custom_prop_pk primary key (event_id, prop_index), 7 constraint event_id_custom_prop_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 8 ) 9 / Table created. SQL> SQL> -- This table records the property values assigned to custom audit events SQL> create table rtd_dms8.audit_custom_propval 2 ( 3 event_id number(20), 4 prop_index int, 5 pos int, 6 str_val nclob, 7 int_val int, 8 bigint_val number(20), 9 dec_val decimal(18,5), 10 date_val timestamp, 11 bool_val number(1), 12 bin_val blob, 13 constraint audit_custom_propval_pk primary key (event_id, prop_index, pos), 14 constraint event_id_custom_propval_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 15 ) 16 / Table created. SQL> SQL> -- This table records session events, which include the following: SQL> -- 1) log on/off SQL> -- 2) session termination SQL> create table rtd_dms8.audit_session 2 ( 3 event_id number(20), 4 sid raw(85), 5 term_session_id number(10), 6 read_only number(1), 7 constraint audit_session_pk primary key (event_id), 8 constraint audit_session_unq unique (event_id, term_session_id), 9 constraint event_id_session_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 10 ) 11 / Table created. SQL> SQL> SQL> -- This table records information about changes to database options SQL> create table rtd_dms8.audit_dboptions 2 ( 3 event_id number(20), 4 option_type number(5) not null, 5 option_name nvarchar2(63), -- should be not null, but allow for empty string 6 option_value nvarchar2(1023), --longest dboption is the audit log archive directory 7 constraint audit_dboptions_pk primary key (event_id), 8 constraint event_id_dboptions_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 9 ) 10 / Table created. SQL> SQL> -- This table records volume events, which include the following: SQL> -- 1) create/modify/delete volumes SQL> -- 2) mount/unmount volumes SQL> -- 3) export/attach volumes SQL> -- 4) roll over volumes SQL> -- 5) modify the security descriptor SQL> create table rtd_dms8.audit_volume 2 ( 3 event_id number(20), 4 vol_id number(10), 5 vol_name nvarchar2(63), -- should be not null, but allow for empty string 6 old_vol_name nvarchar2(63), 7 vol_flags number(10), 8 fixpath nvarchar2(236), 9 rempath nvarchar2(236), 10 maxsize number(20), 11 constraint audit_volume_pk primary key (event_id, vol_id), 12 constraint event_id_volume_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 13 ) 14 / Table created. SQL> SQL> -- This table records trustee (LF trustees and Windows accounts) events, which include the following: SQL> -- 1) change trustee's audit setting SQL> -- 2) create/modify/delete trustee SQL> -- 2) grant/deny Windows authentication SQL> -- 3) link/unlink Windows account SQL> -- 4) change trustee's group membership SQL> -- 5) change trustee's password SQL> -- 6) tag/untag trustees SQL> create table rtd_dms8.audit_account 2 ( 3 event_id number(20), 4 trustee_sid raw(85), -- should be not null, but allow for empty (on error) 5 trustee_id number(10), 6 trustee_name nvarchar2(47), 7 isuser number(1), 8 trustee_flags number(10), 9 privs number(10), 10 uif number(10), 11 readonly number(1), 12 auditmask number(10), 13 old_auditmask number(10), 14 old_trustee_name nvarchar2(47), 15 auth_type number(1), 16 group_id number(10), 17 group_name nvarchar2(47), 18 group_sid raw(85), 19 assoc_id number(10), -- id of associated LF trustee on link/unlink windows account 20 assoc_name nvarchar2(47), -- name of associated LF trustee on link/unlink windows account 21 constraint audit_accounts_pk primary key (event_id), 22 constraint audit_accounts_unq unique (event_id, trustee_id), 23 constraint event_id_account_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 24 ) 25 / Table created. SQL> SQL> -- This table records LDAP profile events, which include the following: SQL> -- 1) register/modify/unregister LDAP profiles SQL> create table rtd_dms8.audit_ldap 2 ( 3 event_id number(20), 4 ldap_id number(10), 5 ldap_name nvarchar2(47), 6 old_ldap_name nvarchar2(47), 7 constraint audit_ldap_pk primary key (event_id), 8 constraint audit_ldap_unq unique (event_id, ldap_id), 9 constraint event_id_ldap_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 10 ) 11 / Table created. SQL> SQL> -- This table records template events, which include the following: SQL> -- 1) create/modify/delete template SQL> -- 2) add/delete field to/from template SQL> -- 3) move the position of a field within a template SQL> -- 4) modify the security descriptor SQL> -- This table also records the template involved when assigning a template to an entry (see audit_entry) SQL> create table rtd_dms8.audit_propset 2 ( 3 event_id number(20), 4 pset_id number(10), 5 pset_name nvarchar2(63), -- should be not null, but allow for empty string 6 pset_new_name nvarchar2(63), 7 is_set number(1), 8 -- Used NCLOB as the alternative to nvarchar(max) in mssql, 9 -- http://msdn.microsoft.com/en-us/library/ms151817.aspx 10 pset_prop_list NCLOB, 11 constraint audit_pset_pk primary key (event_id, pset_id), 12 constraint event_id_propset_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 13 ) 14 / Table created. SQL> SQL> -- This table records field events, which include the following: SQL> -- 1) create/modify/delete field SQL> -- 2) modify the security descriptor SQL> -- This table also records the fields involved when creating or modifying a template (see audit_propset) SQL> -- and field values for setting and unsetting fields (see audit_entry) SQL> create table rtd_dms8.audit_prop 2 ( 3 event_id number(20), 4 prop_name nvarchar2(63), -- should be not null, but allow for empty string 5 prop_id number(10), 6 prop_type char(1), 7 field_pos number(5) default 0 not null, --position of field in a template 8 new_prop_name nvarchar2(63), 9 new_prop_type char(1), 10 new_field_pos number(5), 11 is_set number(1), 12 -- Used NCLOB as the alternative to nvarchar(max) in mssql, 13 -- http://msdn.microsoft.com/en-us/library/ms151817.aspx 14 prop_data NCLOB, 15 constraint audit_prop_pk primary key (event_id, prop_name), 16 constraint event_id_prop_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 17 ) 18 / Table created. SQL> SQL> -- This table records the field values for "set entry prop" events (broken out from audit_prop for SQL> -- sql2k consideration) SQL> create table rtd_dms8.audit_propval 2 ( 3 event_id number(20), 4 pos number(5) default 0, --position of field in multivalue list 5 str_val nclob, 6 num_val decimal(18,5), 7 date_val timestamp, 8 bin_val blob, 9 constraint audit_propval_pk primary key (event_id, pos), 10 constraint event_id_propval_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 11 ) 12 / Table created. SQL> SQL> -- This table records entry events, which include the following: SQL> -- 1) create entry (folder/document/shortcut) SQL> -- 2) delete entry SQL> -- 3) move entry SQL> -- 4) copy entry SQL> -- 5) migrate entry SQL> -- 6) tag/untag entry SQL> -- 7) link/unlink entry SQL> -- 8) set/unset field SQL> -- 9) assign/unassign template SQL> -- 10) view document SQL> -- 11) export/print document SQL> -- 12) change ownership of an entry SQL> -- 13) change the folder filter expression SQL> -- 14) writing to the electronic document SQL> -- 15) writing to the various page parts of a document SQL> -- 16) set/delete versions SQL> -- 17) create/delete pages SQL> -- 18) move pages SQL> -- 19) copy pages SQL> -- 20) create/modify/delete annotations SQL> -- 21) modify the security descriptor SQL> -- 22) freeze/unfreeze entry SQL> -- 23) review vital records SQL> -- 24) create/delete alternate edoc SQL> -- 25) delete/restore/purge edoc SQL> -- This table also records entry information for Records Management events SQL> create table rtd_dms8.audit_entry 2 ( 3 event_id number(20), 4 entry_id number(10), 5 entry_uuid char(36), -- should be not null, but allow for empty string (error) 6 name nvarchar2(1023), -- should be not null, but allow for empty string 7 entry_name_only nvarchar2(255) null, 8 page_part number(1), 9 page_num number(10), 10 revision number(10), 11 dest_entry_id number(10), 12 dest_entry_uuid char(36), 13 dest_entry_name nvarchar2(1023), 14 dest_entry_name_only nvarchar2(255) null, 15 dest_page_num number(10), 16 freeze_reason nvarchar2(800), 17 review_date timestamp, 18 coff_elig_date timestamp, 19 lock_token char(36), 20 altedoc_name char(15), 21 page_id number(20), 22 edoc_storeid number(10), 23 constraint audit_entry_pk primary key (event_id, entry_id), 24 constraint event_id_entry_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 25 ) 26 / Table created. SQL> SQL> -- The following tables record the folder filter expression information for AUDITEVT_CHANGE_FILTER_EXPR SQL> -- (see audit_entry). They are broken up due to potential issues with the row size limit on sql2k SQL> create table rtd_dms8.audit_old_folder_filter 2 ( 3 event_id number(20), 4 old_inherit number(1) not null, -- 0 - use own filter, 1 - inherit filter from parent 5 old_filter_expr nclob, -- should be not null, but allow for empty filter 6 constraint audit_old_folder_filter_pk primary key (event_id), 7 constraint event_id_old_folder_filter_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 8 ) 9 / Table created. SQL> SQL> create table rtd_dms8.audit_new_folder_filter 2 ( 3 event_id number(20), 4 new_inherit number(1)not null, -- 0 - use own filter, 1 - inherit filter from parent 5 new_filter_expr nclob, -- should be not null, but allow for empty filter 6 constraint audit_new_folder_filter_pk primary key (event_id), 7 constraint event_id_new_folder_filter_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 8 ) 9 / Table created. SQL> SQL> -- This table records the ownership information for AUDITEVT_CHANGE_ENTRY_OWNER (see audit_entry) SQL> create table rtd_dms8.audit_entry_owner 2 ( 3 event_id number(20), 4 old_owner_sid raw(85), -- should be not null, but allow for empty string (error) 5 old_owner_name nvarchar2(255), 6 new_owner_sid raw(85), 7 new_owner_name nvarchar2(255), 8 constraint audit_entry_owner_pk primary key (event_id), 9 constraint event_id_entry_owner_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 10 ) 11 / Table created. SQL> SQL> -- This table records page ranges for events that can span multiple pages, which include SQL> -- 1) delete/move/copy pages (see audit_entry) SQL> -- 2) export/print documents (see audit_entry) SQL> create table rtd_dms8.audit_page_range 2 ( 3 event_id number(20), 4 --begin_page_num number(10), 5 --end_page_num number(10), 6 page_range varchar2(500), 7 --constraint audit_page_range_pk primary key (event_id, begin_page_num, end_page_num), 8 constraint audit_page_range_pk primary key (event_id), 9 constraint event_id_page_range_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 10 ) 11 / Table created. SQL> SQL> -- This table records annotation information for page events (see audit_page) SQL> create table rtd_dms8.audit_ann 2 ( 3 event_id number(20), 4 annot_item_id number(10), -- item id or stamp id 5 annot_new_id number(10), -- the new item id (if changed) 6 annot_type number(2), 7 stamp_id number(10), 8 ann_comment nvarchar2(200), 9 xPos number(10), 10 yPos number(10), 11 color number(10), 12 rotation number(5), 13 startPos number(10), 14 endPos number(10), 15 direction number(5), 16 ann_access number(2), 17 constraint audit_ann_pk primary key (event_id, annot_item_id), 18 constraint event_id_ann_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 19 ) 20 / Table created. SQL> SQL> -- This table records create / modify annotation information for page events (see audit_page) SQL> create table rtd_dms8.audit_ann_add_modify 2 ( 3 event_id number(20), 4 num_of_rect number(10) default 0 not null, 5 -- Used CLOB as the alternative to varchar(max) in mssql, 6 -- http://msdn.microsoft.com/en-us/library/ms151817.aspx 7 rect_info CLOB, 8 constraint audit_ann_add_modify_pk primary key (event_id), 9 constraint event_id_ann_add_modify_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 10 ) 11 / Table created. SQL> SQL> -- This table records rectangle information for annotation events like highlights and redactions SQL> create table rtd_dms8.audit_ann_rects 2 ( 3 event_id number(20), 4 rect_num number(10) default 0 not null, 5 annot_item_id number(10), 6 top_pos number(10), -- The coordinates for the various sides of the rectangle 7 bottom_pos number(10), 8 left_pos number(10), 9 right_pos number(10), 10 constraint audit_ann_rects_pk primary key (event_id, annot_item_id, rect_num), 11 constraint event_id_ann_rects_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 12 ) 13 / Table created. SQL> SQL> -- This table records trustee access list information for annotation events SQL> create table rtd_dms8.audit_ann_access 2 ( 3 event_id number(20), 4 annot_item_id number(10), 5 sid raw(85), 6 constraint audit_ann_access_pk primary key (event_id, annot_item_id, sid), 7 constraint event_id_ann_access_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 8 ) 9 / Table created. SQL> SQL> SQL> -- This table records the annotation texts (broken out from audit_ann for sql2k consideration) SQL> create table rtd_dms8.audit_ann_text 2 ( 3 event_id number(20), 4 ann_text nclob, 5 constraint audit_ann_text_pk primary key (event_id), 6 constraint event_id_ann_text_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 7 ) 8 / Table created. SQL> SQL> -- This table records tag information for events that involve tags, which include SQL> -- 1) create/delete/modify tag SQL> -- 2) tag assignment (see audit_entry and audit_acount) SQL> create table rtd_dms8.audit_tag 2 ( 3 event_id number(20), 4 is_security number(1), 5 is_apply number(1), 6 tag_id number(10) not null, 7 tag_name nvarchar2(63), -- should be not null, but allow for empty string 8 tag_descrip nvarchar2(200), 9 old_tag_name nvarchar2(63), 10 tag_notes nvarchar2(1000), 11 tag_wm_text nvarchar2(63) null, 12 tag_wm_x number(1) null, 13 tag_wm_y number(1) null, 14 tag_wm_angle number(3) null, 15 tag_wm_size number(10) null, 16 tag_wm_required number(1) default 0 not null, 17 constraint audit_tag_pk primary key (event_id), 18 constraint event_id_tag_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 19 ) 20 / Table created. SQL> SQL> -- This table records link type information for events that involve link types, which include SQL> -- 1) create/delete/modify link type SQL> -- 2) entry linking (see audit_entry) SQL> create table rtd_dms8.audit_link_type 2 ( 3 event_id number(20), 4 link_type_id number(10) not null, 5 link_op_type number(1), -- 0: unlink, 1: link, 2: modify 6 source_label nvarchar2(63), -- should be not null, but allow for empty string 7 target_label nvarchar2(63), -- should be not null, but allow for empty string 8 link_descrip nvarchar2(200), 9 target_id number(10), 10 target_uuid char(36), 11 target_name nvarchar2(1023), 12 target_name_only nvarchar2(255), 13 old_source_label nvarchar2(63), 14 old_target_label nvarchar2(63), 15 old_link_descrip nvarchar2(200), 16 link_notes nvarchar2(1000), 17 constraint audit_link_type_pk primary key (event_id), 18 constraint audit_link_type_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 19 ) 20 / Table created. SQL> SQL> -- This table records version group information, which include SQL> -- 1) set/delete version group (see audit_entry) SQL> create table rtd_dms8.audit_version_group 2 ( 3 event_id number(20), 4 version_group_id number(10) not null, 5 version_number number(10) not null, 6 version_notes nvarchar2(1000), 7 constraint audit_version_group_pk primary key (event_id), 8 constraint audit_version_group_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 9 ) 10 / Table created. SQL> SQL> -- This table records versioning events SQL> create table rtd_dms8.audit_versioning 2 ( 3 event_id number(20), 4 version_number number(10) not null, 5 version_number2 number(10), -- the "to" version number, when there's a target revision 6 version_comment nvarchar2(800), 7 version_label nvarchar2(200), 8 constraint audit_versioning_pk primary key (event_id), 9 constraint audit_versioning_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 10 ) 11 / Table created. SQL> SQL> -- This table records search events SQL> create table rtd_dms8.audit_search 2 ( 3 event_id number(20), 4 search_text nclob, 5 constraint audit_search_pk primary key (event_id), 6 constraint audit_search_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 7 ) 8 / Table created. SQL> SQL> -- This table records information about events that modify the security descriptor. SQL> -- * Join audit_entry/volume/propset/prop on event_id for the object for which the security descriptor was modified SQL> create table rtd_dms8.audit_sec_desc 2 ( 3 event_id number(20), 4 sec_info number(10), 5 old_sec_desc blob, 6 new_sec_desc blob, 7 constraint audit_sec_desc_pk primary key (event_id), 8 constraint event_id_desc_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 9 ) 10 / Table created. SQL> SQL> -- This table records information about print/export events. (see audit_entry) SQL> -- Note that this is all information supplied by the user at the time of print/export. SQL> -- * Join audit_entry on event_id for information on the entry SQL> create table rtd_dms8.audit_reason 2 ( 3 event_id number(20), 4 reason_id number(10) not null, 5 format nvarchar2(260), --max file name length in windows 6 watermark nvarchar2(80), -- max watermark length 63 + error 7 reason_event_type number(5) not null, 8 constraint audit_reason_pk primary key (event_id, reason_id), 9 constraint event_id_reason_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 10 ) 11 / Table created. SQL> SQL> -- break out the reason and comment texts from audit_reason, since they can fill up the 8kb row size limit in sql2k SQL> create table rtd_dms8.audit_reason_text 2 ( 3 event_id number(20), 4 reason nclob, 5 constraint audit_reason_text_pk primary key (event_id), 6 constraint event_id_reason_text_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 7 ) 8 / Table created. SQL> SQL> create table rtd_dms8.audit_reason_comment 2 ( 3 event_id number(20), 4 comment_text nclob, 5 constraint audit_reason_comment_pk primary key (event_id), 6 constraint event_id_reason_comment_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 7 ) 8 / Table created. SQL> SQL> -- make some views for deletion/export reasons SQL> SQL> CREATE VIEW rtd_dms8.active_entry_delete_reason 2 AS 3 SELECT ev.event_id, MAX(reason.event_id) AS reason_event_id 4 FROM rtd_dms8.audit_reason reason INNER JOIN 5 rtd_dms8.audit_event ev ON reason.event_id < ev.event_id INNER JOIN 6 rtd_dms8.audit_event reason_event ON reason_event.event_id = reason.event_id AND reason_event.session_id = ev.session_id 7 inner join rtd_dms8.audit_entry ev_entry on ev_entry.event_id = ev.event_id 8 inner join rtd_dms8.audit_entry reason_entry on reason_entry.event_id = reason.event_id 9 WHERE reason_entry.entry_id = ev_entry.entry_id and ev.event_type IN (24, 39, 99, 110, 113, 114, 139) AND reason.reason_event_type IN (24, 39, 99, 110, 113, 114, 139) 10 GROUP BY ev.event_id 11 SQL> / View created. SQL> SQL> CREATE VIEW rtd_dms8.active_volume_delete_reason 2 AS 3 SELECT ev.event_id, MAX(reason.event_id) AS reason_event_id 4 FROM rtd_dms8.audit_reason reason INNER JOIN 5 rtd_dms8.audit_event ev ON reason.event_id < ev.event_id INNER JOIN 6 rtd_dms8.audit_event reason_event ON reason_event.event_id = reason.event_id AND reason_event.session_id = ev.session_id 7 inner join rtd_dms8.audit_volume ev_vol on ev_vol.event_id = ev.event_id 8 inner join rtd_dms8.audit_volume reason_vol on reason_vol.event_id = reason.event_id 9 WHERE reason_vol.vol_id = ev_vol.vol_id AND (ev.event_type = 20) AND (reason.reason_event_type = 20) 10 GROUP BY ev.event_id 11 SQL> / View created. SQL> SQL> CREATE VIEW rtd_dms8.active_export_reason 2 AS 3 SELECT event_id, event_id AS reason_event_id 4 FROM rtd_dms8.audit_event 5 WHERE (event_type IN (32, 33, 95)) 6 SQL> / View created. SQL> SQL> CREATE VIEW rtd_dms8.active_reason 2 AS 3 SELECT reason.event_id, r.reason_id, r.format, r.watermark, r.reason_event_type, c.comment_text, t.reason 4 FROM (SELECT event_id, reason_event_id 5 FROM rtd_dms8.active_volume_delete_reason 6 UNION ALL 7 SELECT event_id, reason_event_id 8 FROM rtd_dms8.active_entry_delete_reason 9 UNION ALL 10 SELECT event_id, reason_event_id 11 FROM rtd_dms8.active_export_reason) reason LEFT OUTER JOIN 12 rtd_dms8.audit_reason r ON reason.reason_event_id = r.event_id LEFT OUTER JOIN 13 rtd_dms8.audit_reason_comment c ON r.event_id = c.event_id LEFT OUTER JOIN 14 rtd_dms8.audit_reason_text t ON t.event_id = c.event_id 15 SQL> / View created. SQL> SQL> -- This table records briefcase exports SQL> create table rtd_dms8.audit_briefcase 2 ( 3 event_id number(20), 4 name nvarchar2(1023), 5 export_flags number(10) not null, 6 constraint audit_briefcase_pk primary key (event_id), 7 constraint event_id_briefcase_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 8 ) 9 / Table created. SQL> SQL> -- This table records attempts to purge audit logs SQL> create table rtd_dms8.audit_auditlogs 2 ( 3 event_id number(20), 4 logname nvarchar2(259), 5 startdate timestamp, 6 enddate timestamp, 7 constraint audit_auditlogs_pk primary key (event_id), 8 constraint event_id_auditlogs_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 9 ) 10 / Table created. SQL> SQL> -- This table records information about transfers for Records Management events such as SQL> -- 1) create/modify/delete transfer SQL> -- 2) confirm transfer SQL> -- * Join audit_disp_sched on event_id for information on the disposition schedule SQL> -- * Join audit_location on event_id for information on the location SQL> create table rtd_dms8.audit_transfer 2 ( 3 event_id number(20), 4 is_add number(1), -- 0 edit, 1 add 5 transfer_id number(10), 6 ordinal number(5), 7 action char(1), 8 retention number(5), 9 transfer_descrip nvarchar2(200), 10 confirmation_date timestamp, 11 constraint audit_transfer_pk primary key (event_id, transfer_id), 12 constraint event_id_transfer_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 13 ) 14 / Table created. SQL> SQL> -- This table records information about disposition schedules for Records Management events such as SQL> -- 1) create/modify/delete disposition schedule SQL> -- 2) set/unset alternate disposition schedule SQL> -- 3) confirm transfer SQL> -- 4) confirm final disposition SQL> -- This table also records information about the disposition schedule to which a transfer belongs for SQL> -- create/modify/delete transfers (see audit_transfer) SQL> create table rtd_dms8.audit_disp_sched 2 ( 3 event_id number(20), 4 is_add number(1), -- 0 edit, 1 add 5 disp_sched_id number(10), 6 disp_sched_name nvarchar2(255), 7 disp_sched_code nvarchar2(31), 8 retention number(5), 9 final_disp char(1), 10 final_disp_date timestamp, 11 final_keep char(1), 12 final_locid number(10), 13 final_location nvarchar2(255), 14 disp_sched_descrip nvarchar2(800), 15 alt_disp_id number(10), 16 alt_trigger_id number(10), 17 alt_trigger_set_date timestamp, 18 constraint audit_disp_sched_pk primary key (event_id), 19 constraint event_id_disp_sched_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 20 ) 21 / Table created. SQL> SQL> -- This table records information about cutoff criterion for Records Management events such as SQL> -- 1) create/modify/delete cutoff criterion SQL> -- This table also records information about the cutoff criterion to which a cutoff event belongs for SQL> -- create/modify/delete cutoff events SQL> create table rtd_dms8.audit_coffcrit 2 ( 3 event_id number(20), 4 is_add number(1), -- 0 edit, 1 add 5 coff_id number(10), 6 coff_name nvarchar2(255), -- should be not null, but allow for empty (error) 7 coff_type char(1), 8 coff_descrip nvarchar2(800), 9 any_event number(1), 10 link_id number(10), 11 disp_action char(1), 12 source_label nvarchar2(255), 13 target_label nvarchar2(255), 14 location_id number(10), 15 location_name nvarchar2(255), 16 constraint audit_coffcrit_pk primary key (event_id, coff_id), 17 constraint event_id_coffcrit_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 18 ) 19 / Table created. SQL> SQL> -- This table records information about event definitions for Records Management events such as SQL> -- 1) create/modify/delete event SQL> -- 2) create/modify/delete cutoff criterion event SQL> -- 3) set/unset event dates SQL> -- * Join audit_entry on event_id for the information on the entry SQL> create table rtd_dms8.audit_rm_event 2 ( 3 event_id number(20), 4 is_add number(1), -- 0 edit, 1 add 5 rm_event_id number(10), 6 rm_event_name nvarchar2(255), -- allow for null to handle unsets done by uncutoff events 7 rm_event_descrip nvarchar2(800), 8 rm_event_date timestamp, -- null for unset (unset date is in audit_event) 9 constraint audit_event_def_pk primary key (event_id, rm_event_id), 10 constraint event_id_event_def_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 11 ) 12 / Table created. SQL> SQL> -- This table records the creation/modification of record series and record folders (deletion is just an entry event) SQL> -- * Join audit_entry on event_id for information on the entry SQL> create table rtd_dms8.audit_fileplan 2 ( 3 event_id number(20), 4 series_code nvarchar2(31), -- "" for record folders 5 isperm number(1) default 0 not null, 6 review_cycle number(10), 7 coff_id number(10), 8 disp_sched_id number(10), 9 disp_auth nvarchar2(255), 10 filing_date timestamp, 11 is_cascade number(1), 12 constraint audit_fileplan_pk primary key (event_id), 13 constraint event_id_fileplan_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 14 ) 15 / Table created. SQL> SQL> -- This table records information about calendar cycles for Records Management Events SQL> create table rtd_dms8.audit_period_object 2 ( 3 event_id number(20), 4 is_add number(1), -- 0 edit, 1 add 5 period_id number(10) not null, 6 pc_code nvarchar2(3), -- should be not null, but allow for empty string 7 pc_descrip nvarchar2(200), 8 kind char(1), 9 pc_list varchar2(26), 10 constraint audit_period_object_pk primary key (event_id), 11 constraint event_id_period_object_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 12 ) 13 / Table created. SQL> SQL> -- This table records the periods involved for creating/modifying period cycles SQL> -- * Join audit_period_object on event_id for information on the period cycle SQL> -- This table also records period information for cutoff criteria events SQL> create table rtd_dms8.audit_periods 2 ( 3 event_id number(20), 4 period_ordinal number(2), 5 period_descrip nvarchar2(63), -- should be not null, but allow for empty string 6 constraint audit_periods_pk primary key (event_id, period_ordinal), 7 constraint event_id_periods_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 8 ) 9 / Table created. SQL> SQL> -- This table records information on locations for Records Management events such as SQL> -- 1) create/modify/delete location SQL> -- 2) confirm transfers SQL> -- This table also records information about the location in a transfer definition for SQL> -- create/modify/delete transfers (see audit_tran SQL> create table rtd_dms8.audit_location 2 ( 3 event_id number(20), 4 is_add number(1), -- 0 edit, 1 add 5 location_id number(10), 6 location_name nvarchar2(255), -- should be not null, but allow for empty (error) 7 address nvarchar2(255), 8 phone1 nvarchar2(31), 9 phone2 nvarchar2(31), 10 fax nvarchar2(31), 11 contact1 nvarchar2(63), 12 contact2 nvarchar2(63), 13 location_descrip nvarchar2(800), 14 constraint audit_location_pk primary key (event_id, location_id), 15 constraint event_id_location_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 16 ) 17 / Table created. SQL> SQL> -- This table records information on group_watermark SQL> -- 1) add or delete SQL> -- 2) group watermark text SQL> create table rtd_dms8.audit_group_watermark 2 ( 3 event_id number(20), 4 is_add number(1), -- 0 delete, 1 add 5 group_watermark_text nvarchar2(63), 6 constraint audit_group_watermark_pk primary key (event_id), 7 constraint event_id_group_watermark_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 8 ) 9 / Table created. SQL> SQL> ---- Journal Table ---- SQL> -- create/delete journals (bp history) -- SQL> create table rtd_dms8.audit_journal 2 ( 3 event_id number(20) not null, 4 journal_id int not null, 5 journal_name char(256) not null, 6 constraint event_id_journal_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 7 ) 8 / Table created. SQL> SQL> ---- Business Process Table ---- SQL> -- create/delete/modify/execute business process -- SQL> create table rtd_dms8.audit_business_process 2 ( 3 event_id number(20) not null, 4 bp_id number(10) not null, 5 bp_name nvarchar2(256) not null, 6 bp_type number(3), 7 constraint event_id_business_process_fk foreign key (event_id) references rtd_dms8.audit_event(event_id) ON DELETE CASCADE 8 ) 9 / Table created. SQL> SQL> -- audit views by class -- SQL> create view rtd_dms8.account_events as 2 select 3 a.*, b.trustee_sid, b.trustee_id, b.trustee_name, b.isuser, b.trustee_flags, b.privs, b.uif, 4 b.readonly, b.auditmask, b.old_auditmask, b.old_trustee_name, b.auth_type, b.group_id, b.group_name, 5 b.group_sid, b.assoc_id, b.assoc_name, c.is_apply, c.tag_id, c.tag_name, c.tag_descrip, 6 c.tag_wm_text, c.tag_wm_x, c.tag_wm_y, c.tag_wm_angle, c.tag_wm_size, c.tag_wm_required, 7 d.is_add, d.group_watermark_text 8 from rtd_dms8.audit_event a left join audit_account b on a.event_id = b.event_id 9 left join rtd_dms8.audit_tag c on a.event_id = c.event_id 10 left join rtd_dms8.audit_group_watermark d on a.event_id = d.event_id 11 where a.event_type in (5, 6, 7, 8, 9, 10, 11, 96, 120) 12 / View created. SQL> SQL> create view rtd_dms8.annotation_events as 2 select 3 a.*, b.entry_id, b.entry_uuid, b.name, b.entry_name_only, b.page_num, c.annot_item_id, c.annot_type, c.ann_comment, 4 c.stamp_id, d.ann_text 5 from rtd_dms8.audit_event a left join rtd_dms8.audit_entry b on a.event_id = b.event_id 6 left join rtd_dms8.audit_ann c on a.event_id = c.event_id 7 left join rtd_dms8.audit_ann_text d on a.event_id = d.event_id 8 where a.event_type in (42, 43, 44) 9 / View created. SQL> SQL> create view rtd_dms8.auditing_events as 2 select 3 a.*, b.trustee_id, b.trustee_name, b.trustee_sid, b.old_auditmask, b.auditmask, c.logname, 4 c.startdate, c.enddate 5 from rtd_dms8.audit_event a left join rtd_dms8.audit_account b on a.event_id = b.event_id 6 left join rtd_dms8.audit_auditlogs c on a.event_id = c.event_id 7 where a.event_type in (4, 94) 8 / View created. SQL> SQL> create view rtd_dms8.entry_events as 2 select 3 a.*, b.entry_id, b.entry_uuid, b.name, b.entry_name_only, b.dest_entry_id, b.dest_entry_uuid, b.dest_entry_name, b.dest_entry_name_only, 4 b.dest_page_num, b.lock_token, c.pset_id, c.pset_name, coalesce(c.is_set, l.is_set) is_set, d.vol_id, d.vol_name, e.tag_id, 5 e.tag_name, e.is_apply, e.tag_notes, 6 e.tag_wm_text, e.tag_wm_x, e.tag_wm_y, e.tag_wm_angle, e.tag_wm_size, e.tag_wm_required, 7 f.old_owner_sid, f.old_owner_name, f.new_owner_sid, f.new_owner_name, 8 g.link_type_id, g.link_op_type, g.source_label, g.target_label, g.link_descrip, g.target_id, 9 g.target_uuid, g.target_name, g.target_name_only, g.link_notes, h.version_group_id, h.version_number, h.version_notes, 10 i.sec_info, i.old_sec_desc, i.new_sec_desc, j.old_inherit, j.old_filter_expr, k.new_inherit, 11 k.new_filter_expr, l.prop_id, l.prop_name, l.prop_type, m.pos, m.num_val, m.str_val, m.date_val, m.bin_val 12 from rtd_dms8.audit_event a left join rtd_dms8.audit_entry b on a.event_id = b.event_id 13 left join rtd_dms8.audit_propset c on a.event_id = c.event_id 14 left join rtd_dms8.audit_volume d on a.event_id = d.event_id 15 left join rtd_dms8.audit_tag e on a.event_id = e.event_id 16 left join rtd_dms8.audit_entry_owner f on a.event_id = f.event_id 17 left join rtd_dms8.audit_link_type g on a.event_id = g.event_id 18 left join rtd_dms8.audit_version_group h on a.event_id = h.event_id 19 left join rtd_dms8.audit_sec_desc i on a.event_id = i.event_id 20 left join rtd_dms8.audit_old_folder_filter j on a.event_id = j.event_id 21 left join rtd_dms8.audit_new_folder_filter k on a.event_id = k.event_id 22 left join rtd_dms8.audit_prop l on a.event_id = l.event_id 23 left join rtd_dms8.audit_propval m on a.event_id = m.event_id 24 where a.event_type in (21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 35, 36, 45, 53, 97, 98, 99, 100, 101, 103, 104) 25 / View created. SQL> SQL> create view rtd_dms8.metadata_events as 2 select 3 a.*, b.pset_id, b.pset_name, b.pset_new_name, c.prop_name, c.prop_id, c.prop_type, c.field_pos, 4 c.new_prop_name, c.new_prop_type, c.new_field_pos, d.sec_info, d.old_sec_desc, d.new_sec_desc 5 from rtd_dms8.audit_event a left join rtd_dms8.audit_propset b on a.event_id = b.event_id 6 left join rtd_dms8.audit_prop c on a.event_id = c.event_id 7 left join rtd_dms8.audit_sec_desc d on a.event_id = d.event_id 8 where a.event_type in (46, 47, 48, 49, 50, 51, 52, 54, 55, 56, 57) 9 / View created. SQL> SQL> create view rtd_dms8.page_events as 2 select 3 a.*, b.entry_id, b.entry_uuid, b.name, b.entry_name_only, b.page_num, b.dest_entry_id, b.dest_entry_uuid, b.dest_entry_name, b.dest_entry_name_only, 4 b.dest_page_num, c.page_range 5 from rtd_dms8.audit_event a left join rtd_dms8.audit_entry b on a.event_id = b.event_id 6 left join rtd_dms8.audit_page_range c on a.event_id = c.event_id 7 where a.event_type in (37, 38, 39, 40, 41) 8 / View created. SQL> SQL> create view rtd_dms8.privileged_events as 2 select 3 a.*, b.tag_id, b.is_security, b.tag_name, b.old_tag_name, b.tag_descrip, 4 b.tag_wm_text, b.tag_wm_x, b.tag_wm_y, b.tag_wm_angle, b.tag_wm_size, b.tag_wm_required, 5 c.link_type_id, c.source_label, 6 c.target_label, c.link_descrip, c.old_source_label, c.old_target_label, c.old_link_descrip, d.option_type, 7 d.option_name, d.option_value 8 from rtd_dms8.audit_event a left join rtd_dms8.audit_tag b on a.event_id = b.event_id 9 left join rtd_dms8.audit_link_type c on a.event_id = c.event_id 10 left join rtd_dms8.audit_dboptions d on a.event_id = d.event_id 11 where a.event_type in (58, 59, 60, 61, 62, 63, 102) 12 / View created. SQL> SQL> create view rtd_dms8.record_events as 2 select 3 a.*, b.entry_id, b.entry_uuid, b.name, b.entry_name_only, b.freeze_reason, b.review_date, c.final_keep, c.final_disp, 4 c.final_locid, c.final_location, coalesce(c.disp_sched_id, e.disp_sched_id) disp_sched_id, 5 c.disp_sched_name, c.alt_trigger_set_date, d.rm_event_id, d.rm_event_name, d.rm_event_date, 6 e.series_code, e.isperm, e.review_cycle, e.coff_id, e.disp_auth, e.is_cascade, f.location_id, f.location_name, 7 g.transfer_id, g.confirmation_date 8 from rtd_dms8.audit_event a left join rtd_dms8.audit_entry b on a.event_id = b.event_id 9 left join rtd_dms8.audit_disp_sched c on a.event_id = c.event_id 10 left join rtd_dms8.audit_rm_event d on a.event_id = d.event_id 11 left join rtd_dms8.audit_fileplan e on a.event_id = e.event_id 12 left join rtd_dms8.audit_location f on a.event_id = f.event_id 13 left join rtd_dms8.audit_transfer g on a.event_id = g.event_id 14 where a.event_type in (73, 74, 75, 76, 77, 78, 79, 80, 83, 84, 85, 86, 89) 15 / View created. SQL> SQL> create view rtd_dms8.fileplan_events as 2 select 3 a.*, coalesce(b.is_add, c.is_add, d.is_add, e.is_add, f.is_add, g.is_add) is_add, b.transfer_id, 4 b.ordinal, b.action, coalesce(b.retention, c.retention) retention, b.transfer_descrip, c.disp_sched_id, 5 c.disp_sched_name, c.disp_sched_code, c.final_disp, c.final_locid, c.final_keep, c.disp_sched_descrip, 6 c.alt_disp_id, c.alt_trigger_id, coalesce(d.location_id, e.location_id) location_id, coalesce(d.location_name, e.location_name) location_name, d.address, d.phone1, d.phone2, d.fax, 7 d.contact1, d.contact2, d.location_descrip, e.coff_id, e.coff_name, e.coff_type, e.coff_descrip, 8 e.any_event, e.link_id, e.disp_action, e.source_label, e.target_label, 9 f.period_id, f.pc_code, f.pc_descrip, f.kind, g.rm_event_id, g.rm_event_name, g.rm_event_descrip, 10 h.period_descrip, h.period_ordinal, i.entry_id, i.entry_uuid, i.name, j.series_code, j.isperm, j.is_cascade 11 from rtd_dms8.audit_event a left join rtd_dms8.audit_transfer b on a.event_id = b.event_id 12 left join rtd_dms8.audit_disp_sched c on a.event_id = c.event_id 13 left join rtd_dms8.audit_location d on a.event_id = d.event_id 14 left join rtd_dms8.audit_coffcrit e on a.event_id = e.event_id 15 left join rtd_dms8.audit_period_object f on a.event_id = f.event_id 16 left join rtd_dms8.audit_rm_event g on a.event_id = g.event_id 17 left join rtd_dms8.audit_periods h on a.event_id = h.event_id 18 left join rtd_dms8.audit_entry i on a.event_id = i.event_id 19 left join rtd_dms8.audit_fileplan j on a.event_id = j.event_id 20 where a.event_type in (65, 66, 67, 68, 69, 70, 71, 72, 81, 82, 87, 88, 90, 91, 92, 93) 21 / View created. SQL> SQL> create view rtd_dms8.session_events as 2 select 3 a.*, b.sid, b.term_session_id, b.read_only 4 from rtd_dms8.audit_event a left join rtd_dms8.audit_session b on a.event_id = b.event_id 5 where a.event_type in (1, 2, 3) 6 / View created. SQL> SQL> create view rtd_dms8.view_events as 2 select 3 a.*, b.entry_id, b.entry_uuid, b.name, b.entry_name_only, b.revision 4 from rtd_dms8.audit_event a left join rtd_dms8.audit_entry b on a.event_id = b.event_id 5 where a.event_type = 34 6 / View created. SQL> SQL> create view rtd_dms8.volume_events as 2 select 3 a.*, b.vol_id, b.vol_name, b.old_vol_name, b.vol_flags, b.fixpath, b.rempath, b.maxsize, 4 c.sec_info, c.old_sec_desc, c.new_sec_desc 5 from rtd_dms8.audit_event a left join rtd_dms8.audit_volume b on a.event_id = b.event_id 6 left join rtd_dms8.audit_sec_desc c on a.event_id = c.event_id 7 where a.event_type in (12, 15, 16, 17, 18, 19, 20) 8 / View created. SQL> SQL> -- the schema is a little less obvious for transfer events, so make a "helper" view SQL> create view rtd_dms8.transfer_events as 2 select 3 a.*, b.is_add, b.transfer_id, b.ordinal, b.action, b.retention, b.transfer_descrip, b.confirmation_date, 4 c.disp_sched_id, c.disp_sched_name, d.location_id, d.location_name 5 from rtd_dms8.audit_event a left join rtd_dms8.audit_transfer b on a.event_id = b.event_id 6 left join rtd_dms8.audit_disp_sched c on a.event_id = c.event_id 7 left join rtd_dms8.audit_location d on a.event_id = d.event_id 8 where a.event_type in (65, 66, 84) 9 / View created. SQL> SQL> create view rtd_dms8.export_events as 2 select 3 a.*, b.entry_id, b.entry_uuid, coalesce(b.name, f.name) name, b.entry_name_only, c.reason_id, c.format, c.watermark, 4 d.reason, e.comment_text, f.export_flags, g.page_range 5 from rtd_dms8.audit_event a left join rtd_dms8.audit_entry b on a.event_id = b.event_id 6 left join rtd_dms8.audit_reason c on a.event_id = c.event_id 7 left join rtd_dms8.audit_reason_text d on a.event_id = d.event_id 8 left join rtd_dms8.audit_reason_comment e on a.event_id = e.event_id 9 left join rtd_dms8.audit_briefcase f on a.event_id = f.event_id 10 left join rtd_dms8.audit_page_range g on a.event_id = g.event_id 11 where a.event_type in (32, 33, 95) 12 / View created. SQL> SQL> create view rtd_dms8.search_events as 2 select 3 a.*, b.search_text 4 from rtd_dms8.audit_event a left join rtd_dms8.audit_search b on a.event_id = b.event_id 5 where a.event_type = 64 6 / View created. SQL> SQL> create view rtd_dms8.all_events as 2 select 3 a.*, b.sid, b.term_session_id, b.read_only, c.option_type, c.option_name, c.option_value, 4 d.vol_id, d.vol_name, d.old_vol_name, d.vol_flags, d.fixpath, d.rempath, d.maxsize, 5 e.trustee_sid, e.trustee_id, e.trustee_name, e.isuser, e.trustee_flags, e.privs, e.uif, 6 e.readonly, e.auditmask, e.old_auditmask, e.old_trustee_name, e.auth_type, e.group_id, 7 e.group_name, e.group_sid, e.assoc_id, e.assoc_name, f.pset_id, f.pset_name, f.pset_new_name, 8 coalesce(f.is_set, g.is_set) is_set, g.prop_name, g.prop_id, g.prop_type, g.field_pos, g.new_prop_name, 9 g.new_prop_type, g.new_field_pos, h.pos, h.str_val, h.num_val, h.date_val, h.bin_val, i.entry_id, 10 i.entry_uuid, coalesce(i.name, y.name) name, i.page_part, i.page_num, i.revision, i.dest_entry_id, i.dest_entry_uuid, 11 i.dest_entry_name, i.dest_page_num, i.freeze_reason, i.review_date, j.old_inherit, j.old_filter_expr, 12 k.new_inherit, k.new_filter_expr, l.old_owner_sid, l.old_owner_name, l.new_owner_sid, l.new_owner_name, 13 m.page_range, coalesce(n.annot_item_id, o.annot_item_id) annot_item_id, n.annot_type, n.stamp_id, 14 n.ann_comment, n.xPos, n.yPos, n.color, n.rotation, n.direction, n.ann_access, n.startPos, n.endPos, o.top_pos, o.bottom_pos, 15 o.left_pos, o.right_pos, o.rect_num, p.ann_text, q.is_security, q.is_apply, q.tag_id, q.tag_name, q.tag_descrip, 16 q.old_tag_name, q.tag_notes, 17 q.tag_wm_text, q.tag_wm_x, q.tag_wm_y, q.tag_wm_angle, q.tag_wm_size, q.tag_wm_required, 18 r.link_type_id, r.link_op_type, coalesce(r.source_label, ac.source_label) source_label, coalesce(r.target_label, ac.target_label) target_label, r.link_descrip, 19 r.target_id, r.target_uuid, r.target_name, r.target_name_only, r.old_source_label, r.old_target_label, r.old_link_descrip, 20 r.link_notes, s.version_group_id, s.version_number, s.version_notes, t.search_text, u.sec_info, u.old_sec_desc, 21 u.new_sec_desc, v.reason_id, v.format, v.watermark, v.reason, v.comment_text, y.export_flags, z.logname, 22 z.startdate, z.enddate, coalesce(aa.is_add, ab.is_add, ac.is_add, af.is_add, ah.is_add, ai.is_add) is_add, 23 aa.transfer_id, aa.ordinal, aa.action, coalesce(aa.retention, ab.retention) retention, aa.transfer_descrip, 24 aa.confirmation_date, coalesce(ab.disp_sched_id, ae.disp_sched_id) disp_sched_id, ab.disp_sched_name, ab.disp_sched_code, 25 ab.final_keep, ab.final_disp, ab.final_locid, ab.final_location, ab.disp_sched_descrip, ab.alt_disp_id, ab.alt_trigger_id, 26 ab.alt_trigger_set_date, coalesce(ac.coff_id, ae.coff_id) coff_id, ac.coff_name, ac.coff_type, ac.coff_descrip, 27 ac.any_event, ac.link_id, ac.disp_action, coalesce(ac.location_id, ah.location_id) location_id, coalesce(ac.location_name, ah.location_name) location_name, ad.rm_event_id, 28 ad.rm_event_name, ad.rm_event_descrip, ad.rm_event_date, ae.series_code, ae.isperm, ae.review_cycle, ae.disp_auth, 29 ae.filing_date, ae.is_cascade, af.period_id, af.pc_code, af.pc_descrip, ag.period_ordinal, ag.period_descrip, 30 ah.address, ah.phone1, ah.phone2, ah.fax, ah.contact1, ah.contact2, ah.location_descrip, 31 ai.group_watermark_text, 32 bp.bp_id, bp.bp_name, bp.bp_type, 33 aj.journal_id, aj.journal_name 34 from rtd_dms8.audit_event a left join rtd_dms8.audit_session b on a.event_id = b.event_id 35 left join rtd_dms8.audit_dboptions c on a.event_id = c.event_id 36 left join rtd_dms8.audit_volume d on a.event_id = d.event_id 37 left join rtd_dms8.audit_account e on a.event_id = e.event_id 38 left join rtd_dms8.audit_propset f on a.event_id = f.event_id 39 left join rtd_dms8.audit_prop g on a.event_id = g.event_id 40 left join rtd_dms8.audit_propval h on a.event_id = h.event_id 41 left join rtd_dms8.audit_entry i on a.event_id = i.event_id 42 left join rtd_dms8.audit_old_folder_filter j on a.event_id = j.event_id 43 left join rtd_dms8.audit_new_folder_filter k on a.event_id = k.event_id 44 left join rtd_dms8.audit_entry_owner l on a.event_id = l.event_id 45 left join rtd_dms8.audit_page_range m on a.event_id = m.event_id 46 left join rtd_dms8.audit_ann n on a.event_id = n.event_id 47 left join rtd_dms8.audit_ann_rects o on a.event_id = o.event_id 48 left join rtd_dms8.audit_ann_access oa on a.event_id = oa.event_id 49 left join rtd_dms8.audit_ann_text p on a.event_id = p.event_id 50 left join rtd_dms8.audit_tag q on a.event_id = q.event_id 51 left join rtd_dms8.audit_link_type r on a.event_id = r.event_id 52 left join rtd_dms8.audit_version_group s on a.event_id = s.event_id 53 left join rtd_dms8.audit_search t on a.event_id = t.event_id 54 left join rtd_dms8.audit_sec_desc u on a.event_id = u.event_id 55 left join rtd_dms8.active_reason v on a.event_id = v.event_id 56 left join rtd_dms8.audit_briefcase y on a.event_id = y.event_id 57 left join rtd_dms8.audit_auditlogs z on a.event_id = z.event_id 58 left join rtd_dms8.audit_transfer aa on a.event_id = aa.event_id 59 left join rtd_dms8.audit_disp_sched ab on a.event_id = ab.event_id 60 left join rtd_dms8.audit_coffcrit ac on a.event_id = ac.event_id 61 left join rtd_dms8.audit_rm_event ad on a.event_id = ad.event_id 62 left join rtd_dms8.audit_fileplan ae on a.event_id = ae.event_id 63 left join rtd_dms8.audit_period_object af on a.event_id = af.event_id 64 left join rtd_dms8.audit_periods ag on a.event_id = ag.event_id 65 left join rtd_dms8.audit_location ah on a.event_id = ah.event_id 66 left join rtd_dms8.audit_group_watermark ai on a.event_id = ai.event_id 67 left join rtd_dms8.audit_business_process bp on a.event_id = bp.event_id 68 left join rtd_dms8.audit_journal aj on a.event_id = aj.event_id 69 / View created. SQL> SQL> Create index rtd_dms8.ix_audit_custom_event_id on rtd_dms8.audit_custom(event_id) 2 / Create index rtd_dms8.ix_audit_custom_event_id on rtd_dms8.audit_custom(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_custom_prop_event_id on rtd_dms8.audit_custom_prop(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_custom_propval_event_id on rtd_dms8.audit_custom_propval(event_id) 2 / Create index rtd_dms8.ix_audit_custom_propval_event_id on rtd_dms8.audit_custom_propval(event_id) * ERROR at line 1: ORA-00972: identifier is too long SQL> Create index rtd_dms8.ix_audit_session_event_id on rtd_dms8.audit_session(event_id) 2 / Create index rtd_dms8.ix_audit_session_event_id on rtd_dms8.audit_session(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_dboptions_event_id on rtd_dms8.audit_dboptions(event_id) 2 / Create index rtd_dms8.ix_audit_dboptions_event_id on rtd_dms8.audit_dboptions(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_volume_event_id on rtd_dms8.audit_volume(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_account_event_id on rtd_dms8.audit_account(event_id) 2 / Create index rtd_dms8.ix_audit_account_event_id on rtd_dms8.audit_account(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_ldap_event_id on rtd_dms8.audit_ldap(event_id) 2 / Create index rtd_dms8.ix_audit_ldap_event_id on rtd_dms8.audit_ldap(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_propset_event_id on rtd_dms8.audit_propset(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_prop_event_id on rtd_dms8.audit_prop(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_propval_event_id on rtd_dms8.audit_propval(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_entry_event_id on rtd_dms8.audit_entry(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_old_folder_filter_event_id on rtd_dms8.audit_old_folder_filter(event_id) 2 / Create index rtd_dms8.ix_audit_old_folder_filter_event_id on rtd_dms8.audit_old_folder_filter(event_id) * ERROR at line 1: ORA-00972: identifier is too long SQL> Create index rtd_dms8.ix_audit_new_folder_filter_event_id on rtd_dms8.audit_new_folder_filter(event_id) 2 / Create index rtd_dms8.ix_audit_new_folder_filter_event_id on rtd_dms8.audit_new_folder_filter(event_id) * ERROR at line 1: ORA-00972: identifier is too long SQL> Create index rtd_dms8.ix_audit_entry_owner_event_id on rtd_dms8.audit_entry_owner(event_id) 2 / Create index rtd_dms8.ix_audit_entry_owner_event_id on rtd_dms8.audit_entry_owner(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_page_range_event_id on rtd_dms8.audit_page_range(event_id) 2 / Create index rtd_dms8.ix_audit_page_range_event_id on rtd_dms8.audit_page_range(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_ann_event_id on rtd_dms8.audit_ann(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_ann_add_modify_event_id on rtd_dms8.audit_ann_add_modify(event_id) 2 / Create index rtd_dms8.ix_audit_ann_add_modify_event_id on rtd_dms8.audit_ann_add_modify(event_id) * ERROR at line 1: ORA-00972: identifier is too long SQL> Create index rtd_dms8.ix_audit_ann_rects_event_id on rtd_dms8.audit_ann_rects(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_ann_access_event_id on rtd_dms8.audit_ann_access(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_ann_text_event_id on rtd_dms8.audit_ann_text(event_id) 2 / Create index rtd_dms8.ix_audit_ann_text_event_id on rtd_dms8.audit_ann_text(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_tag_event_id on rtd_dms8.audit_tag(event_id) 2 / Create index rtd_dms8.ix_audit_tag_event_id on rtd_dms8.audit_tag(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_link_type_event_id on rtd_dms8.audit_link_type(event_id) 2 / Create index rtd_dms8.ix_audit_link_type_event_id on rtd_dms8.audit_link_type(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_version_group_event_id on rtd_dms8.audit_version_group(event_id) 2 / Create index rtd_dms8.ix_audit_version_group_event_id on rtd_dms8.audit_version_group(event_id) * ERROR at line 1: ORA-00972: identifier is too long SQL> Create index rtd_dms8.ix_audit_versioning_event_id on rtd_dms8.audit_versioning(event_id) 2 / Create index rtd_dms8.ix_audit_versioning_event_id on rtd_dms8.audit_versioning(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_search_event_id on rtd_dms8.audit_search(event_id) 2 / Create index rtd_dms8.ix_audit_search_event_id on rtd_dms8.audit_search(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_sec_desc_event_id on rtd_dms8.audit_sec_desc(event_id) 2 / Create index rtd_dms8.ix_audit_sec_desc_event_id on rtd_dms8.audit_sec_desc(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_reason_event_id on rtd_dms8.audit_reason(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_reason_text_event_id on rtd_dms8.audit_reason_text(event_id) 2 / Create index rtd_dms8.ix_audit_reason_text_event_id on rtd_dms8.audit_reason_text(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_reason_comment_event_id on rtd_dms8.audit_reason_comment(event_id) 2 / Create index rtd_dms8.ix_audit_reason_comment_event_id on rtd_dms8.audit_reason_comment(event_id) * ERROR at line 1: ORA-00972: identifier is too long SQL> Create index rtd_dms8.ix_audit_briefcase_event_id on rtd_dms8.audit_briefcase(event_id) 2 / Create index rtd_dms8.ix_audit_briefcase_event_id on rtd_dms8.audit_briefcase(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_auditlogs_event_id on rtd_dms8.audit_auditlogs(event_id) 2 / Create index rtd_dms8.ix_audit_auditlogs_event_id on rtd_dms8.audit_auditlogs(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_transfer_event_id on rtd_dms8.audit_transfer(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_disp_sched_event_id on rtd_dms8.audit_disp_sched(event_id) 2 / Create index rtd_dms8.ix_audit_disp_sched_event_id on rtd_dms8.audit_disp_sched(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_coffcrit_event_id on rtd_dms8.audit_coffcrit(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_rm_event_event_id on rtd_dms8.audit_rm_event(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_fileplan_event_id on rtd_dms8.audit_fileplan(event_id) 2 / Create index rtd_dms8.ix_audit_fileplan_event_id on rtd_dms8.audit_fileplan(event_id) * ERROR at line 1: ORA-01408: such column list already indexed SQL> Create index rtd_dms8.ix_audit_period_object_event_id on rtd_dms8.audit_period_object(event_id) 2 / Create index rtd_dms8.ix_audit_period_object_event_id on rtd_dms8.audit_period_object(event_id) * ERROR at line 1: ORA-00972: identifier is too long SQL> Create index rtd_dms8.ix_audit_periods_event_id on rtd_dms8.audit_periods(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_location_event_id on rtd_dms8.audit_location(event_id) 2 / Index created. SQL> Create index rtd_dms8.ix_audit_group_watermark_event_id on rtd_dms8.audit_group_watermark(event_id) 2 / Create index rtd_dms8.ix_audit_group_watermark_event_id on rtd_dms8.audit_group_watermark(event_id) * ERROR at line 1: ORA-00972: identifier is too long SQL> Create index rtd_dms8.ix_audit_cert_event_id on rtd_dms8.audit_cert(event_id) 2 / Create index rtd_dms8.ix_audit_cert_event_id on rtd_dms8.audit_cert(event_id) * ERROR at line 1: ORA-00942: table or view does not exist SQL> Create index rtd_dms8.ix_audit_doc_sign_event_id on rtd_dms8.audit_doc_sign(event_id) 2 / Create index rtd_dms8.ix_audit_doc_sign_event_id on rtd_dms8.audit_doc_sign(event_id) * ERROR at line 1: ORA-00942: table or view does not exist SQL> Create index rtd_dms8.ix_audit_class_event_id on rtd_dms8.audit_class(event_id) 2 / Create index rtd_dms8.ix_audit_class_event_id on rtd_dms8.audit_class(event_id) * ERROR at line 1: ORA-00942: table or view does not exist SQL> Create index rtd_dms8.ix_audit_email_event_id on rtd_dms8.audit_email(event_id) 2 / Create index rtd_dms8.ix_audit_email_event_id on rtd_dms8.audit_email(event_id) * ERROR at line 1: ORA-00942: table or view does not exist SQL> Create index rtd_dms8.ix_audit_parent_event_id on rtd_dms8.audit_parent(event_id) 2 / Create index rtd_dms8.ix_audit_parent_event_id on rtd_dms8.audit_parent(event_id) * ERROR at line 1: ORA-00942: table or view does not exist SQL>