Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Composite Index creation tip from Vivek Oracle
  • Read CSV File using Python Python/PySpark
  • online_ts_bkup.sql Oracle
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • good linux notes Linux/Unix
  • JSON/XML Types in Oracle Oracle
  • sess_server.sql Oracle
  • create a folder in multiple places Linux/Unix
  • get_aix_vmstat.ksh Oracle
  • How to collect CPU usage on Linux using Shell script Linux/Unix
  • Find All internal Parameters Oracle
  • findobj.sql Oracle
  • secure crt settings Linux/Unix
  • Oracle 10g for solaris 10 Oracle
  • How to choose Driver table in SQL statement Oracle

chk_space_SID.ksh

Posted on 14-Mar-2007 By Admin No Comments on chk_space_SID.ksh

. $ENV_FILE

#sqlplus -silent / as sysdba << EOF
sqlplus -silent $ADMIN_USER/$ADMIN_PASSWD << EOF
set echo off

set numwidth 9

Set head off

set feedback off

set verify off

set time off

set timing off

set termout off

set pages 5000

set lines 150

column file_name format a50

column file_id format 9999 heading ‘FLID’

column tablespace_name format a20

column free_mb format a8

column maxsize_mb format 9999999

column free_pct format 99 heading ‘FREE|%’

column EXTMB format 999

column AE format a3 heading Auto|XTND|ON?

spool $LOG_FILE_DIR/chk_space_$ORACLE_SID

select /*+ RULE */

a.tablespace_name, a.file_id ,a.file_name,

nvl( to_char(trunc(decode(a.autoextensible,’YES’,a.maxsize-a.bytes+b.free,’NO’,b.free)/1024/1024)),’NA’) free_mb,

trunc(a.bytes/1024/1024) size_mb ,trunc(a.maxsize/1024/1024) maxsize_mb ,a.autoextensible ae ,

decode (c.allocation_type, ‘SYSTEM’, 0, round(c.next_extent/(1022 * 1024), 0)) EXTMB, c.allocation_type

from

(select file_id , file_name, tablespace_name ,autoextensible ,bytes ,decode(autoextensible,’YES’,maxbytes,bytes) maxsize

from dba_data_files ) a,

(select file_id, tablespace_name, sum(bytes) free

from dba_free_space

group by file_id, tablespace_name) b,

dba_tablespaces c,

