The UNIX and Linux Forums  

Go Back   The UNIX and Linux Forums > Top Forums > Shell Programming and Scripting
Google UNIX.COM


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts here.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Command/script to find size of Unix Box ? sakthifire Shell Programming and Scripting 6 06-17-2008 11:07 PM
unix script to check whether particular file exists and to find its size Balachandar Shell Programming and Scripting 9 02-04-2008 11:56 PM
shell script to find files by date and size dadadc UNIX for Dummies Questions & Answers 1 10-20-2007 02:18 AM
Shell script to Find file size ragsnovel Shell Programming and Scripting 1 08-10-2007 07:01 AM
oracle connection from shell script DILEEP410 Shell Programming and Scripting 2 12-15-2006 06:46 AM

Reply
 
Submit Tools LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 07-23-2008
Registered User
 

Join Date: Jul 2008
Posts: 1
Stumble this Post!
Thumbs up Shell Script to find the tablespace size in oracle.

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
Reply With Quote
Forum Sponsor
  #2 (permalink)  
Old 07-24-2008
Registered User
 

Join Date: Nov 2006
Posts: 165
Stumble this Post!
this simply means that the oracle instance is down.

sql> startup immediate;

and then run your script again
Reply With Quote
  #3 (permalink)  
Old 07-24-2008
Registered User
 

Join Date: Jul 2008
Posts: 19
Stumble this Post!
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
Reply With Quote
  #4 (permalink)  
Old 07-24-2008
Registered User
 

Join Date: Jul 2008
Posts: 19
Stumble this Post!
and try also to check the connection to your database:

sql> select from sysdate from dual; -->you should get system date and hour
Reply With Quote
  #5 (permalink)  
Old 07-26-2008
Registered User
 

Join Date: Jul 2008
Posts: 1
Stumble this Post!
maybe the world oracle in your "ORACLE_SID= oracelinstance " mistake by oracel
Reply With Quote
Google The UNIX and Linux Forums
Reply

Thread Tools
Display Modes




All times are GMT -7. The time now is 05:41 PM.


Powered by: vBulletin, Copyright ©2000 - 2006, Jelsoft Enterprises Limited.
The UNIX and Linux Forums Content Copyright ©1993-2008 The CEP Blog All Rights Reserved -Ad Management by RedTyger Visit The Global Fact Book

Content Relevant URLs by vBSEO 3.2.0