Difference between revisions of "Tablespaces"

Revision as of 16:22, 28 January 2015

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');

Tablespace Growth Report 1

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb  from  ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 
ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;

Tablespace Growth Report 2

The period range for this report is dependent on the snapshot retention period set for the db
[Original page]

set linesize 120
column name format a15
column variance format a20
alter session set nls_date_format='yyyy-mm-dd';
with t as (
select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
from
dba_hist_tbspc_space_usage su,
(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot
group by trunc(BEGIN_INTERVAL_TIME) ) ss,
v$tablespace ts,
dba_tablespaces dt
where su.snap_id = ss.snap_id
and   su.tablespace_id = ts.ts#
and   ts.name          =upper('&TABLESPACE_NAME')
and   ts.name          = dt.tablespace_name )
select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb,
case when e.used_size_gb > b.used_size_gb then to_char(e.used_size_gb - b.used_size_gb)
     when e.used_size_gb = b.used_size_gb then '***NO DATA GROWTH'
     when e.used_size_gb < b.used_size_gb then '******DATA PURGED' end variance
from t e, t b
where e.run_time = b.run_time + 1
order by 1;

Segment Growth Report

column "Percent of Total Disk Usage" justify right format 999.99
column "Space Used (MB)" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off

select * from (select to_char(end_interval_time, 'DD-MON-YY') mydate, sum(a.space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
   dba_hist_snapshot sn,
   dba_hist_seg_stat a,
   dba_objects b,
   dba_segments c
where begin_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = '&segment_name'
group by to_char(end_interval_time, 'DD-MON-YY'))
order by to_date(mydate, 'DD-MON-YY')
/

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
/

Reduce size of temporary tablespace

TEMP tablespace autoextended to 32G but no sessions active. Just a waste of space.
Create a 'temporary' temp tablespace

create temporary tablespace temp1 tempfile '/oracle/RMANV12/oradata1/temp1.dbf' size 2G autoextend on maxsize 4G;

Assign this new 'temporary' temp tablespace to the database

alter database default temporary tablespace temp1;

Check for any sessions still holding on to the TEMP tablespace

select vsu.username
,      vsu.session_num
,      vsu.session_addr
,      vs.sid
from   v$sort_usage vsu
,      v$session    vs
where  1=1
and    vsu.session_num = vs.serial#
/

Kill any sessions still holding on to the TEMP tablespace

alter system disconnect session '&sid,&serial' immediate;

Drop the original temp tablespace

drop tablespace temp including contents and datafiles;

Recreate the original temp tablespace (smaller)

create temporary tablespace temp tempfile '/oracle/RMANV12/oradata2/temp.dbf' size 2G autoextend on maxsize 10G;

Assign the new temp tablespace to the database

alter database default temporary tablespace temp;

Drop the 'temporary' temp tablespace

drop tablespace temp1 including contents and datafiles;