学習ORACLE-エラーORA-01652:unable to extend temp segment by 8192 in tablespace TEMP

18854 ワード
勉強する oracle ORA-01652
夜、データウェアハウス全体がデータを更新し、テーブル文を作成するプロセスを実行した結果、このエラーORA-01652:unable to extend temp segment by 8192 in tablespace TEMPが報告されたことがわかりました.実際には、一時的なテーブルスペースが解放されて実行されるのを待っていればいいのです.正常な解決方法を勉強してください.

1.エラーの原因


テンポラリ・テーブル領域がテンポラリ・セグメントを拡張できないエラーが発生した場合、テンポラリ・テーブル領域を使用する文が大量に存在するかどうかを考慮する必要があります.一時表領域の使用は、通常、次のようになります.1.大規模なソート結果の格納:ソート操作(order by、group by、distinct、union、intersect、minus、sort-merge joins、analyzeを含む);2.管理索引:索引の作成、再構築3.一時テーブルを格納します.4.その他、一時的なスペースを使用する必要がある操作:インポート、ビューの確認など.見つかったら文を変更したり、一時表領域を大きくしたりします.

2.解決方法


2.1一時表領域（ファイル）に関する操作


0.一時表領域使用率の問合せ(ソースネットワーク)
SELECT d.tablespace_name "Name", 
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)", 
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " , 
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" 
FROM dba_tablespaces d, 
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t 
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = t.tablespace_name(+) 
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';

1.クエリー・データベースのデフォルトの一時表領域SELECT * FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';PROPERTY_NAME
PROPERTY_VALUE
DESCRIPTION
DEFAULT_TEMP_TABLESPACE
TEMP
Name of default temporary tablespace
2.一時ファイルに関する情報の照会
SELECT file_name,
       file_id,
       tablespace_name,
       status,
       autoextensible,
       bytes/1024/1024 "file_size(M)"
FROM dba_temp_files;

FILE_NAME
FILE_ID
TABLESPACE_NAME
STATUS
AUTOEXTENSIBLE
file_size(M)
D:\APP\CHUJF\ORADATA\ORCL\TEMP01.DBF
1
TEMP
ONLINE
YES
275
D:\APP\CHUJF\ORADATA\ORCL\TEMP02.DBF
2
TEMP
ONLINE
NO
25
3.テンポラリ表領域のテンポラリファイルを追加
ALTER TABLESPACE temp ADD TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF' SIZE 50M;
SELECT file_name,file_id,tablespace_name,status,autoextensible,bytes/1024/1024 "file_size(M)" 
FROM dba_temp_files 
ORDER BY file_id;

FILE_NAME
FILE_ID
TABLESPACE_NAME
STATUS
AUTOEXTENSIBLE
file_size(M)
D:\APP\CHUJF\ORADATA\ORCL\TEMP01.DBF
1
TEMP
ONLINE
YES
275
D:\APP\CHUJF\ORADATA\ORCL\TEMP02.DBF
2
TEMP
ONLINE
NO
25
D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF
3
TEMP
ONLINE
NO
50
50 Mサイズの拡張不可能なオンライン状態の一時ファイルTEMP03.DBFをTEMP一時表領域4に追加することが分かる.一時ファイルをオフラインにする(オンライン)
ALTER DATABASE TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF' OFFLINE;--   ONLINE
SELECT file_name,file_id,tablespace_name,status,autoextensible,bytes/1024/1024 "file_size(M)" 
FROM dba_temp_files 
ORDER BY file_id;

FILE_NAME
FILE_ID
TABLESPACE_NAME
STATUS
AUTOEXTENSIBLE
file_size(M)
D:\APP\CHUJF\ORADATA\ORCL\TEMP01.DBF
1
TEMP
ONLINE
YES
275
D:\APP\CHUJF\ORADATA\ORCL\TEMP02.DBF
2
TEMP
ONLINE
NO
25
D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF
3
TEMP
OFFLINE
オフライン後の一時ファイルには、いくつかのプロパティ5が表示されなくなりました.一時ファイルのサイズ変更
ALTER DATABASE TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF' RESIZE 100M;
SELECT file_name,file_id,tablespace_name,status,autoextensible,bytes/1024/1024 "file_size(M)" 
FROM dba_temp_files 
ORDER BY file_id;

