rem $Id$
rem Copyright: (c) 2008 Akkaya Consulting GmbH / http://www.akkaya.de
rem First Created: August 2001
rem Author: Adem Akkaya / Akkaya Consulting GmbH / www.akkaya.de
rem Assistance: Joerg Diederich / HP

rem Start/Call: sqlplus /nolog @akk.oracle.sql <Logfile-Name>

rem This program is free software; you can redistribute it and/or modify
rem it under the terms of the GNU General Public License as published by
rem the Free Software Foundation; either version 3 of the License, or
rem (at your option) any later version.

rem This program is distributed in the hope that it will be useful,
rem but WITHOUT ANY WARRANTY; without even the implied warranty of
rem MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
rem GNU General Public License for more details.
rem You should have received a copy of the GNU General Public License
rem along with this program.  If not, see <http://www.gnu.org/licenses/>.

connect / as sysdba
set pagesize 999
set pause off
set linesize 1024

column MBfilesize format 999,999,999,999,900;
column MBtablespacesize format 999,999,999,999,900;
column MBallocpace format 999,999,999,999,900;
column Bytesfreespace format 999,999,999,999,900;
column bytes format 999,999,999,999,900;
column Free format 990.999;
column Alloc format 990.999;
column FILE_NAME format a127;
column tablespace_name format a20;
column status format a25;
column version format a15;
column product format a50;
column comp format a15;
column modified format a20;
column name format a60;
column table format a60;
column index format a60;
column Datafile format a127;

spool &&1
set heading off
select '@@@! DB = ' || rtrim (name)  from v$database;

set heading on
ttitle '@!#### Startime ' left;
SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') Datum from dual;

prompt @!#### DB Info;
ttitle '@!##### DB Archive ' left;
select name DBName, DBID, log_mode Logmode, TO_CHAR(CREATED, 'DD-MON-YYYY HH:MI:SS') Created, OPEN_MODE OpenMode from v$database;
archive log list;

ttitle '@!##### DB Version' left;
select * from v$version;

ttitle '@!##### DB Product' left;
select rtrim(substr(status,1,20)) Status, rtrim(substr(Version,1,15)) Version, rtrim(substr(product,1,40)) Product from product_component_version;

ttitle '@!##### DB Registry' left;
select rtrim(substr(COMP_ID,1,20)) Comp, rtrim(substr(STATUS,1,10)) Status, rtrim(substr(VERSION,1,10)) Version, rtrim(substr(MODIFIED,1,22)) Modified, rtrim(substr(COMP_NAME,1,40)) Name from dba_registry;

prompt @!#### User;
ttitle '@!##### Defined User ' left;
select USERNAME, ACCOUNT_STATUS Status,
       DEFAULT_TABLESPACE DefaultTbs, TEMPORARY_TABLESPACE TempTbs
       from dba_users order by 1;

ttitle '@!##### DBA_Objects for all Users' left;
select owner "Owner", to_char (count(1),'999,999,999') "Objects" from dba_objects group by owner order by 1;

ttitle '@!##### DBA_Objects group by object_type for all User exept sys and system  ' left;
select owner "Owner", object_type "Object_Type", to_char (count(1),'999,999,999') "Count" from dba_objects where owner not in ('SYS', 'SYSTEM') group by owner, object_type order by 1, 2;

ttitle '@!##### Invalid DBA_Objects for all Users ' left;
select owner "Owner", object_type "Object_Type", to_char (count(1),'999,999,999') "Invalid" from dba_objects where status = 'INVALID' group by owner, object_type order by 1, 2;

rem ttitle '@!##### Create Ascii Controlfile ' left;
rem alter database backup controlfile to trace;

prompt @!#### Tablespace;
ttitle '@!##### Tablespace size' left;
break on report
compute sum label Total of MBtablespacesize MBallocpace Bytesfreespace on report
select a.tablespace_name,
       b.total MBtablespacesize,
       c.alloc MBallocpace,
       a.free Bytesfreespace,
       (a.free / b.total * 100 ) Free,
       (c.alloc / b.total * 100 ) Alloc
       from
          (select tablespace_name, sum(bytes / 1024) free from dba_free_space group by tablespace_name) a,
          (select tablespace_name, sum(bytes / 1024) total from dba_data_files group by tablespace_name) b,
          (select tablespace_name, sum(bytes / 1024) alloc from dba_segments group by tablespace_name) c
           where a.tablespace_name=b.tablespace_name and
                 a.tablespace_name=c.tablespace_name(+)
                 order by a.tablespace_name;

