Error in Scripting to monitor tablespace in Oracle DB

Shell Programming and Scripting
1

Buddies, I am writing the below script 'tab.sh' to monitor the tablespaces in Database:-
-------------------------------

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
sqlplus system/oracle as sysdba <<EOF 
spool /home/oracle/tablespace_analysis.log
set lines 2000
set pagesize 100
select a.tablespace_name as "tablespace",allocated as "size in gb", (a.allocated - nvl(b.freespace,0)) as "used space in gb",nvl(b.freespace,0) as "freespace in gb", (a.allocated - nvl(b.freespace,0))*100/a.allocated "percentage used",
100 - (a.allocated - nvl(b.freespace,0))*100/a.allocated "Percentage free"
from
(select tablespace_name, sum(bytes)/1024/1024/1024 allocated from dba_data_files group by tablespace_name ) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 freespace from dba_free_space group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name(+)
and (a.allocated - nvl(b.freespace,0))*100/a.allocated >85
order by 6 desc ;

spool off
EOF     
exit 0

________________________

However while executing the script, I am getting the below error:-

SQL> SQL> SQL> SQL> SP2-0042: unknown command "EOF" - rest of line ignored.

Any idea will be highly appreciated.

2

How about this ?

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
echo "
spool /home/oracle/tablespace_analysis.log
set lines 2000
set pagesize 100
select a.tablespace_name as "tablespace",allocated as "size in gb",
(a.allocated - nvl(b.freespace,0)) as "used space in gb",nvl(b.freespace,0) as "freespace in gb",
(a.allocated - nvl(b.freespace,0))*100/a.allocated "percentage used",100 - (a.allocated - nvl(b.freespace,0))*100/a.allocated "Percentage free" 
from
(select tablespace_name, sum(bytes)/1024/1024/1024 allocated from dba_data_files group by tablespace_name ) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 freespace from dba_free_space group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name(+)
and (a.allocated - nvl(b.freespace,0))*100/a.allocated >85
order by 6 desc ;
spool off;
exit" | sqlplus system/oracle as sysdba
   
exit 0
1 Like
3

Thanks a lot Pravin. However echo commands in that way is producing some errors in my SQL code which is not there otherwise, so need to think in different ways

4

Try with single quote

echo '
set lines 2000
set pagesize 100
select a.tablespace_name as "tablespace",allocated as "size in gb",
(a.allocated - nvl(b.freespace,0)) as "used space in gb",nvl(b.freespace,0) as "freespace in gb",
(a.allocated - nvl(b.freespace,0))*100/a.allocated "percentage used",100 - (a.allocated - nvl(b.freespace,0))*100/a.allocated "Percentage free"
from
(select tablespace_name, sum(bytes)/1024/1024/1024 allocated from dba_data_files group by tablespace_name ) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 freespace from dba_free_space group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name(+)
and (a.allocated - nvl(b.freespace,0))*100/a.allocated >85
order by 6 desc ;
spool off;
exit' | sqlplus system/oracle as sysdba
1 Like
5

Perfect dear, thanks a million!
Dude just one advice more, can you suggest any good book for learning shell scripting .

6

There are lot of books available in the market, I used the below book when i started learning Shell script.
Amazon.com: Unix Shell Programming (3rd Edition) (9780672324901): Stephen G. Kochan, Patrick Wood: Books

Another way is to follow this forum........you will learn surely.