################################################################################
# FUNCTIONS
################################################################################

#-------------------------------------------------------------------------------
# SEARCH FOR V$VIEWS
#-------------------------------------------------------------------------------
f_search_views () {
   echo "Please enter filter criteria: \c"
   read s_search_view_filter
   echo "...searching for views containing <$s_search_view_filter>"

	sqlplus -s "/ as sysdba" <<-EOSQL

		SET PAGES    1000
		SET LINES    150
		--SET HEADING  OFF
		SET FEEDBACK OFF


		select
			view_name
		from
			dba_views
		where
			owner        = 'SYS'
		and     view_name like 'V_$%'
		and     view_name like upper('%$s_search_view_filter%')
		order by 1
		;

	quit
	EOSQL
}



#-------------------------------------------------------------------------------
# SEARCH FOR COLUMNS
#-------------------------------------------------------------------------------
f_search_column () {
   echo "Please enter column name or part of it: \c"
   read s_search_column
   echo "...searching for tables containing column <$s_search_column>"

	sqlplus -s "/ as sysdba" <<-EOSQL

		SET PAGES    1000
		SET LINES    150
		--SET HEADING  OFF
		SET FEEDBACK OFF


		select distinct
			owner || '.' || table_name "TABLE_NAME",
			column_name
		from
			dba_tab_columns
		where
			column_name like upper('%$s_search_column%')
		order by 1, 2
		;

	quit
	EOSQL
}



#-------------------------------------------------------------------------------
# SHOW CONTROLFILE NAMES
#-------------------------------------------------------------------------------
f_show_ctl_file_name () {
   echo "Control files for instance <$ORACLE_SID>"

	sqlplus -s "/ as sysdba" <<-EOSQL

		SET PAGES    1000
		SET LINES    150
		--SET HEADING  OFF
		SET FEEDBACK OFF

		column "CONTROL FILE NAME" format a60

		select
			name "CONTROL FILE NAME"
		from
			v\$controlfile
		order by 1
		;

	quit
	EOSQL
}



