Database Health Check
-----------------------------------------------------------------------
connect as sysdba
SQL> select status, name from v$controlfile;
connect as sysdba
SQL> select * from v$logfile;
connect as sysdba
SQL> archive log list
To determine if a datafile and thus, a tablespace, has AUTOEXTEND capabilities:-
----------------------------------------------------------------------------------
select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name
from dba_data_files
where autoextensible = 'YES';
Location of Datafiles:-
-------------------------------
SQL> select * from v$dbfile;
Check System Tablespace:-
---------------------------
select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');
Check SYSAUX Tablespace:-
------------------------------
select space_usage_kbytes, occupant_name, occupant_desc
from v$sysaux_occupants
order by 1 desc;
Local vs Dictionalr Managed tablespaces:-
-------------------------------------------
select tablespace_name, extent_management
from dba_tablespaces;
Users that have permanent Tablespaces Specified:-
------------------------------------------------------
select u.username, t.tablespace_name
from dba_users u, dba_tablespaces t
where u.temporary_tablespace = t.tablespace_name
and t.contents <> 'TEMPORARY';
Size of Temporary Tablespace:-
-----------------------------------------------------
select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;
High watermark of Temp tablespace:-
-------------------------------------
select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;
Current usage of Temp table space:-
--------------------------------------
select ss.tablespace_name,
sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;
Find number of extent:-
-------------------------
select owner, segment_type, segment_name, tablespace_name,
count(blocks), SUM(bytes/1024) "BYTES K", SUM(blocks)
from dba_extents
where owner NOT IN ('SYS','SYSTEM')
group by owner, segment_type, segment_name, tablespace_name
having count(*) > <--minext-->>
order by segment_type, segment_name;
Find next extent:-
-------------------------
select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);
Rollback Segments:-
-------------------------
select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
s.wraps, s.status
from v$rollstat s, dba_rollback_segs d
where s.usn = d.segment_id
order by 1;
Alert log location:-
------------------------------
show parameter background_dump_dest
Max dump file size:-
---------------------------
show parameter max_dump_file_size
Please write to me on akash007.pramanik@gmail.com for any concerns.
