Tablespaces
From dbawiki
Contents
- 1 User quotas on all tablespaces
- 2 Tablespace Growth Report 1
- 3 Tablespace Growth Report 2
- 4 What is the size of the segments in the SYSAUX tablespace?
- 5 What are the components that make up the SYSAUX tablespace?
- 6 Segment Growth Report
- 7 Report of available free space in tablespaces
- 8 Reduce (or recreate) size of temporary tablespace
- 9 Which tablespaces are encrypted
- 10 Check encryption algorithm
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 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;
What is the size of the segments in the SYSAUX tablespace?
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?
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 /
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 (or recreate) size of temporary tablespace
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 maxsize 30g;
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
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, or with fewer datafiles)
create temporary tablespace temp tempfile '/oracle/RMANV12/oradata2/temp01.dbf' size 2g autoextend on maxsize 30g
, '/oracle/RMANV12/oradata2/temp02.dbf' size 2g autoextend on maxsize 30g
, '/oracle/RMANV12/oradata2/temp03.dbf' size 2g autoextend on maxsize 30g
, '/oracle/RMANV12/oradata2/temp04.dbf' size 2g autoextend on maxsize 30g;
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;
Which tablespaces are encrypted
Encrypt tablespaces to protect data (after having created a wallet)
select tablespace_name, encrypted from dba_tablespaces;
Check encryption algorithm
select t.name , e.encryptionalg , e.encryptedts from v$tablespace t , v$encrypted_tablespaces e where t.ts# = e.ts#;
