set lines 1000 pages 100 col object_name for a30 col warning_value for a30 col critical_value for a30 select metrics_name , object_name , warning_operator , warning_value , critical_operator , critical_value from dba_thresholds /
METRICS_NAME OBJECT_NAME WARNING_OPER WARNING_VALUE CRITICAL_OPE CRITICAL_VALUE ---------------------------------------------------------------- ------------------------------ ------------ ------------------------------ ------------ ------------------------------ Tablespace Space Usage TEMP DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0 Tablespace Space Usage UNDOTBS1 DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0 Tablespace Space Usage GE 85 GE 97
begin
dbms_server_alert.set_threshold ( dbms_server_alert.tablespace_pct_full
, null
, null
, null
, null
, 1
, 1
, null
, dbms_server_alert.object_type_tablespace
, null
);
end;
/
begin
dbms_server_alert.set_threshold ( metrics_id => dbms_server_alert.tablespace_pct_full
, warning_operator => dbms_server_alert.operator_ge
, warning_value => '55'
, critical_operator => dbms_server_alert.operator_ge
, critical_value => '70'
, observation_period => 1
, instance_name => 'upg'
, object_type => dbms_server_alert.object_type_tablespace
, object_name => 'USERS'
,
);
end;
/
select df.tablespace_name
, trunc(fs.bytes/1024/1024/1024) gigsfree
, trunc(df.bytes/1024/1024/1024) gigsused
, trunc( ( (fs.bytes/1024/1024/1024) / (df.bytes/1024/1024/1024) *100), 3 ) freepct
from dba_free_space fs
, dba_data_files df
where fs.tablespace_name = df.tablespace_name
and df.tablespace_name = nvl(upper('&tablespace_name'),df.tablespace_name)
/
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
For temporary tablespaces, it is a non-issue. Just shrink them. It will do what you expect it to do.
ls -l /cln/prg/ora_data2/clnp/temp_system -rw-r----- 1 oracle oinstall 34358697984 Aug 24 12:12 temp_system01.dbf -rw-r----- 1 oracle oinstall 5368717312 Aug 24 11:45 temp_system02.dbf
alter tablespace temp_system shrink space;
ls -l /cln/prg/ora_data2/clnp/temp_system -rw-r----- 1 oracle oinstall 2097152 Aug 24 12:20 temp_system01.dbf -rw-r----- 1 oracle oinstall 2097152 Aug 24 12:20 temp_system02.dbf
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');
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;
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;
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
/
select * from dba_hist_tbspc_space_usage order by to_date(substr(rtime,1,10),'MM/DD/YYYY')
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') /
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;
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 /
select vss.tablespace_name tablespace
, tot.mb_total mb_total
, sum (vss.used_blocks * tot.block_size) / 1024 / 1024 mb_used
, tot.mb_total - sum (vss.used_blocks * tot.block_size) / 1024 / 1024 mb_free
from v$sort_segment vss
, (
select vt.name
, vtmp.block_size
, sum (vtmp.bytes) / 1024 / 1024 mb_total
from v$tablespace vt
, v$tempfile vtmp
where vt.ts# = vtmp.ts#
group by vt.name
, vtmp.block_size
) tot
where vss.tablespace_name = tot.name
group by vss.tablespace_name
, tot.mb_total
/
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
/
Maybe a bug if there are no active sessions on the tablespace being dropped. See Metalink Note 438461.1
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
execute dbms_stats.gather_schema_stats('SYS');
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;
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 /
or
set lines 1000 pages 100 col hash_value for a40 col tablespace for a10 col username for a15 select s.sid , s.username , u.tablespace , s.sql_hash_value||'/'||u.sqlhash hash_value , u.segtype , u.blocks from v$session s , v$tempseg_usage u where s.saddr = u.session_addr order by u.blocks /
…and what SQL statement is running there?
col stmt for a70 wrap
col piece noprint
select sql.piece
, replace(sql.sql_text,chr(10),'') stmt
from v$session ses
, v$sqltext_with_newlines sql
where ses.status = 'ACTIVE'
and ses.username is not null
and ses.sql_address = sql.address
and ses.sql_hash_value = sql.hash_value
and ses.audsid != userenv('SESSIONID')
and ses.sid = &sid
order by sql.piece
/
col killer for a60 col osuser for a15 set lines 1000 pages 100 select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' killer , to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') logon_time , s.type , sci.osuser , s.status , su.tablespace from gv$session s , gv$session_connect_info sci , gv$process p , gv$sort_usage su where 1=1 and p.addr = s.paddr and p.inst_id = s.inst_id and s.sid = su.session_num and s.sid = sci.sid and s.serial# = sci.serial# order by s.logon_time /
set lines 1000 pages 100 col killer for a60 col inst_id nopri col logon for a21 col osuser for a10 col spid for a10 col username for a15 col program for a25 col tablespace for a10 col temp_size for a20 select 'alter system disconnect session '''||a.sid||','||a.serial#||''' immediate;' killer , round(((b.blocks*p.value)/1024/1024),2)||'M' temp_size , a.inst_id instance , nvl(a.username, '(oracle)') 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 and a.status = 'INACTIVE' /
and that wrapped up in PL/SQL
declare
cursor c1 is
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';
stmt varchar2(4000);
begin
for r1 in c1
loop
begin
dbms_output.put_line(r1.stmt);
execute immediate r1.stmt;
exception
when others then
null;
end;
end loop;
end;
/
select vss.tablespace_name
, dtf.total_mb
, dtf.total_mb-round(vss.used_blocks*8/1024) current_free_mb
, round(vss.used_blocks*8/1024) current_used_mb
, round(vss.max_used_blocks*8/1024) max_used_mb
from v$sort_segment vss
, (
select tablespace_name
, round(sum(bytes)/1024/1024) total_mb
from dba_temp_files
group by tablespace_name
) dtf
where 1=1
and vss.tablespace_name = dtf.tablespace_name
/
select hash_value
, sorts
, rows_processed/executions
from v$sql
where hash_value in (
select hash_value
from v$open_cursor
where sid = &sid
)
and sorts > 0
and parsing_schema_name like '&schema_name'
order by rows_processed/executions
/
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 /
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;
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
mkdir -p “${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde” sqlplus / as sysdba alter system set wallet_root=“${ORACLE_BASE}/admin/${ORACLE_SID}/wallet” scope = spfile; shu immediate startup alter system set tde_configuration=“keystore_configuration=file”; administer key management create keystore '${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde' identified by some_password;
set lines 1000 pages 100 col wrl_parameter for a40 select con_id, wrl_type, wrl_parameter, status from v$encryption_wallet;
administer key management set keystore open force keystore identified by some_password; administer key management set key identified by some_password with backup;
administer key management create auto_login keystore from keystore '${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde' identified by some_password;
select tablespace_name, status, encrypted from dba_tablespaces; alter tablespace users encryption online encrypt;
alter system set encrypt_new_tablespaces=always;
Encrypt tablespaces to protect data (after having created a wallet)
select tablespace_name, encrypted from dba_tablespaces;
select t.name , e.encryptionalg , e.encryptedts from v$tablespace t , v$encrypted_tablespaces e where t.ts# = e.ts#;