Tablespace Queries

set line 120 pages 500
col tablespace_name format a35 trunc
col cur_use_mb for 999999999
col cur_sz_mb for 999999999
col free_space_mb for 999999999
col max_sz_mb for 999999999
compute sum of cur_use_mb on report
compute sum of cur_sz_mb on report
compute sum of free_space_mb on report
compute sum of max_sz_mb on report
break on report
select tablespace_name,
round(sum(total_mb)-sum(free_mb),2) cur_use_mb,
round(sum(total_mb),2) cur_sz_mb,
round((sum(total_mb)-sum(free_mb))/sum(total_mb)*100) cur_prct_full,
round(sum(max_mb) - (sum(total_mb)-sum(free_mb)),2) free_space_mb,
round(sum(max_mb),2) max_sz_mb,
round((sum(total_mb)-sum(free_mb))/sum(max_mb)*100) overall_prct_full
from (select tablespace_name,sum(bytes)/1024/1024 free_mb,0 total_mb,0 max_mb from DBA_FREE_SPACE group by tablespace_name
union select tablespace_name,0 current_mb,sum(bytes)/1024/1024 total_mb,sum(decode(maxbytes, 0, bytes, maxbytes))/1024/1024 max_mb
from DBA_DATA_FILES group by tablespace_name) a group by tablespace_name order by overall_prct_full;
 

set pagesize 100
set linesize 120
col mountpoint format a25
col file_name format a50
break on Mountpoint skip 2 on report
compute sum of TRUE_GB on mountpoint
select
substr(file_name,1,instr(file_name,'/',1,4)) Mountpoint,
file_name,
bytes/1024/1024 MB,
maxbytes/1024/1024 MAXMB,
round(decode(maxbytes,0,bytes,maxbytes)/1024/1024/1024,2) True_GB
from dba_data_files
union
select
substr(file_name,1,instr(file_name,'/',1,4)) Mountpoint,
file_name,
bytes/1024/1024 MB,
maxbytes/1024/1024 MAXMB,
round(decode(maxbytes,0,bytes,maxbytes)/1024/1024/1024,2) True_GB
from dba_temp_files
order by file_name;
set pagesize 100
set linesize 120
col mountpoint format a25
col file_name format a50
break on Mountpoint skip 2 on report
compute sum of TRUE_GB on mountpoint
select
substr(file_name,1,instr(file_name,'/',1,4)) Mountpoint,
file_name,
bytes/1024/1024 MB,
maxbytes/1024/1024 MAXMB,
round(decode(maxbytes,0,bytes,maxbytes)/1024/1024/1024,2) True_GB
from dba_data_files where tablespace_name='PSAPBOS'
union
select
substr(file_name,1,instr(file_name,'/',1,4)) Mountpoint,
file_name,
bytes/1024/1024 MB,
maxbytes/1024/1024 MAXMB,
round(decode(maxbytes,0,bytes,maxbytes)/1024/1024/1024,2) True_GB
from dba_temp_files
order by file_name;

set line 120 pages 500
col tablespace_name format a35 trunc
col cur_use_mb for 999999999
col cur_sz_mb for 999999999
col free_space_mb for 999999999
col max_sz_mb for 999999999
compute sum of cur_use_mb on report
compute sum of cur_sz_mb on report
compute sum of free_space_mb on report
compute sum of max_sz_mb on report
break on report
select tablespace_name,
round(sum(total_mb)-sum(free_mb),2) cur_use_mb,
round(sum(total_mb),2) cur_sz_mb,
round((sum(total_mb)-sum(free_mb))/sum(total_mb)*100) cur_prct_full,
round(sum(max_mb) - (sum(total_mb)-sum(free_mb)),2) free_space_mb,
round(sum(max_mb),2) max_sz_mb,
round((sum(total_mb)-sum(free_mb))/sum(max_mb)*100) overall_prct_full
from (select tablespace_name,sum(bytes)/1024/1024 free_mb,0 total_mb,0 max_mb from DBA_FREE_SPACE group by tablespace_name
union select tablespace_name,0 current_mb,sum(bytes)/1024/1024 total_mb,sum(decode(maxbytes, 0, bytes, maxbytes))/1024/1024 max_mb
from DBA_DATA_FILES group by tablespace_name) a group by tablespace_name order by tablespace_name;



 ====




Find out the "Percentage of FreeSpace" on the tablespaces.

select (BYTES/1024)/1024 "Used Space(MB)",
total  "allocated size(MB)",
maxi "maximum allowable (MB)",
maxi-(BYTES/1024)/1024 "effectivefree(MB)",
--maxi-total "free(MB)",
round(((maxi-(BYTES/1024)/1024)/maxi)*100,2) "% FREE"
from
SM$TS_USED,(select sum((BYTES/1024)/1024)
total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024)  maxi from
dba_data_files where tablespace_name in ('&tbs')) where
tablespace_name in ('&tbs');

List all the datafiles of a particular tablespace

col file_name for a60
set lines 170
set pages 200  
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&tablespace_name' order by file_name ;

The free space left for auto extensible files to extend.

col file_name for a40
set lines 170 pages 0  
BREAK on REPORT
compute SUM of space_left_to_extend on report
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible,(maxbytes/1024/1024)-(bytes/1024/1024) space_left_to_extend from dba_data_files 
where file_name like '%/idwl/index6/%' and autoextensible='YES' order by file_name;

List the complete list of tablespaces details at the database level 

set lines 150
set pages 5000
column dummy noprint
column  pct_used format 999.9       heading "% USED"
column  name    format a30      heading "Tablespace Name"
column  Mbytes   format 999,999,999,999    heading "TOTAL_ALLOCATED_IN_MB"
column  used    format 999,999,999,999   heading "USED_IN_MB"
column  free    format 999,999,999,999  heading "FREE_IN_MB"
column  largest    format 999,999,999,999  heading "LARGEST"
clear breaks
clear computes

select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
Mbytes_alloc Mbytes,
Mbytes_alloc-nvl(Mbytes_free,0) used,
nvl(Mbytes_free,0) free,
((Mbytes_alloc-nvl(Mbytes_free,0))/
Mbytes_alloc)*100 pct_used,
nvl(largest,0) largest
from ( select sum(bytes)/1024/1024 Mbytes_free,
max(bytes)/1024/1024 largest,
tablespace_name
from  sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024/1024 Mbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name order by 1;

Adding a databafile to a tablespace with MAXSIZE 

Syntax:
ALTER TABLESPACE users
    ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
      AUTOEXTEND ON
      NEXT 512K
      MAXSIZE 250M;

ALTER TABLESPACE NEW_PRODUCT_MEASUREMENT ADD DATAFILE '/oracle/TJDERBY/oradata7/NEW_PRODUCT_MEASUREMENT97.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 16000;