Расчет размера табличных пространств Oracle (еще один подход к снаряду)

--DBA_DATA_FILES
SELECT /* + RULE */
df.tablespace_name,
round(sum(df.maxbytes)/1048576) "Size (MAX) (MB)",
round(sum(df.bytes)/1048576) "Size (CRNT) (MB)",
round((sum(df.maxbytes) - sum(df.bytes))/1048576) "Free (MB)",
round((sum(df.bytes)*100)/sum(df.maxbytes)) "Free (MAX) (%)"
FROM dba_data_files df
GROUP BY df.tablespace_name
UNION ALL
--DBA_TEMP_FILES
SELECT /* + RULE */
df.tablespace_name,
round(sum(df.maxbytes)/1048576),
round(sum(df.bytes)/1048576),
round((sum(df.maxbytes) - sum(df.bytes))/1048576),
round((sum(df.bytes)*100)/sum(df.maxbytes))
FROM dba_temp_files df
GROUP BY df.tablespace_name
order by 1 desc;

/* bash-вариант */
cat osize.sh
#!/bin/sh
daScr=/tmp/ots_150306.sql
echo --DBA_DATA_FILES>$daScr
echo SELECT /\* + RULE \*/>>$daScr
echo df.tablespace_name,>>$daScr
echo round\(sum\(df.maxbytes\)/1048576\) \"SizeMAX\(MB\)\",>>$daScr
echo round\(sum\(df.bytes\)/1048576\) \"SizeCUR\(MB\)\",>>$daScr
echo round\(\(sum\(df.maxbytes\) - sum\(df.bytes\)\)/1048576\) \"Free\(MB\)\",>>$daScr
echo round\(\(sum\(df.bytes\)\*100\)/sum\(df.maxbytes\)\) \"Free\(MAX\)\(%\)\">>$daScr
echo FROM dba_data_files df>>$daScr
echo GROUP BY df.tablespace_name>>$daScr
echo UNION ALL>>$daScr
echo --DBA_TEMP_FILES>>$daScr
echo SELECT /\* + RULE \*/>>$daScr
echo df.tablespace_name,>>$daScr
echo round\(sum\(df.maxbytes\)/1048576\),>>$daScr
echo round\(sum\(df.bytes\)/1048576\),>>$daScr
echo round\(\(sum\(df.maxbytes\) - sum\(df.bytes\)\)/1048576\),>>$daScr
echo round\(\(sum\(df.bytes\)\*100\)/sum\(df.maxbytes\)\)>>$daScr
echo FROM dba_temp_files df>>$daScr
echo GROUP BY df.tablespace_name>>$daScr
echo order by 1 desc\;>>$daScr
echo exit\;>>$daScr
if [ $USER == root ]
then
su - oracle -c "sqlplus / as sysdba @$daScr"
else
sqlplus / as sysdba @$daScr
fi
rm $daScr
if [[ -n $DISPLAY ]]; then read -p 'Press ENTER key to exit.'; fi