Difference between revisions of "Tablespaces"

From dbawiki
Jump to: navigation, search
(→‎Segment Growth Report 3)
 
(56 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
* [http://oracle-base.com/articles/10g/space-object-transaction-management-10g.php Space management for 10g (oracle-base.com)]
 +
===Update all datafiles in the database to be autoextend on and maxsize unlimited===
 +
A quick fix to ensure all tablespaces can be extended to the max. Have to keep an eye on the filesystem growth now.
 +
<pre>
 +
set lines 1000 pages 0 newpa none feedb off trims on echo off termo off
 +
spool fix_ts.sql
 +
select 'alter database datafile '||''''||file_name||''''||' autoextend on maxsize unlimited;' from dba_data_files;
 +
spool off
 +
@fix_ts
 +
</pre>
 +
 
===User quotas on all tablespaces===
 
===User quotas on all tablespaces===
 
  col quota format a10
 
  col quota format a10
Line 11: Line 22:
 
  and    tablespace_name not in ('TEMP');
 
  and    tablespace_name not in ('TEMP');
  
−
===Space available in tablespaces===
+
===Tablespace Growth Report 1===
−
[http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551402703363001567 Original page]
 
 
<pre>
 
<pre>
−
set linesize 121
+
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;
 +
</pre>
 +
 
 +
===Tablespace Growth Report 2===
 +
The period range for this report is dependent on the snapshot retention period set for the db<br />
 +
[[http://www.toadworld.com/platforms/oracle/w/wiki/10837.tablespace-growth-report.aspx Original page]]
 +
<pre>
 +
set linesize 120
 +
column name format a15
 +
column variance format 9G990D00
 +
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,(e.used_size_gb - b.used_size_gb) variance
 +
from t e, t b
 +
where e.run_time = b.run_time + 1
 +
order by 1;
 +
</pre>
 +
===Tablespace Growth Report 3===
 +
[http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551402703363001567 Original page]<br />
 +
You probably have a problem with the autoextend max_size if column 5 is less than column 8!
 +
<pre>
 +
set pages 400 lines 132
 
-------------------------------------------------------
 
-------------------------------------------------------
 
-- free.sql   
 
-- free.sql   
Line 21: Line 74:
 
--------------------------------------------------------
 
--------------------------------------------------------
  
−
column dummy noprint
+
column dummy noprint
−
column  pct_used format 999.9       heading "%|Used"  
+
column  pct_used     format 999.9             heading "%|Used"  
−
column  name   format a19      heading "Tablespace Name"  
+
column  name         format a25              heading "Tablespace Name"  
−
column  Kbytes   format 999,999,999   heading "KBytes"  
+
column  Kbytes       format 999,999,999,999  heading "KBytes"  
−
column  used   format 999,999,999  heading "Used"  
+
column  used         format 999,999,999,999  heading "Used"  
−
column  free   format 999,999,999 heading "Free"  
+
column  free         format 999,999,999,999  heading "Free"  
−
column  largest   format 999,999,999 heading "Largest"  
+
column  largest     format 999,999,999       heading "Largest"  
−
column  max_size format 999,999,999 heading "MaxPoss|Kbytes"
+
column  max_size     format 9,999,999,999,999 heading "MaxPoss|Kbytes"
−
column  pct_max_used format 999.9       heading "%|Max|Used"  
+
column  pct_max_used format 999.9             heading "%|Max|Used"  
 
break  on report  
 
break  on report  
 
compute sum of kbytes on report  
 
compute sum of kbytes on report  
−
compute sum of free on report  
+
compute sum of used  on report  
−
compute sum of used on report  
+
compute sum of free  on report  
  
−
select (select decode(extent_management,'LOCAL','*',' ') ||  
+
select (
−
              decode(segment_space_management,'AUTO','a ','m ')
+
      select decode(extent_management,'LOCAL','*',' ') ||
−
      from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,  
+
              decode(segment_space_management,'AUTO','a ','m ')
−
nvl(a.tablespace_name,'UNKOWN')) name,
+
      from   dba_tablespaces where tablespace_name = b.tablespace_name
−
  kbytes_alloc kbytes,  
+
      )   ||
−
  kbytes_alloc-nvl(kbytes_free,0) used,
+
      nvl(b.tablespace_name
−
  nvl(kbytes_free,0) free,  
+
,     nvl(a.tablespace_name,'UNKOWN')) name
−
  ((kbytes_alloc-nvl(kbytes_free,0))/  
+
,     kbytes_alloc kbytes
−
  kbytes_alloc)*100 pct_used,
+
,     kbytes_alloc-nvl(kbytes_free,0) used
−
  nvl(largest,0) largest,
+
,     nvl(kbytes_free,0) free
−
  nvl(kbytes_max,kbytes_alloc) Max_Size,
+
,     ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used
−
  decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
+
,     nvl(largest,0) largest
−
from ( select sum(bytes)/1024 Kbytes_free,  
+
,     nvl(kbytes_max,kbytes_alloc) Max_Size
−
  max(bytes)/1024 largest,
+
,     decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
−
  tablespace_name
+
from   (
−
  from sys.dba_free_space  
+
      select sum(bytes)/1024     kbytes_free
−
  group by tablespace_name ) a,
+
      ,     max(bytes)/1024     largest
−
    ( select sum(bytes)/1024 Kbytes_alloc,  
+
      ,     tablespace_name
−
  sum(maxbytes)/1024 Kbytes_max,
+
      from   sys.dba_free_space
−
  tablespace_name  
+
      group by tablespace_name
−
  from sys.dba_data_files  
+
      ) a
−
  group by tablespace_name  
+
,
−
  union all
+
      (
−
      select sum(bytes)/1024 Kbytes_alloc,  
+
      select sum(bytes)/1024     kbytes_alloc
−
  sum(maxbytes)/1024 Kbytes_max,
+
      ,     sum(maxbytes)/1024   kbytes_max
−
  tablespace_name  
+
      ,     tablespace_name
−
  from sys.dba_temp_files  
+
      from   sys.dba_data_files
−
  group by tablespace_name )b
+
      group by tablespace_name
−
where a.tablespace_name (+) = b.tablespace_name
+
      union all
−
order by &1
+
      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
 
/
 
/
−
 
+
</pre>
−
 
 
  
 
If you are stuck on software written last century:
 
If you are stuck on software written last century:
  
−
--------------------------------------------------------
+
<pre>
−
-- free.sql
+
set pages 400 lines 132
 +
-------------------------------------------------------
 +
-- free.sql
 
--
 
--
 
-- This SQL Plus script lists freespace by tablespace
 
-- This SQL Plus script lists freespace by tablespace
 
--------------------------------------------------------
 
--------------------------------------------------------
−
                                                                                                       
+
 
−
column dummy noprint
+
column dummy noprint
−
column  pct_used format 999.9      heading "%|Used"
+
column  pct_used     format 999.9      heading "%|Used"  
−
column  name   format a19     heading "Tablespace Name"
+
column  name         format a19         heading "Tablespace Name"  
−
column  Kbytes   format 999,999,999   heading "KBytes"
+
column  Kbytes       format 999,999,999 heading "KBytes"  
−
column  used   format 999,999,999   heading "Used"
+
column  used         format 999,999,999 heading "Used"  
−
column  free   format 999,999,999 heading "Free"
+
column  free         format 999,999,999 heading "Free"  
−
column  largest   format 999,999,999 heading "Largest"
+
column  largest     format 999,999,999 heading "Largest"  
−
column  max_size format 999,999,999 heading "MaxPoss|Kbytes"
+
column  max_size     format 999,999,999 heading "MaxPoss|Kbytes"
−
column  pct_max_used format 999.9      heading "%|Max|Used"
+
column  pct_max_used format 999.9      heading "%|Max|Used"  
−
break  on report
+
break  on report  
−
compute sum of kbytes on report
+
compute sum of kbytes on report  
−
compute sum of free on report
+
compute sum of free   on report  
−
compute sum of used on report
+
compute sum of used   on report  
 
                                                                                                          
 
                                                                                                          
 
select (select decode(extent_management,'LOCAL','*',' ')
 
select (select decode(extent_management,'LOCAL','*',' ')
Line 122: Line 183:
 
order by &1
 
order by &1
 
/
 
/
 +
</pre>
 +
 +
===Segment Growth Report===
 +
<pre>
 +
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')
 +
/
 +
</pre>
 +
 +
===What is the size of the segments in the SYSAUX tablespace?===
 +
<pre>
 +
set lines 2000
 +
col owner for a30
 +
col segment_name for a40
 +
select owner
 +
,      segment_name
 +
,      (bytes/1024/1024) mb
 +
from  dba_segments
 +
where  tablespace_name = 'SYSAUX'
 +
order  by bytes;
 +
</pre>
 +
===What are the components that make up the SYSAUX tablespace?===
 +
<pre>
 +
set lines 2000
 +
col occupant_name  for a30 heading "Item"
 +
col schema_name    for a20 heading "Schema"
 +
col move_procedure for a50 heading "Move Procedure"
 +
col used                  heading "Space Used (GB)"
 +
select occupant_name
 +
,      round(space_usage_kbytes/1048576,3) used
 +
,      schema_name
 +
,      move_procedure
 +
from  v$sysaux_occupants
 +
order  by 2
 +
/
 +
</pre>
 +
 +
===Alert tablespace with least space available===
 +
<pre>
 +
select t1.naam                                                                  naam
 +
,      round (t1.df_grootte/1024/1024, 0)                                        df_grootte_m
 +
,      round (t2.vrij/1024/1024, 0)                                              vrij_m
 +
,      round (t1.ruimte/1024/1024, 0)                                            ruimte_m
 +
,      round ( (t1.df_grootte - t2.vrij) / (t1.df_grootte + t1.ruimte) * 100, 0) vullingsgraad
 +
,      (
 +
      case when round ( (df_grootte - vrij) / (df_grootte + ruimte) * 100, 2) > 95 then '0'
 +
            when round ( (df_grootte - vrij) / (df_grootte + ruimte) * 100, 2) > 85 then '1'
 +
                                                                                    else '2'
 +
      end
 +
      ) alert_lvl
 +
from  (
 +
      select tablespace_name                        naam
 +
      ,      sum (greatest ( (maxbytes - bytes), 0)) ruimte
 +
      ,      sum (bytes)                            df_grootte
 +
      ,      sum (maxbytes)                          max
 +
      from  dba_data_files df
 +
      group  by df.tablespace_name
 +
      ) t1
 +
,      (
 +
      select tablespace_name naam
 +
      ,      sum (bytes)    vrij
 +
      from  dba_free_space
 +
      group  by tablespace_name
 +
      ) t2
 +
,      (
 +
      select tablespace_name naam
 +
      ,      contents        contents
 +
      from  dba_tablespaces
 +
      where  contents not in ('TEMPORARY', 'UNDO')
 +
      ) t3
 +
where  t1.naam = t2.naam
 +
and    t1.naam = t3.naam
 +
order  by vullingsgraad desc
 +
/
 +
</pre>
 +
 +
===Which sessions are using TEMP tablespace?===
 +
<pre>
 +
set lines 1000
 +
col killer    for a15
 +
col status    for a8
 +
col username  for a15
 +
col osuser    for a15
 +
col spid      for a10
 +
col mb_used    for 99999
 +
col module for a20
 +
col program    for a25
 +
col tablespace for a10
 +
col statements for 999
 +
select s.sid||','||s.serial#                  killer
 +
,      s.status                              status
 +
,      s.username                            username
 +
,      s.osuser                              osuser
 +
,      p.spid                                spid
 +
,      p.pid                                  pid
 +
,      s.module                              module
 +
,      p.program                              program
 +
,      sum(t.blocks)*tbs.block_size/1024/1024 mb_used
 +
,      t.tablespace                          tablespace
 +
,      count(*)                              statements
 +
from  v$sort_usage    t
 +
,      v$session      s
 +
,      dba_tablespaces tbs
 +
,      v$process      p
 +
where  1=1
 +
and    t.session_addr = s.saddr
 +
and    s.paddr        = p.addr
 +
and    t.tablespace  = tbs.tablespace_name
 +
group  by s.sid
 +
,      s.status
 +
,      s.serial#
 +
,      s.username
 +
,      s.osuser
 +
,      p.spid
 +
,      p.pid
 +
,      s.module
 +
,      p.program
 +
,      tbs.block_size
 +
,      t.tablespace
 +
order  by killer
 +
/
 +
</pre>
 +
 +
===What is the currently assigned default temporary tablespace?===
 +
See DEFAULT_TEMP_TABLESPACE to see which TEMP table space is defaulted at database level
 +
<pre>
 +
set lines 200 pages 100 newpa none
 +
col description for a60 noprint
 +
col property_name  for a40
 +
col property_value for a120
 +
select *
 +
from database_properties
 +
/
 +
</pre>
 +
 +
===Reduce (or recreate) size of temporary tablespace===
 +
Sometimes the TEMP tablespace grows too big - out of all proportion to the size of the database.<br />
 +
Create a ''temporary'' temp tablespace
 +
<pre>
 +
create temporary tablespace tempx tempfile '/oracle/RMANV12/oradata1/temp1.dbf' size 1g autoextend on next 1g maxsize unlimited;
 +
</pre>
 +
Assign this new ''temporary'' temp tablespace to the database
 +
<pre>
 +
alter database default temporary tablespace tempx;
 +
</pre>
 +
Check for any sessions still holding on to the TEMP tablespace
 +
<pre>
 +
set lines 1000 pages 0 newpa none
 +
col stmt for a300
 +
select 'alter system disconnect session '''||s.killer||''' immediate;' stmt
 +
from
 +
(
 +
select s.sid||','||s.serial#                  killer
 +
,      s.status                              status
 +
from  v$sort_usage    t
 +
,      v$session      s
 +
,      dba_tablespaces tbs
 +
,      v$process      p
 +
where  1=1
 +
and    t.session_addr = s.saddr
 +
and    s.paddr        = p.addr
 +
and    t.tablespace  = tbs.tablespace_name
 +
) s
 +
where  s.status = 'INACTIVE'
 +
/
 +
</pre>
 +
Drop the original temp tablespace
 +
<pre>
 +
drop tablespace temp including contents and datafiles;
 +
</pre>
 +
Recreate the original temp tablespace (smaller, or with fewer datafiles)
 +
<pre>
 +
create temporary tablespace temp tempfile '/oracle/RMANV12/oradata2/temp01.dbf' size 2g autoextend on next 1g maxsize unlimited
 +
                                ,        '/oracle/RMANV12/oradata2/temp02.dbf' size 2g autoextend on next 1g maxsize unlimited
 +
                                ,        '/oracle/RMANV12/oradata2/temp03.dbf' size 2g autoextend on next 1g maxsize unlimited
 +
                                ,        '/oracle/RMANV12/oradata2/temp04.dbf' size 2g autoextend on next 1g maxsize unlimited;
 +
</pre>
 +
Assign the new temp tablespace to the database
 +
<pre>
 +
alter database default temporary tablespace temp;
 +
</pre>
 +
Drop the ''temporary'' temp tablespace
 +
<pre>
 +
drop tablespace tempx including contents and datafiles;
 +
</pre>
 +
 +
===Recreate the temporary tablespace when it belongs to a tablespace group===
 +
Verify the tablespace groups and their members
 +
<pre>
 +
select * from dba_tablespace_groups;
 +
 +
GROUP_NAME                    TABLESPACE_NAME
 +
------------------------------ ------------------------------
 +
TEMP                          TEMP1
 +
TEMP                          TEMP2
 +
</pre>
 +
Check the filenames for these tablespaces
 +
<pre>
 +
select file_name, tablespace_name from dba_temp_files order by 2,1;
 +
 +
FILE_NAME                                  TABLESPACE_NAME
 +
------------------------------------------- -------------------
 +
/CLN/prg/ora_ebs/db/apps_st/data/temp01.dbf TEMP1
 +
/CLN/prg/ora_ebs/db/apps_st/data/temp12.dbf TEMP1
 +
/CLN/prg/ora_ebs/db/apps_st/data/temp02.dbf TEMP2
 +
/CLN/prg/ora_ebs/db/apps_st/data/temp22.dbf TEMP2
 +
</pre>
 +
Create new tablespaces and assign them to a new group
 +
<pre>
 +
create temporary tablespace temp_g1 tempfile '/CLN/prg/ora_ebs/db/apps_st/data/temp_g01.dbf' size 1g autoextend on next 1g maxsize 10g tablespace group temp_g;
 +
create temporary tablespace temp_g2 tempfile '/CLN/prg/ora_ebs/db/apps_st/data/temp_g02.dbf' size 1g autoextend on next 1g maxsize 10g tablespace group temp_g;
 +
</pre>
 +
Recheck the groups
 +
<pre>
 +
select * from dba_tablespace_groups;
 +
 +
GROUP_NAME                    TABLESPACE_NAME
 +
------------------------------ ------------------------------
 +
TEMP                          TEMP1
 +
TEMP                          TEMP2
 +
TEMP_G                        TEMP_G1
 +
TEMP_G                        TEMP_G2
 +
</pre>
 +
Make the new tablespace group the default at database level
 +
<pre>
 +
alter database default temporary tablespace temp_g;
 +
</pre>
 +
Check for any sessions still using the old default temporary tablespace (group)
 +
<pre>
 +
SET PAGESIZE 50
 +
SET LINESIZE 300
 +
COLUMN tablespace FORMAT A20
 +
COLUMN temp_size FORMAT A20
 +
COLUMN sid_serial FORMAT A20
 +
COLUMN username FORMAT A20
 +
COLUMN program FORMAT A50
 +
 +
SELECT b.tablespace,
 +
      ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
 +
      a.inst_id as Instance,
 +
      a.sid||','||a.serial# AS sid_serial,
 +
      NVL(a.username, '(oracle)') AS username,
 +
      a.program,
 +
      a.status,
 +
      a.sql_id
 +
FROM  gv$session a,
 +
      gv$sort_usage b,
 +
      gv$parameter p
 +
WHERE  p.name  = 'db_block_size'
 +
AND    a.saddr = b.session_addr
 +
AND    a.inst_id=b.inst_id
 +
AND    a.inst_id=p.inst_id
 +
ORDER BY b.tablespace, b.blocks
 +
/
 +
 +
TABLESPACE          TEMP_SIZE              INSTANCE SID_SERIAL          USERNAME            PROGRAM                                            STATUS  SQL_ID
 +
-------------------- -------------------- ---------- -------------------- -------------------- -------------------------------------------------- -------- -------------
 +
TEMP2                1M                            1 467,15748            DBSNMP              JDBC Thin Client                                  INACTIVE
 +
</pre>
 +
Kill any sessions that may be killed
 +
<pre>
 +
alter system disconnect session '467,15748' immediate;
 +
</pre>
 +
Remove the old tablespaces from the tablespace group and reassign the new tablespaces to it
 +
<pre>
 +
alter tablespace temp1 tablespace group '';
 +
alter tablespace temp2 tablespace group '';
 +
alter tablespace temp_g1 tablespace group temp;
 +
alter database default temporary tablespace temp;
 +
alter tablespace temp_g2 tablespace group temp;
 +
</pre>
 +
Drop the old tablespaces
 +
<pre>
 +
drop tablespace temp1 including contents and datafiles;
 +
drop tablespace temp2 including contents and datafiles;
 +
</pre>
 +
Check the final situation
 +
<pre>
 +
select * from dba_tablespace_groups;
 +
 +
GROUP_NAME                    TABLESPACE_NAME
 +
------------------------------ ------------------------------
 +
TEMP                          TEMP_G1
 +
TEMP                          TEMP_G2
 +
</pre>
 +
 +
===Which tablespaces are encrypted===
 +
Encrypt tablespaces to protect data (after having created a wallet)
 +
<pre>
 +
select tablespace_name, encrypted from dba_tablespaces;
 +
</pre>
 +
===Check encryption algorithm===
 +
<pre>
 +
select t.name
 +
,      e.encryptionalg
 +
,      e.encryptedts
 +
from  v$tablespace t
 +
,      v$encrypted_tablespaces e
 +
where  t.ts# = e.ts#;
 
</pre>
 
</pre>

Latest revision as of 12:26, 23 October 2018

Update all datafiles in the database to be autoextend on and maxsize unlimited[edit]

A quick fix to ensure all tablespaces can be extended to the max. Have to keep an eye on the filesystem growth now. 

set lines 1000 pages 0 newpa none feedb off trims on echo off termo off
spool fix_ts.sql
select 'alter database datafile '||''''||file_name||''''||' autoextend on maxsize unlimited;' from dba_data_files;
spool off
@fix_ts

User quotas on all tablespaces[edit]

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[edit]

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[edit]

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 9G990D00
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,(e.used_size_gb - b.used_size_gb) variance
from t e, t b
where e.run_time = b.run_time + 1
order by 1;

Tablespace Growth Report 3[edit]

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
/

Segment Growth Report[edit]

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

What is the size of the segments in the SYSAUX tablespace?[edit]

set lines 2000
col owner for a30
col segment_name for a40
select owner
,      segment_name
,      (bytes/1024/1024) mb
from   dba_segments
where  tablespace_name = 'SYSAUX'
order  by bytes;

What are the components that make up the SYSAUX tablespace?[edit]

set lines 2000
col occupant_name  for a30 heading "Item"
col schema_name    for a20 heading "Schema"
col move_procedure for a50 heading "Move Procedure"
col used                   heading "Space Used (GB)"
select occupant_name
,      round(space_usage_kbytes/1048576,3) used
,      schema_name
,      move_procedure
from   v$sysaux_occupants
order  by 2
/

Alert tablespace with least space available[edit]

select t1.naam                                                                   naam
,      round (t1.df_grootte/1024/1024, 0)                                        df_grootte_m
,      round (t2.vrij/1024/1024, 0)                                              vrij_m
,      round (t1.ruimte/1024/1024, 0)                                            ruimte_m
,      round ( (t1.df_grootte - t2.vrij) / (t1.df_grootte + t1.ruimte) * 100, 0) vullingsgraad
,      (
       case when round ( (df_grootte - vrij) / (df_grootte + ruimte) * 100, 2) > 95 then '0'
            when round ( (df_grootte - vrij) / (df_grootte + ruimte) * 100, 2) > 85 then '1'
                                                                                    else '2'
       end
       ) alert_lvl
from   (
       select tablespace_name                         naam
       ,      sum (greatest ( (maxbytes - bytes), 0)) ruimte
       ,      sum (bytes)                             df_grootte
       ,      sum (maxbytes)                          max
       from   dba_data_files df
       group  by df.tablespace_name
       ) t1
,      (
       select tablespace_name naam
       ,      sum (bytes)     vrij
       from   dba_free_space
       group  by tablespace_name
       ) t2
,      (
       select tablespace_name naam
       ,      contents        contents
       from   dba_tablespaces
       where  contents not in ('TEMPORARY', 'UNDO')
       ) t3
where  t1.naam = t2.naam
and    t1.naam = t3.naam
order  by vullingsgraad desc
/

Which sessions are using TEMP tablespace?[edit]

set lines 1000
col killer     for a15
col status     for a8
col username   for a15
col osuser     for a15
col spid       for a10
col mb_used    for 99999
col module for a20
col program    for a25
col tablespace for a10
col statements for 999
select s.sid||','||s.serial#                  killer
,      s.status                               status
,      s.username                             username
,      s.osuser                               osuser
,      p.spid                                 spid
,      p.pid                                  pid
,      s.module                               module
,      p.program                              program
,      sum(t.blocks)*tbs.block_size/1024/1024 mb_used
,      t.tablespace                           tablespace
,      count(*)                               statements
from   v$sort_usage    t
,      v$session       s
,      dba_tablespaces tbs
,      v$process       p
where  1=1
and    t.session_addr = s.saddr
and    s.paddr        = p.addr
and    t.tablespace   = tbs.tablespace_name
group  by s.sid
,      s.status
,      s.serial#
,      s.username
,      s.osuser
,      p.spid
,      p.pid
,      s.module
,      p.program
,      tbs.block_size
,      t.tablespace
order  by killer
/

What is the currently assigned default temporary tablespace?[edit]

See DEFAULT_TEMP_TABLESPACE to see which TEMP table space is defaulted at database level 

set lines 200 pages 100 newpa none
col description for a60 noprint
col property_name  for a40
col property_value for a120
select *
from database_properties
/

Reduce (or recreate) size of temporary tablespace[edit]

Sometimes the TEMP tablespace grows too big - out of all proportion to the size of the database.
Create a temporary temp tablespace 

create temporary tablespace tempx tempfile '/oracle/RMANV12/oradata1/temp1.dbf' size 1g autoextend on next 1g maxsize unlimited;

Assign this new temporary temp tablespace to the database 

alter database default temporary tablespace tempx;

Check for any sessions still holding on to the TEMP tablespace 

set lines 1000 pages 0 newpa none
col stmt for a300
select 'alter system disconnect session '''||s.killer||''' immediate;' stmt
from
(
select s.sid||','||s.serial#                  killer
,      s.status                               status
from   v$sort_usage    t
,      v$session       s
,      dba_tablespaces tbs
,      v$process       p
where  1=1
and    t.session_addr = s.saddr
and    s.paddr        = p.addr
and    t.tablespace   = tbs.tablespace_name
) s
where  s.status = 'INACTIVE'
/

Drop the original temp tablespace 

drop tablespace temp including contents and datafiles;

Recreate the original temp tablespace (smaller, or with fewer datafiles) 

create temporary tablespace temp tempfile '/oracle/RMANV12/oradata2/temp01.dbf' size 2g autoextend on next 1g maxsize unlimited
                                 ,        '/oracle/RMANV12/oradata2/temp02.dbf' size 2g autoextend on next 1g maxsize unlimited
                                 ,        '/oracle/RMANV12/oradata2/temp03.dbf' size 2g autoextend on next 1g maxsize unlimited
                                 ,        '/oracle/RMANV12/oradata2/temp04.dbf' size 2g autoextend on next 1g maxsize unlimited;

Assign the new temp tablespace to the database 

alter database default temporary tablespace temp;

Drop the temporary temp tablespace 

drop tablespace tempx including contents and datafiles;

Recreate the temporary tablespace when it belongs to a tablespace group[edit]

Verify the tablespace groups and their members 

select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP                           TEMP1
TEMP                           TEMP2

Check the filenames for these tablespaces 

select file_name, tablespace_name from dba_temp_files order by 2,1;

FILE_NAME                                   TABLESPACE_NAME
------------------------------------------- -------------------
/CLN/prg/ora_ebs/db/apps_st/data/temp01.dbf TEMP1
/CLN/prg/ora_ebs/db/apps_st/data/temp12.dbf TEMP1
/CLN/prg/ora_ebs/db/apps_st/data/temp02.dbf TEMP2
/CLN/prg/ora_ebs/db/apps_st/data/temp22.dbf TEMP2

Create new tablespaces and assign them to a new group 

create temporary tablespace temp_g1 tempfile '/CLN/prg/ora_ebs/db/apps_st/data/temp_g01.dbf' size 1g autoextend on next 1g maxsize 10g tablespace group temp_g;
create temporary tablespace temp_g2 tempfile '/CLN/prg/ora_ebs/db/apps_st/data/temp_g02.dbf' size 1g autoextend on next 1g maxsize 10g tablespace group temp_g;

Recheck the groups 

select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP                           TEMP1
TEMP                           TEMP2
TEMP_G                         TEMP_G1
TEMP_G                         TEMP_G2

Make the new tablespace group the default at database level 

alter database default temporary tablespace temp_g;

Check for any sessions still using the old default temporary tablespace (group) 

SET PAGESIZE 50
SET LINESIZE 300
COLUMN tablespace FORMAT A20
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A50

SELECT b.tablespace,
       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
       a.inst_id as Instance,
       a.sid||','||a.serial# AS sid_serial,
       NVL(a.username, '(oracle)') AS username,
       a.program,
       a.status,
       a.sql_id
FROM   gv$session a,
       gv$sort_usage b,
       gv$parameter p
WHERE  p.name  = 'db_block_size'
AND    a.saddr = b.session_addr
AND    a.inst_id=b.inst_id
AND    a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks
/

TABLESPACE           TEMP_SIZE              INSTANCE SID_SERIAL           USERNAME             PROGRAM                                            STATUS   SQL_ID
-------------------- -------------------- ---------- -------------------- -------------------- -------------------------------------------------- -------- -------------
TEMP2                1M                            1 467,15748            DBSNMP               JDBC Thin Client                                   INACTIVE

Kill any sessions that may be killed 

alter system disconnect session '467,15748' immediate;

Remove the old tablespaces from the tablespace group and reassign the new tablespaces to it 

alter tablespace temp1 tablespace group '';
alter tablespace temp2 tablespace group '';
alter tablespace temp_g1 tablespace group temp;
alter database default temporary tablespace temp;
alter tablespace temp_g2 tablespace group temp;

Drop the old tablespaces 

drop tablespace temp1 including contents and datafiles;
drop tablespace temp2 including contents and datafiles;

Check the final situation 

select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP                           TEMP_G1
TEMP                           TEMP_G2

Which tablespaces are encrypted[edit]

Encrypt tablespaces to protect data (after having created a wallet) 

select tablespace_name, encrypted from dba_tablespaces;

Check encryption algorithm[edit]

select t.name
,      e.encryptionalg
,      e.encryptedts
from   v$tablespace t
,      v$encrypted_tablespaces e
where  t.ts# = e.ts#;
Retrieved from "https://mailnest.com/dbawiki/index.php?title=Tablespaces&oldid=3327"