Hi Thomas- Pretty interested in your method!! I tried it out but I guess I have to create the WTW_JOB_NOTIFY table and populate it and I can't figure where the mail notification is used. Assit with the table structure and an explanation on how this table is used.
Thanks and Cheers, CSW Simon. -----Original Message----- Sent: Tuesday, January 20, 2004 4:04 PM To: Multiple recipients of list ORACLE-L Mudhalvan, I generate files that excel can open all the time. they are not actual "real" excel files, but Excel can deal with them quite easily. Here is a tablespace report I run every week. Note the use of the CHR(9)'s. This is a TAB character. This forces each column into a new cell in the spreadsheet. CHR(10) is a line-feed. Feel free to "borrow" all of this! Hope this helps! SET serveroutput ON SET feedback OFF SET lines 150 SET pages 100 SET trimspool ON exec dbms_output.enable(100000) spool tbslspace_rpt.xls DECLARE CURSOR UpTime IS SELECT INITCAP(instance_name) Instance_Name ,INITCAP(Host_Name) Host_Name ,Version, ROUND(SYSDATE+1-startup_time) || DECODE(ROUND(SYSDATE+1-startup_time),1,' Day ', ' Days ') || MOD(ROUND((SYSDATE+1 - startup_time) * 24),24) || ' Hours ' || MOD(ROUND((SYSDATE+1 - startup_time) * 24*60),60) || ' Minutes ' UpTime FROM v$instance; UpTime_Rec UpTime%ROWTYPE; CURSOR TblSpace IS SELECT d.status , d.tablespace_name , TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'999,990') Tbs_Size, TRUNC(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024)Used, TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') Used_Pct , DECODE(SIGN(80 - NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)),-1,'** Warning > 80% **',NULL) Msg FROM sys.DBA_TABLESPACES d, (SELECT tablespace_name, SUM(bytes) bytes FROM DBA_DATA_FILES GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM DBA_FREE_SPACE GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY') ORDER BY 2; TblSpace_Rec TblSpace%ROWTYPE; c_email_list VARCHAR2(300); mail_message VARCHAR2(32000); mail_message1 VARCHAR2(32000); loc_start_time DATE; TblSpace_Msg NUMBER := 0; BEGIN BEGIN SELECT email_notify_txt INTO c_email_list FROM WTW_JOB_NOTIFY WHERE job_name = UPPER('Wtw_Report_Tablespaces'); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; loc_start_time := SYSDATE; OPEN UpTime; FETCH UpTime INTO UpTime_Rec; CLOSE UpTime; dbms_output.put_line(CHR(9) || CHR(9) || UpTime_Rec.Instance_Name || INITCAP(' Uptime/TABLESPACE Report FOR ') || TO_CHAR(SYSDATE,'fmMonth ddth, yyyy')); dbms_output.put_Line(CHR(9) || CHR(9) || 'UpTime : ' || UpTime_Rec.UpTime); dbms_output.put_Line( 'Status' || CHR(9) || INITCAP('TABLESPACE Name') || CHR(9) || INITCAP('SIZE (M)') || CHR(9) || 'Used (M)' || CHR(9) || 'Used (Pct)' || CHR(9) || 'Message'); OPEN TblSpace; FETCH TblSpace INTO TblSpace_Rec; WHILE TblSpace%FOUND LOOP dbms_output.put_Line( TblSpace_Rec.Status || CHR(9) || TblSpace_Rec.Tablespace_Name || CHR(9) || TblSpace_Rec.Tbs_Size || CHR(9) || TblSpace_Rec.Used || CHR(9) || TblSpace_Rec.Used_Pct || CHR(9) || TblSpace_Rec.Msg); IF TblSpace_Rec.Msg IS NOT NULL THEN TblSpace_Msg := 1; END IF; FETCH TblSpace INTO TblSpace_Rec; EXIT WHEN TblSpace%NOTFOUND; END LOOP; CLOSE TblSPace; END; / spool OFF exit Tom Mercadante Oracle Certified Professional -----Original Message----- Sent: Monday, January 19, 2004 11:44 PM To: Multiple recipients of list ORACLE-L Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).