Info
Die Select-Statements in diesem Blogpost habe ich von anderen Webseiten kopiert. Daher ist dieser Beitrag eher als Zusammenfassung unterschiedlicher Lösungsversuche zu sehen und dient mir als schnelle Hilfe bei der Verkleinerung eines zu großen Tablespaces. Schaut euch die Quellen an, die sehr viel detaillierter auf die jeweiligen Probleme eingehen.
Wer kennt nicht die Situation? Der DBA ruft an und meint der TEMP Tablespace verbraucht mehrere hundert Gigabyte an Speicher.
Was ist in solch einer Situation zu tun?
In dem Moment wo ein TEMP Tablespace überproportional ansteigt, muss eine Session diesen Anstieg verursachen. Mit dem folgenden Select erfahren Sie welche Session wie viel Speicher im TEMP-Tablespace verbraucht.
-- Source: http://stackoverflow.com/questions/174727/discover-what-process-query-is-using-oracle-temp-tablespace
select b.tablespace
, b.segfile#
, b.segblk#
, round ( ( ( b.blocks * p.value ) / 1024 / 1024 ), 2 ) size_mb
, a.sid
, a.serial#
, a.sql_id
, a.username
, a.osuser
, a.program
, a.status
from v$session a
, v$sort_usage b
, v$process c
, v$parameter p
where p.name = 'db_block_size'
and a.saddr = b.session_addr
and a.paddr = c.addr
order by b.tablespace
, b.segfile#
, b.segblk#
, b.blocks;
Über die SQL_ID können Sie wenn vorhanden auch auf das Verursacher-Select zugreifen:
-- Source: http://cheatsheet4oracledba.blogspot.de/2014/01/how-to-find-top-temp-using-oracle.html
select sql_text
from v$sql
where sql_id='b6kta08q9jj3f';
Über den SQL Developer > Tools > Monitor Sessions können Sie die betroffene SID killen.
Anschließend wird der Verbrauch des TEMP-Tablespace zwar wieder zurückgefahren, aber die Größe bleibt bestehen.
Prüfen Sie daher zunächst die aktuelle verwendete Größe im TEMP-Tablespace:
-- Source: https://alexzeng.wordpress.com/2012/06/13/how-to-find-the-sql-that-using-lots-of-temp-tablespace-in-oracle/
select b.total_mb,
b.total_mb - round(a.used_blocks*8/1024) current_free_mb,
round(used_blocks*8/1024) current_used_mb,
round(max_used_blocks*8/1024) max_used_mb
from v$sort_segment a,
(select round(sum(bytes)/1024/1024) total_mb from dba_temp_files ) b;
-- Oder:
select * from dba_temp_free_space;
Wenn der Wert (current_used_mb) entsprechend klein ist, dann können Sie den TEMP-Tablespace verkleinern, andernfalls haben Sie nicht die richtige Session gekillt.
TEMP-Tablespace verkleinern:
select file_name,bytes,blocks from dba_temp_files;
-- .../tempfile/temp.911 564863696896 68953088
alter tablespace temp shrink space;
select file_name,bytes,blocks from dba_temp_files;
-- ../tempfile/temp.911 289513472 35341
Es kann aber auch vorkommen das eine normaler Tablespace zu groß wurde und dadurch viel mehr Platz verbraucht als es aktuell verwendet.
Um darüber einen Überblick zu erhalten, führen Sie folgendes Select aus:
-- Source: Nicht mehr bekannt :(
select
mb.*
,nvl(round(100 * freemb / sizemb,1),0) free_prozent
from
(select
b.tablespace_name
,round(tbs_size,2) as sizemb
,a.free_space freemb
from
(select
tablespace_name
,round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name
) a
,(select
tablespace_name,
sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
union
select
tablespace_name,
sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name
) b
where a.tablespace_name(+)=b.tablespace_name
) mb
order by free_prozent;
Bei einer solchen Situation muss anstelle des Tablespaces die Datendatei verkleinert werden.
Nun benötigen Sie dafür noch den richtigen Dateinamen, um die korrekte Datei zu verkleinern:
SELECT
name,
bytes/1024/1024 AS size_mb
FROM v$datafile
;
Statement zum verkleinern der Datendatei:
ALTER DATABASE DATAFILE '.../DATAFILE/my_schema.033.123331' RESIZE 3G
Die Verkleinerung kann aber in einem ORA-03297 Fehler enden.
ALTER DATABASE DATAFILE '.../DATAFILE/my_schema.033.123331' RESIZE 3G
Error report -
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
Jetzt bleiben Ihnen 3 Schritte um mit geringem Aufwand diese Datei doch noch zu verkleinern:
1. Fragmentierung bereinigen
Die Datendatei wurde fragmentiert und eine Tabelle liegt am Ende der Datei und verhindert dadurch die Verkleinerung.
Beispiel:
Die Datendatei ist 90 GB groß und tatsächlich werden nur 2 GB verwendet.
Eine Tabelle liegt von der Verteilung her zwischen 82-83 GB. Heißt, ich könnte die Datendatei nur auf 84G verkleinern.
Also müssen Sie in solch einem Fall das Objekt ausfindig machen und löschen. Sinnvollerweise kopiere ich vorher die Tabelle in einen anderen Tablespace, um diese anschließend wieder herstellen zu können. :)
Um die Blockverteilung analysieren zu können, muss vorher die richtige File-ID ausgelesen werden:
select
s.tablespace_name, s.owner, s.segment_name, s.segment_type,
sum(s.bytes) size_in_bytes,
round(sum(s.bytes) / 1024 / 1024, 2) size_in_m,
sum(round(sum(s.bytes) / 1024 / 1024, 2)) over() as size_in_m_gesamt,
f.file_id,
f.file_name
from sys.dba_segments s, sys.dba_data_files f
where f.tablespace_name = s.tablespace_name
and f.file_id = s.header_file
and s.tablespace_name in ('NTDC03')
group by s.tablespace_name, s.owner, s.segment_name, s.segment_type, f.file_id, f.file_name
order by s.tablespace_name, s.owner, s.segment_name;
Das folgende Select zeigt die Blockverteilung mit den verwendeten DB-Objekten innerhalb der Datendatei:
Source: http://www.orait.de/db_fehler/ora-03297_file-contains-used-data-beyond.html
select
file_id,
block_id,
blocks*8192/1024/1024 as mb,
owner||'.'||segment_name as object_name,
block_id*8192/1024/1024 as position_mb
from sys.dba_extents
where file_id = 206
union
select
file_id,
block_id,
blocks*8192/1024/1024 as mb,
'Free' as object_name,
block_id*8192/1024/1024 as position_mb
from sys.dba_free_space
where file_id = 206
order by 1,2,3;
Wenn Sie die betroffenen Tabellen gelöscht haben, dann klappt auch die Verkleinerung wieder:
ALTER DATABASE DATAFILE '.../DATAFILE/my_schema.033.123331' RESIZE 3G
Database datafile '.../DATAFILE/my_schema.033.123331' altered.
2. Recycle Bin löschen
purge recyclebin;
3. Coalesce Tablespace
alter tablespace fred coalesce;
Info: TEMP Tablespace auf Unlimited setzen
alter database tempfile '.../TEMPFILE/temp.910.901132571' autoextend on next 250m maxsize unlimited;
https://community.oracle.com/message/14924079#14924079 
https://community.oracle.com/message/14924079#14924079 
