Oracle DBA basic activities

Total Size of The Database
--------------------------------------------------------
An oracle database consists of data files, redo log files, control files, temporary files.
The size of the database actually means the total size of all these files.
the used space can be calculated from dba_sagments.

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Query to find the 10 Largest Objects in DB
--------------------------------------------------------
SELECT * FROM
(
select
    SEGMENT_NAME,
    SEGMENT_TYPE,
    BYTES/1024/1024/1024 GB,
    TABLESPACE_NAME
from
    dba_segments
order by 3 desc
) WHERE
ROWNUM <= 10

Oracle DBA Daily Checklist
--------------------------------------------------------
1. Check that all instances are up.

2. Monitor alert log entries (using tail -f)

3. Check that dbsnmp(SNMP subagent for Oracle DB) is running.

4. Check all last night backups were successful.

5. Check all database archiving are done.

6. Check tablespaces should not be used more that 95%.

7. Check all crons and Oracle Jobs are completed without any error

8. Verify resources for acceptable performance.

9. Identify bad growth of Segments.

10. Identify atleast 1 top resource consuming query

Display all datafiles, tempfiles and logfiles (and their sizes)
--------------------------------------------------------
set lines 100 pages 999
col name format a50
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#
union all
select name, 0
from v$controlfile) used
, (select sum(bytes) as p
from dba_free_space) free

How large is the database
--------------------------------------------------------
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p

Some Routine DB Checklist Queries
--------------------------------------------------------
Mviews Not Refreshed from Last 7 Days:
select mview_name from user_mviews where LAST_REFRESH_DATE < sysdate - 7;

Total Number of Tables:
select count(1) from user_tables where table_name not like '%$%'

Total Number of Mviews:
select count(1) from user_mviews

Total Number of Indexes:
select count(1) from user_indexes where index_type in ('FUNCTION-BASED NORMAL','NORMAL')

Total Number of Invalid Objects:
select count(1) from user_objects where status = 'INVALID'

Total Number of Objects Created in last 7 days:
select count(1) from user_objects where CREATED >= sysdate - 7

Total Database Size:
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual


I hope you all have enjoyed reading this article. Comments are welcome....