ttitle '@!##### Temp Tablespace size' left;
break on report
compute sum label Total of MBtablespacesize MBallocpace Bytesfreespace on report
select tablespace_name,
       total MBtablespacesize
       from
          (select tablespace_name, sum(bytes / 1024) total from dba_temp_files group by tablespace_name)
                 order by tablespace_name;

ttitle '@!##### Tablespace management' left;
select tablespace_name, extent_management, MAX_EXTENTS, STATUS, ALLOCATION_TYPE from dba_tablespaces;

ttitle '@!##### Rollback Segmente' left;
select substr(b.name,1,15) "SEGMENT_NAME",
       c.tablespace_name, a.status, a.extents, c.next_extent "Next", c.max_extents "Max",
       a.rssize/1024 "Rssize" , a.optsize/1024 "optsize", a.aveactive, a.hwmsize/1024 "HWsize"
       from v$rollstat a,  v$rollname b, sys.dba_rollback_segs c
            where a.usn = b.usn and
                  b.name = c.SEGMENT_NAME
                  order by b.name ;

prompt @!#### Content;
ttitle '@!##### 10 greatest Tables' left;
select * from (select substr(segment_name,1,25) "Table ",bytes/1024 Mbytes,
                      EXTENTS, MAX_EXTENTS "Max ", substr(OWNER,1,10) "Owner", TABLESPACE_NAME,
                      INITIAL_EXTENT/1024 Init_Mb,
                      NEXT_EXTENT/1024  Next_Mb,
                      MIN_EXTENTS Min
                      from dba_segments where segment_type='TABLE' order by 2 desc)
               where rownum<11;

ttitle '@!##### 10 greatest Indexes' left;
select * from (select substr(segment_name,1,25) "Index ",bytes/1024 Mbytes,
                      EXTENTS, MAX_EXTENTS "Max ", substr(OWNER,1,10) "Owner", TABLESPACE_NAME,
                      INITIAL_EXTENT/1024 Init_Mb,
                      NEXT_EXTENT/1024  Next_Mb,
                      MIN_EXTENTS Min
                      from dba_segments where segment_type='INDEX' order by 2 desc)
               where rownum<11;

prompt @!#### Files;
ttitle '@!##### Datafiles' left;
break on report
compute sum label Total of MBfilesize on report
select substr(a.tablespace_name,1,15) "Tablespace ",
       a.status, b.status, b.ENABLED, a.AUTOEXTENSIBLE "Autoextent ",
       to_char(b.CREATION_TIME, 'YYYY.MM.DD HH:MI:SS') "Created ",
       a.file_id, (a.bytes / 1024) MBfilesize, rtrim(substr(a.file_name,1,127)) "Datafile "
       from dba_data_files a, v$datafile b
            where a.file_id = b.file#
                  order by 1, 6;

ttitle '@!##### Tempfiles' left;
break on report
compute sum label Total of MBfilesize on report
select substr(a.tablespace_name,1,15) "Tablespace ",
       a.status, b.status, b.ENABLED, a.AUTOEXTENSIBLE "Autoextent ",
       to_char(b.CREATION_TIME, 'YYYY.MM.DD HH:MI:SS') "Created ",
       a.file_id, (a.bytes / 1024) MBfilesize, rtrim(substr(a.file_name,1,127)) "Datafile "
       from dba_temp_files a, v$tempfile b
            where a.file_id = b.file#
                  order by 1, 6;

ttitle '@!##### Logfile' left;
select a.group#, b.status, b.archived,
        b.bytes/1024 Mbytes,
        rtrim(substr(a.member,1,127)) file_name
        from v$logfile a, v$log b
             where a.group# = b.group# order by 2, 1;

ttitle '@!##### Controlfile' left;
select status, rtrim(substr(name,1,127)) file_name from v$controlfile order by file_name;


prompt @!#### Parameter;
ttitle '@!##### NLS' left;
select substr(PARAMETER,1,25) Parameter, substr(VALUE,1,20) Value from v$nls_parameters where parameter = 'NLS_CHARACTERSET' or parameter = 'NLS_NCHAR_CHARACTERSET';

ttitle '@!##### All' left;
show parameter

ttitle '@!#### Endtime ' left;
SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') Datum from dual;

spool off
host echo  " " >> &&1
host echo  "### Unix Filesystem" >> &&1
host df -kP ${ORACLE_BASE}/${ORACLE_SID}/sapdata* >>&&1
host sed -e 's/  *$//' &&1 > &&1.1
host mv &&1.1 &&1
exit