#-------------------------------------------------------------------------------
# SHOW LAST SESSIONS
#-------------------------------------------------------------------------------
f_show_last_sessions () {
   echo "Sessions logged in during the last <n> minutes (enter value for <n>): \c"
   read s_minutes
   echo ""

	sqlplus -s "/ as sysdba" <<-EOSQL

		SET PAGES    1000
		SET LINES    150
		--SET HEADING  OFF
		SET FEEDBACK OFF

		column OS-USER        format a12
		column ORACLE-USER    format a12
		column OS-PID         format a15

		select
			s.OSUSER                                      "OS-USER" ,       -- Operating system client user name
			s.USERNAME                                    "ORACLE-USER" ,   -- Oracle username
			to_char(s.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') "LOGON-TIME" ,    -- Database logon time
			p.PID                                         "ORA-PID" ,       -- Oracle process identifier
			p.SPID                                        "OS-PID " ,       -- Operating system process identifier
			s.SID                                         "SESSION-ID"      -- Session identifier
		from
			v\$process p,
			v\$session s
		where s.paddr        = p.addr
		and   s.logon_time   > sysdate-$s_minutes/1440
		order by 3
		;

	quit
	EOSQL
}



#-------------------------------------------------------------------------------
# SHOW REDO LOG INFO
#-------------------------------------------------------------------------------
f_show_redo_log_info () {
   echo "Redo log files for instance <$ORACLE_SID>"

	sqlplus -s "/ as sysdba" <<-EOSQL

		SET PAGES    1000
		SET LINES    150
		--SET HEADING  OFF
		SET FEEDBACK OFF

		column member format a50

		select
			l.group#,
			l.members,
			l.archived,
			l.status,
			f.type,
			f.member
		from
			v\$log l, v\$logfile f
		where   l.group# = f.group#
		order   by 1, 6
		;

	quit
	EOSQL
}



#-------------------------------------------------------------------------------
# SHOW DATABASE SIZE
#-------------------------------------------------------------------------------
f_show_dbsize () {
   echo "Current sizing of instance <$ORACLE_SID>"

	sqlplus -s "/ as sysdba" <<-EOSQL

		SET PAGES    1000
		SET LINES    150
		--SET HEADING  OFF
		SET FEEDBACK OFF

		column "TABLESPACE NAME" format a60

		select
			tablespace_name               "TABLESPACE NAME",
			sum(round(bytes/1024/1024))   "MB"
		from
			dba_segments
		group by tablespace_name
		;



		select
			sum(round(bytes/1024/1024)) "SUM DATAFILES (MB)"
		from
			v\$datafile
		;



		column "DATAFILE NAME" format a60

		select
			name                   "DATAFILE NAME",
			round(bytes/1024/1024) "MB"
		from
			v\$datafile
		order by 1
		;



		select
			sum(round(bytes/1024/1024)) "SUM TEMPFILES (MB)"
		from
			v\$tempfile
		;



		column "TEMPFILE NAME" format a60

		select
			name                   "TEMPFILE NAME",
			round(bytes/1024/1024) "MB"
		from
			v\$tempfile
		order by 1
		;



		select
			sum(round(bytes/1024/1024)) "SUM REDOLOGS (MB) PER MEMBER"
		from
			v\$log
		;



		column "REDO LOG FILE" format a60

		select
			f.member "REDO LOG FILE",
			round(l.bytes/1024/1024) "MB"
		from
			v\$log l, v\$logfile f
		where
			l.group# = f.group#
		order by 1
		;

	quit
	EOSQL
}



#-------------------------------------------------------------------------------
# DESCRIBE OBJECTS
#-------------------------------------------------------------------------------
f_describe_object () {
   echo "Please enter object name: \c"
   read s_describe_object
   echo "Description for object <$s_describe_object>"

	sqlplus -s "/ as sysdba" <<-EOSQL

		SET PAGES    1000
		SET LINES    150
		SET HEADING  OFF
		SET FEEDBACK OFF


		desc $s_describe_object

	quit
	EOSQL
}



f_no_func () {
   echo "hallo" >/dev/null
}



#-------------------------------------------------------------------------------
# SHOW PARAMETER
#-------------------------------------------------------------------------------
f_show_parameter () {
   echo "Please enter parameter name or part of it: \c"
   read s_parameter
   echo "Parameters found for selection <$s_parameter>"

	sqlplus -s "/ as sysdba" <<-EOSQL

		SET PAGES    1000
		SET LINES    150
		SET HEADING  OFF
		SET FEEDBACK OFF


		show parameter $s_parameter

	quit
	EOSQL
}



f_no_func () {
   echo "hallo" >/dev/null
}



################################################################################
# MAINLINE
################################################################################
while true
do
   echo "--------------------------------------------------------------------------------------------------"
   echo "|                                | 10 - Show parameter             | 20 - Database sizes         |"
   echo "| 01 - Search for views          | 11 - Show control file names    | 21 -                        |"
   echo "| 02 - Search for columns        | 12 - Show redo log info         | 22 -                        |"
   echo "| 03 -                           | 13 - Show last sessions         | 23 -                        |"
   echo "| 04 -                           | 14 -                            | 24 -                        |"
   echo "|                                |                                 |                             |"
   echo "| 99 - Describe object           |                                 |  q - Quit                   |"
   echo "--------------------------------------------------------------------------------------------------"
   echo "Please enter your choice: \c"

   read s_choice

   case ${s_choice} in
       "01")   f_search_views ;;
       "02")   f_search_column ;;
       "10")   f_show_parameter ;;
       "11")   f_show_ctl_file_name ;;
       "12")   f_show_redo_log_info ;;
       "13")   f_show_last_sessions ;;
       "20")   f_show_dbsize ;;
       "99")   f_describe_object ;;
       "q"|"quit")   exit ;;
         *)   f_no_func ;;
   esac
done



################################################################################
# FINE
################################################################################
exit