#!/bin/bash
VALUE=`sqlplus -S / as sysdba <<EOF
set head on;
set feed off;
set trimspool on;
set linesize 32767;
set pagesize 32767;
set colsep ","
set echo off;
set termout off;
--instance_name
spool /tmp/oracle/instance_name.csv
select instance_name from v\\$instance;
spool off
--created_time
spool /tmp/oracle/created_time.csv
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select created from v\\$database;
spool off
--version.csv
spool /tmp/oracle/version.csv
COL PRODUCT FORMAT A40
COL VERSION FORMAT A15
COL STATUS FORMAT A20
select * from product_component_version;
spool off
--hostname
spool /tmp/oracle/hostname.csv
select host_name from v\\$instance;
spool off
--character
spool /tmp/oracle/character.csv
COL VALUE FORMAT A15
select value from nls_database_parameters where PARAMETER='NLS_CHARACTERSET';
spool off
--updata
spool /tmp/oracle/update.csv
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select startup_time from v\\$instance;
spool off
--status
spool /tmp/oracle/status.csv
select status from v\\$instance;
spool off
--connect
spool /tmp/oracle/connect.csv
select sysdate from dual;
spool off
--sga
spool /tmp/oracle/sga.csv
select * from v\\$sgainfo;
spool off
--tablespace_info
spool /tmp/oracle/tablespace_info.csv
select * from v\\$tablespace;
spool off
--tablespace_space
spool /tmp/oracle/tablespace_space.csv
select df.tablespace_name "Tablespace",
totalusedspace "Used Bytes",
(df.totalspace - tu.totalusedspace) "Free Bytes",
df.totalspace "Total Bytes",
round(10000 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))/100
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes)) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
spool off
--process_limit
spool /tmp/oracle/process.csv
select value from v\\$parameter where name = 'processes';
spool off
--session_current
spool /tmp/oracle/session_current.csv
select count(s.status) TOTAL_SESSIONS from gv\\$session s;
spool off
--session_active
spool /tmp/oracle/session_active.csv
select count(s.status) ACTIVE_SESSIONS
from gv\\$session s, v\\$process p
where
p.addr=s.paddr and
s.status='ACTIVE';
spool off
--session_inactive
spool /tmp/oracle/session_inactive.csv
select count(s.status) INACTIVE_SESSIONS
from gv\\$session s, v\\$process p
where
p.addr=s.paddr and
s.status='INACTIVE';
spool off
--session_locked
spool /tmp/oracle/session_locked.csv
SELECT count(b.sid) INACTIVE_SESSIONS
FROM v\\$locked_object a,v\\$session b  
WHERE a.session_id = b.sid order by b.logon_time;
spool off
exit;
EOF`
