J5m69gah

1/*
2###############################################################################
3#        DO NOT MODIFY THIS SCRIPT OR ITS OUTPUT WITHOUT PRIOR NOTICE!        #
4###############################################################################
5
6<< REQUIREMENTS >>
7
8gdbiX requires Oracle 11gR2 or later.
9
10
11
12<< RATIONALE >>
13
14gdbiX gathers various information about the state of the database, Oracle instance, 
15and some application-specific data (e.g., locales). In many cases the collected data 
16is sufficient for a full initial evaluation of potential issues in areas such as 
17performance or maintenance. 
18
19gdbiX does not make any changes to the instance(s) or database on which it is executed.
20
21
22
23<< USAGE >>
24
25sqlplus <exec_user>[/<password>]@<tnsname> @gdbiX.sql <user>
26
27<exec_user>
28	The user executing the script. Must have sufficient privileges (DBA
29	role or SELECT ANY (TABLE|DICTIONARY)).
30
31<password>
32	The password for the user above (optional).
33
34<tnsname>
35	The TNS name of the database server.
36
37<user>
38	The user that is being used by the application servers to connect to
39	the database.
40
41NLS_LANG must be set to the correct locale (i.e., the one used by the database). 
42
43
44
45<< EXAMPLES >>
46
47  # sqlplus -S system/intershop@ISSERVER @gdbiX.sql INTERSHOP
48
49This command runs the script as the SYSTEM user, which in this example is
50identified by the password "intershop". The TNS name ISSERVER will be used
51to connect to the database (given that a corresponding TNS entry exists). 
52All user-related data will be gathered for INTERSHOP, which would be the 
53username the application uses to connect to the database.
54
55  # sqlplus / as sysdba @gdbiX.sql INTERSHOP
56
57This command can be used if you have remote access to a database server, and
58a copy of gdbiX.sql exists on it. Data will be gathered for the user INTERSHOP,
59as described in the example above. Note that the user executing this command
60must be in the dba group on UNIX-like systems or ORADBA on Windows systems.
61
62
63
64<< OUTPUT >>
65
66The script collects data in a XML file in the current directory, having the 
67following naming scheme: gdbiX_v<version>__<target_schema>__<date>.xml
68
69
70
71<< SYSTEMVIEW ACCOUNT >>
72
73If you prefer to run this script using a less privileged account than SYS or
74SYSTEM, use the following commands to create an account with a set of privileges 
75that are required to properly execute gdbiX:
76
77CREATE USER systemview IDENTIFIED BY systemview;
78GRANT CREATE SESSION TO systemview;
79GRANT SELECT ANY TABLE, SELECT ANY DICTIONARY TO systemview;
80
81
82
83<< TROUBLE SHOOTING >>
84
85If you receive one of ORA-2000{1,2,3,4} when attempting to run the script, see
86below for details and how to avoid those errors:
87
88ORA-20001 <user> does not have sufficient privileges to run this script.
89------------------------------------------------------------------------
90You have to run this script with sufficient privileges, in order to allow it
91to access tables/views in the database, which belong to the user SYS. See 
92section << USAGE >> and << SYSTEMVIEW ACCOUNT >>.
93
94
95ORA-20002 Cannot determine user privileges.
96-------------------------------------------
97The script was not able to check user privileges and assumes they are not
98appropriate. This should never happen, unless you revoke SELECT privileges on
99USER_[ROLE|SYS]_PRIVS for the user executing the script. Contact your DBA or
100try using a different user to execute the script (e.g. SYSTEM).
101
102
103ORA-20003 The schema of user <user> is no valid target for gdbiX.
104-----------------------------------------------------------------
105The specified <user> does not exist or the schema does not look like a valid
106Enfinity/IS7 schema. See << USAGE >> for details.
107
108
109ORA-20004 This script requires Oracle 11gR2 or later.
110--------------------------------------------------
111Please contact Intershop Customer Support.
112
113
114Segmentation faults in sqlplus binary (Linux only)
115--------------------------------------------------
116In some cases executing the script causes SQL*Plus to crash due to segmentation 
117fault or errors similar to  
118
119*** glibc detected *** sqlplus: free(): invalid next size (normal): 0x0000000000fa3240 *** or
120*** glibc detected *** sqlplus: malloc(): memory corruption: 0x0000000000fa34b0 ***
121
122This can be fixed by setting NLS_LANG to the correct locale and MALLOC_CHECK to 0 before running
123the script.
124
125
126
127<< AUTHOR >>
128
129(c) 2007, 2018 Intershop Communications AG, All rights reserved.
130
131*/
132
133-- gdbiX version
134DEFINE version='0.9.4'
135
136-- user as specified on command line (mixed case version)
137PROMPT * If the script asks for a "value of 1", enter the user name that is being used 
138PROMPT * by the application servers to connect to the database.
139PROMPT
140
141DEFINE user_cli=&1
142
143SET APPINFO gdbiX;
144
145SET SERVEROUTPUT ON SIZE UNLIMITED;
146
147SET ARRAYSIZE 200;
148
149SET LINESIZE 150
150SET PAGESIZE 0;
151
152-- This attempts to allocate roughly 200M of memory to accommodate for (potentially) very large query results, 
153-- which may cause an ORA-24817. Try to reduce the values in incremental steps (e.g., start with 50% of the
154-- current value) until the error disappears. Note that this may result in partially corrupted content of the script
155-- output. If the value(s) are too low, the output may become unusable.
156SET LONG 200000000;
157SET LONGCHUNKSIZE 200000000;
158
159SET TRIMSPOOL OFF;
160SET WRAP ON;
161
162SET CONCAT .
163
164SET FEEDBACK OFF;
165SET SHOWMODE OFF;
166SET TIMING OFF;
167SET VERIFY OFF;
168SET ECHO OFF;
169
170SET NULL NULL;
171
172
173-- convert supplied user name (schema) to upper case
174COLUMN user_uc NEW_VALUE user_uc NOPRINT
175SELECT UPPER('&user_cli') user_uc FROM DUAL;
176
177-- version info
178EXEC dbms_output.put_line('gdbiX &version, (c) 2007, 2018 Intershop Communications AG');
179
180-- if we raise an error below, we want to abort script execution
181WHENEVER SQLERROR EXIT FAILURE
182
183-- check privileges of the user executing the script
184EXEC dbms_output.put_line(' -> Running prerequisite checks...');
185EXEC dbms_output.put_line(' ->  Checking user privileges...');
186
187DECLARE
188	perms NUMBER := 0;
189BEGIN
190	SELECT
191	(
192		(
193			SELECT NVL(SUM(2), 0)
194			FROM user_role_privs
195			WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') 
196		)
197	+
198		(
199			SELECT NVL(SUM(1), 0)
200			FROM user_sys_privs
201			WHERE privilege IN('SELECT ANY TABLE', 'SELECT ANY DICTIONARY')
202		)
203	)
204	INTO perms
205	FROM dual;
206
207	-- if at least one of the conditions above is met, `perms` will be at least 2
208	IF perms < 2 THEN
209		dbms_output.put_line('FAILED');
210		raise_application_error(-20001, USER ||
211			' does not have sufficient privileges to run this script.');
212	END IF;
213
214	EXCEPTION
215		WHEN no_data_found THEN
216				dbms_output.put_line('FAILED');
217				raise_application_error(-20002, 'Unable to determine user privileges for ' || USER || '.');
218END;
219/
220
221EXEC dbms_output.put_line(' ->  Passed.');
222
223-- check if the specified schema exists (<schema>)
224EXEC dbms_output.put_line(' ->  Checking user-specified schema...');
225
226DECLARE
227	test NUMBER := 0;
228BEGIN
229	SELECT 1
230	INTO test
231	FROM sys.dba_tables
232	WHERE owner = '&user_uc'
233	AND UPPER(table_name) = 'PROCESS';
234
235	EXCEPTION
236		WHEN OTHERS THEN
237			dbms_output.put_line('FAILED');
238			raise_application_error(-20003,	'User `&user_uc` does not exist or its schema is no valid target for gdbiX.');
239END;
240/
241
242EXEC dbms_output.put_line(' ->  Passed.');
243
244-- check if running on Oracle 11gR2 or later
245EXEC dbms_output.put_line(' ->  Checking Oracle version...');
246
247BEGIN
248	IF (dbms_db_version.version = 11 AND dbms_db_version.release < 2) OR (dbms_db_version.version < 11)
249	THEN
250		raise_application_error(-20004,	'Oracle 11g R2 or later required to run this script.');
251	END IF;
252END;
253/
254
255EXEC dbms_output.put_line(' ->  Passed.');
256EXEC dbms_output.put_line(' -> Prerequisite checks were successful.');
257EXEC dbms_output.put_line(' -> Gathering data... (This may take a couple of minutes, press CTRL+C to abort if needed)');
258
259
260-- continue gathering data, even if some of the queries fail
261WHENEVER SQLERROR CONTINUE
262
263-- turn off console output
264SET TERMOUT OFF
265
266-- setup and start spooling
267COLUMN exec_date NEW_VALUE exec_date NOPRINT
268SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD_HH24_MI_SS') exec_date FROM DUAL;
269SPOOL gdbiX_v&version.__&user_uc.__&exec_date..xml
270
271-- XML header
272EXEC dbms_output.put_line('<gdbiX>');
273
274-- start of generated content
275
276EXEC dbms_output.put_line('<!--');
277SET TERMOUT ON
278EXEC dbms_output.put_line(' ->  Autotask Clients (Oracle) [Jobs]');
279SET TERMOUT OFF
280EXEC dbms_output.put_line('-->');
281
282SELECT XMLELEMENT
283(
284	"DB_AUTOTASK_CLIENT",
285	XMLAGG
286	(
287		XMLELEMENT
288		(
289			"ROW",
290			XMLFOREST
291			(
292				client_name,
293				status,
294				consumer_group,
295				client_tag,
296				priority_override,
297				attributes,
298				window_group,
299				service_name,
300				resource_percentage,
301				use_resource_estimates,
302				ROUND(
303					EXTRACT(DAY FROM mean_job_duration) * 86400 +
304					EXTRACT(HOUR FROM mean_job_duration) * 3600 +
305					EXTRACT(MINUTE FROM mean_job_duration) * 60 +
306					EXTRACT(SECOND FROM mean_job_duration)
307				, 2) AS mean_job_duration,
308				ROUND(
309					EXTRACT(DAY FROM mean_job_cpu) * 86400 +
310					EXTRACT(HOUR FROM mean_job_cpu) * 3600 +
311					EXTRACT(MINUTE FROM mean_job_cpu) * 60 +
312					EXTRACT(SECOND FROM mean_job_cpu)
313				, 2) AS mean_job_cpu,
314				ROUND(
315					EXTRACT(DAY FROM mean_job_duration) * 86400 +
316					EXTRACT(HOUR FROM mean_job_duration) * 3600 +
317					EXTRACT(MINUTE FROM mean_job_duration) * 60 +
318					EXTRACT(SECOND FROM mean_job_duration)
319				, 2) AS mean_job_duration,
320				mean_job_attempts,
321				mean_incoming_tasks_7_days,
322				mean_incoming_tasks_30_days,
323				ROUND(
324					EXTRACT(DAY FROM total_cpu_last_7_days) * 86400 +
325					EXTRACT(HOUR FROM total_cpu_last_7_days) * 3600 +
326					EXTRACT(MINUTE FROM total_cpu_last_7_days) * 60 +
327					EXTRACT(SECOND FROM total_cpu_last_7_days)
328				, 2) AS total_cpu_last_7_days,
329				ROUND(
330					EXTRACT(DAY FROM total_cpu_last_30_days) * 86400 +
331					EXTRACT(HOUR FROM total_cpu_last_30_days) * 3600 +
332					EXTRACT(MINUTE FROM total_cpu_last_30_days) * 60 +
333					EXTRACT(SECOND FROM total_cpu_last_30_days)
334				, 2) AS total_cpu_last_30_days,
335				ROUND(
336					EXTRACT(DAY FROM max_duration_last_7_days) * 86400 +
337					EXTRACT(HOUR FROM max_duration_last_7_days) * 3600 +
338					EXTRACT(MINUTE FROM max_duration_last_7_days) * 60 +
339					EXTRACT(SECOND FROM max_duration_last_7_days)
340				, 2) AS max_duration_last_7_days,
341				ROUND(
342					EXTRACT(DAY FROM max_duration_last_30_days) * 86400 +
343					EXTRACT(HOUR FROM max_duration_last_30_days) * 3600 +
344					EXTRACT(MINUTE FROM max_duration_last_30_days) * 60 +
345					EXTRACT(SECOND FROM max_duration_last_30_days)
346				, 2) AS max_duration_last_30_days,
347				ROUND(
348					EXTRACT(DAY FROM window_duration_last_7_days) * 86400 +
349					EXTRACT(HOUR FROM window_duration_last_7_days) * 3600 +
350					EXTRACT(MINUTE FROM window_duration_last_7_days) * 60 +
351					EXTRACT(SECOND FROM window_duration_last_7_days)
352				, 2) AS window_duration_last_7_days,
353				ROUND(
354					EXTRACT(DAY FROM window_duration_last_30_days) * 86400 +
355					EXTRACT(HOUR FROM window_duration_last_30_days) * 3600 +
356					EXTRACT(MINUTE FROM window_duration_last_30_days) * 60 +
357					EXTRACT(SECOND FROM window_duration_last_30_days)
358				, 2) AS window_duration_last_30_days
359			)
360		) ORDER BY client_name, status
361	)
362).getclobval()
363FROM dba_autotask_client		
364/
365
366EXEC dbms_output.put_line('<!--');
367SET TERMOUT ON
368EXEC dbms_output.put_line(' ->  Auxiliary Statistics [Locking / Statistics]');
369SET TERMOUT OFF
370EXEC dbms_output.put_line('-->');
371
372SELECT XMLELEMENT
373(
374	"INSTANCE_AUX_STATISTICS",
375	XMLAGG
376	(
377		XMLELEMENT
378		(
379			"ROW",
380			XMLFOREST
381			(
382				sname,
383				pname,
384				pval1,
385				pval2
386			)
387		)
388	)
389).getclobval()
390FROM sys.aux_stats$
391/
392
393EXEC dbms_output.put_line('<!--');
394SET TERMOUT ON
395EXEC dbms_output.put_line(' ->  Buffer Cache Advisory [SGA/PGA Advisories]');
396SET TERMOUT OFF
397EXEC dbms_output.put_line('-->');
398
399SELECT XMLELEMENT
400(
401	"INSTANCE_DB_CACHE_ADVICE",
402	XMLAGG
403	(
404		XMLELEMENT
405		(
406			"ROW",
407			XMLFOREST
408			(
409				inst_id,
410				id,
411				name,
412				block_size,
413				advice_status,
414				size_for_estimate,
415				size_factor,
416				buffers_for_estimate,
417				estd_physical_read_factor,
418				estd_physical_reads
419			)
420		) ORDER BY inst_id, id, size_factor
421	)
422).getclobval()
423FROM gv$db_cache_advice
424/
425
426EXEC dbms_output.put_line('<!--');
427SET TERMOUT ON
428EXEC dbms_output.put_line(' ->  Build Versions [Enfinity/Intershop]');
429SET TERMOUT OFF
430EXEC dbms_output.put_line('-->');
431
432SELECT XMLELEMENT
433(
434	"APP_BUILD",
435	XMLAGG(
436		XMLELEMENT
437		(
438			"ROW",
439			XMLFOREST
440			(
441				version,
442				internalname,
443				displayname,
444				build
445			)
446		) ORDER BY internalname
447	)
448).getclobval()
449FROM &user_uc..cartridgeinformation
450/
451
452EXEC dbms_output.put_line('<!--');
453SET TERMOUT ON
454EXEC dbms_output.put_line(' ->  CPU Time [SQL]');
455SET TERMOUT OFF
456EXEC dbms_output.put_line('-->');
457
458WITH top_20_inst AS (
459	SELECT inst_id,
460		sql_id
461	FROM (
462		SELECT inst_id,
463			sql_id,
464			ROW_NUMBER() OVER (PARTITION BY inst_id ORDER BY SUM(cpu_time) DESC) rnum
465		FROM gv$sql
466		WHERE (module != 'gdbiX' OR module IS NULL)
467		GROUP BY inst_id, sql_id
468	)
469	WHERE rnum <= 20
470),
471stats AS (
472	SELECT DISTINCT inst_id,
473		SUM(buffer_gets) OVER (PARTITION BY inst_id) AS buffer_gets_inst,
474		SUM(buffer_gets) OVER () AS buffer_gets_all,
475		SUM(cpu_time) OVER (PARTITION BY inst_id) AS cpu_time_inst,
476		SUM(cpu_time) OVER () AS cpu_time_all,
477		SUM(elapsed_time) OVER (PARTITION BY inst_id) AS elapsed_time_inst,
478		SUM(elapsed_time) OVER () AS elapsed_time_all,
479		SUM(disk_reads) OVER (PARTITION BY inst_id) AS disk_reads_inst,
480		SUM(disk_reads) OVER () AS disk_reads_all,
481		SUM(physical_read_requests) OVER (PARTITION BY inst_id) AS physical_read_requests_inst,
482		SUM(physical_read_requests) OVER () AS physical_read_requests_all,
483		SUM(physical_read_bytes) OVER (PARTITION BY inst_id) AS physical_read_bytes_inst,
484		SUM(physical_read_bytes) OVER () AS physical_read_bytes_all,
485		SUM(physical_write_requests) OVER (PARTITION BY inst_id) AS physical_write_requests_inst,
486		SUM(physical_write_requests) OVER () AS physical_write_requests_all,
487		SUM(physical_write_bytes) OVER (PARTITION BY inst_id) AS physical_write_bytes_inst,
488		SUM(physical_write_bytes) OVER () AS physical_write_bytes_all,
489		SUM(optimized_phy_read_requests) OVER (PARTITION BY inst_id) AS opt_phy_read_req_inst,
490		SUM(optimized_phy_read_requests) OVER () AS opt_phy_read_req_all
491	FROM gv$sql
492)
493SELECT XMLELEMENT(
494	"SQL_CPU_TIME_EXT",
495	XMLAGG(
496		XMLELEMENT(
497			"ROW",
498			XMLFOREST(
499				inst_id,
500				sql_id,	
501				child_number,
502				plan_hash_value,
503				sql_profile,
504				parsing_schema_name,
505				module,
506				sql_text,
507				sharable_mem,
508				persistent_mem,
509				runtime_mem,
510				sorts_exec,
511				fetches,
512				end_of_fetch_count,
513				px_servers_executions,
514				loads,
515				invalidations,
516				is_bind_sensitive,
517				is_shareable,
518				io_interconnect_bytes,				
519				phy_read_requests,
520				phy_read_requests_exec,
521				phy_read_requests_inst,
522				phy_read_requests_all,
523				phy_read_bytes,
524				phy_read_bytes_exec,
525				phy_read_bytes_inst,
526				phy_read_bytes_all,
527				phy_write_requests,
528				phy_write_requests_exec,
529				phy_write_requests_inst,
530				phy_write_requests_all,
531				phy_write_bytes,
532				phy_write_bytes_exec,
533				phy_write_bytes_inst,
534				phy_write_bytes_all,
535				opt_phy_read_requests,
536				opt_phy_read_requests_exec,
537				opt_phy_read_requests_inst,
538				opt_phy_read_requests_all,				
539				cpu_time,
540				cpu_time_exec,
541				cpu_time_all,
542				cpu_time_inst,
543				elapsed_time,
544				elapsed_time_exec,
545				elapsed_time_inst,
546				elapsed_time_all,
547				buffer_gets,
548				buffer_gets_exec,
549				buffer_gets_row,
550				buffer_gets_all,
551				buffer_gets_inst,
552				disk_reads,
553				disk_reads_exec,
554				disk_reads_row,
555				disk_reads_all,
556				disk_reads_inst,
557				executions,
558				parse_calls,
559				execute_parse_ratio,
560				first_load_time,
561				last_load_time,
562				last_active_time,		
563				application_wait_time,
564				application_wait_time_exec,
565				concurrency_wait_time,
566				concurrency_wait_time_exec,
567				cluster_wait_time,
568				cluster_wait_time_exec,
569				user_io_wait_time,
570				user_io_wait_time_exec,
571				rows_processed,
572				rows_processed_exec,
573				optimizer_cost
574            )
575        ) ORDER BY cpu_time_all DESC NULLS LAST
576    )
577).getclobval() 
578FROM (
579	SELECT inst_id,
580		parsing_schema_name,
581		child_number,
582		sql_id,
583		REPLACE(module, CHR(0), '') AS module,
584		loads,
585		invalidations,
586		sharable_mem,
587		persistent_mem,
588		runtime_mem,
589		ROUND(sorts / NULLIF(executions, 0), 4) AS sorts_exec,
590		fetches,
591		end_of_fetch_count,
592		px_servers_executions,
593		plan_hash_value,
594		is_bind_sensitive,
595		is_shareable,
596		io_interconnect_bytes,
597		sql_profile,
598		physical_read_requests AS phy_read_requests,
599		ROUND(physical_read_requests / NULLIF(executions, 0), 4) AS phy_read_requests_exec,
600		ROUND(physical_read_requests / NULLIF(physical_read_requests_inst, 0) * 100, 2) AS phy_read_requests_inst,
601		ROUND(physical_read_requests / NULLIF(physical_read_requests_all, 0) * 100, 2) AS phy_read_requests_all,
602		physical_read_bytes AS phy_read_bytes,
603		ROUND(physical_read_bytes / NULLIF(executions, 0), 4) AS phy_read_bytes_exec,
604		ROUND(physical_read_bytes / NULLIF(physical_read_bytes_inst, 0) * 100, 2) AS phy_read_bytes_inst,
605		ROUND(physical_read_bytes / NULLIF(physical_read_bytes_all, 0) * 100, 2) AS phy_read_bytes_all,
606		physical_write_requests AS phy_write_requests,
607		ROUND(physical_write_requests / NULLIF(executions, 0), 4) AS phy_write_requests_exec,
608		ROUND(physical_write_requests / NULLIF(physical_write_requests_inst, 0) * 100, 2) AS phy_write_requests_inst,
609		ROUND(physical_write_requests / NULLIF(physical_write_requests_all, 0) * 100, 2) AS phy_write_requests_all,
610		physical_write_bytes AS phy_write_bytes,
611		ROUND(physical_write_bytes / NULLIF(executions, 0), 4) AS phy_write_bytes_exec,
612		ROUND(physical_write_bytes / NULLIF(physical_write_bytes_inst, 0) * 100, 2) AS phy_write_bytes_inst,
613		ROUND(physical_write_bytes / NULLIF(physical_write_bytes_all, 0) * 100, 2) AS phy_write_bytes_all,
614		optimized_phy_read_requests AS opt_phy_read_requests,
615		ROUND(optimized_phy_read_requests / NULLIF(executions, 0), 4) AS opt_phy_read_requests_exec,
616		ROUND(optimized_phy_read_requests / NULLIF(opt_phy_read_req_inst, 0) * 100, 2) AS opt_phy_read_requests_inst,
617		ROUND(optimized_phy_read_requests / NULLIF(opt_phy_read_req_all, 0) * 100, 2) AS opt_phy_read_requests_all,
618		REPLACE(sql_fulltext, CHR(0), '') AS sql_text,
619		cpu_time / 1000 AS cpu_time,
620		ROUND(cpu_time / (1000 * NULLIF(executions, 0)), 4) cpu_time_exec,
621		ROUND(100 * (cpu_time / NULLIF(cpu_time_inst, 0)), 2) cpu_time_inst,
622		ROUND(100 * (cpu_time / NULLIF(cpu_time_all, 0)), 2) AS cpu_time_all,
623		elapsed_time / 1000 AS elapsed_time,
624		ROUND(elapsed_time / (1000 * NULLIF(executions, 0)), 4) AS elapsed_time_exec,
625		ROUND(100 * (elapsed_time / NULLIF(elapsed_time_inst, 0)), 2) AS elapsed_time_inst,
626		ROUND(100 * (elapsed_time / NULLIF(elapsed_time_all, 0)), 2) AS elapsed_time_all,
627		buffer_gets,
628		ROUND(buffer_gets / NULLIF(executions, 0), 4) AS buffer_gets_exec,
629		ROUND(100 * (buffer_gets / NULLIF(buffer_gets_all, 0)), 2) AS buffer_gets_all,
630		ROUND(100 * (buffer_gets / NULLIF(buffer_gets_inst, 0)), 2) buffer_gets_inst,
631		ROUND(buffer_gets / NULLIF(rows_processed, 0), 2) buffer_gets_row,
632		disk_reads,
633		ROUND(disk_reads / NULLIF(executions, 0), 4) AS disk_reads_exec,
634		ROUND(100 * (disk_reads / NULLIF(disk_reads_all, 0)), 2) AS disk_reads_all,
635		ROUND(100 * (disk_reads / NULLIF(disk_reads_inst, 0)), 2) disk_reads_inst,
636		ROUND(disk_reads / NULLIF(rows_processed, 0) * 100, 2) disk_reads_row,
637		executions,
638		parse_calls,
639		ROUND(100 * (1 - parse_calls / NULLIF(executions, 0)), 2) AS execute_parse_ratio,
640		TO_CHAR(TO_TIMESTAMP(first_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS first_load_time,
641		TO_CHAR(TO_TIMESTAMP(last_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS last_load_time,
642		TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI:SS') AS last_active_time,
643		application_wait_time / 1000 AS application_wait_time,
644		ROUND(application_wait_time / (1000 * NULLIF(executions, 0)), 4) AS application_wait_time_exec,
645		concurrency_wait_time / 1000 AS concurrency_wait_time,
646		ROUND(concurrency_wait_time / (1000 * NULLIF(executions, 0)), 4) AS concurrency_wait_time_exec,
647		cluster_wait_time / 1000 AS cluster_wait_time,
648		ROUND(cluster_wait_time / (1000 * NULLIF(executions, 0)), 4) AS cluster_wait_time_exec,
649		user_io_wait_time / 1000 AS user_io_wait_time,
650		ROUND(user_io_wait_time / (1000 * NULLIF(executions, 0)), 4) AS user_io_wait_time_exec,
651		rows_processed,
652		ROUND(rows_processed / NULLIF(executions, 0), 4) AS rows_processed_exec,
653		optimizer_cost
654	FROM gv$sql
655		JOIN top_20_inst USING (sql_id, inst_id)
656		JOIN stats USING (inst_id)
657)
658/
659
660EXEC dbms_output.put_line('<!--');
661SET TERMOUT ON
662EXEC dbms_output.put_line(' ->  Components [RDBMS]');
663SET TERMOUT OFF
664EXEC dbms_output.put_line('-->');
665
666SELECT XMLELEMENT
667(
668	"ORACLE_REGISTRY",
669	XMLAGG
670	(
671		XMLELEMENT
672		(
673			"ROW",
674			XMLFOREST
675			(
676				comp_id,
677				comp_name,
678				version,
679				status,
680				modified,
681				control,
682				schema,
683				procedure,
684				startup,
685				parent_id
686			)
687		) ORDER BY comp_name
688	)
689).getclobval()
690FROM dba_registry
691/
692
693EXEC dbms_output.put_line('<!--');
694SET TERMOUT ON
695EXEC dbms_output.put_line(' ->  Constraints [Constraints]');
696SET TERMOUT OFF
697EXEC dbms_output.put_line('-->');
698
699WITH constraint_columns AS
700(
701	SELECT table_name,
702		constraint_name,
703		LTRIM(SYS_CONNECT_BY_PATH(column_name, ', '), ', ') cons_columns
704	FROM 
705	(
706		SELECT table_name,
707			constraint_name,
708			column_name,
709			ROW_NUMBER() OVER (PARTITION BY constraint_name ORDER BY position) col,
710			COUNT(*) OVER (PARTITION BY constraint_name) totalcol
711		FROM dba_cons_columns
712		WHERE owner = '&user_uc.'
713	)
714	WHERE col = totalcol
715	CONNECT BY PRIOR col = col - 1
716	AND PRIOR constraint_name = constraint_name
717	START WITH col = 1
718), constraints_subset AS (
719	SELECT 
720		EXTRACTVALUE(xmlseq.object_value, '/ROW/TABLE_NAME') AS table_name,      
721		EXTRACTVALUE(xmlseq.object_value, '/ROW/CONSTRAINT_NAME') AS constraint_name,
722		EXTRACTVALUE(xmlseq.object_value, '/ROW/SEARCH_CONDITION') AS search_condition,
723		EXTRACTVALUE(xmlseq.object_value, '/ROW/CONSTRAINT_TYPE') AS constraint_type,
724		EXTRACTVALUE(xmlseq.object_value, '/ROW/R_CONSTRAINT_NAME') AS r_constraint_name,
725		EXTRACTVALUE(xmlseq.object_value, '/ROW/STATUS') AS status,
726		EXTRACTVALUE(xmlseq.object_value, '/ROW/VALIDATED') AS validated,
727		EXTRACTVALUE(xmlseq.object_value, '/ROW/DEFERRABLE') AS deferrable,
728		EXTRACTVALUE(xmlseq.object_value, '/ROW/DEFERRED') AS deferred,
729		EXTRACTVALUE(xmlseq.object_value, '/ROW/INDEX_NAME') AS index_name,
730		EXTRACTVALUE(xmlseq.object_value, '/ROW/LAST_CHANGE') AS last_change,
731		EXTRACTVALUE(xmlseq.object_value, '/ROW/DELETE_RULE') AS delete_rule
732	FROM (
733		SELECT XMLTYPE(dbms_xmlgen.getxml(
734			'SELECT table_name, 
735				constraint_name, 
736				search_condition, 
737				constraint_type, 
738				r_constraint_name, 
739				status, 
740				validated, 
741				deferrable, 
742				deferred, 
743				index_name, 
744				last_change, 
745				delete_rule 
746			FROM dba_constraints 
747			WHERE owner = ''&user_uc.'''
748			)
749		) AS xml
750    	FROM DUAL
751    ) dc_xml, 
752    TABLE(XMLSEQUENCE(EXTRACT(dc_xml.xml, '/ROWSET/ROW'))) xmlseq
753)
754SELECT XMLELEMENT
755(
756	"USR_CONSTRAINTS_EXT",
757	XMLAGG
758	(
759		XMLELEMENT
760		(
761			"ROW",
762			XMLFOREST
763			(
764				constraint_type,
765				constraint_name,
766				search_condition,
767				delete_rule,
768				table_name,
769				columns,
770				r_table_name,
771				r_columns,
772				r_constraint_name,
773				status,
774				validated,
775				deferrable AS is_deferrable,
776				deferred AS initially_deferred,
777				index_name,
778				TO_CHAR(TO_DATE(last_change), 'YYYY-MM-DD HH24:MI:SS') AS last_change
779			)
780		) ORDER BY table_name, columns, constraint_type
781	)
782).getclobval()
783FROM
784(
785	SELECT dc.constraint_type,
786		dc.constraint_name,
787		dc.search_condition,
788		dc.delete_rule,
789		cc_child.table_name,
790		cc_child.cons_columns columns,
791		cc_parent.table_name r_table_name,  
792		cc_parent.cons_columns r_columns,
793		dc.r_constraint_name,
794		dc.status,
795		dc.validated,
796		dc.deferrable,
797		dc.deferred,
798		dc.index_name,
799		dc.last_change
800	FROM constraints_subset dc,
801		constraint_columns cc_parent,
802		constraint_columns cc_child
803	WHERE dc.r_constraint_name = cc_parent.constraint_name
804	AND dc.constraint_name = cc_child.constraint_name
805	AND dc.constraint_type = 'R'
806	UNION ALL
807	SELECT dc.constraint_type,
808		dc.constraint_name,
809		dc.search_condition,
810		dc.delete_rule,
811		cc.table_name,
812		cc.cons_columns columns,
813		null,
814		null,
815		null,
816		dc.status,
817		dc.validated,
818		dc.deferrable,
819		dc.deferred,
820		dc.index_name,
821		dc.last_change
822	FROM constraints_subset dc,
823		constraint_columns cc
824	WHERE dc.constraint_name = cc.constraint_name	
825	AND constraint_type != 'R'
826)
827/
828
829EXEC dbms_output.put_line('<!--');
830SET TERMOUT ON
831EXEC dbms_output.put_line(' ->  Context Indexes (Condition) [Indexes]');
832SET TERMOUT OFF
833EXEC dbms_output.put_line('-->');
834
835DECLARE
836	TYPE pending_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
837	pending_table pending_table_type;
838	token NUMBER;
839	negative_list NUMBER;
840	func_map NUMBER;
841BEGIN
842	dbms_output.put_line('<CONTEXT_INDEX_CONDITION>');
843
844	FOR pending IN
845	(
846		SELECT pnd_index_name, count(*) pcount
847		FROM ctxsys.ctx_pending, dba_indexes
848		WHERE pnd_index_owner = owner
849		AND pnd_index_name = index_name
850		AND index_type = 'DOMAIN'
851		AND owner = '&user_uc.'
852		GROUP BY pnd_index_name
853	)
854	LOOP
855		pending_table(pending.pnd_index_name) := pending.pcount;
856	END LOOP;
857
858	FOR idx IN
859	(
860		SELECT idx_name
861		FROM ctxsys.ctx_indexes
862		WHERE idx_status = 'INDEXED'
863		AND idx_owner = '&user_uc.'
864		ORDER BY idx_name
865	)
866	LOOP
867		dbms_output.put_line('<ROW>');
868		dbms_output.put_line('<INDEX_NAME>' || idx.idx_name || '</INDEX_NAME>');
869
870		BEGIN
871			EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID = ''stmt_token'' FOR SELECT COUNT(*) FROM &user_uc..DR$' || idx.idx_name || '$I';
872
873			SELECT MAX(cardinality) KEEP (DENSE_RANK LAST ORDER BY id) 
874			INTO token 
875			FROM plan_table						
876			WHERE statement_id = 'stmt_token';
877
878			-- remove plan for current stmt from plan table to avoid duplicate statement IDs if plan table is not a GTT 
879			DELETE FROM plan_table 
880			WHERE statement_id = 'stmt_token';
881
882			dbms_output.put_line('<TOKEN>' || token || '</TOKEN>');
883
884			EXCEPTION
885				WHEN others THEN dbms_output.put_line('<TOKEN>0</TOKEN>');
886		END;
887
888		BEGIN
889			EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID = ''stmt_negative_list'' FOR SELECT COUNT(*) FROM &user_uc..DR$' || idx.idx_name || '$N';
890
891			SELECT MAX(cardinality) KEEP (DENSE_RANK LAST ORDER BY id) 
892			INTO negative_list 
893			FROM plan_table 
894			WHERE statement_id = 'stmt_negative_list';
895
896			-- remove plan for current stmt from plan table to avoid duplicate statement IDs if plan table is not a GTT 
897			DELETE FROM plan_table 
898			WHERE statement_id = 'stmt_negative_list';
899
900			dbms_output.put_line('<NEGATIVE_LIST>' || negative_list || '</NEGATIVE_LIST>');
901
902			EXCEPTION
903				WHEN others THEN dbms_output.put_line('<NEGATIVE_LIST>0</NEGATIVE_LIST>');
904		END;
905
906		BEGIN
907			EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID = ''stmt_func_map'' FOR SELECT COUNT(*) FROM &user_uc..DR$' || idx.idx_name || '$K';
908
909			SELECT MAX(cardinality) KEEP (DENSE_RANK LAST ORDER BY id) 
910			INTO func_map 
911			FROM plan_table 
912			WHERE statement_id = 'stmt_func_map';
913
914			-- remove plan for current stmt from plan table to avoid duplicate statement IDs if plan table is not a GTT 
915			DELETE FROM plan_table 
916			WHERE statement_id = 'stmt_func_map';
917
918			dbms_output.put_line('<FUNC_MAP>' || func_map || '</FUNC_MAP>');	
919
920			EXCEPTION
921				WHEN others THEN dbms_output.put_line('<FUNC_MAP>0</FUNC_MAP>');
922		END;
923
924		BEGIN
925			dbms_output.put_line('<PENDING_UPDATES>' || pending_table(idx.idx_name) || '</PENDING_UPDATES>');
926
927			EXCEPTION
928				WHEN no_data_found THEN	dbms_output.put_line('<PENDING_UPDATES>0</PENDING_UPDATES>');
929		END;
930
931		dbms_output.put_line('</ROW>');
932	END LOOP;
933
934	dbms_output.put_line('</CONTEXT_INDEX_CONDITION>');
935END;
936/
937
938EXEC dbms_output.put_line('<!--');
939SET TERMOUT ON
940EXEC dbms_output.put_line(' ->  Context Indexes (Preference Values) [Indexes]');
941SET TERMOUT OFF
942EXEC dbms_output.put_line('-->');
943
944SELECT XMLELEMENT
945(
946	"CONTEXT_PREFERENCE_VALUES",
947	XMLAGG
948	(
949		XMLELEMENT
950		(
951			"ROW",
952			XMLFOREST
953			(
954				prv_preference,
955				prv_attribute,
956				prv_value
957			)
958		) ORDER BY prv_preference, prv_attribute
959	)
960).getclobval()
961FROM ctxsys.ctx_preference_values
962WHERE prv_owner = '&user_uc.'
963/
964
965EXEC dbms_output.put_line('<!--');
966SET TERMOUT ON
967EXEC dbms_output.put_line(' ->  Context Indexes (Section Groups) [Indexes]');
968SET TERMOUT OFF
969EXEC dbms_output.put_line('-->');
970
971SELECT XMLELEMENT
972(
973	"CONTEXT_SECTION_GROUPS",
974	XMLAGG
975	(
976		XMLELEMENT
977		(
978			"ROW",
979			XMLFOREST
980			(
981				sgp_name,
982				sgp_type
983			)
984		) ORDER BY sgp_name
985	)
986).getclobval()
987FROM ctxsys.ctx_section_groups
988WHERE sgp_owner = '&user_uc.'		
989/
990
991EXEC dbms_output.put_line('<!--');
992SET TERMOUT ON
993EXEC dbms_output.put_line(' ->  Context Indexes (Status) [Indexes]');
994SET TERMOUT OFF
995EXEC dbms_output.put_line('-->');
996
997SELECT XMLELEMENT
998(
999	"CONTEXT_INDEX_STATUS",
1000	XMLAGG
1001	(
1002		XMLELEMENT
1003		(
1004			"ROW",
1005			XMLFOREST
1006			(
1007				index_name,
1008				status,
1009				domidx_status,
1010				domidx_opstatus,
1011				ctx_status
1012			)
1013		) ORDER BY index_name
1014	)
1015).getclobval()
1016FROM
1017(
1018	SELECT index_name,
1019		status,
1020		domidx_status,
1021		domidx_opstatus,
1022		idx_status ctx_status
1023	FROM dba_indexes LEFT JOIN ctxsys.ctx_indexes ON (owner = idx_owner AND index_name = idx_name)
1024	WHERE index_type = 'DOMAIN'
1025	AND owner = '&user_uc.'
1026)
1027/
1028
1029EXEC dbms_output.put_line('<!--');
1030SET TERMOUT ON
1031EXEC dbms_output.put_line(' ->  Context Indexes (Stoplists) [Indexes]');
1032SET TERMOUT OFF
1033EXEC dbms_output.put_line('-->');
1034
1035SELECT XMLELEMENT
1036(
1037	"CONTEXT_STOPLISTS",
1038	XMLAGG
1039	(
1040		XMLELEMENT
1041		(
1042			"ROW",
1043			XMLFOREST
1044			(
1045				spl_name,
1046				spl_count,
1047				spl_type
1048			)
1049		) ORDER BY spl_owner, spl_name
1050	)
1051).getclobval()
1052FROM ctxsys.ctx_stoplists
1053WHERE spl_owner = '&user_uc.'		
1054/
1055
1056EXEC dbms_output.put_line('<!--');
1057SET TERMOUT ON
1058EXEC dbms_output.put_line(' ->  Context Indexes (Stopwords) [Indexes]');
1059SET TERMOUT OFF
1060EXEC dbms_output.put_line('-->');
1061
1062SELECT XMLELEMENT
1063(
1064	"CONTEXT_STOPWORDS",
1065	XMLAGG
1066	(
1067		XMLELEMENT
1068		(
1069			"ROW",
1070			XMLFOREST
1071			(
1072				spw_stoplist,
1073				spw_type,
1074				spw_word,
1075				spw_language
1076			)
1077		) ORDER BY spw_owner, spw_stoplist, spw_type, spw_word
1078	)
1079).getclobval()
1080FROM ctxsys.ctx_stopwords
1081WHERE spw_owner = '&user_uc.'		
1082/
1083
1084EXEC dbms_output.put_line('<!--');
1085SET TERMOUT ON
1086EXEC dbms_output.put_line(' ->  Current Sessions [Sessions]');
1087SET TERMOUT OFF
1088EXEC dbms_output.put_line('-->');
1089
1090SELECT XMLELEMENT
1091(
1092	"SQL_SESSION",
1093	XMLAGG
1094	(
1095		XMLELEMENT
1096		(
1097			"ROW",
1098			XMLFOREST
1099			(
1100				inst_id,
1101				sid,
1102				serial# AS serial_num,
1103				uga_mem_byte,
1104				pga_mem_byte,
1105				username,
1106				client_info,
1107				module,
1108				action,
1109				status,
1110				machine,
1111				program,
1112				osuser,
1113				TO_CHAR(logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time,
1114				last_call_et,
1115				sql_id,
1116				sql_text,
1117				prev_sql_id,
1118				prev_sql_text,
1119				event,
1120				wait_class,
1121				p1,
1122				p1text,
1123				p2,
1124				p2text,
1125				p3,
1126				p3text,
1127				wait_time,
1128				seconds_in_wait,
1129				state
1130			)
1131		) ORDER BY status, last_call_et
1132	)
1133).getclobval()
1134FROM (
1135	WITH mem_stats AS (
1136			SELECT inst_id, sid, name, value
1137			FROM SYS.gv_$sesstat JOIN SYS.gv_$statname USING (inst_id, statistic#)
1138			WHERE name IN ('session pga memory', 'session uga memory')
1139		)
1140	SELECT ROW_NUMBER() OVER (PARTITION BY ses.inst_id, sid, serial# ORDER BY ses.inst_id, sid, serial#) rn,
1141		ses.inst_id,
1142		sid,
1143		serial#,
1144		(
1145			SELECT value
1146			FROM mem_stats
1147			WHERE inst_id = ses.inst_id
1148			AND sid = ses.sid
1149			AND name = 'session uga memory'
1150		) uga_mem_byte,
1151		(
1152			SELECT value
1153			FROM mem_stats
1154			WHERE inst_id = ses.inst_id
1155			AND sid = ses.sid
1156			AND name = 'session pga memory'
1157		) pga_mem_byte,
1158		username,
1159		lockwait,
1160		client_info,
1161		REPLACE(ses.module, CHR(0), ' ') AS module,
1162		REPLACE(ses.action, CHR(0), ' ') AS action,
1163		status,
1164		REPLACE(machine, CHR(0), ' ') AS machine,
1165		REPLACE(program, CHR(0), ' ') AS program,
1166		osuser,
1167		logon_time,
1168		last_call_et,
1169		ses.sql_id,
1170		REPLACE(sa1.sql_fulltext, CHR(0), ' ') sql_text,
1171		ses.prev_sql_id,
1172		REPLACE(sa2.sql_fulltext, CHR(0), ' ') prev_sql_text,event,
1173		wait_class,
1174		p1,
1175		p1text,
1176		p2,
1177		p2text,
1178		p3,
1179		p3text,
1180		wait_time,
1181		seconds_in_wait,
1182		state
1183	FROM gv$session ses 
1184		LEFT JOIN gv$sqlarea sa1 ON (ses.sql_id = sa1.sql_id) 
1185		LEFT JOIN gv$sqlarea sa2 ON (ses.prev_sql_id = sa2.sql_id)	
1186)
1187WHERE rn < 2	
1188/
1189
1190EXEC dbms_output.put_line('<!--');
1191SET TERMOUT ON
1192EXEC dbms_output.put_line(' ->  DBMS_STATS Parameters [Database]');
1193SET TERMOUT OFF
1194EXEC dbms_output.put_line('-->');
1195
1196SELECT XMLELEMENT
1197(
1198	"DB_OPTSTAT_HIST_CONTROL",
1199	XMLAGG
1200	(
1201		XMLELEMENT
1202		(
1203			"ROW",
1204			XMLFOREST
1205			(
1206				sname AS parameter,
1207				sval1,
1208				TO_CHAR(sval2, 'YYYY-MM-DD HH24:MI:SS') AS last_modified,
1209				spare1,
1210				spare2,
1211				spare3,
1212				spare4,
1213				spare5,
1214				TO_CHAR(spare6, 'YYYY-MM-DD HH24:MI:SS') AS spare6
1215			)
1216		) ORDER BY sname
1217	)
1218).getclobval()
1219FROM sys.optstat_hist_control$
1220/
1221
1222EXEC dbms_output.put_line('<!--');
1223SET TERMOUT ON
1224EXEC dbms_output.put_line(' ->  Data Files [Database Files]');
1225SET TERMOUT OFF
1226EXEC dbms_output.put_line('-->');
1227
1228SELECT XMLELEMENT
1229(
1230	"DB_DATA_FILES",
1231	XMLAGG
1232	(
1233		XMLELEMENT
1234		(
1235			"ROW",
1236			XMLFOREST
1237			(
1238				file_name,
1239				file_id,
1240				tablespace_name,
1241				bytes,
1242				blocks,
1243				status,
1244				relative_fno,
1245				autoextensible,
1246				maxbytes,
1247				maxblocks,
1248				increment_by,
1249				user_bytes,
1250				user_blocks
1251			)
1252		) ORDER BY tablespace_name, file_name
1253	)
1254).getclobval()
1255FROM dba_data_files		
1256/
1257
1258EXEC dbms_output.put_line('<!--');
1259SET TERMOUT ON
1260EXEC dbms_output.put_line(' ->  Database Jobs (Non-Scheduler) [Jobs]');
1261SET TERMOUT OFF
1262EXEC dbms_output.put_line('-->');
1263
1264SELECT XMLELEMENT
1265(
1266	"DB_JOBS",
1267	XMLAGG
1268	(
1269		XMLELEMENT
1270		(
1271			"ROW",
1272			XMLFOREST
1273			(
1274				job,
1275				log_user,
1276				priv_user,
1277				schema_user,
1278				TO_CHAR(last_date, 'YYYY-MM-DD HH24:MI:SS') AS last_date,
1279				TO_CHAR(next_date, 'YYYY-MM-DD HH24:MI:SS') AS next_date,
1280				ROUND(total_time, 2) AS total_time,
1281				broken,
1282				interval,
1283				failures,
1284				what,
1285				instance
1286			)
1287		) ORDER BY log_user
1288	)
1289).getclobval()
1290FROM dba_jobs
1291/
1292
1293EXEC dbms_output.put_line('<!--');
1294SET TERMOUT ON
1295EXEC dbms_output.put_line(' ->  Database Jobs (Scheduler) [Jobs]');
1296SET TERMOUT OFF
1297EXEC dbms_output.put_line('-->');
1298
1299SELECT XMLELEMENT
1300(
1301	"DB_JOBS_SCHEDULER",
1302	XMLAGG
1303	(
1304		XMLELEMENT
1305		(
1306			"ROW",
1307			XMLFOREST
1308			(
1309				owner,
1310				job_name,
1311				job_creator,
1312				program_owner,
1313				program_name,
1314				job_type,
1315				job_action,
1316				schedule_owner,
1317				schedule_name,
1318				schedule_type,
1319				TO_CHAR(start_date, 'YYYY-MM-DD HH24:MI:SS') AS start_date,
1320				TO_CHAR(end_date, 'YYYY-MM-DD HH24:MI:SS') AS end_date,
1321				repeat_interval,
1322				job_class,
1323				enabled,
1324				auto_drop,
1325				restartable,
1326				state,
1327				job_priority,
1328				run_count,
1329				max_runs,
1330				failure_count,
1331				max_failures,
1332				retry_count,
1333				TO_CHAR(last_start_date, 'YYYY-MM-DD HH24:MI:SS') AS last_start_date,
1334				ROUND(
1335					EXTRACT(DAY FROM last_run_duration) * 86400 +
1336					EXTRACT(HOUR FROM last_run_duration) * 3600 +
1337					EXTRACT(MINUTE FROM last_run_duration) * 60 +
1338					EXTRACT(SECOND FROM last_run_duration)
1339				, 2) AS last_run_duration,
1340				TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') AS next_run_date,
1341				ROUND(
1342					EXTRACT(DAY FROM schedule_limit) * 86400 +
1343					EXTRACT(HOUR FROM schedule_limit) * 3600 +
1344					EXTRACT(MINUTE FROM schedule_limit) * 60 +
1345					EXTRACT(SECOND FROM schedule_limit)
1346				, 2) AS schedule_limit,
1347				ROUND(
1348					EXTRACT(DAY FROM max_run_duration) * 86400 +
1349					EXTRACT(HOUR FROM max_run_duration) * 3600 +
1350					EXTRACT(MINUTE FROM max_run_duration) * 60 +
1351					EXTRACT(SECOND FROM max_run_duration)
1352				, 2) AS max_run_duration,
1353				logging_level,
1354				stop_on_window_close,
1355				instance_stickiness,
1356				raise_events,
1357				system,
1358				job_weight,
1359				comments
1360			)
1361		) ORDER BY owner, job_name
1362	)
1363).getclobval()
1364FROM dba_scheduler_jobs		
1365/
1366
1367EXEC dbms_output.put_line('<!--');
1368SET TERMOUT ON
1369EXEC dbms_output.put_line(' ->  Database Links [Database]');
1370SET TERMOUT OFF
1371EXEC dbms_output.put_line('-->');
1372
1373SELECT XMLELEMENT
1374(
1375	"DB_DATABASE_LINKS",
1376	XMLAGG
1377	(
1378		XMLELEMENT
1379		(
1380			"ROW",
1381			XMLFOREST
1382			(
1383				owner,
1384				db_link,
1385				username,
1386				host,
1387				TO_CHAR(created, 'YYYY-MM-DD HH24:MI:SS') AS created
1388			)
1389		) ORDER BY owner, db_link
1390	)
1391).getclobval()
1392FROM dba_db_links		
1393/
1394
1395EXEC dbms_output.put_line('<!--');
1396SET TERMOUT ON
1397EXEC dbms_output.put_line(' ->  Database NLS Settings [NLS]');
1398SET TERMOUT OFF
1399EXEC dbms_output.put_line('-->');
1400
1401SELECT XMLELEMENT
1402(
1403	"DB_NLS_PARAMETER",
1404	XMLAGG
1405	(
1406		XMLELEMENT
1407		(
1408			"ROW",
1409			XMLFOREST
1410			(
1411				parameter,
1412				value
1413			)
1414		)
1415	)
1416).getclobval()
1417FROM nls_database_parameters
1418/
1419
1420EXEC dbms_output.put_line('<!--');
1421SET TERMOUT ON
1422EXEC dbms_output.put_line(' ->  Database Properties [Database]');
1423SET TERMOUT OFF
1424EXEC dbms_output.put_line('-->');
1425
1426SELECT XMLELEMENT
1427(
1428	"DB_PROPERTIES",
1429	XMLAGG
1430	(
1431		XMLELEMENT
1432		(
1433			"ROW",
1434			XMLFOREST
1435			(
1436				property_name,
1437				property_value,
1438				description
1439			)
1440		)
1441	)
1442).getclobval()
1443FROM database_properties	
1444/
1445
1446EXEC dbms_output.put_line('<!--');
1447SET TERMOUT ON
1448EXEC dbms_output.put_line(' ->  Datafile I/O Statistics [Database Files]');
1449SET TERMOUT OFF
1450EXEC dbms_output.put_line('-->');
1451
1452SELECT XMLELEMENT
1453(
1454	"DB_DATAFILES_IO_STATISTICS",
1455	XMLAGG
1456	(
1457		XMLELEMENT
1458		(
1459			"ROW",
1460			XMLFOREST
1461			(
1462				inst_id,
1463				file# AS file_num,
1464				phyrds,
1465				phywrts,
1466				phyblkrd,
1467				phyblkwrt,
1468				readtim,
1469				writetim,
1470				avgiotim,
1471				lstiotim,
1472				miniotim,
1473				maxiowtm,
1474				maxiortm
1475			)
1476		) ORDER BY inst_id, file#
1477	)
1478).getclobval()
1479FROM gv$filestat		
1480/
1481
1482EXEC dbms_output.put_line('<!--');
1483SET TERMOUT ON
1484EXEC dbms_output.put_line(' ->  Dynamic SGA Components [Instance]');
1485SET TERMOUT OFF
1486EXEC dbms_output.put_line('-->');
1487
1488SELECT XMLELEMENT
1489(
1490	"INSTANCE_SGA_COMPONENTS",
1491	XMLAGG
1492	(
1493		XMLELEMENT
1494		(
1495			"ROW",
1496			XMLFOREST
1497			(
1498				inst_id,
1499				component,
1500				current_size,
1501				min_size,
1502				max_size,
1503				oper_count,
1504				last_oper_type,
1505				last_oper_mode,
1506				TO_CHAR(last_oper_time, 'YYYY-MM-DD HH24:MI:SS') AS last_oper_time,
1507				granule_size
1508			)
1509		) ORDER BY inst_id, current_size DESC, component
1510	)
1511).getclobval()
1512FROM gv$sga_dynamic_components
1513/
1514
1515EXEC dbms_output.put_line('<!--');
1516SET TERMOUT ON
1517EXEC dbms_output.put_line(' ->  Elapsed Time [SQL]');
1518SET TERMOUT OFF
1519EXEC dbms_output.put_line('-->');
1520
1521WITH top_20_inst AS (
1522	SELECT inst_id,
1523		sql_id
1524	FROM (
1525		SELECT inst_id,
1526			sql_id,
1527			ROW_NUMBER() OVER (PARTITION BY inst_id ORDER BY SUM(elapsed_time) DESC) rnum
1528		FROM gv$sql
1529		WHERE (module != 'gdbiX' OR module IS NULL)
1530		GROUP BY inst_id, sql_id
1531	)
1532	WHERE rnum <= 20
1533),
1534stats AS (
1535	SELECT DISTINCT inst_id,
1536		SUM(buffer_gets) OVER (PARTITION BY inst_id) AS buffer_gets_inst,
1537		SUM(buffer_gets) OVER () AS buffer_gets_all,
1538		SUM(cpu_time) OVER (PARTITION BY inst_id) AS cpu_time_inst,
1539		SUM(cpu_time) OVER () AS cpu_time_all,
1540		SUM(elapsed_time) OVER (PARTITION BY inst_id) AS elapsed_time_inst,
1541		SUM(elapsed_time) OVER () AS elapsed_time_all,
1542		SUM(disk_reads) OVER (PARTITION BY inst_id) AS disk_reads_inst,
1543		SUM(disk_reads) OVER () AS disk_reads_all,
1544		SUM(physical_read_requests) OVER (PARTITION BY inst_id) AS physical_read_requests_inst,
1545		SUM(physical_read_requests) OVER () AS physical_read_requests_all,
1546		SUM(physical_read_bytes) OVER (PARTITION BY inst_id) AS physical_read_bytes_inst,
1547		SUM(physical_read_bytes) OVER () AS physical_read_bytes_all,
1548		SUM(physical_write_requests) OVER (PARTITION BY inst_id) AS physical_write_requests_inst,
1549		SUM(physical_write_requests) OVER () AS physical_write_requests_all,
1550		SUM(physical_write_bytes) OVER (PARTITION BY inst_id) AS physical_write_bytes_inst,
1551		SUM(physical_write_bytes) OVER () AS physical_write_bytes_all,
1552		SUM(optimized_phy_read_requests) OVER (PARTITION BY inst_id) AS opt_phy_read_req_inst,
1553		SUM(optimized_phy_read_requests) OVER () AS opt_phy_read_req_all
1554	FROM gv$sql
1555)
1556SELECT XMLELEMENT(
1557	"SQL_ELAPSED_TIME_EXT",
1558	XMLAGG(
1559		XMLELEMENT(
1560			"ROW",
1561			XMLFOREST(
1562				inst_id,
1563				sql_id,	
1564				child_number,
1565				plan_hash_value,
1566				sql_profile,
1567				parsing_schema_name,
1568				module,
1569				sql_text,
1570				sharable_mem,
1571				persistent_mem,
1572				runtime_mem,
1573				sorts_exec,
1574				fetches,
1575				end_of_fetch_count,
1576				px_servers_executions,
1577				loads,
1578				invalidations,
1579				is_bind_sensitive,
1580				is_shareable,
1581				io_interconnect_bytes,				
1582				phy_read_requests,
1583				phy_read_requests_exec,
1584				phy_read_requests_inst,
1585				phy_read_requests_all,
1586				phy_read_bytes,
1587				phy_read_bytes_exec,
1588				phy_read_bytes_inst,
1589				phy_read_bytes_all,
1590				phy_write_requests,
1591				phy_write_requests_exec,
1592				phy_write_requests_inst,
1593				phy_write_requests_all,
1594				phy_write_bytes,
1595				phy_write_bytes_exec,
1596				phy_write_bytes_inst,
1597				phy_write_bytes_all,
1598				opt_phy_read_requests,
1599				opt_phy_read_requests_exec,
1600				opt_phy_read_requests_inst,
1601				opt_phy_read_requests_all,				
1602				cpu_time,
1603				cpu_time_exec,
1604				cpu_time_all,
1605				cpu_time_inst,
1606				elapsed_time,
1607				elapsed_time_exec,
1608				elapsed_time_inst,
1609				elapsed_time_all,
1610				buffer_gets,
1611				buffer_gets_exec,
1612				buffer_gets_row,
1613				buffer_gets_all,
1614				buffer_gets_inst,
1615				disk_reads,
1616				disk_reads_exec,
1617				disk_reads_row,
1618				disk_reads_all,
1619				disk_reads_inst,
1620				executions,
1621				parse_calls,
1622				execute_parse_ratio,
1623				first_load_time,
1624				last_load_time,
1625				last_active_time,		
1626				application_wait_time,
1627				application_wait_time_exec,
1628				concurrency_wait_time,
1629				concurrency_wait_time_exec,
1630				cluster_wait_time,
1631				cluster_wait_time_exec,
1632				user_io_wait_time,
1633				user_io_wait_time_exec,
1634				rows_processed,
1635				rows_processed_exec,
1636				optimizer_cost
1637            )
1638        ) ORDER BY elapsed_time_all DESC NULLS LAST
1639    )
1640).getclobval() 
1641FROM (
1642	SELECT inst_id,
1643		parsing_schema_name,
1644		child_number,
1645		sql_id,
1646		REPLACE(module, CHR(0), '') AS module,
1647		loads,
1648		invalidations,
1649		sharable_mem,
1650		persistent_mem,
1651		runtime_mem,
1652		ROUND(sorts / NULLIF(executions, 0), 4) AS sorts_exec,
1653		fetches,
1654		end_of_fetch_count,
1655		px_servers_executions,
1656		plan_hash_value,
1657		is_bind_sensitive,
1658		is_shareable,
1659		io_interconnect_bytes,
1660		sql_profile,
1661		physical_read_requests AS phy_read_requests,
1662		ROUND(physical_read_requests / NULLIF(executions, 0), 4) AS phy_read_requests_exec,
1663		ROUND(physical_read_requests / NULLIF(physical_read_requests_inst, 0) * 100, 2) AS phy_read_requests_inst,
1664		ROUND(physical_read_requests / NULLIF(physical_read_requests_all, 0) * 100, 2) AS phy_read_requests_all,
1665		physical_read_bytes AS phy_read_bytes,
1666		ROUND(physical_read_bytes / NULLIF(executions, 0), 4) AS phy_read_bytes_exec,
1667		ROUND(physical_read_bytes / NULLIF(physical_read_bytes_inst, 0) * 100, 2) AS phy_read_bytes_inst,
1668		ROUND(physical_read_bytes / NULLIF(physical_read_bytes_all, 0) * 100, 2) AS phy_read_bytes_all,
1669		physical_write_requests AS phy_write_requests,
1670		ROUND(physical_write_requests / NULLIF(executions, 0), 4) AS phy_write_requests_exec,
1671		ROUND(physical_write_requests / NULLIF(physical_write_requests_inst, 0) * 100, 2) AS phy_write_requests_inst,
1672		ROUND(physical_write_requests / NULLIF(physical_write_requests_all, 0) * 100, 2) AS phy_write_requests_all,
1673		physical_write_bytes AS phy_write_bytes,
1674		ROUND(physical_write_bytes / NULLIF(executions, 0), 4) AS phy_write_bytes_exec,
1675		ROUND(physical_write_bytes / NULLIF(physical_write_bytes_inst, 0) * 100, 2) AS phy_write_bytes_inst,
1676		ROUND(physical_write_bytes / NULLIF(physical_write_bytes_all, 0) * 100, 2) AS phy_write_bytes_all,
1677		optimized_phy_read_requests AS opt_phy_read_requests,
1678		ROUND(optimized_phy_read_requests / NULLIF(executions, 0), 4) AS opt_phy_read_requests_exec,
1679		ROUND(optimized_phy_read_requests / NULLIF(opt_phy_read_req_inst, 0) * 100, 2) AS opt_phy_read_requests_inst,
1680		ROUND(optimized_phy_read_requests / NULLIF(opt_phy_read_req_all, 0) * 100, 2) AS opt_phy_read_requests_all,
1681		REPLACE(sql_fulltext, CHR(0), '') AS sql_text,
1682		cpu_time / 1000 AS cpu_time,
1683		ROUND(cpu_time / (1000 * NULLIF(executions, 0)), 4) cpu_time_exec,
1684		ROUND(100 * (cpu_time / NULLIF(cpu_time_inst, 0)), 2) cpu_time_inst,
1685		ROUND(100 * (cpu_time / NULLIF(cpu_time_all, 0)), 2) AS cpu_time_all,
1686		elapsed_time / 1000 AS elapsed_time,
1687		ROUND(elapsed_time / (1000 * NULLIF(executions, 0)), 4) AS elapsed_time_exec,
1688		ROUND(100 * (elapsed_time / NULLIF(elapsed_time_inst, 0)), 2) AS elapsed_time_inst,
1689		ROUND(100 * (elapsed_time / NULLIF(elapsed_time_all, 0)), 2) AS elapsed_time_all,
1690		buffer_gets,
1691		ROUND(buffer_gets / NULLIF(executions, 0), 4) AS buffer_gets_exec,
1692		ROUND(100 * (buffer_gets / NULLIF(buffer_gets_all, 0)), 2) AS buffer_gets_all,
1693		ROUND(100 * (buffer_gets / NULLIF(buffer_gets_inst, 0)), 2) buffer_gets_inst,
1694		ROUND(buffer_gets / NULLIF(rows_processed, 0), 2) buffer_gets_row,
1695		disk_reads,
1696		ROUND(disk_reads / NULLIF(executions, 0), 4) AS disk_reads_exec,
1697		ROUND(100 * (disk_reads / NULLIF(disk_reads_all, 0)), 2) AS disk_reads_all,
1698		ROUND(100 * (disk_reads / NULLIF(disk_reads_inst, 0)), 2) disk_reads_inst,
1699		ROUND(disk_reads / NULLIF(rows_processed, 0) * 100, 2) disk_reads_row,
1700		executions,
1701		parse_calls,
1702		ROUND(100 * (1 - parse_calls / NULLIF(executions, 0)), 2) AS execute_parse_ratio,
1703		TO_CHAR(TO_TIMESTAMP(first_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS first_load_time,
1704		TO_CHAR(TO_TIMESTAMP(last_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS last_load_time,
1705		TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI:SS') AS last_active_time,
1706		application_wait_time / 1000 AS application_wait_time,
1707		ROUND(application_wait_time / (1000 * NULLIF(executions, 0)), 4) AS application_wait_time_exec,
1708		concurrency_wait_time / 1000 AS concurrency_wait_time,
1709		ROUND(concurrency_wait_time / (1000 * NULLIF(executions, 0)), 4) AS concurrency_wait_time_exec,
1710		cluster_wait_time / 1000 AS cluster_wait_time,
1711		ROUND(cluster_wait_time / (1000 * NULLIF(executions, 0)), 4) AS cluster_wait_time_exec,
1712		user_io_wait_time / 1000 AS user_io_wait_time,
1713		ROUND(user_io_wait_time / (1000 * NULLIF(executions, 0)), 4) AS user_io_wait_time_exec,
1714		rows_processed,
1715		ROUND(rows_processed / NULLIF(executions, 0), 4) AS rows_processed_exec,
1716		optimizer_cost
1717	FROM gv$sql
1718		JOIN top_20_inst USING (sql_id, inst_id)
1719		JOIN stats USING (inst_id)
1720)
1721/
1722
1723EXEC dbms_output.put_line('<!--');
1724SET TERMOUT ON
1725EXEC dbms_output.put_line(' ->  Enfinity/Intershop Jobs [Jobs]');
1726SET TERMOUT OFF
1727EXEC dbms_output.put_line('-->');
1728
1729SELECT XMLELEMENT
1730(
1731	"APP_JOBS",
1732	XMLAGG
1733	(
1734		XMLELEMENT
1735		(
1736			"ROW",
1737			XMLFOREST
1738			(
1739				domainname,
1740				name,
1741				enabledflag,
1742				pipelinename,
1743				startnodename,
1744				TO_CHAR(lastexecutiontime, 'YYYY-MM-DD HH24:MI:SS') AS lastexecutiontime,
1745				lastexecutionduration,
1746				factor,
1747				unit,
1748				ROUND(potential_daily_runtime, 2) AS potential_daily_runtime,
1749				status,
1750				TO_CHAR(startdate, 'YYYY-MM-DD HH24:MI:SS') AS startdate,
1751				TO_CHAR(enddate, 'YYYY-MM-DD HH24:MI:SS') AS enddate,
1752				relation,
1753				day,
1754				month,
1755				year,
1756				x,
1757				servername,
1758				login,
1759				description
1760			)
1761		) ORDER BY enabledflag DESC, domainname, lastexecutiontime DESC
1762	)
1763).getclobval()
1764FROM
1765(
1766	SELECT jc.enabledflag,
1767		di.domainname,
1768		jc.name,
1769		jc.pipelinename,
1770		jc.startnodename,
1771		jc.lastexecutiontime,
1772		jc.lastexecutionduration,
1773		cond.factor,
1774		DECODE(cond.unit, 0, 'minute', 1, 'hour', 2, 'day', 4, 'week', 5, 'month', 6, 'year') AS unit,
1775		(
1776			CASE
1777				WHEN cond.factor = 0 THEN 0
1778				WHEN cond.unit = 0 THEN 1440 / cond.factor
1779				WHEN cond.unit = 1 THEN 24 / cond.factor 
1780				WHEN cond.unit = 2 THEN cond.factor
1781				WHEN cond.unit = 4 THEN 1 / (cond.factor * 7)
1782				WHEN cond.unit = 5 THEN 1 / (cond.factor * 30)
1783				WHEN cond.unit = 6 THEN 1 / (cond.factor * 365)
1784				ELSE 0
1785			END
1786		) * jc.lastexecutionduration AS potential_daily_runtime,
1787		jc.status,
1788		cond.startdate,
1789		cond.enddate,
1790		cond.relation,
1791		DECODE(BITAND(cond.day, 2), 2, 'SUN ', NULL) ||
1792		DECODE(BITAND(cond.day, 4), 4, 'MON ', NULL) ||
1793		DECODE(BITAND(cond.day, 8), 8, 'TUE ', NULL) ||
1794		DECODE(BITAND(cond.day, 16), 16, 'WED ', NULL) ||
1795		DECODE(BITAND(cond.day, 32), 32, 'THU ', NULL) ||
1796		DECODE(BITAND(cond.day, 64), 64, 'FRI ', NULL) ||
1797		DECODE(BITAND(cond.day, 128), 128, 'SAT ', NULL) AS day,
1798		cond.month,
1799		cond.year,
1800		cond.x,
1801		servername,
1802		login,
1803		description
1804	FROM &user_uc..jobconfiguration jc LEFT JOIN
1805	(
1806		SELECT uuid,
1807			domainid,
1808			lastmodified,
1809			startdate,
1810			enddate,
1811			NULL AS relation,
1812			NULL AS day,
1813			NULL AS month,
1814			NULL AS year,
1815			NULL AS x,
1816			factor,
1817			unit
1818		FROM &user_uc..jobintermittenttimecondition
1819		UNION
1820		SELECT uuid,
1821			domainid,
1822			lastmodified,
1823			startdate,
1824			enddate,
1825			relation,
1826			day,
1827			month,
1828			year,
1829			x,
1830			NULL,
1831			NULL
1832		FROM &user_uc..jobrelativetimecondition
1833	) cond ON (jc.conditionid = cond.uuid) JOIN &user_uc..domaininformation di ON (di.domainid = jc.domainid)
1834)		
1835/
1836
1837EXEC dbms_output.put_line('<!--');
1838SET TERMOUT ON
1839EXEC dbms_output.put_line(' ->  Environment [Environment]');
1840SET TERMOUT OFF
1841EXEC dbms_output.put_line('-->');
1842
1843SELECT XMLELEMENT
1844(
1845	"ENVIRONMENT",
1846	XMLAGG
1847	(
1848		XMLELEMENT
1849		(
1850			"ROW",
1851			XMLFOREST
1852			(
1853				dbms_utility.port_string() as os,
1854				user AS db_user,
1855				'&user_uc.' AS target_user,
1856				 (
1857				 	SELECT version
1858				 	FROM dba_registry
1859				 	WHERE comp_id = 'CATALOG'
1860				 ) db_release,
1861				'0.9.4' AS gdbix_version,
1862				TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS db_time,
1863				SYS_GUID() AS guid
1864			)
1865		)
1866	)
1867).getclobval()
1868FROM DUAL
1869/
1870
1871EXEC dbms_output.put_line('<!--');
1872SET TERMOUT ON
1873EXEC dbms_output.put_line(' ->  Indexed Columns [Indexes]');
1874SET TERMOUT OFF
1875EXEC dbms_output.put_line('-->');
1876
1877SELECT XMLELEMENT
1878(
1879	"USR_INDEXED_COLUMNS_PV",
1880	XMLAGG
1881	(
1882		XMLELEMENT
1883		(
1884			"ROW",
1885			XMLFOREST
1886			(
1887				table_name,
1888				index_name,
1889				index_type,
1890				indexed_columns,
1891				columns_length
1892			)
1893		) ORDER BY table_name, index_name
1894	)
1895).getclobval()
1896FROM
1897(
1898	SELECT table_name,
1899		index_name,
1900		index_type,
1901		LTRIM(SYS_CONNECT_BY_PATH(column_name, ', '), ', ') indexed_columns,
1902		LTRIM(SYS_CONNECT_BY_PATH(column_length, ', '), ', ') columns_length
1903		FROM
1904		(
1905			SELECT dic.table_name,
1906				dic.index_name,
1907				di.index_type,
1908				dic.column_name,
1909				dic.column_length,
1910				ROW_NUMBER() OVER (PARTITION BY dic.index_name ORDER BY column_position) col,
1911				COUNT(*) OVER (PARTITION BY dic.index_name) totalcol
1912			FROM dba_ind_columns dic JOIN dba_indexes di ON (dic.index_owner = di.owner AND dic.index_name = di.index_name)
1913			WHERE index_owner = '&user_uc.'
1914			AND dic.table_name NOT LIKE 'BIN$%'
1915		)
1916		WHERE col = totalcol
1917		CONNECT BY PRIOR col = col - 1 AND PRIOR index_name = index_name
1918		START WITH col = 1
1919)
1920/
1921
1922EXEC dbms_output.put_line('<!--');
1923SET TERMOUT ON
1924EXEC dbms_output.put_line(' ->  Indexes [Indexes]');
1925SET TERMOUT OFF
1926EXEC dbms_output.put_line('-->');
1927
1928SELECT XMLELEMENT
1929(
1930	"USR_INDEXES",
1931	XMLAGG
1932	(
1933		XMLELEMENT
1934		(
1935			"ROW",
1936			XMLFOREST
1937			(
1938				index_name,
1939				index_type,
1940				table_owner,
1941				table_name,
1942				table_type,
1943				uniqueness,
1944				compression,
1945				prefix_length,
1946				tablespace_name,
1947				ini_trans,
1948				max_trans,
1949				initial_extent,
1950				next_extent,
1951				min_extents,
1952				max_extents,
1953				pct_increase,
1954				pct_threshold,
1955				include_column,
1956				freelists,
1957				freelist_groups,
1958				pct_free,
1959				logging,
1960				blevel,
1961				leaf_blocks,
1962				distinct_keys,
1963				avg_leaf_blocks_per_key,
1964				avg_data_blocks_per_key,
1965				clustering_factor,
1966				status,
1967				num_rows,
1968				sample_size,
1969				TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed,
1970				degree,
1971				instances,
1972				partitioned,
1973				temporary,
1974				generated,
1975				secondary,
1976				buffer_pool,
1977				user_stats,
1978				duration,
1979				pct_direct_access,
1980				ityp_owner,
1981				ityp_name,
1982				parameters,
1983				global_stats,
1984				domidx_status,
1985				domidx_opstatus,
1986				funcidx_status
1987			)
1988		) ORDER BY index_name
1989	)
1990).getclobval()
1991FROM dba_indexes
1992WHERE owner = '&user_uc.'
1993AND table_name NOT LIKE 'BIN$%'
1994/
1995
1996EXEC dbms_output.put_line('<!--');
1997SET TERMOUT ON
1998EXEC dbms_output.put_line(' ->  Initialization Parameters [Instance]');
1999SET TERMOUT OFF
2000EXEC dbms_output.put_line('-->');
2001
2002SELECT XMLELEMENT
2003(
2004	"INSTANCE_INIT_PARAMETER",
2005	XMLAGG
2006	(
2007		XMLELEMENT
2008		(
2009			"ROW",
2010			XMLFOREST
2011			(
2012				inst_id,
2013				name,
2014				value,
2015				isdefault,
2016				ismodified,
2017				issys_modifiable
2018			)
2019		) ORDER BY inst_id, name, isdefault
2020	)
2021).getclobval()
2022FROM gv$parameter
2023/
2024
2025EXEC dbms_output.put_line('<!--');
2026SET TERMOUT ON
2027EXEC dbms_output.put_line(' ->  Instance NLS Settings [NLS]');
2028SET TERMOUT OFF
2029EXEC dbms_output.put_line('-->');
2030
2031SELECT XMLELEMENT
2032(
2033	"INSTANCE_NLS_PARAMETER",
2034	XMLAGG
2035	(
2036		XMLELEMENT
2037		(
2038			"ROW",
2039			XMLFOREST
2040			(
2041				parameter,
2042				value
2043			)
2044		) ORDER BY parameter
2045	)
2046).getclobval()
2047FROM nls_instance_parameters		
2048/
2049
2050EXEC dbms_output.put_line('<!--');
2051SET TERMOUT ON
2052EXEC dbms_output.put_line(' ->  Invalid Objects [Database]');
2053SET TERMOUT OFF
2054EXEC dbms_output.put_line('-->');
2055
2056SELECT XMLELEMENT
2057(
2058	"DB_INVALID_OBJECTS",
2059	XMLAGG
2060	(
2061		XMLELEMENT
2062		(
2063			"ROW",
2064			XMLFOREST
2065			(
2066				owner,
2067				object_type,
2068				object_name,
2069				TO_CHAR(last_ddl_time, 'YYYY-MM-DD HH24:MI:SS') AS last_ddl_time
2070			)
2071		)
2072	)
2073).getclobval()
2074FROM
2075(
2076	SELECT owner,
2077		object_type,
2078		object_name,
2079		last_ddl_time
2080	FROM all_objects
2081	WHERE status = 'INVALID'
2082	AND SUBSTR(object_name, 1, 4) != 'BIN$'
2083	AND SUBSTR(object_name, 1, 3) != 'DR$'
2084	ORDER BY owner, object_type, object_name
2085)	
2086/
2087
2088EXEC dbms_output.put_line('<!--');
2089SET TERMOUT ON
2090EXEC dbms_output.put_line(' ->  LOBs [LOBs]');
2091SET TERMOUT OFF
2092EXEC dbms_output.put_line('-->');
2093
2094SELECT XMLELEMENT
2095(
2096	"USR_LOBS",
2097	XMLAGG
2098	(
2099		XMLELEMENT
2100		(
2101			"ROW",
2102			XMLFOREST
2103			(
2104				table_name,
2105				column_name,
2106				segment_name,
2107				tablespace_name,
2108				index_name,
2109				chunk,
2110				pctversion,
2111				retention,
2112				freepools,
2113				cache,
2114				logging,
2115				encrypt,
2116				compression,
2117				deduplication,
2118				in_row,
2119				format,
2120				partitioned,
2121				securefile,
2122				retention_type,
2123				retention_value
2124			)
2125		) ORDER BY table_name, column_name, segment_name
2126	)
2127).getclobval()
2128FROM dba_lobs
2129WHERE owner = '&user_uc.'
2130AND table_name NOT LIKE 'BIN$%'	
2131/
2132
2133EXEC dbms_output.put_line('<!--');
2134SET TERMOUT ON
2135EXEC dbms_output.put_line(' ->  Latch Statistics [Locking / Statistics]');
2136SET TERMOUT OFF
2137EXEC dbms_output.put_line('-->');
2138
2139SELECT XMLELEMENT
2140(
2141	"INSTANCE_LATCHES",
2142	XMLAGG
2143	(
2144		XMLELEMENT
2145		(
2146			"ROW",
2147			XMLFOREST
2148			(
2149				inst_id,
2150				latch# AS latch_num,
2151				level# AS level_num,
2152				name,
2153				gets,
2154				misses,
2155				sleeps,
2156				immediate_gets,
2157				immediate_misses,
2158				spin_gets,
2159				wait_time
2160			)
2161		) ORDER BY inst_id, level#, wait_time DESC
2162	)
2163).getclobval()
2164FROM gv$latch
2165/
2166
2167EXEC dbms_output.put_line('<!--');
2168SET TERMOUT ON
2169EXEC dbms_output.put_line(' ->  Locales [Enfinity/Intershop]');
2170SET TERMOUT OFF
2171EXEC dbms_output.put_line('-->');
2172
2173SELECT XMLELEMENT
2174(
2175	"APP_LOCALEINFORMATION",
2176	XMLAGG(
2177		XMLELEMENT
2178		(
2179			"ROW",
2180			XMLFOREST
2181			(
2182				leadlocaleflag,
2183				localeid,
2184				javalanguagekey,
2185				javacountrykey,
2186				defaultregionalsettingsid,
2187				currencycode
2188			)
2189		) 
2190	) 
2191).getclobval()
2192FROM &user_uc..localeinformation		
2193/
2194
2195EXEC dbms_output.put_line('<!--');
2196SET TERMOUT ON
2197EXEC dbms_output.put_line(' ->  Locks [Locking / Statistics]');
2198SET TERMOUT OFF
2199EXEC dbms_output.put_line('-->');
2200
2201SELECT XMLELEMENT
2202(
2203	"DB_LOCKS",
2204	XMLAGG
2205	(
2206		XMLELEMENT
2207		(
2208			"ROW",
2209			XMLFOREST
2210			(
2211				session_id,
2212				lock_type,
2213				mode_held,
2214				mode_requested,
2215				(
2216					SELECT NVL(object_name, lock_id1)
2217					FROM dba_objects
2218					WHERE object_id = lock_id1
2219				) AS object_name,
2220				(
2221					SELECT NVL(object_type, lock_id2)
2222					FROM dba_objects
2223					WHERE object_id = lock_id1
2224				) AS object_type,
2225				lock_id2,
2226				last_convert,
2227				blocking_others
2228			)
2229		) ORDER BY blocking_others
2230	)
2231).getclobval()
2232FROM dba_lock
2233WHERE lock_type NOT IN ('AE')		
2234/
2235
2236EXEC dbms_output.put_line('<!--');
2237SET TERMOUT ON
2238EXEC dbms_output.put_line(' ->  Logical I/O [SQL]');
2239SET TERMOUT OFF
2240EXEC dbms_output.put_line('-->');
2241
2242WITH top_20_inst AS (
2243	SELECT inst_id,
2244		sql_id
2245	FROM (
2246		SELECT inst_id,
2247			sql_id,
2248			ROW_NUMBER() OVER (PARTITION BY inst_id ORDER BY SUM(buffer_gets) DESC) rnum
2249		FROM gv$sql
2250		WHERE (module != 'gdbiX' OR module IS NULL)
2251		GROUP BY inst_id, sql_id
2252	)
2253	WHERE rnum <= 20
2254),
2255stats AS (
2256	SELECT DISTINCT inst_id,
2257		SUM(buffer_gets) OVER (PARTITION BY inst_id) AS buffer_gets_inst,
2258		SUM(buffer_gets) OVER () AS buffer_gets_all,
2259		SUM(cpu_time) OVER (PARTITION BY inst_id) AS cpu_time_inst,
2260		SUM(cpu_time) OVER () AS cpu_time_all,
2261		SUM(elapsed_time) OVER (PARTITION BY inst_id) AS elapsed_time_inst,
2262		SUM(elapsed_time) OVER () AS elapsed_time_all,
2263		SUM(disk_reads) OVER (PARTITION BY inst_id) AS disk_reads_inst,
2264		SUM(disk_reads) OVER () AS disk_reads_all,
2265		SUM(physical_read_requests) OVER (PARTITION BY inst_id) AS physical_read_requests_inst,
2266		SUM(physical_read_requests) OVER () AS physical_read_requests_all,
2267		SUM(physical_read_bytes) OVER (PARTITION BY inst_id) AS physical_read_bytes_inst,
2268		SUM(physical_read_bytes) OVER () AS physical_read_bytes_all,
2269		SUM(physical_write_requests) OVER (PARTITION BY inst_id) AS physical_write_requests_inst,
2270		SUM(physical_write_requests) OVER () AS physical_write_requests_all,
2271		SUM(physical_write_bytes) OVER (PARTITION BY inst_id) AS physical_write_bytes_inst,
2272		SUM(physical_write_bytes) OVER () AS physical_write_bytes_all,
2273		SUM(optimized_phy_read_requests) OVER (PARTITION BY inst_id) AS opt_phy_read_req_inst,
2274		SUM(optimized_phy_read_requests) OVER () AS opt_phy_read_req_all
2275	FROM gv$sql
2276)
2277SELECT XMLELEMENT(
2278	"SQL_BUFFER_GETS_EXT",
2279	XMLAGG(
2280		XMLELEMENT(
2281			"ROW",
2282			XMLFOREST(
2283				inst_id,
2284				sql_id,	
2285				child_number,
2286				plan_hash_value,
2287				sql_profile,
2288				parsing_schema_name,
2289				module,
2290				sql_text,
2291				sharable_mem,
2292				persistent_mem,
2293				runtime_mem,
2294				sorts_exec,
2295				fetches,
2296				end_of_fetch_count,
2297				px_servers_executions,
2298				loads,
2299				invalidations,
2300				is_bind_sensitive,
2301				is_shareable,
2302				io_interconnect_bytes,				
2303				phy_read_requests,
2304				phy_read_requests_exec,
2305				phy_read_requests_inst,
2306				phy_read_requests_all,
2307				phy_read_bytes,
2308				phy_read_bytes_exec,
2309				phy_read_bytes_inst,
2310				phy_read_bytes_all,
2311				phy_write_requests,
2312				phy_write_requests_exec,
2313				phy_write_requests_inst,
2314				phy_write_requests_all,
2315				phy_write_bytes,
2316				phy_write_bytes_exec,
2317				phy_write_bytes_inst,
2318				phy_write_bytes_all,
2319				opt_phy_read_requests,
2320				opt_phy_read_requests_exec,
2321				opt_phy_read_requests_inst,
2322				opt_phy_read_requests_all,				
2323				cpu_time,
2324				cpu_time_exec,
2325				cpu_time_all,
2326				cpu_time_inst,
2327				elapsed_time,
2328				elapsed_time_exec,
2329				elapsed_time_inst,
2330				elapsed_time_all,
2331				buffer_gets,
2332				buffer_gets_exec,
2333				buffer_gets_row,
2334				buffer_gets_all,
2335				buffer_gets_inst,
2336				disk_reads,
2337				disk_reads_exec,
2338				disk_reads_row,
2339				disk_reads_all,
2340				disk_reads_inst,
2341				executions,
2342				parse_calls,
2343				execute_parse_ratio,
2344				first_load_time,
2345				last_load_time,
2346				last_active_time,		
2347				application_wait_time,
2348				application_wait_time_exec,
2349				concurrency_wait_time,
2350				concurrency_wait_time_exec,
2351				cluster_wait_time,
2352				cluster_wait_time_exec,
2353				user_io_wait_time,
2354				user_io_wait_time_exec,
2355				rows_processed,
2356				rows_processed_exec,
2357				optimizer_cost
2358            )
2359        ) ORDER BY buffer_gets_all DESC NULLS LAST
2360    )
2361).getclobval() 
2362FROM (
2363	SELECT inst_id,
2364		parsing_schema_name,
2365		child_number,
2366		sql_id,
2367		REPLACE(module, CHR(0), '') AS module,
2368		loads,
2369		invalidations,
2370		sharable_mem,
2371		persistent_mem,
2372		runtime_mem,
2373		ROUND(sorts / NULLIF(executions, 0), 4) AS sorts_exec,
2374		fetches,
2375		end_of_fetch_count,
2376		px_servers_executions,
2377		plan_hash_value,
2378		is_bind_sensitive,
2379		is_shareable,
2380		io_interconnect_bytes,
2381		sql_profile,
2382		physical_read_requests AS phy_read_requests,
2383		ROUND(physical_read_requests / NULLIF(executions, 0), 4) AS phy_read_requests_exec,
2384		ROUND(physical_read_requests / NULLIF(physical_read_requests_inst, 0) * 100, 2) AS phy_read_requests_inst,
2385		ROUND(physical_read_requests / NULLIF(physical_read_requests_all, 0) * 100, 2) AS phy_read_requests_all,
2386		physical_read_bytes AS phy_read_bytes,
2387		ROUND(physical_read_bytes / NULLIF(executions, 0), 4) AS phy_read_bytes_exec,
2388		ROUND(physical_read_bytes / NULLIF(physical_read_bytes_inst, 0) * 100, 2) AS phy_read_bytes_inst,
2389		ROUND(physical_read_bytes / NULLIF(physical_read_bytes_all, 0) * 100, 2) AS phy_read_bytes_all,
2390		physical_write_requests AS phy_write_requests,
2391		ROUND(physical_write_requests / NULLIF(executions, 0), 4) AS phy_write_requests_exec,
2392		ROUND(physical_write_requests / NULLIF(physical_write_requests_inst, 0) * 100, 2) AS phy_write_requests_inst,
2393		ROUND(physical_write_requests / NULLIF(physical_write_requests_all, 0) * 100, 2) AS phy_write_requests_all,
2394		physical_write_bytes AS phy_write_bytes,
2395		ROUND(physical_write_bytes / NULLIF(executions, 0), 4) AS phy_write_bytes_exec,
2396		ROUND(physical_write_bytes / NULLIF(physical_write_bytes_inst, 0) * 100, 2) AS phy_write_bytes_inst,
2397		ROUND(physical_write_bytes / NULLIF(physical_write_bytes_all, 0) * 100, 2) AS phy_write_bytes_all,
2398		optimized_phy_read_requests AS opt_phy_read_requests,
2399		ROUND(optimized_phy_read_requests / NULLIF(executions, 0), 4) AS opt_phy_read_requests_exec,
2400		ROUND(optimized_phy_read_requests / NULLIF(opt_phy_read_req_inst, 0) * 100, 2) AS opt_phy_read_requests_inst,
2401		ROUND(optimized_phy_read_requests / NULLIF(opt_phy_read_req_all, 0) * 100, 2) AS opt_phy_read_requests_all,
2402		REPLACE(sql_fulltext, CHR(0), '') AS sql_text,
2403		cpu_time / 1000 AS cpu_time,
2404		ROUND(cpu_time / (1000 * NULLIF(executions, 0)), 4) cpu_time_exec,
2405		ROUND(100 * (cpu_time / NULLIF(cpu_time_inst, 0)), 2) cpu_time_inst,
2406		ROUND(100 * (cpu_time / NULLIF(cpu_time_all, 0)), 2) AS cpu_time_all,
2407		elapsed_time / 1000 AS elapsed_time,
2408		ROUND(elapsed_time / (1000 * NULLIF(executions, 0)), 4) AS elapsed_time_exec,
2409		ROUND(100 * (elapsed_time / NULLIF(elapsed_time_inst, 0)), 2) AS elapsed_time_inst,
2410		ROUND(100 * (elapsed_time / NULLIF(elapsed_time_all, 0)), 2) AS elapsed_time_all,
2411		buffer_gets,
2412		ROUND(buffer_gets / NULLIF(executions, 0), 4) AS buffer_gets_exec,
2413		ROUND(100 * (buffer_gets / NULLIF(buffer_gets_all, 0)), 2) AS buffer_gets_all,
2414		ROUND(100 * (buffer_gets / NULLIF(buffer_gets_inst, 0)), 2) buffer_gets_inst,
2415		ROUND(buffer_gets / NULLIF(rows_processed, 0), 2) buffer_gets_row,
2416		disk_reads,
2417		ROUND(disk_reads / NULLIF(executions, 0), 4) AS disk_reads_exec,
2418		ROUND(100 * (disk_reads / NULLIF(disk_reads_all, 0)), 2) AS disk_reads_all,
2419		ROUND(100 * (disk_reads / NULLIF(disk_reads_inst, 0)), 2) disk_reads_inst,
2420		ROUND(disk_reads / NULLIF(rows_processed, 0) * 100, 2) disk_reads_row,
2421		executions,
2422		parse_calls,
2423		ROUND(100 * (1 - parse_calls / NULLIF(executions, 0)), 2) AS execute_parse_ratio,
2424		TO_CHAR(TO_TIMESTAMP(first_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS first_load_time,
2425		TO_CHAR(TO_TIMESTAMP(last_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS last_load_time,
2426		TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI:SS') AS last_active_time,
2427		application_wait_time / 1000 AS application_wait_time,
2428		ROUND(application_wait_time / (1000 * NULLIF(executions, 0)), 4) AS application_wait_time_exec,
2429		concurrency_wait_time / 1000 AS concurrency_wait_time,
2430		ROUND(concurrency_wait_time / (1000 * NULLIF(executions, 0)), 4) AS concurrency_wait_time_exec,
2431		cluster_wait_time / 1000 AS cluster_wait_time,
2432		ROUND(cluster_wait_time / (1000 * NULLIF(executions, 0)), 4) AS cluster_wait_time_exec,
2433		user_io_wait_time / 1000 AS user_io_wait_time,
2434		ROUND(user_io_wait_time / (1000 * NULLIF(executions, 0)), 4) AS user_io_wait_time_exec,
2435		rows_processed,
2436		ROUND(rows_processed / NULLIF(executions, 0), 4) AS rows_processed_exec,
2437		optimizer_cost
2438	FROM gv$sql
2439		JOIN top_20_inst USING (sql_id, inst_id)
2440		JOIN stats USING (inst_id)
2441)
2442/
2443
2444EXEC dbms_output.put_line('<!--');
2445SET TERMOUT ON
2446EXEC dbms_output.put_line(' ->  Materialized Views [Views]');
2447SET TERMOUT OFF
2448EXEC dbms_output.put_line('-->');
2449
2450SELECT XMLELEMENT
2451(
2452	"USR_MVIEWS",
2453	XMLAGG
2454	(
2455		XMLELEMENT
2456		(
2457			"ROW",
2458			XMLFOREST
2459			(
2460				mview_name,
2461				updatable,
2462				rewrite_enabled,
2463				rewrite_capability,
2464				refresh_mode,
2465				refresh_method,
2466				build_mode,
2467				fast_refreshable,
2468				last_refresh_type,
2469				TO_CHAR(last_refresh_date, 'YYYY-MM-DD HH24:MI:SS') AS last_refresh_date,
2470				staleness,
2471				compile_state,
2472				use_no_index
2473			)
2474		) ORDER BY mview_name
2475	)
2476).getclobval()
2477FROM dba_mviews
2478WHERE owner = '&user_uc.'
2479/
2480
2481EXEC dbms_output.put_line('<!--');
2482SET TERMOUT ON
2483EXEC dbms_output.put_line(' ->  Memory Target Advisory [SGA/PGA Advisories]');
2484SET TERMOUT OFF
2485EXEC dbms_output.put_line('-->');
2486
2487SELECT XMLELEMENT
2488(
2489	"INSTANCE_MEMORY_TARGET_ADVICE",
2490	XMLAGG
2491	(
2492		XMLELEMENT
2493		(
2494			"ROW",
2495			XMLFOREST
2496			(
2497				inst_id,
2498				memory_size,
2499				memory_size_factor,
2500				estd_db_time,
2501				estd_db_time_factor,
2502				version
2503			)
2504		) ORDER BY inst_id, memory_size_factor
2505	)
2506).getclobval()
2507FROM gv$memory_target_advice
2508/
2509
2510EXEC dbms_output.put_line('<!--');
2511SET TERMOUT ON
2512EXEC dbms_output.put_line(' ->  Object Errors [Database]');
2513SET TERMOUT OFF
2514EXEC dbms_output.put_line('-->');
2515
2516SELECT XMLELEMENT
2517(
2518	"DB_ERRORS",
2519	XMLAGG
2520	(
2521		XMLELEMENT
2522		(
2523			"ROW",
2524			XMLFOREST
2525			(
2526				owner,
2527				name,
2528				type,
2529				sequence,
2530				line,
2531				position,
2532				text
2533			)
2534		) ORDER BY owner, type, name
2535	)
2536).getclobval()
2537FROM dba_errors		
2538/
2539
2540EXEC dbms_output.put_line('<!--');
2541SET TERMOUT ON
2542EXEC dbms_output.put_line(' ->  Object Privileges [Privileges / Roles]');
2543SET TERMOUT OFF
2544EXEC dbms_output.put_line('-->');
2545
2546SELECT XMLELEMENT
2547(
2548	"USR_OBJECT_PRIVILEGES",
2549	XMLAGG
2550	(
2551		XMLELEMENT
2552		(
2553			"ROW",
2554			XMLFOREST
2555			(
2556				owner,
2557				table_name,
2558				privilege,
2559				grantable
2560			)
2561		) ORDER BY owner, table_name, privilege
2562	)
2563).getclobval()
2564FROM dba_tab_privs
2565WHERE grantee = '&user_uc.'
2566/
2567
2568EXEC dbms_output.put_line('<!--');
2569SET TERMOUT ON
2570EXEC dbms_output.put_line(' ->  Open Cursors [SQL]');
2571SET TERMOUT OFF
2572EXEC dbms_output.put_line('-->');
2573
2574SELECT XMLELEMENT
2575(
2576	"SQL_OPEN_CURSORS",
2577	XMLAGG
2578	(
2579		XMLELEMENT
2580		(
2581			"ROW",
2582			XMLFOREST
2583			(
2584				inst_id,
2585				sid,
2586				serial_num,
2587				username,
2588				value AS open_cursors
2589			)
2590		) ORDER BY value DESC
2591	)
2592).getclobval()
2593FROM
2594(
2595	SELECT s.inst_id,
2596		ss.sid,
2597		s.serial# AS serial_num,
2598		s.username,
2599		ss.value
2600	FROM gv$sesstat ss,
2601		gv$session s,
2602		gv$statname sn
2603	WHERE ss.sid = s.sid
2604	AND ss.inst_id = s.inst_id
2605	AND ss.inst_id = sn.inst_id
2606	AND ss.statistic# = sn.statistic#
2607	AND sn.name = 'opened cursors current'
2608)
2609/
2610
2611EXEC dbms_output.put_line('<!--');
2612SET TERMOUT ON
2613EXEC dbms_output.put_line(' ->  PGA Statistics [Locking / Statistics]');
2614SET TERMOUT OFF
2615EXEC dbms_output.put_line('-->');
2616
2617SELECT XMLELEMENT
2618(
2619	"INSTANCE_PGA_STATISTICS",
2620	XMLAGG
2621	(
2622		XMLELEMENT
2623		(
2624			"ROW",
2625			XMLFOREST
2626			(
2627				inst_id,
2628				name,
2629				value,
2630				unit
2631			)
2632		) ORDER BY inst_id, name
2633	)
2634).getclobval()
2635FROM gv$pgastat
2636/
2637
2638EXEC dbms_output.put_line('<!--');
2639SET TERMOUT ON
2640EXEC dbms_output.put_line(' ->  PGA Target Advisory [SGA/PGA Advisories]');
2641SET TERMOUT OFF
2642EXEC dbms_output.put_line('-->');
2643
2644SELECT XMLELEMENT
2645(
2646	"INSTANCE_PGA_TARGET_ADVICE",
2647	XMLAGG
2648	(
2649		XMLELEMENT
2650		(
2651			"ROW",
2652			XMLFOREST
2653			(
2654				inst_id,
2655				pga_target_for_estimate,
2656				pga_target_factor,
2657				advice_status,
2658				bytes_processed,
2659				estd_extra_bytes_rw,
2660				estd_pga_cache_hit_percentage,
2661				estd_overalloc_count
2662			)
2663		) ORDER BY inst_id, pga_target_factor
2664	)
2665).getclobval()
2666FROM gv$pga_target_advice
2667/
2668
2669EXEC dbms_output.put_line('<!--');
2670SET TERMOUT ON
2671EXEC dbms_output.put_line(' ->  Physical I/O [SQL]');
2672SET TERMOUT OFF
2673EXEC dbms_output.put_line('-->');
2674
2675WITH top_20_inst AS (
2676	SELECT inst_id,
2677		sql_id
2678	FROM (
2679		SELECT inst_id,
2680			sql_id,
2681			ROW_NUMBER() OVER (PARTITION BY inst_id ORDER BY SUM(disk_reads) DESC) rnum
2682		FROM gv$sql
2683		WHERE (module != 'gdbiX' OR module IS NULL)
2684		GROUP BY inst_id, sql_id
2685	)
2686	WHERE rnum <= 20
2687),
2688stats AS (
2689	SELECT DISTINCT inst_id,
2690		SUM(buffer_gets) OVER (PARTITION BY inst_id) AS buffer_gets_inst,
2691		SUM(buffer_gets) OVER () AS buffer_gets_all,
2692		SUM(cpu_time) OVER (PARTITION BY inst_id) AS cpu_time_inst,
2693		SUM(cpu_time) OVER () AS cpu_time_all,
2694		SUM(elapsed_time) OVER (PARTITION BY inst_id) AS elapsed_time_inst,
2695		SUM(elapsed_time) OVER () AS elapsed_time_all,
2696		SUM(disk_reads) OVER (PARTITION BY inst_id) AS disk_reads_inst,
2697		SUM(disk_reads) OVER () AS disk_reads_all,
2698		SUM(physical_read_requests) OVER (PARTITION BY inst_id) AS physical_read_requests_inst,
2699		SUM(physical_read_requests) OVER () AS physical_read_requests_all,
2700		SUM(physical_read_bytes) OVER (PARTITION BY inst_id) AS physical_read_bytes_inst,
2701		SUM(physical_read_bytes) OVER () AS physical_read_bytes_all,
2702		SUM(physical_write_requests) OVER (PARTITION BY inst_id) AS physical_write_requests_inst,
2703		SUM(physical_write_requests) OVER () AS physical_write_requests_all,
2704		SUM(physical_write_bytes) OVER (PARTITION BY inst_id) AS physical_write_bytes_inst,
2705		SUM(physical_write_bytes) OVER () AS physical_write_bytes_all,
2706		SUM(optimized_phy_read_requests) OVER (PARTITION BY inst_id) AS opt_phy_read_req_inst,
2707		SUM(optimized_phy_read_requests) OVER () AS opt_phy_read_req_all
2708	FROM gv$sql
2709)
2710SELECT XMLELEMENT(
2711	"SQL_DISK_READS_EXT",
2712	XMLAGG(
2713		XMLELEMENT(
2714			"ROW",
2715			XMLFOREST(
2716				inst_id,
2717				sql_id,	
2718				child_number,
2719				plan_hash_value,
2720				sql_profile,
2721				parsing_schema_name,
2722				module,
2723				sql_text,
2724				sharable_mem,
2725				persistent_mem,
2726				runtime_mem,
2727				sorts_exec,
2728				fetches,
2729				end_of_fetch_count,
2730				px_servers_executions,
2731				loads,
2732				invalidations,
2733				is_bind_sensitive,
2734				is_shareable,
2735				io_interconnect_bytes,				
2736				phy_read_requests,
2737				phy_read_requests_exec,
2738				phy_read_requests_inst,
2739				phy_read_requests_all,
2740				phy_read_bytes,
2741				phy_read_bytes_exec,
2742				phy_read_bytes_inst,
2743				phy_read_bytes_all,
2744				phy_write_requests,
2745				phy_write_requests_exec,
2746				phy_write_requests_inst,
2747				phy_write_requests_all,
2748				phy_write_bytes,
2749				phy_write_bytes_exec,
2750				phy_write_bytes_inst,
2751				phy_write_bytes_all,
2752				opt_phy_read_requests,
2753				opt_phy_read_requests_exec,
2754				opt_phy_read_requests_inst,
2755				opt_phy_read_requests_all,				
2756				cpu_time,
2757				cpu_time_exec,
2758				cpu_time_all,
2759				cpu_time_inst,
2760				elapsed_time,
2761				elapsed_time_exec,
2762				elapsed_time_inst,
2763				elapsed_time_all,
2764				buffer_gets,
2765				buffer_gets_exec,
2766				buffer_gets_row,
2767				buffer_gets_all,
2768				buffer_gets_inst,
2769				disk_reads,
2770				disk_reads_exec,
2771				disk_reads_row,
2772				disk_reads_all,
2773				disk_reads_inst,
2774				executions,
2775				parse_calls,
2776				execute_parse_ratio,
2777				first_load_time,
2778				last_load_time,
2779				last_active_time,		
2780				application_wait_time,
2781				application_wait_time_exec,
2782				concurrency_wait_time,
2783				concurrency_wait_time_exec,
2784				cluster_wait_time,
2785				cluster_wait_time_exec,
2786				user_io_wait_time,
2787				user_io_wait_time_exec,
2788				rows_processed,
2789				rows_processed_exec,
2790				optimizer_cost
2791            )
2792        ) ORDER BY disk_reads_all DESC NULLS LAST
2793    )
2794).getclobval() 
2795FROM (
2796	SELECT inst_id,
2797		parsing_schema_name,
2798		child_number,
2799		sql_id,
2800		REPLACE(module, CHR(0), '') AS module,
2801		loads,
2802		invalidations,
2803		sharable_mem,
2804		persistent_mem,
2805		runtime_mem,
2806		ROUND(sorts / NULLIF(executions, 0), 4) AS sorts_exec,
2807		fetches,
2808		end_of_fetch_count,
2809		px_servers_executions,
2810		plan_hash_value,
2811		is_bind_sensitive,
2812		is_shareable,
2813		io_interconnect_bytes,
2814		sql_profile,
2815		physical_read_requests AS phy_read_requests,
2816		ROUND(physical_read_requests / NULLIF(executions, 0), 4) AS phy_read_requests_exec,
2817		ROUND(physical_read_requests / NULLIF(physical_read_requests_inst, 0) * 100, 2) AS phy_read_requests_inst,
2818		ROUND(physical_read_requests / NULLIF(physical_read_requests_all, 0) * 100, 2) AS phy_read_requests_all,
2819		physical_read_bytes AS phy_read_bytes,
2820		ROUND(physical_read_bytes / NULLIF(executions, 0), 4) AS phy_read_bytes_exec,
2821		ROUND(physical_read_bytes / NULLIF(physical_read_bytes_inst, 0) * 100, 2) AS phy_read_bytes_inst,
2822		ROUND(physical_read_bytes / NULLIF(physical_read_bytes_all, 0) * 100, 2) AS phy_read_bytes_all,
2823		physical_write_requests AS phy_write_requests,
2824		ROUND(physical_write_requests / NULLIF(executions, 0), 4) AS phy_write_requests_exec,
2825		ROUND(physical_write_requests / NULLIF(physical_write_requests_inst, 0) * 100, 2) AS phy_write_requests_inst,
2826		ROUND(physical_write_requests / NULLIF(physical_write_requests_all, 0) * 100, 2) AS phy_write_requests_all,
2827		physical_write_bytes AS phy_write_bytes,
2828		ROUND(physical_write_bytes / NULLIF(executions, 0), 4) AS phy_write_bytes_exec,
2829		ROUND(physical_write_bytes / NULLIF(physical_write_bytes_inst, 0) * 100, 2) AS phy_write_bytes_inst,
2830		ROUND(physical_write_bytes / NULLIF(physical_write_bytes_all, 0) * 100, 2) AS phy_write_bytes_all,
2831		optimized_phy_read_requests AS opt_phy_read_requests,
2832		ROUND(optimized_phy_read_requests / NULLIF(executions, 0), 4) AS opt_phy_read_requests_exec,
2833		ROUND(optimized_phy_read_requests / NULLIF(opt_phy_read_req_inst, 0) * 100, 2) AS opt_phy_read_requests_inst,
2834		ROUND(optimized_phy_read_requests / NULLIF(opt_phy_read_req_all, 0) * 100, 2) AS opt_phy_read_requests_all,
2835		REPLACE(sql_fulltext, CHR(0), '') AS sql_text,
2836		cpu_time / 1000 AS cpu_time,
2837		ROUND(cpu_time / (1000 * NULLIF(executions, 0)), 4) cpu_time_exec,
2838		ROUND(100 * (cpu_time / NULLIF(cpu_time_inst, 0)), 2) cpu_time_inst,
2839		ROUND(100 * (cpu_time / NULLIF(cpu_time_all, 0)), 2) AS cpu_time_all,
2840		elapsed_time / 1000 AS elapsed_time,
2841		ROUND(elapsed_time / (1000 * NULLIF(executions, 0)), 4) AS elapsed_time_exec,
2842		ROUND(100 * (elapsed_time / NULLIF(elapsed_time_inst, 0)), 2) AS elapsed_time_inst,
2843		ROUND(100 * (elapsed_time / NULLIF(elapsed_time_all, 0)), 2) AS elapsed_time_all,
2844		buffer_gets,
2845		ROUND(buffer_gets / NULLIF(executions, 0), 4) AS buffer_gets_exec,
2846		ROUND(100 * (buffer_gets / NULLIF(buffer_gets_all, 0)), 2) AS buffer_gets_all,
2847		ROUND(100 * (buffer_gets / NULLIF(buffer_gets_inst, 0)), 2) buffer_gets_inst,
2848		ROUND(buffer_gets / NULLIF(rows_processed, 0), 2) buffer_gets_row,
2849		disk_reads,
2850		ROUND(disk_reads / NULLIF(executions, 0), 4) AS disk_reads_exec,
2851		ROUND(100 * (disk_reads / NULLIF(disk_reads_all, 0)), 2) AS disk_reads_all,
2852		ROUND(100 * (disk_reads / NULLIF(disk_reads_inst, 0)), 2) disk_reads_inst,
2853		ROUND(disk_reads / NULLIF(rows_processed, 0) * 100, 2) disk_reads_row,
2854		executions,
2855		parse_calls,
2856		ROUND(100 * (1 - parse_calls / NULLIF(executions, 0)), 2) AS execute_parse_ratio,
2857		TO_CHAR(TO_TIMESTAMP(first_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS first_load_time,
2858		TO_CHAR(TO_TIMESTAMP(last_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS last_load_time,
2859		TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI:SS') AS last_active_time,
2860		application_wait_time / 1000 AS application_wait_time,
2861		ROUND(application_wait_time / (1000 * NULLIF(executions, 0)), 4) AS application_wait_time_exec,
2862		concurrency_wait_time / 1000 AS concurrency_wait_time,
2863		ROUND(concurrency_wait_time / (1000 * NULLIF(executions, 0)), 4) AS concurrency_wait_time_exec,
2864		cluster_wait_time / 1000 AS cluster_wait_time,
2865		ROUND(cluster_wait_time / (1000 * NULLIF(executions, 0)), 4) AS cluster_wait_time_exec,
2866		user_io_wait_time / 1000 AS user_io_wait_time,
2867		ROUND(user_io_wait_time / (1000 * NULLIF(executions, 0)), 4) AS user_io_wait_time_exec,
2868		rows_processed,
2869		ROUND(rows_processed / NULLIF(executions, 0), 4) AS rows_processed_exec,
2870		optimizer_cost
2871	FROM gv$sql
2872		JOIN top_20_inst USING (sql_id, inst_id)
2873		JOIN stats USING (inst_id)
2874)
2875/
2876
2877EXEC dbms_output.put_line('<!--');
2878SET TERMOUT ON
2879EXEC dbms_output.put_line(' ->  Processes [Instance]');
2880SET TERMOUT OFF
2881EXEC dbms_output.put_line('-->');
2882
2883SELECT XMLELEMENT
2884(
2885	"INSTANCE_PROCESSES",
2886	XMLAGG
2887	(
2888		XMLELEMENT
2889		(
2890			"ROW",
2891			XMLFOREST
2892			(
2893				p.inst_id,
2894				addr,
2895				pid,
2896				REPLACE(spid, CHR(0), ' ') spid,
2897				p.serial# AS serial_num,
2898				s.sid AS session_id,
2899				s.serial# AS session_serial_num,
2900				REPLACE(p.username, CHR(0), ' ') username,
2901				REPLACE(p.terminal, CHR(0), ' ') terminal,
2902				REPLACE(p.program, CHR(0), ' ') program,
2903				background,
2904				latchwait,
2905				latchspin,
2906				pga_used_mem,
2907				pga_alloc_mem,
2908				pga_freeable_mem,
2909				pga_max_mem
2910			)
2911		) ORDER BY p.inst_id, p.username
2912	)
2913).getclobval()
2914FROM gv$process p LEFT JOIN gv$session s ON (p.inst_id = s.inst_id AND p.addr = s.paddr)
2915/
2916
2917EXEC dbms_output.put_line('<!--');
2918SET TERMOUT ON
2919EXEC dbms_output.put_line(' ->  Redo Log Files [Database Files]');
2920SET TERMOUT OFF
2921EXEC dbms_output.put_line('-->');
2922
2923SELECT XMLELEMENT
2924(
2925	"DB_REDO_LOG_FILES",
2926	XMLAGG
2927	(
2928		XMLELEMENT
2929		(
2930			"ROW",
2931			XMLFOREST
2932			(
2933				inst_id,
2934				group# AS group_num,
2935				status,
2936				type,
2937				member
2938			)
2939		) ORDER BY inst_id, group#
2940	)
2941).getclobval()
2942FROM gv$logfile
2943/
2944
2945EXEC dbms_output.put_line('<!--');
2946SET TERMOUT ON
2947EXEC dbms_output.put_line(' ->  Redo Log Groups [Database Files]');
2948SET TERMOUT OFF
2949EXEC dbms_output.put_line('-->');
2950
2951SELECT XMLELEMENT
2952(
2953	"DB_REDO_LOG_GROUPS",
2954	XMLAGG
2955	(
2956		XMLELEMENT
2957		(
2958			"ROW",
2959			XMLFOREST
2960			(
2961				inst_id,
2962				group# AS group_num,
2963				thread# AS thread_num,
2964				sequence# AS sequence_num,
2965				bytes / 1048576 AS mbytes,
2966				blocksize,
2967				members,
2968				archived,
2969				status,
2970				first_change# AS first_change_num,
2971				TO_CHAR(first_time, 'YYYY-MM-DD HH24:MI:SS') AS first_time
2972			)
2973		) ORDER BY inst_id, group#, thread#, sequence#
2974	)
2975).getclobval()
2976FROM gv$log	
2977/
2978
2979EXEC dbms_output.put_line('<!--');
2980SET TERMOUT ON
2981EXEC dbms_output.put_line(' ->  Redo Log Switch Statistics [Database Files]');
2982SET TERMOUT OFF
2983EXEC dbms_output.put_line('-->');
2984
2985SELECT XMLELEMENT
2986(
2987	"DB_REDO_LOG_SWITCH",
2988	XMLAGG
2989	(
2990		XMLELEMENT
2991		(
2992			"ROW",
2993			XMLFOREST
2994			(
2995				inst_id,
2996				recid,
2997				stamp,
2998				thread# AS thread_num,
2999				sequence# AS sequence_num,
3000				first_change# AS first_change_num,
3001				TO_CHAR(first_time, 'YYYY-MM-DD HH24:MI:SS') AS first_time,
3002				seconds_before_switch,
3003				next_change# AS next_change_num
3004			)
3005		)
3006	)
3007).getclobval()
3008FROM
3009(
3010	SELECT inst_id,
3011		recid,
3012		stamp,
3013		thread#,
3014		sequence#,
3015		first_change#,
3016		first_time,
3017		TRUNC((first_time - LAG(first_time, 1) OVER (PARTITION BY thread# ORDER BY sequence#)) * 86400) AS seconds_before_switch,
3018		next_change#
3019	FROM gv$log_history
3020	WHERE first_time >= SYSDATE - 7
3021	AND (inst_id, thread#) IN (
3022		SELECT inst_id, thread#
3023		FROM gv$instance
3024	)
3025	ORDER BY inst_id, thread#, sequence# DESC
3026)
3027/
3028
3029EXEC dbms_output.put_line('<!--');
3030SET TERMOUT ON
3031EXEC dbms_output.put_line(' ->  Release [RDBMS]');
3032SET TERMOUT OFF
3033EXEC dbms_output.put_line('-->');
3034
3035SELECT XMLELEMENT
3036(
3037	"ORACLE_RELEASE",
3038	XMLAGG
3039	(
3040		XMLELEMENT
3041		(
3042			"ROW",
3043			XMLFOREST
3044			(
3045				inst_id,
3046				banner
3047			)
3048		) ORDER BY inst_id
3049	)
3050).getclobval()
3051FROM gv$version	
3052/
3053
3054EXEC dbms_output.put_line('<!--');
3055SET TERMOUT ON
3056EXEC dbms_output.put_line(' ->  Resource Limits [Instance]');
3057SET TERMOUT OFF
3058EXEC dbms_output.put_line('-->');
3059
3060SELECT XMLELEMENT
3061(
3062	"INSTANCE_RESOURCE_LIMITS",
3063	XMLAGG
3064	(
3065		XMLELEMENT
3066		(
3067			"ROW",
3068			XMLFOREST
3069			(
3070				inst_id,
3071				resource_name,
3072				current_utilization,
3073				max_utilization,
3074				initial_allocation,
3075				limit_value
3076			)
3077		) ORDER BY inst_id, resource_name
3078	)
3079).getclobval()
3080FROM gv$resource_limit
3081/
3082
3083EXEC dbms_output.put_line('<!--');
3084SET TERMOUT ON
3085EXEC dbms_output.put_line(' ->  Resource Profiles [Database]');
3086SET TERMOUT OFF
3087EXEC dbms_output.put_line('-->');
3088
3089SELECT XMLELEMENT
3090(
3091	"DB_PROFILES",
3092	XMLAGG
3093	(
3094		XMLELEMENT
3095		(
3096			"ROW",
3097			XMLFOREST
3098			(
3099				profile,
3100				resource_name,
3101				resource_type,
3102				limit AS limit_value
3103			)
3104		) ORDER BY profile, resource_type, resource_name
3105	)
3106).getclobval()
3107FROM dba_profiles	
3108/
3109
3110EXEC dbms_output.put_line('<!--');
3111SET TERMOUT ON
3112EXEC dbms_output.put_line(' ->  Role Privileges [Privileges / Roles]');
3113SET TERMOUT OFF
3114EXEC dbms_output.put_line('-->');
3115
3116SELECT XMLELEMENT
3117(
3118	"USR_ROLES",
3119	XMLAGG
3120	(
3121		XMLELEMENT
3122		(
3123			"ROW",
3124			XMLFOREST
3125			(
3126				granted_role,
3127				admin_option,
3128				default_role
3129			)
3130		)
3131	)
3132).getclobval()
3133FROM dba_role_privs
3134WHERE grantee = '&user_uc.'
3135/
3136
3137EXEC dbms_output.put_line('<!--');
3138SET TERMOUT ON
3139EXEC dbms_output.put_line(' ->  SGA Target Advisory [SGA/PGA Advisories]');
3140SET TERMOUT OFF
3141EXEC dbms_output.put_line('-->');
3142
3143SELECT XMLELEMENT
3144(
3145	"INSTANCE_SGA_TARGET_ADVICE",
3146	XMLAGG
3147	(
3148		XMLELEMENT
3149		(
3150			"ROW",
3151			XMLFOREST
3152			(
3153				inst_id,
3154				sga_size,
3155				sga_size_factor,
3156				estd_db_time,
3157				estd_db_time_factor,
3158				estd_physical_reads
3159			)
3160		) ORDER BY inst_id, sga_size_factor
3161	)
3162).getclobval()
3163FROM gv$sga_target_advice	
3164/
3165
3166EXEC dbms_output.put_line('<!--');
3167SET TERMOUT ON
3168EXEC dbms_output.put_line(' ->  Segment Sizes (By Segment) [Database]');
3169SET TERMOUT OFF
3170EXEC dbms_output.put_line('-->');
3171
3172SELECT XMLELEMENT
3173(
3174	"USR_SEGMENT_SIZES_EXT",
3175	XMLAGG
3176	(
3177		XMLELEMENT
3178		(
3179			"ROW",
3180			XMLFOREST
3181			(
3182				segment_type,
3183				segment_name,
3184				tablespace_name,
3185				mbytes,
3186				blocks
3187			)
3188		)
3189	)
3190).getclobval()
3191FROM
3192(
3193	SELECT segment_type,
3194		segment_name,
3195		tablespace_name,
3196		SUM(bytes) / 1048576 AS mbytes,
3197		SUM(blocks) AS blocks
3198	FROM dba_segments
3199	WHERE owner = '&user_uc.'
3200	GROUP BY segment_type, segment_name, tablespace_name
3201	ORDER BY 4 DESC, 1, 2
3202)	
3203/
3204
3205EXEC dbms_output.put_line('<!--');
3206SET TERMOUT ON
3207EXEC dbms_output.put_line(' ->  Segment Statistics [Locking / Statistics]');
3208SET TERMOUT OFF
3209EXEC dbms_output.put_line('-->');
3210
3211SELECT XMLELEMENT
3212(
3213	"DB_SEGMENT_STATISTICS",
3214	XMLAGG
3215	(
3216		XMLELEMENT
3217		(
3218			"ROW",
3219			XMLFOREST
3220			(
3221				inst_id,
3222				owner,
3223				object_name,
3224				object_type,
3225				statistic_name,
3226				aggval
3227			)
3228		) ORDER BY owner, object_name, statistic_name, inst_id
3229	)
3230).getclobval()
3231FROM (
3232	SELECT inst_id, owner, object_type, object_name, statistic_name, SUM(value) aggval
3233	FROM gv$segstat ss JOIN dba_objects do ON (ss.obj# = do.object_id)
3234	WHERE ss.value != 0
3235	AND owner IN ('CTXSYS', '&user_uc.')
3236	AND LOWER(ss.statistic_name) IN (
3237		'buffer busy waits',
3238		'db block changes',
3239		'ITL waits',	
3240		'logical reads',
3241		'physical reads',
3242		'physical reads direct',
3243		'physical writes',
3244		'physical writes direct',
3245		'row lock waits',
3246		'segment scans',
3247		'space allocated',
3248		'space used'
3249	)
3250	GROUP BY owner, object_type, object_name, statistic_name, inst_id
3251)
3252/
3253
3254EXEC dbms_output.put_line('<!--');
3255SET TERMOUT ON
3256EXEC dbms_output.put_line(' ->  Sequences [Sequences]');
3257SET TERMOUT OFF
3258EXEC dbms_output.put_line('-->');
3259
3260SELECT XMLELEMENT
3261(
3262	"USR_SEQUENCES",
3263	XMLAGG
3264	(
3265		XMLELEMENT
3266		(
3267			"ROW",
3268			XMLFOREST
3269			(
3270				sequence_name,
3271				min_value,
3272				max_value,
3273				increment_by,
3274				cycle_flag,
3275				order_flag,
3276				cache_size,
3277				last_number
3278			)
3279		) ORDER BY sequence_name
3280	)
3281).getclobval()
3282FROM dba_sequences
3283WHERE sequence_owner = '&user_uc.'
3284/
3285
3286EXEC dbms_output.put_line('<!--');
3287SET TERMOUT ON
3288EXEC dbms_output.put_line(' ->  Server Information [Environment]');
3289SET TERMOUT OFF
3290EXEC dbms_output.put_line('-->');
3291
3292SELECT XMLELEMENT
3293(
3294	"ENVIRONMENT_OS_INFO",
3295	XMLAGG
3296	(
3297		XMLELEMENT
3298		(
3299			"ROW",
3300			XMLFOREST
3301			(
3302				inst_id,
3303				stat_name,
3304				value,
3305				comments,
3306				cumulative
3307			)
3308		) ORDER BY stat_name, inst_id
3309	)
3310).getclobval()
3311FROM gv$osstat
3312/
3313
3314EXEC dbms_output.put_line('<!--');
3315SET TERMOUT ON
3316EXEC dbms_output.put_line(' ->  Session I/O [Sessions]');
3317SET TERMOUT OFF
3318EXEC dbms_output.put_line('-->');
3319
3320SELECT XMLELEMENT
3321(
3322	"SQL_SESSION_IO",
3323	XMLAGG
3324	(
3325		XMLELEMENT
3326		(
3327			"ROW",
3328			XMLFOREST
3329			(
3330				s.inst_id,
3331				sio.sid,
3332				s.serial# AS serial_num,
3333				username,
3334				block_gets,
3335				consistent_gets,
3336				physical_reads,
3337				block_changes,
3338				consistent_changes
3339			)
3340		) ORDER BY username
3341	)
3342).getclobval()
3343FROM gv$sess_io sio, gv$session s
3344WHERE sio.sid = s.sid
3345AND sio.inst_id = s.inst_id	
3346/
3347
3348EXEC dbms_output.put_line('<!--');
3349SET TERMOUT ON
3350EXEC dbms_output.put_line(' ->  Session Long Operations [Sessions]');
3351SET TERMOUT OFF
3352EXEC dbms_output.put_line('-->');
3353
3354SELECT XMLELEMENT
3355(
3356	"SQL_SESSION_LONGOPS",
3357	XMLAGG
3358	(
3359		XMLELEMENT
3360		(
3361			"ROW",
3362			XMLFOREST
3363			(
3364				inst_id,
3365				sid,
3366				serial_num,
3367				username,
3368				opname,
3369				target,
3370				target_desc,
3371				sofar,
3372				totalwork,
3373				units,
3374				start_time,
3375				last_update_time,
3376				time_remaining,
3377				elapsed_seconds,
3378				context,
3379				message,
3380				sql_id,
3381				sql_text
3382			)
3383		) 
3384	)
3385).getclobval()
3386FROM (
3387	SELECT slops.inst_id,
3388		sid,
3389		serial# AS serial_num,
3390		username,
3391		opname,
3392		target,
3393		target_desc,
3394		sofar,
3395		totalwork,
3396		units,
3397		TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
3398		TO_CHAR(last_update_time, 'YYYY-MM-DD HH24:MI:SS') AS last_update_time,
3399		time_remaining,
3400		elapsed_seconds,
3401		context,
3402		message,
3403		slops.sql_id,
3404		REPLACE(sql_fulltext, CHR(0), ' ') AS sql_text
3405 FROM 
3406	gv$session_longops slops LEFT JOIN gv$sqlarea sql ON (slops.sql_id = sql.sql_id AND slops.inst_id = sql.inst_id)
3407 ORDER BY elapsed_time DESC NULLS LAST
3408)
3409WHERE ROWNUM <= 100	
3410/
3411
3412EXEC dbms_output.put_line('<!--');
3413SET TERMOUT ON
3414EXEC dbms_output.put_line(' ->  Shared Pool Advisory [SGA/PGA Advisories]');
3415SET TERMOUT OFF
3416EXEC dbms_output.put_line('-->');
3417
3418SELECT XMLELEMENT
3419(
3420	"INSTANCE_SHARED_POOL_ADVICE",
3421	XMLAGG
3422	(
3423		XMLELEMENT
3424		(
3425			"ROW",
3426			XMLFOREST
3427			(
3428				inst_id,
3429				shared_pool_size_for_estimate,
3430				shared_pool_size_factor,
3431				estd_lc_size,
3432				estd_lc_memory_objects,
3433				estd_lc_time_saved,
3434				estd_lc_time_saved_factor,
3435				estd_lc_memory_object_hits
3436			)
3437		) ORDER BY inst_id, shared_pool_size_factor
3438	)
3439).getclobval()
3440FROM gv$shared_pool_advice	
3441/
3442
3443EXEC dbms_output.put_line('<!--');
3444SET TERMOUT ON
3445EXEC dbms_output.put_line(' ->  Staging Table [Enfinity/Intershop]');
3446SET TERMOUT OFF
3447EXEC dbms_output.put_line('-->');
3448
3449SELECT XMLELEMENT
3450(
3451	"APP_STAGING_TABLE",
3452	XMLAGG(
3453		XMLELEMENT
3454		(
3455			"ROW",
3456			XMLFOREST
3457			(
3458				domainname,
3459				tablename,
3460				domainspecificflag,
3461				readonlyflag,
3462				foreignkeyname,
3463				columnnameofdomainid,
3464				cartridgename,
3465				livetablenamesuffix,
3466				staginggroupid,
3467				preparationflag,
3468				preparername,
3469				st.uuid,
3470				TO_CHAR(st.lastmodified, 'YYYY-MM-DD HH24:MI:SS') AS lastmodified
3471			)
3472		) ORDER BY domainname, tablename
3473	) 
3474).getclobval()
3475FROM &user_uc..stagingtable st, &user_uc..domaininformation di
3476WHERE st.domainid = di.domainid
3477/
3478
3479EXEC dbms_output.put_line('<!--');
3480SET TERMOUT ON
3481EXEC dbms_output.put_line(' ->  Status [Database]');
3482SET TERMOUT OFF
3483EXEC dbms_output.put_line('-->');
3484
3485SELECT XMLELEMENT
3486(
3487	"DB_OVERVIEW",
3488	XMLAGG
3489	(
3490		XMLELEMENT
3491		(
3492			"ROW",
3493			XMLFOREST
3494			(
3495				inst_id,
3496				dbid,
3497				name,
3498				TO_CHAR(created, 'YYYY-MM-DD HH24:MI:SS') AS created,
3499				resetlogs_change# AS resetlogs_change_num,
3500				TO_CHAR(resetlogs_time, 'YYYY-MM-DD HH24:MI:SS') AS resetlogs_time,
3501				prior_resetlogs_change# AS prior_resetlogs_change,
3502				TO_CHAR(prior_resetlogs_time, 'YYYY-MM-DD HH24:MI:SS') AS prior_resetlogs_time_num,
3503				log_mode,
3504				force_logging,
3505				flashback_on,
3506				checkpoint_change# AS checkpoint_change_num,
3507				archive_change# AS archive_change_num,
3508				controlfile_type,
3509				TO_CHAR(controlfile_created, 'YYYY-MM-DD HH24:MI:SS') AS controlfile_created,
3510				controlfile_sequence# AS controlfile_sequence_num,
3511				controlfile_change# AS controlfile_change_num,
3512				TO_CHAR(controlfile_time, 'YYYY-MM-DD HH24:MI:SS') AS controlfile_time,
3513				open_resetlogs,
3514				TO_CHAR(version_time, 'YYYY-MM-DD HH24:MI:SS') AS version_time,
3515				open_mode
3516			)
3517		) ORDER BY inst_id
3518	)
3519).getclobval()
3520FROM gv$database
3521/
3522
3523EXEC dbms_output.put_line('<!--');
3524SET TERMOUT ON
3525EXEC dbms_output.put_line(' ->  Status [Instance]');
3526SET TERMOUT OFF
3527EXEC dbms_output.put_line('-->');
3528
3529SELECT XMLELEMENT
3530(
3531	"INSTANCE_OVERVIEW",
3532	XMLAGG
3533	(
3534		XMLELEMENT
3535		(
3536			"ROW",
3537			XMLFOREST
3538			(
3539				inst_id,
3540				instance_number,
3541				instance_name,
3542				host_name,
3543				version,
3544				TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS') AS startup_time,
3545				status,
3546				parallel,
3547				thread# AS thread_num,
3548				archiver,
3549				log_switch_wait,
3550				logins,
3551				shutdown_pending,
3552				database_status,
3553				instance_role
3554			)
3555		) ORDER BY inst_id
3556	)
3557).getclobval()
3558FROM gv$instance
3559/
3560
3561EXEC dbms_output.put_line('<!--');
3562SET TERMOUT ON
3563EXEC dbms_output.put_line(' ->  Synonyms [Tables]');
3564SET TERMOUT OFF
3565EXEC dbms_output.put_line('-->');
3566
3567SELECT XMLELEMENT
3568(
3569	"USR_SYNONYMS",
3570	XMLAGG
3571	(
3572		XMLELEMENT
3573		(
3574			"ROW",
3575			XMLFOREST
3576			(
3577				synonym_name,
3578				table_owner,
3579				table_name,
3580				db_link
3581			)
3582		) ORDER BY synonym_name
3583	)
3584).getclobval()
3585FROM dba_synonyms
3586WHERE owner = '&user_uc.'
3587AND table_name NOT LIKE 'BIN$%'
3588/
3589
3590EXEC dbms_output.put_line('<!--');
3591SET TERMOUT ON
3592EXEC dbms_output.put_line(' ->  System Statistics [Locking / Statistics]');
3593SET TERMOUT OFF
3594EXEC dbms_output.put_line('-->');
3595
3596SELECT XMLELEMENT
3597(
3598	"INSTANCE_SYSTEM_STATISTICS",
3599	XMLAGG
3600	(
3601		XMLELEMENT
3602		(
3603			"ROW",
3604			XMLFOREST
3605			(
3606				inst_id,
3607				statistic# AS statistic_num,
3608				name,
3609				REGEXP_REPLACE(
3610					CASE WHEN BITAND(class, 1) = 1 THEN 'User/' END ||
3611					CASE WHEN BITAND(class, 2) = 2 THEN 'Redo/' END ||
3612					CASE WHEN BITAND(class, 4) = 4 THEN 'Enqueue/' END ||
3613					CASE WHEN BITAND(class, 8) = 8 THEN 'Cache/' END ||
3614					CASE WHEN BITAND(class, 16) = 16 THEN 'OS/' END ||
3615					CASE WHEN BITAND(class, 32) = 32 THEN 'RAC/' END ||
3616					CASE WHEN BITAND(class, 64) = 64 THEN 'SQL/' END ||
3617					CASE WHEN BITAND(class, 128) = 128 THEN 'Debug/' END,
3618					'\/$',
3619					''
3620				) AS class,
3621				value
3622			)
3623		) ORDER BY inst_id, class, name
3624	)
3625).getclobval()
3626FROM gv$sysstat	
3627/
3628
3629EXEC dbms_output.put_line('<!--');
3630SET TERMOUT ON
3631EXEC dbms_output.put_line(' ->  System Wait Events [Locking / Statistics]');
3632SET TERMOUT OFF
3633EXEC dbms_output.put_line('-->');
3634
3635SELECT XMLELEMENT
3636(
3637	"INSTANCE_WAIT_EVENTS",
3638	XMLAGG
3639	(
3640		XMLELEMENT
3641		(
3642			"ROW",
3643			XMLFOREST
3644			(
3645				se.inst_id,
3646				se.event,
3647				swc.wait_class,
3648				se.total_waits,
3649				se.total_timeouts,
3650				se.time_waited / 100 time_waited,
3651				average_wait / 100 average_wait
3652			)
3653		) ORDER BY se.inst_id, se.event
3654	)
3655).getclobval()
3656FROM gv$system_event se JOIN gv$system_wait_class swc ON (
3657	se.wait_class_id = swc.wait_class_id 
3658	AND se.inst_id = swc.inst_id
3659)
3660/
3661
3662EXEC dbms_output.put_line('<!--');
3663SET TERMOUT ON
3664EXEC dbms_output.put_line(' ->  Table Columns [Tables]');
3665SET TERMOUT OFF
3666EXEC dbms_output.put_line('-->');
3667
3668SELECT XMLELEMENT
3669(
3670	"USR_TAB_COLUMNS",
3671	XMLAGG
3672	(
3673		XMLELEMENT
3674		(
3675			"ROW",
3676			XMLFOREST
3677			(
3678				table_name,
3679				column_name,
3680				data_type,
3681				data_type_mod,
3682				data_type_owner,
3683				data_length,
3684				data_precision,
3685				data_scale,
3686				nullable,
3687				column_id,
3688				default_length,
3689				num_distinct,
3690				low_value,
3691				high_value,
3692				density,
3693				num_nulls,
3694				num_buckets,
3695				histogram,
3696				TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed,
3697				sample_size,
3698				character_set_name,
3699				char_col_decl_length,
3700				global_stats,
3701				user_stats,
3702				avg_col_len,
3703				char_length,
3704				char_used
3705			)
3706		) ORDER BY table_name, column_id
3707	)
3708).getclobval()
3709FROM dba_tab_columns 
3710WHERE owner = '&user_uc.'
3711AND table_name NOT LIKE 'BIN$%'	
3712/
3713
3714EXEC dbms_output.put_line('<!--');
3715SET TERMOUT ON
3716EXEC dbms_output.put_line(' ->  Table Modification Statistics [Tables]');
3717SET TERMOUT OFF
3718EXEC dbms_output.put_line('-->');
3719
3720SELECT XMLELEMENT
3721(
3722	"USR_TAB_MODIFICATIONS",
3723	XMLAGG
3724	(
3725		XMLELEMENT
3726		(
3727			"USR_TAB_MODIFICATIONS_ROW",
3728			XMLFOREST
3729			(
3730				table_name,
3731				inserts,
3732				updates,
3733				deletes,
3734				num_rows,
3735				change_rate,
3736				ts,
3737				last_analyzed
3738			)
3739		) ORDER BY table_name
3740	)
3741).getclobval()
3742FROM
3743(
3744	SELECT dtm.table_name,
3745		dtm.inserts,
3746		dtm.updates,
3747		dtm.deletes,
3748		dt.num_rows,
3749		ROUND((dtm.inserts + dtm.updates + dtm.deletes) / DECODE(num_rows, 0, 1, num_rows), 4) AS change_rate,
3750		TO_CHAR(dtm.timestamp, 'YYYY-MM-DD HH24:MI:SS') AS ts,
3751		TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed
3752	FROM sys.dba_tab_modifications dtm,
3753		dba_tables dt
3754	WHERE dtm.table_name = dt.table_name
3755	AND dtm.table_owner = dt.owner
3756	AND dtm.table_name NOT LIKE 'BIN$%'
3757	AND dtm.table_name NOT LIKE 'DR$%'
3758	AND dtm.table_owner = '&user_uc.'
3759)
3760/
3761
3762EXEC dbms_output.put_line('<!--');
3763SET TERMOUT ON
3764EXEC dbms_output.put_line(' ->  Tables [Tables]');
3765SET TERMOUT OFF
3766EXEC dbms_output.put_line('-->');
3767
3768SELECT XMLELEMENT
3769(
3770	"USR_TABLES",
3771	XMLAGG
3772	(
3773		XMLELEMENT
3774		(
3775			"ROW",
3776			XMLFOREST
3777			(
3778				t.table_name,
3779				ts.tablespace_name,
3780				cluster_name,
3781				iot_name,
3782				pct_free,
3783				pct_used,
3784				ini_trans,
3785				max_trans,
3786				t.initial_extent,
3787				t.next_extent,
3788				t.min_extents,
3789				t.max_extents,
3790				t.pct_increase,
3791				freelists,
3792				freelist_groups,
3793				t.logging,
3794				backed_up,
3795				t.num_rows,
3796				t.blocks,
3797				CEIL((t.avg_row_len * t.num_rows) / ((block_size - 64 - 24 * t.ini_trans) * (100 - t.pct_free) / 100)) estd_blocks_needed,
3798				t.empty_blocks,
3799				t.avg_space,
3800				t.chain_cnt,
3801				t.avg_row_len,
3802				t.avg_space_freelist_blocks,
3803				t.num_freelist_blocks,
3804				avg_cached_blocks,
3805				avg_cache_hit_ratio,
3806				degree,
3807				instances,
3808				cache,
3809				table_lock,
3810				t.sample_size,
3811				TO_CHAR(t.last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed,
3812				partitioned,
3813				iot_type,
3814				temporary,
3815				secondary,
3816				nested,
3817				buffer_pool,
3818				row_movement,
3819				t.global_stats,
3820				t.user_stats,
3821				duration,
3822				skip_corrupt,
3823				monitoring,
3824				stattype_locked,
3825				stale_stats,
3826				compression
3827			)
3828		) ORDER BY t.table_name
3829	)
3830).getclobval()
3831FROM dba_tables t 
3832	JOIN dba_tablespaces ts ON (t.tablespace_name = ts.tablespace_name) 
3833	JOIN dba_tab_statistics tst ON (t.table_name = tst.table_name AND t.owner = tst.owner)
3834WHERE t.owner = '&user_uc.'
3835AND t.table_name NOT LIKE 'BIN$%'
3836/
3837
3838EXEC dbms_output.put_line('<!--');
3839SET TERMOUT ON
3840EXEC dbms_output.put_line(' ->  Tablespace Usage [Tablespaces]');
3841SET TERMOUT OFF
3842EXEC dbms_output.put_line('-->');
3843
3844
3845			WITH ts_allocation AS (
3846			  SELECT tablespace_name,
3847			    ROUND(SUM(bytes) / 1048576, 2) current_allocation_mb,
3848			    ROUND((SUM(CASE WHEN (autoextensible = 'YES') THEN GREATEST(maxbytes, bytes) ELSE bytes END)) / 1048576, 2) max_allocation_mb
3849			  FROM dba_data_files JOIN dba_tablespaces USING (tablespace_name)
3850			  GROUP BY tablespace_name
3851			), ts_usage AS (
3852			  SELECT tablespace_name, 
3853			    SUM(bytes) / 1048576 free_space_mb
3854			  FROM dba_free_space
3855			  GROUP BY tablespace_name
3856			)
3857			SELECT XMLELEMENT
3858			(
3859				"DB_TS_USAGE",
3860				XMLAGG
3861				(
3862					XMLELEMENT
3863					(
3864						"ROW",
3865						XMLFOREST
3866						(
3867							tablespace_name,
3868							current_allocation_mb,
3869							max_allocation_mb,
3870							used_mb,
3871							free_space_mb,
3872							current_usage,
3873							limit_usage				
3874						)
3875					) ORDER BY tablespace_name
3876				)
3877			).getclobval()
3878			FROM (
3879				SELECT tablespace_name,
3880				  current_allocation_mb,
3881				  max_allocation_mb,
3882				  current_allocation_mb - free_space_mb used_mb,
3883				  free_space_mb,
3884				  ROUND((current_allocation_mb - free_space_mb) / current_allocation_mb, 4) current_usage,
3885				  ROUND((current_allocation_mb - free_space_mb) / max_allocation_mb, 4) limit_usage
3886				FROM ts_allocation JOIN ts_usage USING (tablespace_name)
3887			)
3888/
3889
3890EXEC dbms_output.put_line('<!--');
3891SET TERMOUT ON
3892EXEC dbms_output.put_line(' ->  Tablespaces [Tablespaces]');
3893SET TERMOUT OFF
3894EXEC dbms_output.put_line('-->');
3895
3896SELECT XMLELEMENT
3897(
3898	"DB_TABLESPACES",
3899	XMLAGG
3900	(
3901		XMLELEMENT
3902		(
3903			"ROW",
3904			XMLFOREST
3905			(
3906				tablespace_name,
3907				block_size,
3908				initial_extent,
3909				next_extent,
3910				min_extents,
3911				max_extents,
3912				pct_increase,
3913				min_extlen,
3914				status,
3915				contents,
3916				logging,
3917				force_logging,
3918				extent_management,
3919				allocation_type,
3920				plugged_in,
3921				segment_space_management
3922			)
3923		) ORDER BY tablespace_name
3924	)
3925).getclobval()
3926FROM dba_tablespaces	
3927/
3928
3929EXEC dbms_output.put_line('<!--');
3930SET TERMOUT ON
3931EXEC dbms_output.put_line(' ->  Temp Files [Database Files]');
3932SET TERMOUT OFF
3933EXEC dbms_output.put_line('-->');
3934
3935SELECT XMLELEMENT
3936(
3937	"DB_TEMP_FILES",
3938	XMLAGG
3939	(
3940		XMLELEMENT
3941		(
3942			"ROW",
3943			XMLFOREST
3944			(
3945				file_name,
3946				file_id,
3947				tablespace_name,
3948				bytes,
3949				blocks,
3950				status,
3951				relative_fno,
3952				autoextensible,
3953				maxbytes,
3954				maxblocks,
3955				increment_by,
3956				user_bytes,
3957				user_blocks			
3958			)
3959		) ORDER BY tablespace_name, file_name
3960	)
3961).getclobval()
3962FROM dba_temp_files
3963/
3964
3965EXEC dbms_output.put_line('<!--');
3966SET TERMOUT ON
3967EXEC dbms_output.put_line(' ->  Tempfile I/O Statistics [Database Files]');
3968SET TERMOUT OFF
3969EXEC dbms_output.put_line('-->');
3970
3971SELECT XMLELEMENT
3972(
3973	"DB_TEMPFILES_IO_STATISTICS",
3974	XMLAGG
3975	(
3976		XMLELEMENT
3977		(
3978			"ROW",
3979			XMLFOREST
3980			(
3981				inst_id,
3982				file# AS file_num,
3983				phyrds,
3984				phywrts,
3985				phyblkrd,
3986				phyblkwrt,
3987				readtim,
3988				writetim,
3989				avgiotim,
3990				lstiotim,
3991				miniotim,
3992				maxiowtm,
3993				maxiortm
3994			)
3995		) ORDER BY inst_id, file#
3996	)
3997).getclobval()
3998FROM gv$tempstat
3999/
4000
4001EXEC dbms_output.put_line('<!--');
4002SET TERMOUT ON
4003EXEC dbms_output.put_line(' ->  Temporary Segment Usage [Instance]');
4004SET TERMOUT OFF
4005EXEC dbms_output.put_line('-->');
4006
4007DECLARE
4008	query VARCHAR2(1000) := '
4009		SELECT XMLELEMENT(
4010			"INSTANCE_TEMPSEG_USAGE",
4011			XMLAGG(
4012				XMLELEMENT(
4013					"ROW",
4014					XMLFOREST(
4015						tu.inst_id,
4016						tu.username,
4017						tu.session_addr,
4018						#sql_id_column#,
4019						sa.sql_text,
4020						tu.tablespace,
4021						tu.contents,
4022						tu.segtype,
4023						tu.extents,
4024						tu.blocks
4025					)
4026				) ORDER BY tu.inst_id, tu.blocks DESC
4027			)
4028		).getclobval()
4029	FROM #from_clause#';
4030
4031	sql_id_column VARCHAR2(10) := 'tu.sql_id';
4032	sql_id_column_12c VARCHAR2(30) := 'tu.sql_id_tempseg AS sql_id';
4033	oracle_version NUMBER;
4034	res CLOB;
4035	offset NUMBER := 1;
4036BEGIN
4037	SELECT TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.') - 1))
4038	INTO oracle_version
4039	FROM dba_registry
4040	WHERE comp_id = 'CATALOG';
4041
4042	IF (oracle_version < 12) THEN
4043		query := REPLACE(query, '#sql_id_column#', sql_id_column);
4044		query := REPLACE(query, '#from_clause#', 'gv$tempseg_usage tu LEFT JOIN gv$sqlarea sa ON (tu.sql_id = sa.sql_id AND tu.inst_id = sa.inst_id)');
4045	ELSE
4046		query := REPLACE(query, '#sql_id_column#', sql_id_column_12c);
4047		query := REPLACE(query, '#from_clause#', 'gv$tempseg_usage tu LEFT JOIN gv$sqlarea sa ON (tu.sql_id_tempseg = sa.sql_id AND tu.inst_id = sa.inst_id)');
4048	END IF;
4049
4050	EXECUTE IMMEDIATE query INTO res;
4051
4052	LOOP EXIT WHEN offset >= dbms_lob.getlength(res);
4053		dbms_output.put_line(dbms_lob.substr(res, 150, offset));
4054		offset := offset + 150;
4055	END LOOP;
4056END;
4057/
4058
4059EXEC dbms_output.put_line('<!--');
4060SET TERMOUT ON
4061EXEC dbms_output.put_line(' ->  Triggers [Triggers]');
4062SET TERMOUT OFF
4063EXEC dbms_output.put_line('-->');
4064
4065SELECT XMLELEMENT
4066(
4067	"USR_TRIGGER",
4068	XMLAGG
4069	(
4070		XMLELEMENT
4071		(
4072			"ROW",
4073			XMLFOREST
4074			(
4075				table_name,
4076				trigger_name,
4077				status,
4078				trigger_type,
4079				triggering_event
4080			)
4081		) ORDER BY table_name, trigger_name
4082	)
4083).getclobval()
4084FROM dba_triggers
4085WHERE owner = '&user_uc.'
4086AND table_name NOT LIKE 'BIN$%'	
4087/
4088
4089EXEC dbms_output.put_line('<!--');
4090SET TERMOUT ON
4091EXEC dbms_output.put_line(' ->  User Accounts [Database]');
4092SET TERMOUT OFF
4093EXEC dbms_output.put_line('-->');
4094
4095SELECT XMLELEMENT
4096(
4097	"DB_USERS",
4098	XMLAGG
4099	(
4100		XMLELEMENT
4101		(
4102			"ROW",
4103			XMLFOREST
4104			(
4105				username,
4106				user_id,
4107				TO_CHAR(created, 'YYYY-MM-DD HH24:MI:SS') AS created,
4108				account_status,
4109				TO_CHAR(lock_date, 'YYYY-MM-DD HH24:MI:SS') AS lock_date,
4110				TO_CHAR(expiry_date, 'YYYY-MM-DD HH24:MI:SS') AS expiry_date,
4111				default_tablespace,
4112				temporary_tablespace,
4113				profile,
4114				external_name
4115			)
4116		) ORDER BY username
4117	)
4118).getclobval()
4119FROM dba_users
4120/
4121
4122EXEC dbms_output.put_line('<!--');
4123SET TERMOUT ON
4124EXEC dbms_output.put_line(' ->  Views [Views]');
4125SET TERMOUT OFF
4126EXEC dbms_output.put_line('-->');
4127
4128SELECT XMLELEMENT
4129(
4130	"USR_VIEWS",
4131	XMLAGG
4132	(
4133		XMLELEMENT
4134		(
4135			"ROW",
4136			XMLFOREST
4137			(
4138				view_name,
4139				text
4140			)
4141		) ORDER BY view_name
4142	)
4143).getclobval()
4144FROM
4145(
4146	SELECT 
4147		EXTRACTVALUE(xmlseq.object_value, '/ROW/VIEW_NAME') AS view_name, 
4148		EXTRACTVALUE(xmlseq.object_value, '/ROW/TEXT') AS text    	
4149	FROM (
4150		SELECT XMLTYPE(dbms_xmlgen.getxml(
4151			'SELECT view_name, 
4152				text
4153			FROM dba_views 
4154			WHERE owner = ''&user_uc.'''
4155			)
4156		) AS xml
4157		FROM DUAL
4158	) dc_xml, 
4159	TABLE(XMLSEQUENCE(EXTRACT(dc_xml.xml, '/ROWSET/ROW'))) xmlseq
4160)
4161/
4162
4163EXEC dbms_output.put_line('<!--');
4164SET TERMOUT ON
4165EXEC dbms_output.put_line(' ->  Wait Event Histograms [Locking / Statistics]');
4166SET TERMOUT OFF
4167EXEC dbms_output.put_line('-->');
4168
4169SELECT XMLELEMENT
4170(
4171	"INSTANCE_EVENT_HISTOGRAM",
4172	XMLAGG
4173	(
4174		XMLELEMENT
4175		(
4176			"ROW",
4177			XMLFOREST
4178			(
4179				inst_id,
4180				event,
4181				wait_time_milli,
4182				wait_count,
4183				last_update_time								
4184			)
4185		) ORDER BY inst_id, event, wait_time_milli
4186	)
4187).getclobval()
4188FROM gv$event_histogram
4189/
4190
4191
4192
4193-- end of generated content
4194
4195/*
4196	flush spool
4197*/
4198
4199-- add closing tag
4200EXECUTE dbms_output.put_line('</gdbiX>');
4201
4202-- enable terminal output before flushing, to catch errors due to I/O problems, etc., when writing the result to disk
4203SET TERMOUT ON
4204
4205-- flush spool file
4206SPOOL OFF;
4207
4208EXEC dbms_output.put_line('-> gdbiX finished successfully (output written to ''gdbiX_v&version.__&user_uc.__&exec_date..xml'').');
4209
4210QUIT;