Tablespace and ASM usage Scripts

#FULL DB TABLESPACE CHECK#
------------------------------------------------------------

SELECT   FreeSpace.Tablespace_Name "Tablespace"                  ,
DataFiles.TotalSpace "Total MB"                         ,
( DataFiles.TotalSpace - FreeSpace.FreeSpace ) "Used MB",
FreeSpace.FreeSpace "Free MB"                           ,
         ROUND ( 100 * ( FreeSpace.FreeSpace / DataFiles.TotalSpace ) ) "Pct.Free"
FROM
         ( SELECT  Tablespace_Name,
                  ROUND ( SUM ( Bytes ) / ( 1024 * 1024 ) ) TotalSpace
         FROM     DBA_DATA_FILES
         GROUP BY Tablespace_Name
         ) DataFiles              ,
         ( SELECT  Tablespace_Name,
                  ROUND ( SUM ( Bytes ) / ( 1024 * 1024 ) ) FreeSpace
         FROM     DBA_FREE_SPACE
         GROUP BY Tablespace_Name
         ) FreeSpace
WHERE    DataFiles.Tablespace_Name = FreeSpace.Tablespace_Name
ORDER BY 5 DESC;


TEMP_TABLESPACE :
---------------------------


SET PAGESIZE 60
SET LINESIZE 300

SELECT
   A.tablespace_name tablespace,
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
   v$sort_segment A,
(
SELECT
   B.name,
   C.block_size,
   SUM (C.bytes) / 1024 / 1024 mb_total
FROM
   v$tablespace B,
   v$tempfile C
WHERE
   B.ts#= C.ts#
GROUP BY
   B.name,
   C.block_size
) D
WHERE
   A.tablespace_name = D.name
GROUP by
   A.tablespace_name,
   D.mb_total

##ASM_USAGE##

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

SELECT name,group_number, total_mb,free_mb,(total_mb - free_mb)used_mb,ROUND((free_mb/total_mb)*100,2) "%FREE" , ROUND(((total_mb - free_mb)/total_mb)*100,2) "%USED" FROM v$asm_diskgroup;

ASM_DISK LEVEL DETAIL##

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

set lines 300 pages 300

col NAME for a20

col PATH for a30

col LABEL for a20

col HEADER_STATUS for a10

select name,label,GROUP_NUMBER,DISK_NUMBER,HEADER_STATUS,STATE,TOTAL_MB/1024,FREE_MB/1024,OS_MB/1024,path from v$asm_disk order by 1,2;

//

select name

       ,round(TOTAL_MB/1024/2,0)                       "TOTAL GB"

       ,round((TOTAL_MB/1024/2)-(FREE_MB/1024/2),0)    "USED GB"

       ,round(FREE_MB/1024/2,0)                        "FREE GB"

       ,round(REQUIRED_MIRROR_FREE_MB/1024/2,0)        "REQUIRED FREE GB"

       ,round(USABLE_FILE_MB/1024,0)                   "USABLE FREE (GB)"

       ,round((USABLE_FILE_MB/1024)/(TOTAL_MB/1024/2)*100,0)    "USABLE PERCENT"

       from v$asm_diskgroup

       where name like '%DATA%'

DATA_FILE_CHECKS :

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

select file_name,FILE_ID,bytes/1024/1024/1024,maxbytes/1024/1024/1024,autoextensible from dba_data_files where tablespace_name='&1';

select tablespace_name,used_percent from dba_tablespace_usage_metrics where used_percent > 80;

select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='&1';

select sum(bytes)/1024/1024/1024 from dba_free_space where tablespace_name='&1';

SELECT s.username, s.sid, u.TABLESPACE, u.CONTENTS, u.extents, u.blocks*8/1024 "MB util" FROM v$session s, v$sort_usage u WHERE s.saddr = u.session_addr order by 6 desc;

Database_size:

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

-- get database size from v$datafile:

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

select round((sum(bytes)/1048576/1024),2)"DB_SIZE_GB" from v$datafile;

-- get Oracle database size from dba_data_files:

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

select

"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"

from(

select 

(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",

(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"

from dual

);

db_recovery_file_dest_size check:

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

col name for a32

col size_m for 999,999,999

col used_m for 999,999,999

col pct_used for 999

SELECT name

, ceil( space_limit / 1024 / 1024) SIZE_M

, ceil( space_used  / 1024 / 1024) USED_M

, decode( nvl( space_used, 0),

0, 0

, ceil ( ( space_used / space_limit) * 100) ) PCT_USED

FROM v$recovery_file_dest

ORDER BY name

/

## To see how much UNDO information has been written in the past hour ##

//

select begin_time, end_time, undoblks, txncount, maxconcurrency as maxcon,nospaceerrcnt from V$UNDOSTAT where begin_time > sysdate-(4/24);

//