Difference between revisions of "Tablespaces"
From dbawiki
(→Report of available free space in tablespaces) |
(→Report of available free space in tablespaces) |
||
| Line 36: | Line 36: | ||
compute sum of free on report | compute sum of free on report | ||
| − | select (select decode(extent_management,'LOCAL','*',' ') || | + | select ( |
| − | + | select decode(extent_management,'LOCAL','*',' ') || | |
| − | + | decode(segment_space_management,'AUTO','a ','m ') | |
| − | + | from dba_tablespaces where tablespace_name = b.tablespace_name | |
| − | + | ) || | |
| − | + | nvl(b.tablespace_name, | |
| − | + | nvl(a.tablespace_name,'UNKOWN')) name, | |
| − | + | kbytes_alloc kbytes, | |
| − | + | kbytes_alloc-nvl(kbytes_free,0) used, | |
| − | + | nvl(kbytes_free,0) free, | |
| − | + | ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used, | |
| − | + | nvl(largest,0) largest, | |
| − | from ( select sum(bytes)/1024 | + | nvl(kbytes_max,kbytes_alloc) Max_Size, |
| − | + | decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used | |
| − | + | from ( | |
| − | + | select sum(bytes)/1024 kbytes_free, | |
| − | + | max(bytes)/1024 largest, | |
| − | + | tablespace_name | |
| − | + | from sys.dba_free_space | |
| − | + | group by tablespace_name | |
| − | + | ) a, | |
| − | + | ( | |
| − | + | select sum(bytes)/1024 kbytes_alloc, | |
| − | + | sum(maxbytes)/1024 kbytes_max, | |
| − | + | tablespace_name | |
| − | + | from sys.dba_data_files | |
| − | + | group by tablespace_name | |
| − | + | union all | |
| − | where a.tablespace_name (+) = b.tablespace_name | + | select sum(bytes)/1024 kbytes_alloc, |
| − | order by &1 | + | sum(maxbytes)/1024 kbytes_max, |
| + | tablespace_name | ||
| + | from sys.dba_temp_files | ||
| + | group by tablespace_name | ||
| + | ) b | ||
| + | where a.tablespace_name (+) = b.tablespace_name | ||
| + | order by &1 | ||
/ | / | ||
</pre> | </pre> | ||
Revision as of 21:21, 1 August 2013
User quotas on all tablespaces
col quota format a10
select username
, tablespace_name
, decode ( max_bytes
, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024)||'M'
) quota
from dba_ts_quotas
where 1=1
and tablespace_name not in ('TEMP');
Report of available free space in tablespaces
Original page
You probably have a problem with the autoextend max_size if column 5 is less than column 8!
set pages 400 lines 132
-------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a25 heading "Tablespace Name"
column Kbytes format 999,999,999,999 heading "KBytes"
column used format 999,999,999,999 heading "Used"
column free format 999,999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 9,999,999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of used on report
compute sum of free on report
select (
select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name
) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from (
select sum(bytes)/1024 kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name
) a,
(
select sum(bytes)/1024 kbytes_alloc,
sum(maxbytes)/1024 kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 kbytes_alloc,
sum(maxbytes)/1024 kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name
) b
where a.tablespace_name (+) = b.tablespace_name
order by &1
/
If you are stuck on software written last century:
set pages 400 lines 132
-------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a19 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select (select decode(extent_management,'LOCAL','*',' ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by &1
/
