#!/usr/bin/ksh
#
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
SBIN2=${SBIN}/module2
WK_SBIN=${SBIN}/module2/s5
TMP=$SBIN/tmp
FOUT=$TMP/free_space_summary_$ORACLE_SID.txt
function help
{
cat <<EOF

     Tablespace informations:

     frg
     frg -b|-g [-t <tablespace name> ]
     frg -os -t <tablespace name> -rn <nn> -u <OWNER>
     frg -i
     frg -dus <user>                                       # If <user> is not given then all schema are processed
     frg -cr [<tablespace name>] [-q]                      # Generate statement to create tablespace, if no tbs name, 
                                                           # then create statement for all. 

        -b : figures in bytes
      -dus : Disk usage per user per tablespace
        -g : figures in Giga bytes
        -i : List metadata info on tablespaces
       -rn : Limite selection to <nn> rows
        -q : add user quota on tbs 
       -os : List Object size
        -t : limit to this tablespace, it accpet partial name

        -h : this help



EOF
exit
}
typeset -u PAR2
typeset -u fnew
typeset -u ftbs
ROWNUM=30
while [ -n "$1" ]
do
 case "$1" in
    -b | b ) UNIT=B;;
    -g | g ) UNIT=G ; VAR=Gigs;;
      -cr  ) CHOICE=CREATE 
             if [ -n "$2" -a ! "$2" = '-q' ] ;then
                  ftbs=$2; shift
             fi
             ;;
       -i  ) CHOICE=INFO ;;
     -dus  ) CHOICE=DUS
             if [ -n "$2" ];then
               TARGET_OWNER=$2 ; shift
               WHERE=where
               unset OWN_SYS
               AND_OWNER=" OWNER=upper('$TARGET_OWNER')"
             fi
              ;;
     -new  ) fnew=$2 ; shift ;;
      -os  ) CHOICE=LIST_OBJECT_SIZE;;
       -q  ) QUOTA=TRUE ;;
       -u  ) AND_OWNER=" and owner = upper('$2')"; shift ;;
       -t  ) PAR2=$2 ; AND_TBS=" and b.tablespace_name like '%$PAR2%'"; shift ;;
      -rn  ) ROWNUM=$2; shift ;;
       -h  ) help ;;
        *  ) echo "Unknonw parameters $1";;
    esac
    shift
done
UNIT=${UNIT:-M}
VAR=${VAR:-Megs}
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get the password of $S_USER"
   exit 0
fi
echo

if [ $UNIT = 'B' ];then
     sqlplus -s  "$CONNECT_STRING" @$WK_SBIN/smenu_free_space_summary $HOST $ORACLE_SID $FOUT
     exit
fi

if [ "$UNIT" = "M" ];then
   DIV=1048576
   SIZE_MB="Size(mb)"
else
   DIV=1073741824
   SIZE_MB="Size(G)"
fi
# ........................................................

if [ "$CHOICE" = "CREATE" ];then
   if [ -n "$ftbs" ];then
       WHERE=" where tablespace_name = '$ftbs' "
   fi
   if [ "$QUOTA" = "TRUE" ];then
       SQL_QUOTA="select 'alter user ' || USERNAME || ' quota ' || decode (MAX_BYTES,-1,' unlimited' , MAX_BYTES ) ||
                ' on ' ||  TABLESPACE_NAME || ';' from dba_ts_quotas $WHERE order by username ;"
   fi
sqlplus -s "$CONNECT_STRING" <<EOF
set serveroutput on size unlimited
set pagesize 0 long 100000 heading off linesize 132 

select dbms_metadata.get_ddl('TABLESPACE',dba_tablespaces.tablespace_name) from dba_tablespaces $WHERE;
$SQL_QUOTA
EOF
exit
# ........................................................
elif [ "$CHOICE" = "DUS" ];then
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID ' right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 100 heading off pause off embedded off verify off
select 'Date                -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username            -  '||USER  nline ,
       'Database usage per user and tablespace' nline
from sys.dual
/
prompt
set linesize 190
comp sum of ttotal on report
break on report
col Owner head "User" justify l
col tablespace_name format A27 head "Tablespace" justify l
col ttotal format 999,990 head "Total space|By $SIZE_MB" justify c
col totbytes justify center format 999,999 head "Tablespace|$SIZE_MB" justify c
col tbsperc justify right format A10 head "Percent in|Tablespace"
col pctusdf format   a14 justify c heading '% tbs used| with Auto ext'
col Owner head "User" justify l
col fsize format 99990.99 head "$SIZE_MB"

