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


Related Links:
- Oracle DBA Daily Checklist
- Total Size of Database
- Find 10 largest objects