As part of a larger project trying to pull this data into Powershell, I am using a PL/SQL script to pull the undo values - current and recommended, from Oracle and output to an XML file that I can then parse with Powershell. I would also eventually like the recommended undo parameter to return with a timestamp that the query was run at to compare how it changes over a period of time.
At this point though, I am just working with trying to output as follows:
<currundo>903</currundo>
<recundo>14400</recundo>
When I run this pl/sql I am getting:
ERROR at line 5:
ORA-06550: line 5, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
Here is the code:
set heading off
DECLARE
l_xmltype XMLTYPE;
BEGIN
select dbms_xmlgen.getxml('SELECT SUBSTR(e.value,1,25) "curundo", ROUND(d.undo_size / (to_number(f.value) * g.undo_block_per_sec)) "recundo"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ''UNDO''
AND c.status = ''ONLINE''
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) into d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) into g
WHERE e.name = ''undo_retention''
AND f.name = ''db_block_size''') from dual;
END;
/
