shell格式化输出结果

shenjinchun

现在想用shell定时输出表空间，现在用这个语句输出结果echo `su - oracle -c "sqlplus '/ as sysdba'</oracle/show_db_space.sql"` >1.log，但是结果都挤在一行中，且很乱，各位仁兄帮帮看看没有方法使结果输出变得整齐一点，万分感激。

show_db_space.sql 内容如下
--查看oracle表空间使用情况
SELECT upper(f.tablespace_name) "Tablespacename",
d.Tot_grootte_Mb "TablespaceSize",
d.Tot_grootte_Mb - f.total_bytes "Used",
to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "Rate",
f.total_bytes "Avail",
f.max_bytes "MaxBlock"
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name,round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;
----查看oracle表临时空间使用情况
SELECT  d.status "Status", d.tablespace_name "Name", d.contents "Type",
             d.extent_management "Extent Management",
             TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
             NVL(t.bytes, 0)/1024/1024 ||'/'||NVL(a.bytes/1024/1024, 0) "Used (M)",
             TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM  sys.dba_tablespaces d,
         (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
         (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE  d.tablespace_name = a.tablespace_name(+)
           AND  d.tablespace_name = t.tablespace_name(+)
           AND  d.extent_management like 'LOCAL'
           AND  d.contents like 'TEMPORARY';




echo `su - oracle -c "sqlplus '/ as sysdba'</oracle/show_db_space.sql"` >1.log

1.log内容如下，也就是sql查询后的结果集如下
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 11月 24 12:46:33 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Tablespacename TablespaceSize Used Rate Avail ------------------------------ -------------- ---------- ------- ---------- MaxBlock ---------- SYSTEM 520 516.12 99.25 3.88 3.88 USERS 53.75 53.06 98.72 .69 .69 SYSAUX 300 292.19 97.40 7.81 6.94 Tablespacename TablespaceSize Used Rate Avail ------------------------------ -------------- ---------- ------- ---------- MaxBlock ---------- NNC_INDEX03 3072 1568.69 51.06 1503.31 535.94 NNC_DATA03 5120 2218.31 43.33 2901.69 630.94 NNC_INDEX01 2048 886.12 43.27 1161.88 726.94 Tablespacename TablespaceSize Used Rate Avail ------------------------------ -------------- ---------- ------- ---------- MaxBlock ---------- NNC_DATA02 1024 438.37 42.81 585.63 585.63 NNC_DATA01 10240 4145.62 40.48 6094.38 849.94 NNC_INDEX02 1024 384.44 37.54 639.56 639.56 Tablespacename TablespaceSize Used Rate Avail ------------------------------ -------------- ---------- ------- ---------- MaxBlock ---------- NCDATA 1024 361.31 35.28 662.69 662.69 UNDOTBS1 210 23.44 11.16 186.56 69 11 rows selected. SQL> 2 3 4 5 6 7 8 9 10 11 12 Status Name Type Extent Man Size (M) --------- ------------------------------ --------- ---------- --------------- Used (M) -------------------------------------------------------------------------------- Used % ------- ONLINE TEMP TEMPORARY LOCAL 20.000 18/20 90.00 SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

ywlscpl

su - oracle -c "sqlplus '/ as sysdba'</oracle/show_db_space.sql"  >1.log