(select /*+ ALL_ROWS */ file_id, count(1) EXT# from dba_extents group by file_id) d

where a.file_id=b.file_id(+)

and a.tablespace_name=b.tablespace_name(+)

and a.tablespace_name = c.tablespace_name

and a.file_id = d.file_id(+)

order by a.tablespace_name asc;

spool off

exit;

EOF

IFS=’

‘

line_counter=1;

TS_OK=`echo “N”`;

SEND_NOTIFICATION=`echo “N”`;

tot_lines=`cat $LOG_FILE_DIR/chk_space_$ORACLE_SID.lst |wc -l`;

for line in `cat $LOG_FILE_DIR/chk_space_$ORACLE_SID.lst`

do

v_tsname=`echo $line | awk ‘{print $1}’`;

v_fileid=`echo $line | awk ‘{print $2}’`;

v_filenm=`echo $line | awk ‘{print $3}’`;

v_freemb=`echo $line | awk ‘{print $4}’`;

v_sizemb=`echo $line | awk ‘{print $5}’`;

v_mxsize=`echo $line | awk ‘{print $6}’`;

v_autxtn=`echo $line | awk ‘{print $7}’`;

v_extsz=`echo $line | awk ‘{print $8}’`;

v_allotype=`echo $line | awk ‘{print $9}’`;

v_mountsz=`df -k $v_filenm | awk ‘{print $4}’ | sed ‘1d’`;

let v_mountsz=$v_mountsz/1024;

# print “ts-name : $v_tsname”;

# print “fileid : $v_fileid”;

# print “filenm : $v_filenm”;

# print “freemb : $v_freemb”;

# print “sizemb : $v_sizemb”;

# print “mxsize : $v_mxsize”;

# print “autxtn : $v_autxtn”;

# print “extsz : $v_extsz”;

# print “allotype : $v_allotype”;

# print “mountsz : $v_mountsz”;

# print “Line is as below”;

# print $line;

# print “Line Ends”;

if [ $line_counter -eq 1 ]

then

prv_tsname=$v_tsname;

fi

if [ $prv_tsname = $v_tsname -a $TS_OK = “Y” ]

then

# /* continue with next line from file as this tablespace has free space available, no need to check other datafiles; */

let line_counter=$line_counter+1;

if [ $line_counter -eq $tot_lines -a $TS_OK = “N” ]

then

echo “Tablespace $v_tsname WILL BE UNABLE TO EXTEND”;

SEND_NOTIFICATION=`echo “Y”`;

elif [ $line_counter -eq $tot_lines -a $TS_OK = “Y” ]

then

echo “Tablespace $v_tsname is OKAY”;

fi

continue;

elif [ $prv_tsname != $v_tsname -a $TS_OK = “N” ]

then

# /* Raise Error for previous TS and write that error in a log. */

print “Tablespace $prv_tsname WILL BE UNABLE TO EXTEND”;

SEND_NOTIFICATION=`echo “Y”`;

elif [ $prv_tsname != $v_tsname -a $TS_OK = “Y” ]

then

print “Tablespace $prv_tsname is OKAY”;

fi

#/*

# — prv_ts != v_tsname and TSOK =N then error message like prv TS has problem

# — prv_tsname = vtsname and TSOK =N then following code needs to execute…. for curren TS

# — prv ts != v_ts name and TSOK=Y then follwoign code need to execute…for current TS.

#*/

#/* Following code will set TSOK and prv_tsname values */

if [ $v_freemb = “NA” ]

then

let free_space=$v_mxsize-$v_sizemb;

else

let free_space=$v_freemb;

fi

# print “ts: $v_tsname and fname=$v_filenm and free_space=$free_space and v_extsz=$v_extsz”;

# following condition is for SYSTEM allocated extents..Where Extent size is unavailable. So checking only enough free space on mount point.

if [ $v_extsz = “NA” ]

then

if [ $v_mountsz -lt $SYSTEM_ALLOCATED_THRESHOLD ]

then

TS_OK=`echo “N”`;

SEND_NOTIFICATION=`echo “Y”`;

else

TS_OK=`echo “Y”`;

fi

else

# following condition is for UNIFORM extents…

if [ $v_extsz -le $free_space ]

then

if [ $v_extsz -le $v_mountsz ]

then

TS_OK=`echo “Y”`;

else

TS_OK=`echo “N”`;

fi

else

TS_OK=`echo “N”`;

fi

fi

prv_tsname=$v_tsname;

if [ $line_counter -eq $tot_lines -a $TS_OK = “N” ]

then

echo “Tablespace $v_tsname WILL BE UNABLE TO EXTEND”;

SEND_NOTIFICATION=`echo “Y”`;

elif [ $line_counter -eq $tot_lines -a $TS_OK = “Y” ]

then

echo “Tablespace $v_tsname is OKAY”;

fi

let line_counter=$line_counter+1;

# print “line_counter=$line_counter”;

done

print “SEND_NOTIFICATION=$SEND_NOTIFICATION”;

if [ $SEND_NOTIFICATION = “Y” ]

then

mailx -s “$ORACLE_SID at $NODENAME1 : Impending Next Extent Failure” $NOTIFY_LIST<Check $HOME/oraprocs/logs/chk_space_$ORACLE_SID.log and $HOME/oraprocs/logs/chk_space_$ORACLE_SID.err for details.

Also execute ts2.sql to identify exact tablespace that will be unable to extend.

Next Extent Allocation will fail for indicated tablespaces.

EOF

fi

Linux/Unix, shell

Post navigation

Previous Post: To Find Orphan OS processes.
Next Post: export import with parameter file.

Related Posts

  • scripts to take listener.log backup Linux/Unix
  • catall.sh Linux/Unix
  • How to collect CPU usage on Linux using Shell script Linux/Unix
  • kill all processes from specific user in solaris. Linux/Unix
  • switchlogfile.sh Linux/Unix
  • Running select from V$ views from remote server Linux/Unix

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • eplan9i.sql Oracle
  • create user with unlimited quota Oracle
  • tab.sql Oracle
  • Gather Stats manually using DBMS_STATS after disabling DBMS_SCHEDULER jobs as previous entry Oracle
  • logminer and my_lbu Oracle
  • nfs mount command Linux/Unix
  • get_ratio.sql get the ratio of users from v$session and this uses CASE-WHEN-THEN clause Oracle
  • Finding Oracle Patches with opatch Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme