Hi,

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

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

this simply means that the oracle instance is down.

sql> startup immediate;

and then run your script again

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