###检查是否有temp 在使用
step 2: 检查是否有事务使用到temp,并且进行删除。
SELECT vt.inst_id,
vs.sid,
vs.serial#,
vs.username,
vs.osuser,
vs.machine,
vs.saddr,
vs.client_info,
vs.program,
vs.module,
vs.logon_time,
vt.tempseg_usage,
vt.segtype
FROM gv$session vs,
(SELECT inst_id,
username,
session_addr,
segtype,
ROUND(SUM(blocks) * 8192 / 1024 / 1024 / 1024, 2) tempseg_usage
FROM gv$tempseg_usage
GROUP BY inst_id, username, session_addr, segtype
ORDER BY 4 DESC) vt
WHERE vs.inst_id = vt.inst_id
AND vs.saddr = vt.session_addr
order by tempseg_usage desc;
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024;
TROUBLESHOOTING GUIDE (TSG) : ORA-1652: unable to extend temp segment (文档 ID 1267351.1) | ![]() | ![]() |
![](https://support.oracle.com/epmos/adf/images/t.gif)
In this Document
APPLIES TO:Oracle Database - Enterprise Edition - Version 8.0.6.0 to 11.2.0.2.0 [Release 8.0.6 to 11.2]Information in this document applies to any platform. ***Checked for relevance on 21-Jul-2017*** SYMPTOMSOracle database instances will from time to time experience CAUSEEXAMPLE 1: SOLUTIONFirst it is important to forget what is known about past behavior of the instance as the current tablespace size is insufficient to handle the demand by current sessions |
How Can Temporary Segment Usage Be Monitored Over Time? (文档 ID 364417.1) | ![]() | ![]() |
![](https://support.oracle.com/epmos/adf/images/t.gif)
In this Document
APPLIES TO:Oracle Database - Enterprise Edition - Version 8.1.7.0 and laterInformation in this document applies to any platform. ***Checked for relevance on 21-Jul-2017*** GOALSuggest a method by which temporary segment usage be monitored over time SOLUTION
CASE STUDY: EXAMPLE of how to monitor Temporary Segment Usage over time
-- NOTE: If SYS is unacceptable then use a schema that has access of each of the DBA views below -- Create a table to hold your temporary space monitoring
CREATE TABLE TEMP_TEMP_SEG_USAGE(
DATE_TIME DATE, USERNAME VARCHAR2(30), SID VARCHAR2(6), SERIAL# VARCHAR2(6), OS_USER VARCHAR2(30), SPACE_USED NUMBER, SQL_TEXT VARCHAR2(1000));
CREATE OR REPLACE PROCEDURE TEMP_TEMP_SEG_USAGE_INSERT IS
BEGIN insert into TEMP_TEMP_SEG_USAGE SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text FROM v$session a, v$sort_usage b, v$sqlarea c WHERE b.tablespace = 'TEMP' and a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024; COMMIT; END; / SELECT JOB FROM DBA_JOBS; JOB ---------- 19 BEGIN DBMS_JOB.ISUBMIT(JOB => 20, WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;', NEXT_DATE => SYSDATE, INTERVAL => 'SYSDATE + (5/1440)'); COMMIT; END; /
CREATE OR REPLACE PROCEDURE TEMP_TEMP_SEG_USAGE_INSERT IS
BEGIN insert into TEMP_TEMP_SEG_USAGE SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text FROM v$session a, v$sort_usage b, v$sqlarea c WHERE b.tablespace = 'TEMP' and a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024; COMMIT; END; / SELECT JOB FROM DBA_JOBS; JOB ---------- 19 BEGIN DBMS_JOB.ISUBMIT(JOB => 20, WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;', NEXT_DATE => SYSDATE, INTERVAL => 'SYSDATE + (5/1440)'); COMMIT; END; /
select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb truncate table <temporary tablespace name to monitor>_TEMP_SEG_USAGE;
select * from temp_temp_seg_usage;
DATE_TIME USERNAME SID SERIAL --------- --------------------- ----- ---- OS_USER SPACE_USED ------------------------------ ---------- SQL_TEXT ----------------------------------------- 29-JUN-07 SYS 158 13 sygaw-ca\sygaw 768 select * from dba_objects order by object_id, object_name select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb from dba_segments where segment_name='TEMP_TEMP_SEG_USAGE'; SEGMENT_NAME ------------------------------------------ TABLESPACE_NAME USEDMB ------------------------------ ---------- TEMP_TEMP_SEG_USAGE SYSTEM .0625 truncate table temp_temp_seg_usage; Table truncated.
|
select max(bytes)/1024/1024 from dba_free_space
where tablespace_name = 'TBS_BIEETL_DATA';
SQL> select count(*) from dba_recyclebin;
COUNT(*)
----------
1875
SQL> select max(bytes)/1024/1024 from dba_free_space
2 where tablespace_name = 'TBS_BIEETL_DATA';
MAX(BYTES)/1024/1024
--------------------
3968
select tablespace_name, file_id, bytes_used, bytes_free
from v$temp_space_header ;
alter system set events '1658 trace name errorstack level 3';
alter system set events '1653 trace name errorstack level 3';
alter system set events '1652 trace name errorstack level 3';
SQL*Loader Fils on Load with ORA-1653 (文档 ID 205296.1) | ![]() | ![]() |
![](https://support.oracle.com/epmos/adf/images/t.gif)
Fact(s) ~~~~~~~~ Oracle RDBMS Server Oracle SQL*Loader Symptom(s) ~~~~~~~~~~ SQL*Loader fails on small load with ORA-1653: unable to extend table <schema>.<table_name> by <size> in tablespace <tbs> Loads on other tables work without problem. There is sufficient space within the tablespace to accomodate the load. Change(s) ~~~~~~~~~~ N/A Cause ~~~~~~~ NEXT extent requested by the table is larger than the largest available extent within the tablespace. It is possible that PCTINCREASE is set to some non-zero value and each request for extents getslarger and larger. Fix ~~~~ Possible resolutions include: 1. Coalesce the tablespace: SQL> alter tablespace <tbs> coalesce; 2. Add a datafile to the tablespace: SQL> alter tablespace <tbs> add datafile 'path/name' size <size>; 3. Alter the NEXT attribute for the table that you are trying to load into to a value smaller than the largest available free extent in the tablespace: SQL> alter table <table_name> storage (next <size M/K>); where M/K = Megabytes or Kilobytes. To get the Next extent that will be requested by the table do: SQL> select to_char (next_extent,'999G999G999G999D00')||' Mb' as "NEXT" from user_tables where table_name = '<TABLE_NAME>'; To get the largest free extent available in the tablespace run: SQL> select tablespace_name, to_char (max(bytes),'999G999G999G999D00')||' MB' as "LARGEST FREE EXTENT" from dba_free_space where tablespace_name = '<TBS>' group by tablespace_name; |