#!/usr/bin/ksh
#set -xv
# Program : smenu_lst_disk.sh
# Author  : Bernard Polarski
# note    : The resize datafiles part is from Jonathan Lewis book Practical Oracle 8i
HOST=`hostname`
PAR1=$1
PAR2=$2
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
TMP=$SBIN/tmp
FOUT=$TMP/tmp_dsc$ORACLE_SID.txt

if [ "$PAR1" = "-h" ];then

      cat <<EOF

   List datfiles utility:

        lstd                          : List datafiles size and auto extend info
        lstd -m <TABLESPACE_NAME>     : List candidate shrink datafiles
        lstd -s <TABLESPACE_NAME>     : show SQL statement to shink datafiles, default is all unless TBS name is given
        lstd -e                       : List datafile in autoextend that cannot extend anymore
        lstd -es                      : Show statement to set off datafiles that cannot extend anymore
        lstd -ck                      : List last checkpoint time  for each datafile



EOF

elif [ "$PAR1" = "-ck" ];then
sqlplus -s "$CONNECT_STRING" <<EOF

set echo off pause off feedback off verify off pagesize 100 linesize 120
set termout on heading off embedded off verify off

ttitle skip 1 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   right 'Page:' format 999 sql.pno skip 2
column nline newline
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'List last checkpoint time  for each datafile ' nline from sys.dual
/
set linesize 120
set heading on
col name format A60 heading 'Name'
col ltime format A20 heading 'Date'
col Checkpoint format 999,999,999,999.0  heading 'Checkpoint'

select name, checkpoint_change# Checkpoint,
       to_char(checkpoint_time, 'YYYY-MM-DD HH24:MI:SS')  ltime
       from v\$datafile_header
/
EOF
exit
elif [ "$PAR1" = "-es" ];then
sqlplus -s  "$CONNECT_STRING" <<EOF
set head off pagesize 333 linesize 124
select 'alter database datafile '''|| a.file_name ||''' autoextend off;'  from
   dba_data_files a, (
select file_name, bytes/1024/1024 bytes,increment_by next,maxbytes/1024/1024 maxsize from dba_data_files where
autoextensible='YES' and (bytes + increment_by) > maxbytes) b
where a.file_name = b.file_name
/
EOF

elif [ "$PAR1" = "-e" ];then
sqlplus -s  "$CONNECT_STRING" <<EOF

ttitle  'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   right 'Page:' format 999 sql.pno skip 2
column nline newline
set pause off pagesize 66 linesize 80 heading off embedded off verify off termout on

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'List - Datafile that cannot extend ' from dual;
set pagesize 333 linesize 124 head on
col file_name format a65
col bytes format 99990.99 head "Size(meg)"
col maxsize format 99990.99 head "Maxsize(meg)"
select file_name, bytes/1024/1024 bytes,increment_by next,maxbytes/1024/1024 maxsize from dba_data_files where
autoextensible='YES' and (bytes + increment_by) > maxbytes;
EOF

elif [ "$PAR1" = "-m" ];then

if [ -n "$PAR2" ];then
    WHERE_TBS=" where tablespace_name = upper('$PAR2') "
fi
sqlplus -s  "$CONNECT_STRING" <<EOF

ttitle  'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   right 'Page:' format 999 sql.pno skip 2
column nline newline
set pause off pagesize 66 linesize 80 heading off embedded off verify off termout on

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'List - Datafile shrinkable ' from sys.dual
/

Prompt .        lstd -s   to display SQL statement to shrink
prompt
set embedded on linesize 124 pagesize 66 heading on
col name format a65

select
       df.name, ceil(hwm.mb)              hwm,
       20*ceil((hwm.mb-1)/20)+1           target,
       round(df.bytes/1048576,0)          curr
from v\$datafile df,
     ( select file_id,
              max((bytes/blocks)*(block_id+blocks-1))/1048576  mb
         from dba_extents $WHERE_TBS group by file_id ) hwm
where hwm.file_id = df.file# 
  -- and 20*ceil((hwm.mb-1)/20)+1 < ceil(df.bytes/1048576)
order by df.ts#, df.name
/
EOF

elif [ "$PAR1" = "-s" ];then

if [ -n "$PAR2" ];then
    WHERE_TBS=" where tablespace_name = upper('$PAR2') "
fi
sqlplus -s  "$CONNECT_STRING" <<EOF
set linesize 125
set head off
select
       'alter database datafile '''||df.name|| ''' resize ' ||
       to_char(20*ceil((hwm.mb-1)/20)+1)    || 'm;'
from v\$datafile df, ( select file_id,
              max((bytes/blocks)*(block_id+blocks-1))/1048576  mb
         from dba_extents $WHERE_TBS group by file_id ) hwm
where hwm.file_id = df.file# and
      20*ceil((hwm.mb-1)/20)+1 < ceil(df.bytes/1048576)
order by df.ts#, df.name
/
EOF

else

sqlplus -s  "$CONNECT_STRING" <<EOF
   ttitle  'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   right 'Page:' format 999 sql.pno skip 2
column nline newline
set pause off
set pagesize 66
set linesize 80
set heading off
set embedded off
set verify off
set termout on


select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'List of all datafiles, size, autoextent, next, maxsize and tablespaces  '
from sys.dual
/
        set pages 0 verify off feed off echo off head off linesize 132
        column nline newline
        col member format a65
        col name format a65
        col gr format a5
        col file_id format a8
        col megs format 9999999
        col mmegs format 99999
        col next format 9999
        col file_name format a65
        col tablespace_name format a22
        col status format a1
        col value new_value db_block_size noprint
        select '                                                                     Size                 Max    Next' nline , 'Datafile/redo/control                                               (meg) Type  id   Av  Size   (meg) Tablespace' nline , '===================================================================================================================' nline ,
        value from v\$parameter where name = 'db_block_size';

        select rpad(name,72),  'Control file' ctl from v\$controlfile;

        break on tablespace_name
        select  member, (bytes/1024/1024) megs,
               'Redo', 'gr:'||to_char(l.group#) gr, ' ',lower(l.status)
               from v\$log l, v\$logfile f
               where f.group# = l.group# order by gr;
--break on tablespace_name skip 1
--compute sum of megs on tablespace_name

        select file_name , trunc(bytes/1024/1024,1)  megs,
               'Dbf', ' id:' || to_char(file_id) file_id , lower(substr(autoextensible,1,1)) satus,
                maxbytes/1024/1024 mmmegs, (increment_by*&db_block_size)/1024/1024 next, tablespace_name
               from dba_data_files order by tablespace_name,file_name;

        select file_name , trunc(bytes/1024/1024,1)  megs,
               'Tmp', ' id:' || to_char(file_id) file_id , lower(substr(autoextensible,1,1)) status,
                maxbytes/1024/1024 mmegs, (increment_by*&db_block_size)/1024/1024 next,tablespace_name
               from dba_temp_files order by tablespace_name,file_name;

EOF

fi