Tablespaces
From dbawiki
Contents
- 1 Update all datafiles in the database to be autoextend on and maxsize unlimited
- 2 User quotas on all tablespaces
- 3 Tablespace Growth Report 1
- 4 Tablespace Growth Report 2
- 5 Tablespace Growth Report 3
- 6 Segment Growth Report
- 7 What is the size of the segments in the SYSAUX tablespace?
- 8 What are the components that make up the SYSAUX tablespace?
- 9 Alert tablespace with least space available
- 10 Which sessions are using TEMP tablespace?
- 11 What is the currently assigned default temporary tablespace?
- 12 Reduce (or recreate) size of temporary tablespace
- 13 Recreate the temporary tablespace when it belongs to a tablespace group
- 14 Which tablespaces are encrypted
- 15 Check encryption algorithm
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#;
