Oracle
ORA-01652 - 無法擴展 TEMP 段？
有一個問題，我似乎無法在網上找到任何解決方案。
$$ 72000 $$$$ 1652 $$ORA-01652: 無法在表空間 TEMP 位置中將 temp 段擴展 256 位: 581
我們的一位使用者每季度執行一次查詢，沒有任何問題，然後今天他們在查詢中收到了上面的錯誤。我以前從未見過它。
到目前為止，我已經嘗試將另一個數據文件添加到 TEMP 表空間，它似乎有足夠的空間甚至不需要擴展現有的數據文件（90 GB）。
我該怎麼辦？
編輯：添加查詢的解釋計劃…
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2297118615 -------------------------------------------------------------------------------- ------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempS pc| Cost (%CPU)| Time | Inst |IN-OUT| -------------------------------------------------------------------------------- ------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 128 | | 89436 (1)| 00:00:04 | | | |* 1 | HASH JOIN | | 1 | 128 | | 89436 (1)| 00:00:04 | | | | 2 | MERGE JOIN CARTESIAN | | 1 | 84 | | 89200 (1)| 00:00:04 | | | |* 3 | HASH JOIN | | 1 | 78 | | 89196 (1)| 00:00:04 | | | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 4 | MERGE JOIN CARTESIAN | | 1 | 39 | | 810 (1)| 00:00:01 | | | | 5 | REMOTE | TERM | 1 | 13 | | 4 (0)| 00:00:01 | SAM_S~ | R->S | | 6 | BUFFER SORT | | 1019K| 25M| | 806 (1)| 00:00:01 | | | | 7 | REMOTE | SECTION | 1019K| 25M| PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 806 (1)| 00:00:01 | SAM_S~ | R->S | | 8 | VIEW | | 10M| 388M| | 88370 (1)| 00:00:04 | | | | 9 | HASH UNIQUE | | 10M| 388M| 51 9M| 88370 (1)| 00:00:04 | | | | 10 | REMOTE | SECTIONPLACEMENT | 10M| 388M| | 995 (4)| 00:00:01 | SAM_S~ | R->S | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 11 | BUFFER SORT | | 2709 | 16254 | | 89200 (1)| 00:00:04 | | | | 12 | SORT UNIQUE | | 2709 | 16254 | | 4 (0)| 00:00:01 | | | | 13 | MAT_VIEW ACCESS FULL| MV_ASSIGNMENT_SECTIONS | 2709 | 16254 | | 4 (0)| 00:00:01 | | | |* 14 | TABLE ACCESS FULL | MV_SAM_MAPPING | 77753 | 3340K| | 236 (1)| 00:00:01 | | | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("MSMAP"."SECTIONID"="SEC"."SECTIONID" AND "MSMAP"."SECTIONID"="SEC TIONID") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 3 - access("SPLA"."TERMID"="TER"."TERMID" AND "SEC"."SECTIONID"="SPLA"."SECTI ONID" AND "SEC"."TRIALID"="SPLA"."TRIALID") 14 - filter("MSMAP"."SECTIONID" IS NOT NULL) Remote SQL Information (identified by operation id): ---------------------------------------------------- 5 - SELECT "TERMID" FROM "SIS_DATA"."TERM" "TER" (accessing 'SAM_SIS_DATA' ) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 7 - SELECT "SECTIONID","TRIALID" FROM "SIS_DATA"."SECTION" "SEC" (accessing ' SAM_SIS_DATA' ) 10 - SELECT "SECTIONID","TERMID","TRIALID" FROM "SIS_DATA"."SECTIONPLACEMENT" "SECTIONPLACEMENT" (accessing 'SAM_SIS_DATA' ) 40 rows selected.
我們使用此查詢來找出誰使用了過多的 TEMP。
select /*+ RULE */ s.sid, s.serial#, p.spid "OS SID", s.sql_hash_value "HASH VALUE", s.username "ORA USER", s.status, s.osuser "OS USER", s.machine, s.terminal, s.type, s.program, s.logon_time, s.last_call_et, su.contents, ROUND(((su.blocks*param.value)/1024/1024),2) "MB", tablespace, s.sql_id FROM v$session s, v$sort_usage su, v$process p, v$parameter param WHERE param.name = 'db_block_size' and su.session_addr = s.saddr and p.addr = s.paddr and ((su.blocks*param.value)/1024/1024) > 100 ORDER BY su.blocks DESC
請提供 SQL 以及最後分析的基表。
select owner,table_name,'GOLBAL' partition_name, num_rows, blocks, avg_row_len, last_analyzed from dba_tables where (owner,table_name) in （從 v$sql_plan 中選擇 OBJECT_OWNER,OBJECT_NAME，其中 sql_id='&sql_id' 和（OBJECT_TYPE='TABLE' 或 OPERATION='UPDATE'）） 聯盟 select table_owner,table_name,Partition_name, num_rows, blocks, avg_row_len, last_analyzed from dba_tab_partitions where (table_owner,table_name) in (select OBJECT_OWNER,OBJECT_NAME from v$sql_plan where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE')); select owner, index_name,'GLOBAL' partitionn_name, clustering_factor, distinct_keys, last_analyzed from dba_indexes where (owner,table_name) in （從 v$sql_plan 中選擇不同的 OBJECT_OWNER、OBJECT_NAME，其中 sql_id='&sql_id' 和（OBJECT_TYPE='TABLE' 或 OPERATION='UPDATE'）） 聯盟 select owner, i.index_name,partition_name, clustering_factor, distinct_keys, last_analyzed from dba_ind_partitions i,dba_part_indexes p where p.table_name in (select distinct OBJECT_NAME from v$sql_plan where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE')) i.index_name=p.index_name and i.index_owner=p.owner and owner in (select distinct OBJECT_OWNER from v$sql_plan where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE')); select owner, column_name , num_distinct, num_nulls, last_analyzed from dba_tab_cols where (owner,table_name) in (select OBJECT_OWNER,OBJECT_NAME from v$sql_plan where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE'));