db_info.sql

#REM Script for Checking the Status of the Database
#REM 

spool db_info.txt
set echo off
set heading off

col machine for a50
select 'Name of the Machine' from dual
/
select substr(program,instr(program,'@',1)+1,(instr(program,' ',1)-instr(program,'@',1))) machine from v$process where pid = 2
/
select 'Name of the Instance and its Log-Mode' from dual
/
col name for a40
col log_mode for a30
select name,log_mode from v$database
/
col name for a70
select 'Oracle and Tools Version Information' from dual
/
select * from v$version
/
col name for a70
select 'ControlFileInformation' from dual
/
select name from v$controlfile 
/
select 'LogFileInformation' from dual
/
col member for a65
select status,member from v$logfile 
/
select 'DataFileInformation' from dual
/
col name for a65
select status,bytes,name from v$datafile 
/
select 'Tablespace Information and Allocated Space Information' from dual
/
select tablespace_name,sum(bytes/1000000)||' MB' AllocatedSpace from dba_data_files group by tablespace_name
/
select 'User Information And Associated Tablespace' from dual
/
col username for a20
col default_tablespace for a25
col temporary_tablespace for a25
select username,default_tablespace,temporary_tablespace from dba_users 
/
select 'Complete Information on Tablespace and Storage Parameters' from dual
/
col tbs_name for a20
select tablespace_name tbs_name,initial_extent i_extent,next_extent n_extent,min_extents,max_extents,status from dba_tablespaces 
/
select 'Information on Datafile and Associated Tbs and Size' from dual
/
col tbs_name for a32
col file_name for a70
col Total_Space for a33
select tablespace_name tbs_name,file_name,(bytes/1000000) || 'MB' Total_Space from dba_data_files 
order by tablespace_name
/
select 'Information on Users and Quotas on Tablespaces' from dual
/
col tablespace_name for a30
col "Allowed Quota on Tablespace" for a38
select tablespace_name,username,decode(max_bytes,'-1','UnLimitedQuota',max_bytes/1000000 || ' MB') "Allowed Quota on Tablespace" from dba_ts_quotas 
/
select 'List of Users Not Created with Specific Quotas' from dual
/
select distinct a.username from dba_users a,dba_ts_quotas b 
where a.username not in (select distinct username from dba_ts_quotas)
/
select 'Database Link Information' from dual
/
col owner for a30
col db_link for a48
col username for a20
select owner,db_link,username from dba_db_links 
/
spool off