Tablespace Usage Information

To Check free/used space per all tablespaces

SELECT a.tablespace_name,
  ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
  ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
  ROUND(a.used_percent, 2) AS "USED_PERCENT"
  FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
  DBA_TABLESPACES b
  ON a.tablespace_name = b.tablespace_name
  WHERE a.used_percent>80;

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;



(OR)

select a.tablespace_name name,
           round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/1024/1024)      Total_MB,
           round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
           sum(a.bytes)/count( distinct b.file_id ))/1024/1024)                       Used_MB,
           round((sum(a.bytes)/count( distinct b.file_id ))/1024/1024)                       Free_MB,
           round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
                           (sum(a.bytes)/count( distinct b.file_id ) )) /
           (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )))  pct_used,
  round(100 - round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
                           (sum(a.bytes)/count( distinct b.file_id ) )) /
           (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))))  "PCT_FREE(%)"
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
order by pct_used;

(OR)

To check the tablespace freespace below 6%

SELECT F.TABLESPACE_NAME as Tablespace,
  TO_CHAR (T.TOTAL_SPACE, '999,999') as "TOTAL(MB)",
       TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') as "USED(MB)",
       TO_CHAR (F.FREE_SPACE, '999,999') "FREE(MB)",
       TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' "FREE_PER"
FROM   (
       SELECT       TABLESPACE_NAME, 
                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024
                                        FROM V$PARAMETER 
                                        WHERE NAME = 'db_block_size')/1024)
                           ) FREE_SPACE
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
       ) F,
       (
       SELECT TABLESPACE_NAME,
       ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
       ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 6 order by FREE_PER;

To Check free/used space per one tablespace

set feedback off
set pages 1000
set linesize 123
col tablespace_name format a30
col file_name format a45
col free_bytes format 9999999999999
col Used_bytes format 9999999999999
col max_bytes format 9999999999999
col num_files format 999
SELECT ts_usage.tablespace_name,
ts_usage.num_files num_files,
ts_usage.free_bytes free_bytes,
ts_usage.total_bytes Max_bytes, ts_usage.file_bytes Used_Bytes,
ceil((ts_usage.used_bytes*100)/ts_usage.total_bytes) percent_used
FROM
(select d.tablespace_name tablespace_name,
d.num_files num_files,
nvl(f.bytes,0) free_bytes,
d.file_bytes file_bytes,
case when d.maxbytes<d.file_bytes then d.file_bytes else d.maxbytes END total_bytes,
d.file_bytes-nvl(f.bytes,0) used_bytes
from (select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) f,
(select tablespace_name, count(distinct(file_id)) num_files, sum(bytes) file_bytes,
sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes
from dba_data_files group by tablespace_name) d
where d.tablespace_name = f.tablespace_name (+)
and d.tablespace_name not like '%UNDO%' and d.tablespace_name like '&tsname'
order by d.tablespace_name ) TS_USAGE
WHERE ceil((ts_usage.used_bytes*100)/ts_usage.total_bytes) between 80 and 100;



To Check Datafiles associated with Tablespace

set lines 200
set pages 200
col FILE_NAME for a50

select file_name, bytes/1024/1024 sizem,maxbytes/1024/1024 msizem,autoextensible,increment_by from dba_data_files where tablespace_name='&Tablespace_Name';

To add a Datafile:

alter tablespace xxxxx add datafile '/u1/oracle/data/xxxxxx05.dbf' size 1024M autoextend on next 100M maxsize 4096M;

###################################################################################

On ASM:

alter tablespace users add datafile ‘+data1’ size 5M;

alter tablespace TS_MASTER add datafile '+DATA' size 1g autoextend on;

ALTER TABLESPACE USERS ADD DATAFILE '+WEBLOGDB_DATA3' SIZE 100M  AUTOEXTEND ON  MAXSIZE 10240M;


alter tablespace APPS_TS_MEDIA add datafile '+DATAC2' size 10240M autoextend on next 2048M maxsize 30720M;

New Steps for Adding Datafile to a tablespace

1) Capture Info before and verify

set lines 200
set pages 200
col file_name for a55
select file_name, bytes/1024/1024,autoextensible, increment_by, maxbytes/1024/1024 from dba_data_files where tablespace_name='<tablespace_name>';

2) Identify sequence of datafile

select max(regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', '')) from dba_data_files where tablespace_name='<tablespace_name>';

3) Create command:

select 'alter tablespace ' ||
tablespace_name ||
' add datafile '''||
substr(file_name,1,25) ||
to_char(regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', '') + 1) ||
'.dbf'' size 2048M autoextend on next ' ||
INCREMENT_BY ||
' maxsize ' ||
maxbytes/1024/1024 ||
'M;'
from dba_data_files
where tablespace_name='<tablespace_name>'
and regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', '')=(select
max(regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', ''))
from dba_data_files
where tablespace_name='<tablespace_name>');

Note: We need to replace <tablespace_name> with actual tablespace name in alert in above sqls.

4) Verify and execute the result of above query -- this will add the data file to tablespace

5) Capture Info after and verify

set lines 200
set pages 200
col file_name for a55
select file_name, bytes/1024/1024,autoextensible, increment_by, maxbytes/1024/1024 from dba_data_files where tablespace_name='<tablespace_name>';