Script to check Oracle tablespace

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script to check Oracle tablespace
# 1  
Old 06-28-2011
Script to check Oracle tablespace

I'm new to unix script, and I need to check the tablespaces daily, here is the script(quite simple), but it does not work. Did I missed something ?
Please guide me, Many thanks !

HTML Code:


sqlplus system/oracle as sysdba;

set pagesize 9999;
set linesize 132;

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used


Last edited by dehetoxic; 06-28-2011 at 02:08 PM..
# 2  
Old 06-28-2011
sqlplus system/oracle as sysdba <<EOF

This User Gave Thanks to DGPickett For This Post:
# 3  
Old 06-28-2011
thanks, but I got this error, any ideas ?

table spaces:
./ sqlplus: not found
# 4  
Old 06-28-2011
Well, you need the environment variable $PATH to include the dir where sqlplus is installed, and $ORACLE_HOME correctly set, too. I assume SQL*PLUS is installed. Can I interest you in a nice portable JDBC GUI called SQuirreL, or a nice command line JDBC tool from xigole called JISQL?, or can you get an SQL*PLUS installation, or TOAD?
This User Gave Thanks to DGPickett For This Post:
# 5  
Old 06-28-2011
now its work, but I got the problem with $ sign, is there anyway to fix it ?
# 6  
Old 06-29-2011
I never liked << for most literal to stdin, preferring "echo ' ... '| ", as single quotes are pretty much all literal. You can escape $ with \, if you like << that much.
This User Gave Thanks to DGPickett For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Tablespace growth trend

Hi experts, I have the below details with me.How to calculate the tablespace growth between two dates. sample data(have data upto 1 year): INSTANCE_NAME DATE TABLESPACE_NAME MB_ALLOC MB_FREE MB_USED PCT_FREE PCT_USED MAX ---------------- ---------... (1 Reply)
Discussion started by: navsan420
1 Replies

2. Shell Programming and Scripting

Help to check if Oracle table exists

I am trying to write a script which allows a user to select the what manipulation he needs to do on a table. I want to check if the table exists or not. If it exists I will continue the other things or else I exit saying table doesn't exist. How might I achieve this. (1 Reply)
Discussion started by: gmatcat
1 Replies

3. Shell Programming and Scripting

Error in Scripting to monitor tablespace in Oracle DB

Buddies, I am writing the below script '' 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... (5 Replies)
Discussion started by: sandip250382
5 Replies

4. Shell Programming and Scripting

tablespace monitoring script

I have prepared the below script to monitor the tablespace and alert the users whenever it reaches a threshold limit. #!/bin/sh . /home/.profile sqlplus -s $LOGON << .eof > $scripts/check_tablespace.temp set pages 0 select tablespace_name, free_percent from ( SELECT... (4 Replies)
Discussion started by: svajhala
4 Replies

5. Shell Programming and Scripting

alias to a shell script - tablespace size

I have developed the following shell script. The idea was to create an alias to execute it and return the tablespaces and their sizes from an oracle database. When I execute is via the alias, I have issues. So, here is the script first: > cat #!/bin/ksh xwhere=" " xwild=" "... (8 Replies)
Discussion started by: desibabu
8 Replies

6. Shell Programming and Scripting

error in script path for tablespace usage

Hi , I am trying to run this script in crontab but I get errors. When I run it explicitly like ./monitor_tblsp from another location then it runs fine . I am messing somewhere with paths but I don't know where and how . Please help . Here is the error part ./monitor_tblsp: touch: not... (2 Replies)
Discussion started by: capri_drm
2 Replies

7. Shell Programming and Scripting

Need help in a shell script to edit a tablespace creation script.

Hi, CREATE TABLESPACE aps_blob_large01 DATAFILE '/c2r6u13/u03/oradata/qnoldv01/aps_blob_large0101.dbf' SIZE X 270532608 REUSE DEFAULT STORAGE (INITIAL 134217728 NEXT... (2 Replies)
Discussion started by: rparavastu
2 Replies

8. Shell Programming and Scripting

Bourne script: Check for root and oracle user

I have 2 separate Bourne shell scripts with 2 questions in Sun O/S & UNIX environment. Question 1: One of the scripts is supposed to be executed by "root" user only but cannot be executed after user executes "su - oracle". How can I check in the script whether the current user is "root" user?... (8 Replies)
Discussion started by: totziens
8 Replies

9. Shell Programming and Scripting

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.:confused: Script Executed:- #!/bin/ksh ORACLE_SID= oracelinstance ORACLE_HOME= oracle path PATH=$ORACLE_HOME/bin export ORACLE_SID ORACLE_HOME PATH sqlplus... (4 Replies)
Discussion started by: vighna
4 Replies

10. Solaris

Check version of Oracle

Hi All, Please advice me how i can check the Oracle version on my UNIX Solaris machine without logging in to oracle? Thanks, Am (1 Reply)
Discussion started by: am_yadav
1 Replies
Login or Register to Ask a Question