set embedded on pause off heading on feedback off PAGESIZE 50 FEEDBACK OFF LINESIZE 124 space 1

select owner, a.tablespace_name, sum(bytes/$DIV)   ttotal,
       totbytes, '   '||lpad(decode(totbytes,0,'0.0','   ' || to_char(round((sum(bytes/$DIV)*100)/totbytes,2))),4,' ')   || '%' tbsperc,
       '   '||lpad(decode(b.maxbytes,0, 0,round(100-((b.maxbytes-(b.totbytes-c.freebytes))/b.maxbytes*100),2)),4,' ') || '%  ' pctusdf
    from dba_extents a ,
         ( select tablespace_name, trunc(sum(bytes/$DIV)) totbytes, sum(maxbytes/$DIV) maxbytes
                 from dba_data_files group by tablespace_name
         union all
          select tablespace_name, sum(bytes/$DIV) totbytes,  sum(maxbytes/$DIV) maxbytes
                 from dba_temp_files group by tablespace_name) b,
         (select sum(bytes/$DIV) freebytes ,tablespace_name from dba_free_space group by tablespace_name) c
    where  $OWN_SYS $AND_OWNER
    and a.tablespace_name = b.tablespace_name
    and a.tablespace_name = c.tablespace_name (+)
  group by owner, a.tablespace_name, totbytes,decode(b.maxbytes,0, 0,round(100-((b.maxbytes-(b.totbytes-c.freebytes))/b.maxbytes*100),2))
  order by owner;
EOF
exit
elif [ "$CHOICE" = "INFO" ];then

sqlplus -s "$CONNECT_STRING" <<!EOF
clear screen
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID ' right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 100 heading off pause off embedded off verify off
select 'Date                -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username            -  '||USER  nline ,
       'Tablespace default values ' nline
from sys.dual
/
prompt
set embedded on pause off
set heading on
set feedback off PAGESIZE 50 FEEDBACK OFF LINESIZE 124 space 1
COLUMN Tablespace_name FORMAT           A25 HEADING 'Tablespace Name'
COLUMN INITIAL_EXTENT FORMAT  9,999,999 HEADING 'Default|Initial|Extent(k)' justify c
COLUMN NEXT_EXTENT FORMAT  9,999,999 HEADING 'Default|Next|Extent(k)' justify c
COLUMN MIN_EXTENTS FORMAT 9,999 HEADING 'Min|Ext.' justify c
COLUMN logging FORMAT  A12 HEADING 'Logging|mode' justify c
COLUMN force_logging FORMAT  A4 HEADING 'Force|Logging' justify c
COLUMN PCT_INCREASE FORMAT  990 HEADING 'Pct|Increase' justify c
COLUMN STATUS FORMAT  A9 HEADING 'Status'  justify c
COLUMN EXTMAN FORMAT  A8 HEADING 'Extent|Manag.'  justify c truncate
COLUMN ALLOC FORMAT  A6 HEADING 'Alloc.|Type.'  justify c truncate
COLUMN CONTENTS FORMAT  A4 HEADING 'Type'  justify c truncate
COLUMN assm FORMAT  A4 HEADING 'ASSM'  justify c truncate
COLUMN AUTO FORMAT  A6 HEADING 'Auto|Ext'  justify c truncate
col xtprob  format a8 justify c heading 'next ext|problem'

select a.tablespace_name,INITIAL_EXTENT/1024 INITIAL_EXTENT,NEXT_EXTENT/1024 NEXT_EXTENT,
       decode(s.fext,0,'',NULL,'','S') xtprob,
       logging,PCT_INCREASE,STATUS, CONTENTS,
       decode(FILEXT,'Y','  Y','  N')  AUTO , EXTENT_MANAGEMENT EXTMAN, ALLOCATION_TYPE ALLOC,SEGMENT_SPACE_MANAGEMENT assm
