Tablespace Usage Scripts

set lines 300 pages 300;
select     a.TABLESPACE_NAME,
     a.BYTES bytes_used_GB,
     b.BYTES bytes_free_GB,
     b.largest,
     round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
     (
           select     TABLESPACE_NAME,
                sum(BYTES)/1024/1024/1024 BYTES
           from dba_data_files
           group      by TABLESPACE_NAME
     )
     a,
     (
           select     TABLESPACE_NAME,
                sum(BYTES)/1024/1024/1024 BYTES ,
                max(BYTES) largest
           from dba_free_space
           group      by TABLESPACE_NAME
     )
     b
where      a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME=b.TABLESPACE_NAME
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;



Tablespace Monitoring
*********************
column "PCT Used" format 9999
break on report
compute sum of TOTAL on report
COMPUTE SUM Of USED on report
COMPUTE SUM Of FREE on report
select
a.tablespace_name,
round(b.bytes/(1024*1024)) as TOTAL,
round(b.bytes/(1024*1024)-a.bytes/(1024*1024)) as USED,
round(a.bytes/(1024*1024)) "FREE" ,
round((round(b.bytes/(1024*1024)-a.bytes/(1024*1024))/(b.bytes/(1024*1024)))*100) as "PCT Used" ,'%'
fromsys.sm$ts_free a, sys.sm$ts_avail b where a.tablespace_name=b.tablespace_name order by 5

singletablespace monitoring

set pages 999
set linesize 80
column tablespace_name for a15
SELECT * FROM (
SELECT c.tablespace_name,
ROUND(a.bytes/1048576,2) MB_Allocated,
ROUND(b.bytes/1048576,2) MB_Free,
ROUND((a.bytes-b.bytes)/1048576,2) MB_Used, ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used FROM (SELECT tablespace_name,
SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
Where a.tablespace_name='&tbs'
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+) AND a.file_id = b.file_id (+) GROUP BY a.tablespace_name) b, sys.DBA_TABLESPACES c WHERE a.tablespace_name = b.tablespace_name(+) AND a.tablespace_name = c.tablespace_name
) WHERE tot_Pct_Used>=0
ORDER BY tablespace_name;

List information about tablespace to which datafiles belong
***********************************************************

SELECT file_id,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024,INCREMENT_BY FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME='APPS_TS';
TO check in asm space:
--------------------------
selectname,total_mb,free_mb,(free_mb/total_mb)*100 from v$asm_diskgroup;

alter tablespace APPS_TS add datafile '+DATA' size 2000m



and this is for temp files
you can view the tempfile in dba_temp_files only
***********************

SELECT file_id,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024,INCREMENT_BY FROM DBA_TEMP_FILES WHERE TABLESPACE_NAME='TEMP_STD_TBL';

********************************************************************************************************************************************

altertablespace TEMP_STD_TBL add tempfile '+DATA1' size 10000m;
***************



SELECT A.tablespace_nametablespace, 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;



Temp tablepace job failure\



selectFILE_NAME,TABLESPACE_NAME,bytes/1024/1024 from DBA_TEMP_FILES;
SELECT A.tablespace_nametablespace, 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;



df -h|grep /dbs

Tablespace IPF_STD_TBL is 80 percent full


select tablespace_name
       , count(*) as no_of_data_files
       , sum(maxblocks) as max_size
from dba_data_files
group by tablespace_name;

select tablespace_name, max_size
from dba_tablespaces
/

select tablespace_name
       , initial_extent + (next_extent * (max_extents-1)) as calc_max_size
from dba_tablespaces
/