Shell Script to find the tablespace size in oracle.

Shell Programming and Scripting
1

Hi,

I need to execute a script to find the tablespace size in oracle.But i get an error.:confused:

Script Executed:-

#!/bin/ksh
ORACLE_SID= oracelinstance 
ORACLE_HOME= oracle path
PATH=$ORACLE_HOME/bin
export ORACLE_SID ORACLE_HOME PATH 

sqlplus username/password@oracle_instance <<-EOF
set serveroutput on
whenever sqlerror exit 1;
spool /tmp/tab_tmp.log
connect sys/standard as sysdba;
select tablespace_name, round((sum(bytes)/1024/1024),2) size_in_mb,
sum(greatest(maxbytes,bytes))/1024/1024 total_available,
round(((sum(bytes)/1024/1024)/(sum(greatest(maxbytes,bytes))/1024/1024)),2) pct_used
from DBA_DATA_FILES group by tablespace_name order by 1;
spool off
EOF
exit 0
 
Error :
 
SQL> 
SQL> connect sys/standard as sysdba;
Connected to an idle instance.
SQL> 
SQL> select tablespace_name, round((sum(bytes)/1024/1024),2) size_in_mb,sum(greatest(maxbytes,bytes))/1024/1024 total_available,
round(((sum(bytes)/1024/1024)/(sum(greatest(maxbytes,bytes))/1024/1024)),2) pct_used
from DBA_DATA_FILES group by tablespace_name order by 1;
 
select tablespace_name, round((sum(bytes)/1024/1024),2) size_in_mb,
sum(greatest(maxbytes,bytes))/1024/1024 total_available,
round(((sum(bytes)/1024/1024)/(sum(greatest(maxbytes,bytes))/1024/1024)),2) pct_used
from DBA_DATA_FILES group by tablespace_name order by 1
*
ERROR at line 1:
ORA-01034: ORACLE not available
2

this simply means that the oracle instance is down.

sql> startup immediate;

and then run your script again

3

Hi,

first you have to check if all instances od oracle are running:

oracle> ps -ef |grep pmon --> you should see all oracle instances on output

4

and try also to check the connection to your database:

sql> select from sysdate from dual; -->you should get system date and hour

5

maybe the world oracle in your "ORACLE_SID= oracelinstance " mistake by oracel