from  dba_tablespaces a,
      ( select distinct tablespace_name, 'Y' FILEXT from dba_data_files where AUTOEXTENSIBLE = 'YES') b,
      ( select count(*) fext , tablespace_name from dba_segments x
          where next_extent > (Select Max(bytes)
                                      From dba_free_space
                                      Where Tablespace_name = x.Tablespace_name)
          and next_extent > (Select Max(Maxbytes - Bytes)
                                      From dba_data_files
                                      Where Tablespace_name = x.Tablespace_name)
          Group by tablespace_name
       ) s
where a.tablespace_name = b.tablespace_name (+)
   and a.Tablespace_name = S.Tablespace_name(+)
/
prompt
exit
!EOF

elif [ "$CHOICE" = "LIST_OBJECT_SIZE" ];then
     if [ -n "$PAR2" ];then
        WHERE_TABLESPACE_NAME="where tablespace_name = upper('$PAR2')"
     else
         TBS=" , tablespace_name "
     fi
     #cat  <<EOF
     sqlplus -s  "$CONNECT_STRING" <<EOF

set pause off pagesize 66 linesize 80 heading off embedded off verify off termout on
ttitle  'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   '         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 object size into tablespace $PAR2 (limited to first $ROWNUM rows) ' nline from sys.dual
/
set linesize 132 head on
col segment_name format a55
col owner format a14
col mb format 999990.9 head "$SIZE_MB"
col tablspace_name format a16
prompt  Type 'frg -h' for help
prompt
break on owner on report
comp sum of mb  on report
select s.owner $TBS,
       s.segment_name || (case when s.partition_name is null then ''
                 else '.' || s.partition_name end) as segment_name,
       s.segment_type, s.size_m as mb
from
  (select segment_name $TBS, partition_name, owner, bytes/$DIV size_M, segment_type
     from dba_segments $WHERE_TABLESPACE_NAME $AND_OWNER order by size_M desc) s
  where rownum <= $ROWNUM;

EOF
else # default --> return tablespace list
     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 ,
       'FREE - Free space summary by Tablespace  ' nline from sys.dual
/
prompt  Type 'frg -h' for help
prompt
set embedded on pagesize 66 linesize 124 heading on
comp sum of nfrags totsiz avasiz on report
break on report

set feed on
column dummy noprint
col name  format         a25 justify c heading 'Tablespace'
col nfrags  format     999,990 justify c heading 'Free|Frags'
col mxfrag  format 9,999,990.0 justify c heading 'Largest|Frag ($VAR)'
col totsiz  format 9,999,990.0 justify c heading 'Total|($VAR)'
col avasiz  format 9,999,990.0 justify c heading 'Available|($VAR)'
col pctusd  format         990 justify c heading '%|Used'
col pctusdf format         990 justify c heading '% used| Auto ext'

SELECT /*+ ordered */ h.tablespace_name name, 0 nfrags,0 mxfrag,
       sum(h.bytes_free+h.bytes_used)/$DIV totsiz,
       sum(h.bytes_free/$DIV) avasiz,
       sum(h.bytes_used)*100/sum(h.bytes_used+h.bytes_free) pctusd,
       0 pctusdf
FROM v\$temp_space_header h
      GROUP BY h.tablespace_name
union all
select  b.tablespace_name  name,
        nfrags,
        nvl(mxfrag/$DIV,0) mxfrag,
        nvl(totbytes/$DIV,0) totsiz,
        nvl(freebytes/$DIV,0) avasiz,
        decode(b.totbytes,0, 0,round(100-((b.totbytes-(b.totbytes-a.freebytes))/b.totbytes*100),2)) pctusd,
        decode(b.maxbytes,0, 0,round(100-((b.maxbytes-(b.totbytes-a.freebytes))/b.maxbytes*100),2)) pctusdf
from
  (select sum(bytes) freebytes ,tablespace_name from dba_free_space group by tablespace_name) a,
  (select sum(bytes) totbytes, sum(decode(maxbytes,0,bytes,maxbytes)) maxbytes, tablespace_name from  dba_data_files group by tablespace_name)  b,
  (select count(1)  nfrags , max(bytes) mxfrag , tablespace_name from dba_free_space group by tablespace_name) c
where
      b.tablespace_name = a.tablespace_name(+) $AND_TBS
  and b.tablespace_name = c.tablespace_name (+)
order by  1
/
exit
EOF

fi