Sizes checking in database level scripts

===============================================================
check sizes
===============================================================

============check datafile size===================

SELECT SUM(a.bytes/1048576) Megs_Alloc,
       SUM(b.bytes/1048576) Megs_Free,
       SUM((a.bytes-b.bytes)/1048576) Megs_Used
FROM (SELECT SUM(a.bytes) bytes FROM sys.dba_data_files a) a,
(SELECT NVL(SUM(b.bytes),0) bytes
FROM sys.dba_free_space b) b;

=============Check tablespace ==============

SELECT tablespace_name, SUM(bytes/1024/1024) FROM dba_data_files GROUP BY tablespace_name ORDER BY 2 DESC;



========check schema size =================
----- All schema ------
select OWNER,sum(BYTES/1024/1024) from dba_segments group by owner order by 2;

-----particular schema----
select
   owner,
   sum(bytes)/1024/1024/1024 schema_size_gig
from
   dba_segments where owner='schema_name'
group by
   owner;
========check table size with table name =================

select segment_name,segment_type,bytes/1024/1024 MB
 from dba_segments
 where segment_type='TABLE' and segment_name='<yourtablename>';


==============redo size===================
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

==========================================================

---Archive log Size count based on hours ---

==========================================================

select to_char(first_time, 'MM-DD-YYYY') ArchiveDate, to_char(first_time, 'HH24') ArchiveHour,

       sum(BLOCKS*BLOCK_SIZE/1024/1024) LogMB from v$archived_log 

where first_time > sysdate - 7

group by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24')

order by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24');