============================
This is an example of generating a HTML report from a PLSQL.
The Report in this example, reports Tablespace usage.
In this example, there
============================
The Flow
============================
In this example:
Loop on a table holding a list of remote Database Links.
Per each Database Link - run SQL to get the max space, used space, free space, and %free.
It the %free is below a limit (in this example 15%) write this record to report.
addHtmlLine is responsible for adding each line in the report.
============================
Generated Report Example
============================
============================
Code parts
============================
SQL - to get the Tablespace usage data
Main program
addHtmlLine procedure
============================
SQL - to get the Tablespace usage data
============================
============================
SELECT TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE))AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END
AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE@DB_LINK_XXX
WHERE tablespace_name LIKE '%MY_TBS_A%'
OR tablespace_name LIKE '%MY_TBS_B%'
OR tablespace_name LIKE '%REPORTS%'
OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END/1024/1024)
AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES@DB_LINK_XXX
WHERE tablespace_name LIKE '%MY_TBS_A%'
OR tablespace_name LIKE '%MY_TBS_B%'
OR tablespace_name LIKE '%REPORTS%'
OR tablespace_name = 'SYSTEM'
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS@DB_LINK_XXX
WHERE tablespace_name LIKE '%MY_TBS_A%'
OR tablespace_name LIKE '%MY_TBS_B%'
OR tablespace_name LIKE '%REPORTS%'
OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;
============================
main program
============================
main program
============================
c_cursor SYS_REFCURSOR;
CURSOR getDbListCur IS
SELECT ALL_DEST_VW.service_name,
ALL_DEST_VW.customer_name,
ALL_DEST_VW.user_name,
ALL_DEST_VW.db_link,
USEFUL_LINKS.host host
FROM (
SELECT DBA_DB_LINKS.host, MIN(ALL_DEST_VW.user_name||ALL_DEST_VW.service_name||ALL_DEST_VW.db_link) AS service_db_link
FROM DBA_DB_LINKS,
ALL_DEST_VW
WHERE active = 'Y'
AND ALL_DEST_VW.db_link = DBA_DB_LINKS.db_link
AND host NOT IN ('GEN_FRANK_SPARX')
GROUP BY DBA_DB_LINKS.host
ORDER BY 1,2) USEFUL_LINKS,
ALL_DEST_VW,
DBA_DB_LINKS
WHERE USEFUL_LINKS.service_db_link = ALL_DEST_VW.user_name||ALL_DEST_VW.service_name||ALL_DEST_VW.db_link
AND ALL_DEST_VW.db_link = DBA_DB_LINKS.db_link
AND ALL_DEST_VW.active = 'Y'
AND ALL_DEST_VW.active = 'Y'
ORDER BY service_name, customer_name;
v_MailModuleName VARCHAR2(100);
v_tablespace_name DBA_TABLESPACES.tablespace_name%TYPE;
v_free_space NUMBER(9);
v_used_space NUMBER(9);
v_max_space NUMBER(9);
v_free_pct NUMBER(9);
v_add_more_space VARCHAR2(1);
v_run_date VARCHAR2(10);
v_report_text VARCHAR2(32000) := '';
v_sql_get_free_space_base VARCHAR2(2000);
v_sql_get_free_space VARCHAR2(2000);
v_send_report_ind VARCHAR2(1);
v_counter NUMBER;
v_html_color VARCHAR2(100);
v_close_db_link_base VARCHAR2(200);
v_close_db_link_sql VARCHAR2(200);
v_error_msg VARCHAR2(200);
BEGIN
v_send_report_ind := 'N';
v_MailModuleName := 'REPORT_DISK_SPACE_ON_GATES';
v_close_db_link_base := 'ALTER SESSION CLOSE DATABASE LINK ';
v_run_date := TO_CHAR(SYSDATE-1,'DD/MM/YYYY');
v_counter := 0;
v_report_text := '';
v_report_text :='Daily Report Disk Space on Gates for date: '||v_run_date||'<BR><BR>';
v_report_text :=v_report_text||'<TABLE BORDER=1 BGCOLOR="DARKBLUE">';
v_report_text :=v_report_text||'<TR BGCOLOR="WHITE">';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Service Name</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Customer Name</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:20%">Schema</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">DB Link</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Host</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Tablespace Name</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Free Space MB</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Used Space MB</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">Max Space MB</FONT></TH>';
v_report_text :=v_report_text||'<TH><FONT COLOR="DARKBLUE" style="width:10%">% Free</FONT></TH>';
v_report_text :=v_report_text||'</TR>';
v_sql_get_free_space_base :=
'SELECT TABLESPACE_NAME, '||
-- 'MAX(free_space) AS DBA_FREE_SPACE_MB, '||
'(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB, '||
'MAX(USED_SPACE) AS USED_SPACE_MB, '||
'MAX(MAX_SPACE) AS MAX_SPACE_MB, '||
'ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, '||
'CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN ''Y'' ELSE ''N'' END AS ADD_MORE_SPACE '||
'FROM ( '||
'SELECT tablespace_name, '||
'ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, '||
'0 AS MAX_SPACE, '||
'0 AS USED_SPACE '||
'FROM DBA_FREE_SPACE@DB_LINK_XXX '||
'WHERE tablespace_name LIKE ''%IGT%'' OR tablespace_name LIKE ''%DWH%'' OR tablespace_name LIKE ''%REPORTS%'' OR tablespace_name = ''SYSTEM'' '||
'GROUP BY tablespace_name '||
'UNION ALL '||
'SELECT tablespace_name, '||
'0 AS FREE_SPACE, '||
'ROUND(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END/1024/1024) AS MAX_SPACE, '||
'0 AS USED_SPACE '||
'FROM DBA_DATA_FILES@DB_LINK_XXX '||
'WHERE tablespace_name LIKE ''%IGT%'' OR tablespace_name LIKE ''%DWH%'' OR tablespace_name LIKE ''%REPORTS%'' OR tablespace_name = ''SYSTEM'' '||
'UNION ALL '||
'SELECT tablespace_name, '||
'0 AS FREE_SPACE, '||
'0 AS MAX_SPACE, '||
'ROUND(SUM(bytes/1024/1024)) AS USED_SPACE '||
'FROM DBA_SEGMENTS@DB_LINK_XXX '||
'WHERE tablespace_name LIKE ''%IGT%'' OR tablespace_name LIKE ''%DWH%'' OR tablespace_name LIKE ''%REPORTS%'' OR tablespace_name = ''SYSTEM'' '||
'GROUP BY tablespace_name '||
') '||
'GROUP BY tablespace_name '
;
FOR getDbListRec IN getDbListCur LOOP
BEGIN
v_sql_get_free_space := REPLACE(v_sql_get_free_space_base, 'DB_LINK_XXX', getDbListRec.db_link);
OPEN c_cursor FOR v_sql_get_free_space;
LOOP
FETCH c_cursor INTO v_tablespace_name,v_free_space, v_used_space, v_max_space, v_free_pct,v_add_more_space;
IF v_add_more_space = 'Y' THEN
-- IF 'Y' = 'Y' THEN
v_send_report_ind := 'Y';
v_counter := v_counter + 1;
IF MOD(v_counter,2)=0 THEN
v_html_color := C_HTML_COLOR_GREEN;
ELSE
v_html_color := C_HTML_COLOR_YELLOW;
END IF;
IF v_free_pct <= C_LIMIT_FOR_RED THEN
v_html_color := C_HTML_COLOR_RED;
END IF;
addHtmlLine(v_html_color,
v_report_text,
getDbListRec.service_name,
getDbListRec.customer_name,
getDbListRec.user_name,
getDbListRec.db_link,
getDbListRec.host,
v_tablespace_name,
v_free_space,
v_used_space,
v_max_space,
v_free_pct);
END IF;
EXIT WHEN c_cursor%NOTFOUND;
END LOOP;
CLOSE c_cursor;
--Close the DB LINKS
BEGIN
commit;
DBMS_LOCK.sleep(2);
v_close_db_link_sql := v_close_db_link_base || getDbListRec.db_link;
EXECUTE IMMEDIATE v_close_db_link_sql;
DBMS_LOCK.sleep(2);
END;
------------------------------
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -2081 THEN --DBLINK NOT OPEN
NULL;
ELSIF (SQLCODE = -12170 --TNS Connection Timeout
OR
SQLCODE = -12560 --TNS Protocol Adapter Error
OR
SQLCODE = -942 --Table or View Does not Exists
)
THEN
IF SQLCODE = -12170 THEN
v_error_msg := 'Error: ORA-12170 TNS Connection Timeout';
ELSIF SQLCODE = -12560 THEN
v_error_msg := 'Error:ORA-12560 TNS Protocol Adapter Error';
ELSIF SQLCODE = -942 THEN
v_error_msg := 'Error: ORA-00942 Table or View Does not Exists';
END IF;
--TNS Connection Timeout
/*
v_counter := v_counter + 1;
IF MOD(v_counter,2)=0 THEN
v_html_color := C_HTML_COLOR_GREEN;
ELSE
v_html_color := C_HTML_COLOR_YELLOW;
END IF;
IF v_send_report_ind = 'Y' THEN
addHtmlLine(v_html_color,
v_report_text,
getDbListRec.service_name,
getDbListRec.customer_name,
getDbListRec.user_name,
getDbListRec.db_link,
getDbListRec.host,
v_error_msg ,
NULL,
NULL,
NULL,
NULL);
END IF;
*/
--Close the DB LINKS
BEGIN
commit;
DBMS_LOCK.sleep(2);
v_close_db_link_sql := v_close_db_link_base || getDbListRec.db_link;
EXECUTE IMMEDIATE v_close_db_link_sql;
DBMS_LOCK.sleep(2);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
ELSE
RAISE;
END IF;
END;
------------------------------
END LOOP;
v_report_text := v_report_text || '</TABLE>';
IF v_send_report_ind = 'N' THEN
v_report_text := v_report_text || '<BR><BR>NOTHING TO REPORT';
END IF;
MANAGER.SEND_HTML_MAIL_BY_MODULE (pModuleName => v_MailModuleName,
pText => 'STAM',
pMailMessage => v_report_text,
pConcatHostName => FALSE);
EXCEPTION
WHEN OTHERS THEN
v_report_text := v_report_text ||CHR(10)||v_MailModuleName|| ' has Failed.'||CHR(10)||'Error Details: '||SUBSTR(SQLERRM, 1, 900);
SEND_MAIL_BY_MODULE(pModuleName => v_MailModuleName, pMailMessage => v_report_text);
RAISE;
END reportDiskSpaceOnTbs;
============================
addHtmlLine procedure
============================
-----------------------------------------------------------
PROCEDURE addHtmlLine(p_bgcolor IN VARCHAR2,
p_msg IN OUT VARCHAR2,
p_msg_part1 IN VARCHAR2,
p_msg_part2 IN VARCHAR2,
p_msg_part3 IN VARCHAR2,
p_msg_part4 IN VARCHAR2,
p_msg_part5 IN VARCHAR2,
p_msg_part6 IN VARCHAR2,
p_msg_part7 IN VARCHAR2,
p_msg_part8 IN VARCHAR2,
p_msg_part9 IN VARCHAR2,
p_msg_part10 IN VARCHAR2
) IS
v_bgcolor VARCHAR2(100);
v_record_str VARCHAR2(32000);
BEGIN
IF p_bgcolor IS NULL THEN
v_bgcolor := 'BGCOLOR="WHITE"';
ELSE
v_bgcolor := 'BGCOLOR="'||p_bgcolor||'"';
END IF;
v_record_str := p_msg ||'<TR '||v_bgcolor||'>';
v_record_str := v_record_str||'<TD>'|| p_msg_part1 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part2 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part3 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part4 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part5 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part6 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part7 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part8 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part9 ||'</TD>';
v_record_str := v_record_str||'<TD>'|| p_msg_part10 ||'</TD>';
v_record_str := v_record_str ||'</TR>';
p_msg := v_record_str;
END addHtmlLine;
-----------------------------------------------------------
Hello,
ReplyDeleteWhat is the table structure and view?
Can you please share the ddl for the same.
Thanks,