FILE_NAME
FILE_ID
TABLESPACE_NAME
STATUS
AUTOEXTENSIBLE
file_size(M)
D:\APP\CHUJF\ORADATA\ORCL\TEMP01.DBF
1
TEMP
ONLINE
YES
275
D:\APP\CHUJF\ORADATA\ORCL\TEMP02.DBF
2
TEMP
ONLINE
NO
25
D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF
3
TEMP
ONLINE
NO
100
一時ファイルがオフラインの場合、サイズやその他のプロパティ6を変更することはできません.自動拡張のための一時ファイルの変更
ALTER DATABASE TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;--       AUTOEXTEND OFF
SELECT file_name,file_id,tablespace_name,status,autoextensible,
       increment_by*8192/1024/1024 "increment_by(M)",
       bytes/1024/1024 "file_size(M)",
       maxbytes/1024/1024 "max_file_size(M)"
FROM dba_temp_files 
ORDER BY file_id;

FILE_NAME
FILE_ID
TABLESPACE_NAME
STATUS
AUTOEXTENSIBLE
increment_by(M)
file_size(M)
max_file_size(M)
D:\APP\CHUJF\ORADATA\ORCL\TEMP01.DBF
1
TEMP
ONLINE
YES
.625
275
32767.984375
D:\APP\CHUJF\ORADATA\ORCL\TEMP02.DBF
2
TEMP
ONLINE
NO
0
25
0
D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF
3
TEMP
ONLINE
YES
5
100
32767.984375 TEMP03.DBFの関連属性が変更することがわかる.一時ファイルの削除
ALTER TABLESPACE temp DROP TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF';
SELECT file_name,file_id,tablespace_name,status,autoextensible,bytes/1024/1024 "file_size(M)" 
FROM dba_temp_files;

FILE_NAME
FILE_ID
TABLESPACE_NAME
STATUS
AUTOEXTENSIBLE
file_size(M)
D:\APP\CHUJF\ORADATA\ORCL\TEMP01.DBF
1
TEMP
ONLINE
YES
275
D:\APP\CHUJF\ORADATA\ORCL\TEMP02.DBF
2
TEMP
ONLINE
NO
25 TEMP03.DBFが削除されたことがわかり、windowsではすぐに物理ファイルが削除されません.別の削除方法:
ALTER TABLESPACE temp ADD TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP04.DBF' SIZE 50M;
SELECT file_name,file_id,tablespace_name,status,autoextensible,bytes/1024/1024 "file_size(M)" 
FROM dba_temp_files ORDER BY file_id;

FILE_NAME
FILE_ID
TABLESPACE_NAME
STATUS
AUTOEXTENSIBLE
file_size(M)
D:\APP\CHUJF\ORADATA\ORCL\TEMP01.DBF
1
TEMP
ONLINE
YES
275
D:\APP\CHUJF\ORADATA\ORCL\TEMP02.DBF
2
TEMP
ONLINE
NO
25
D:\APP\CHUJF\ORADATA\ORCL\TEMP04.DBF
3
TEMP
ONLINE
NO
50
ファイルを削除:
ALTER DATABASE TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP04.DBF' DROP INCLUDING DATAFILES;
SELECT file_name,file_id,tablespace_name,status,autoextensible,bytes/1024/1024 "file_size(M)" 
FROM dba_temp_files ORDER BY file_id;

FILE_NAME
FILE_ID
TABLESPACE_NAME
STATUS
AUTOEXTENSIBLE
file_size(M)
D:\APP\CHUJF\ORADATA\ORCL\TEMP01.DBF
1
TEMP
ONLINE
YES
275
D:\APP\CHUJF\ORADATA\ORCL\TEMP02.DBF
2
TEMP
ONLINE
NO
25
同期によって物理ファイルが削除されたことがわかりました.

2.2解決方法


一時表領域が拡張できない以上、一時表領域のサイズを大きくしてみましょう.以上のまとめから、3つの方法を見つけることができます.一時ファイルのサイズを大きくする:ALTER DATABASE TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF' RESIZE 100M;.一時ファイルを自動拡張に変更:ALTER DATABASE TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;.一時表領域への一時ファイルの追加:ALTER TABLESPACE temp ADD TEMPFILE 'D:\APP\CHUJF\ORADATA\ORCL\TEMP03.DBF' SIZE 50M;
掲示板データベースの作成
210118-210122 TIL