-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathsdua_usage.sql
220 lines (218 loc) · 7.69 KB
/
sdua_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
--------------------------------------------------------------------------------
-- OraDBA - Oracle Database Infrastructure and Security, 5630 Muri, Switzerland
--------------------------------------------------------------------------------
-- Name......: sdua_usage.sql
-- Author....: Stefan Oehrli (oes) [email protected]
-- Editor....: Stefan Oehrli
-- Date......: 2023.07.06
-- Revision..:
-- Purpose...: Show Unified Audit trail storage usage
-- Notes.....:
-- Reference.: SYS (or grant manually to a DBA)
-- License...: Apache License Version 2.0, January 2004 as shown
-- at http://www.apache.org/licenses/
--------------------------------------------------------------------------------
SET PAGESIZE 66 HEADING ON VERIFY OFF
SET FEEDBACK OFF SQLCASE UPPER NEWPAGE 1
SET SQLCASE mixed
ALTER SESSION SET nls_date_format='DD.MM.YYYY HH24:MI:SS';
ALTER SESSION SET nls_timestamp_format='DD.MM.YYYY HH24:MI:SS';
COLUMN name format a80 wrap heading "Name"
COLUMN value format a40 wrap heading "Value"
SPOOL sdua_usage.log
SELECT
name,
value
FROM
(
SELECT
1 AS id,
'Sum of audit records' AS name,
to_char(COUNT(*)) AS value
FROM
unified_audit_trail
UNION
SELECT
2 AS id,
'Sum of audit records for current DBID ('
|| con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
|| ')' AS name,
to_char(COUNT(*)) AS value
FROM
unified_audit_trail
WHERE
dbid = con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
UNION
SELECT
3 AS id,
'Oldest audit record for current DBID ('
|| con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
|| ')' AS name,
to_char(MIN(event_timestamp),
'DD.MM.YYYY HH24:MI:SS') AS value
FROM
unified_audit_trail
WHERE
dbid = con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
UNION
SELECT
4 AS id,
'Newest audit record for current DBID ('
|| con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
|| ')' AS name,
to_char(MAX(event_timestamp),
'DD.MM.YYYY HH24:MI:SS') AS value
FROM
unified_audit_trail
WHERE
dbid = con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
UNION
SELECT
5 AS id,
'Last archive timestamp for current DBID ('
|| con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
|| ')' AS name,
to_char(MAX(last_archive_ts),
'DD.MM.YYYY HH24:MI:SS') AS value
FROM
dba_audit_mgmt_last_arch_ts
WHERE
audit_trail = 'UNIFIED AUDIT TRAIL'
UNION
SELECT
6 AS id,
'Sum of audit records older than event timestamp for current DBID ('
|| con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
|| ')' AS name,
to_char(COUNT(u.event_timestamp)) AS value
FROM
unified_audit_trail u,
dba_audit_mgmt_last_arch_ts t
WHERE
t.audit_trail = 'UNIFIED AUDIT TRAIL'
AND u.event_timestamp < t.last_archive_ts
UNION
SELECT
*
FROM
(
SELECT
7 AS id,
'Sum of audit records for foreign DBID' AS name,
to_char(COUNT(*)) AS value
FROM
unified_audit_trail
WHERE
dbid <> con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
)
WHERE
value > 0
UNION
SELECT
*
FROM
(
SELECT
8 AS id,
'Oldest audit record for foreign DBID' AS name,
to_char(MIN(event_timestamp),
'DD.MM.YYYY HH24:MI:SS') AS value
FROM
unified_audit_trail
WHERE
dbid <> con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
)
WHERE
value IS NOT NULL
UNION
SELECT
*
FROM
(
SELECT
9 AS id,
'Newest audit record for foreign DBID' AS name,
to_char(MAX(event_timestamp),
'DD.MM.YYYY HH24:MI:SS') AS value
FROM
unified_audit_trail
WHERE
dbid <> con_id_to_dbid(sys_context('USERENV', 'CON_ID'))
)
WHERE
value IS NOT NULL
UNION
SELECT
10 AS id,
'Tablespace in use' AS name,
LISTAGG(DISTINCT tablespace_name, ', ') WITHIN GROUP(
ORDER BY
tablespace_name
) AS value
FROM
dba_segments
WHERE
segment_name = 'AUD$UNIFIED'
UNION
SELECT
11 AS id,
'Amount of audit trail data partitions' AS name,
to_char(COUNT(partition_name)) AS value
FROM
dba_tab_partitions
WHERE
table_name = 'AUD$UNIFIED'
UNION
SELECT
12 AS id,
'Audit Trail data partition interval' AS name,
INTERVAL AS value
FROM
dba_part_tables
WHERE
owner = 'AUDSYS'
AND table_name = 'AUD$UNIFIED'
UNION
SELECT
13 AS id,
'Audit Trail data partition type' AS name,
partitioning_type AS value
FROM
dba_part_tables
WHERE
owner = 'AUDSYS'
AND table_name = 'AUD$UNIFIED'
UNION
SELECT
14 AS id,
'Sum of audit trail segments (data, lob, index)' AS name,
to_char(dbms_xplan.format_size(SUM(bytes))) AS value
FROM
dba_segments
WHERE
owner = 'AUDSYS'
UNION
SELECT
15 AS id,
'Sum of audit trail segments (data, lob, index) for tablespace ' || tablespace_name AS name,
to_char(dbms_xplan.format_size(SUM(bytes))) AS value
FROM
dba_segments
WHERE
owner = 'AUDSYS'
GROUP BY
tablespace_name
UNION
SELECT
16 AS id,
'Average audit record length (last analyzed '
|| to_char(last_analyzed, 'DD.MM.YYYY HH24:MI:SS')
|| ')' AS name,
to_char(avg_row_len) AS value
FROM
dba_tables
WHERE
owner = 'AUDSYS'
);
SPOOL OFF
-- EOF -------------------------------------------------------------------------