#!/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
# --------------------------------------------------------------
function help {

      cat <<EOF

   List datfiles utility:

        lstd  [ -t <tablespace_name>] : List datafiles size and auto extend info, eventually limit to <tbs>
        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
exit
}
# --------------------------------------------------------------
TMP=$SBIN/tmp
FOUT=$TMP/tmp_dsc$ORACLE_SID.txt

while [ -n "$1" ]
do
  case "$1" in
     -ck ) PAR1=checkpoint ;;
     -es ) PAR1=es ;;
      -e ) PAR1=autoextend ;;
      -m ) PAR1=shrink ; TBS="$2"; shift ;;
      -s ) PAR1=shrink2 ; TBS="$2"; shift ;;
      -h ) help ;;
      -t ) TBS="$2" ; shift ;;
  esac
  shift
done

. $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

# --------------------------------------------------------------
if [ "$PAR1" = "checkpoint" ];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" = "autoextend" ];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" = "shrink" ];then

if [ -n "$TBS" ];then
    WHERE_TBS=" where tablespace_name = upper('$TBS') "
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" = "shrink2" ];then

if [ -n "$TBS" ];then
    WHERE_TBS=" where tablespace_name = upper('$TBS') "
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

if [ -n "$TBS" ];then
    WHERE_TBS=" where tablespace_name = upper('$TBS') "
    AND=" and 1=2"
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 of all datafiles, size, autoextent, next, maxsize and tablespaces  '
from sys.dual
/
        set pages 0 verify off feed off echo off head off linesize 190
        column nline newline
        col member format a70
        col ctrl format a70
        col ctl for a16
        col name format a70
        col gr format a5
        col file_id format a8
        col megs format 99999990.9
        col mmegs format 999990.9
        col next format 9999
        col file_name format a70
        col tablespace_name format a25
        col status format a1
        col value new_value db_block_size noprint
        select '                                                                          Size                  Auto    Max      Next' nline , 'Datafile/redo/control                                                    (meg)     Type  id    Extend   Size    (meg) Tablespace' nline , '========================================================================================================================' nline ,
        value from v\$parameter where name = 'db_block_size'  ;

        break on tablespace_name
        select rpad(name,72) ctrl, 0 megs , 'Control file' ctl, null gr ,null,null from v\$controlfile where 1=1 $AND
        union all
        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# $AND order by  gr  ;
        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 mmegs, (increment_by*&db_block_size)/1024/1024 next, tablespace_name
               from dba_data_files $WHERE_TBS 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  $WHERE_TBS order by tablespace_name,file_name;
EOF

fi