ora-1652-白红宇

ora-1652

阅读量：6599 次

发布时间：2019-06-24

本文共 14533 字，大约阅读时间需要 48 分钟。

###检查是否有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)

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***

SYMPTOMS

Oracle database instances will from time to time experience

ORA-1652: unable to extend temp segment by %s in tablespace %s

A vast majority of the time this is a normal but annoying condition due to a temporary tablespace having its space exhausted

CAUSE

EXAMPLE 1:

Temporary tablespace TEMP is being used and is 50gb in size (a recommended minimum for 11g)

TIME 1 : Session 1 starts a long running query

TIME 2 : Session 2 starts a query and at this point in time Session 1 has consumed 48gb of TEMP's free space

TIME 3 : Session 1 and Session 2 receive an ORA-1652 because the tablespace has exhausted of of its free space

Both sessions fail .. and all temp space used by the sessions are freed (the segments used are marked FREE for reuse)

TIME 4 : SMON cleans up the temporary segments used by Session 1 and Session 2 (deallocates the storage)

TIME 5 : Queries are run against the views V$SORTSEG_USAGE or V$TEMSEG_USAGE and V$SORT_SEGMENT ... and it is found that no space is being used (this is normal)

EXAMPLE 2:

Permanent tablespace INDEX_TBS is being used and has 20gb of space free

TIME 1 : Session 1 begins a CREATE INDEX command with the index stored in INDEX_TBS

TIME 2 : Session 1 exhausts all of the free space in INDEX_TBS as a result the CREATE INDEX abends

TIME 3 : SMON cleans up the temporary segments that were used to attempt to create the index

TIME 4 : Queries are run against the views V$SORTSEG_USAGE or V$TEMSEG_USAGE ... and it is found that the INDEX_TBS has no space used (this is normal)

SOLUTION

First 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

There are three recommended methods of supplying sufficient storage space to meet the needs of current sessions by increasing the size of your temporary tablespace

1) Set one or more of the tempfiles|datafiles for the tablespace to AUTOEXTEND with MAXSIZE set ... so that you do not exhaust all available disk volume space

(discuss this with a System Administrator)

After a month or so of having this setting ... AUTOEXTEND can be disabled .. as it is likely that the system has found its true high watermark for temporary segment usage

(This is the most recommended method as it allows the database instance to find its own high watermark)

2) Monitor the temporary segment usage via queries like

SELECT sum(blocks)*<block size of the temporary tablespace>

FROM v$tempseg_usage

WHERE tablespace = '<name of the temporary tablespace>';

and resize one or more of the tempfiles|datafiles for the tablespace as the tablespace becomes close to exhausted

3) Add a tempfile|datafile to the temporary tablespace with the problem and monitor usage as described in #2

Another good idea is to monitor temporary tablespace usage over time to determine what queries are consuming the temporary space space

For example: How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

This note was written to monitor temporary tablespaces .. but may be able to be modified to also monitor permanent tablespaces

If after adjusting the 'granularity' of the monitoring job (Doc ID 364417.1) to a small sampling period (1 min should be sufficient) it is found that there truly are never any points in time where temporary segment usage approaches the point where the tablespace has been exhausted then we recommend opening a service request via My Oracle Support so a possible bug can be investigated

##################2

How Can Temporary Segment Usage Be Monitored Over Time? (文档 ID 364417.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.0 and later

Information in this document applies to any platform.

***Checked for relevance on 21-Jul-2017***

GOAL

Suggest a method by which temporary segment usage be monitored over time

Such a process would aid greatly in investigating

ORA-1652 "unable to extend temp segment by %s in tablespace %s"

EXAMPLE

Suppose we have a 90gb temporary tablespace (only one temp tablespace for this example)

We also have enabled tracing for error 1652 using ALTER SYSTEM SET EVENTS '1652 TRACE NAME ERRORSTACK LEVEL 3';

At time 1 ... a long ... complicated query starts running

after several hours ... it has consumed 85gb of space ... and will continue running for a long time

At time 2 (time 1 + several hours) another query runs and quickly consumes 5gb of space ... and generates an ORA-1652

this session then crashes and releases the 5gb of space ... This will not cause the query started at time 1 .. or any other query to fail

unless they also needed MORE temp space (ie a new extent) .. at exactly the same moment

At this point .. the errorstack for 1652 will show the query started at time 2 ... not the BIG temp space consumer ... started at time 1

At time 3 ... This note is used to setup the monitoring job to watch the temp segment usage over time ...

the first update should show our query that has been running since time 1 ... which has consumed 85gbgb of space

Now .. suppose while setting up the job ... the query from time 1 ... ends ... and frees up its 85gb of space ... then our job will not show

the consumption ... as it is now freed

At time 4 .. the same query that consumed the 85gb of space is run again

At time 5 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 5gb of temp space

At time 6 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 50gb of temp space

At time 7 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 85gb of temp space

At time 8 ... another query is started that quickly consumes the remaining 5gb of space ... and as a result .. an ORA-1652 occurs

Again .. our errorstack trace will point to this query .. an not the query started at Time 4 ...

The job that is running ... WILL show the session that consumed the 85gb of space

Once the 'top consumers' of space have been determined .. those query can be investigated for possible tuning to use less space ... and if they cannot be tuned then more space needs to be added to the temporary tablespace that is running out of space

SOLUTION

CASE STUDY: EXAMPLE of how to monitor Temporary Segment Usage over time

sqlplus / as sysdba

-- 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 <temporary tablespace name to monitor>_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 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 a job in the job queue to insert into your monitoring table

-- For 8.1.7 to 9.1

CREATE OR REPLACE PROCEDURE <temporary tablespace name to monitor>_TEMP_SEG_USAGE_INSERT IS

BEGIN

insert into <temporary tablespace name to monitor>_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 = <temporary tablespace name to monitor>

and a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

AND b.blocks*<block size of the temp tablesapace> > <threshold>

ORDER BY b.tablespace, b.blocks;

commit;

END;

BEGIN

DBMS_JOB.ISUBMIT(

JOB => <A job # not in use in the DBA_JOBS>,

WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;'

NEXT_DATE => SYSDATE,

INTERVAL => 'SYSDATE + (<NUMBER OF MINUTES BETWEEN SAMPLES>/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

----------

BEGIN

DBMS_JOB.ISUBMIT(JOB => 20,

WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;',

NEXT_DATE => SYSDATE,

INTERVAL => 'SYSDATE + (5/1440)');

COMMIT;

END;

-- For 9.2 and above:

CREATE OR REPLACE PROCEDURE <temporary tablespace name to monitor>_TEMP_SEG_USAGE_INSERT IS

BEGIN

insert into <temporary tablespace name to monitor>_TEMP_SEG_USAGE

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE b.tablespace = <temporary tablespace name to monitor>

and a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

AND b.blocks*<block size of the temp tablesapace> > <threshold>

ORDER BY b.tablespace, b.blocks;

COMMIT;

END;

BEGIN

DBMS_JOB.ISUBMIT(

JOB => <A job # not in use in the DBA_JOBS>,

WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;'

NEXT_DATE => SYSDATE,

INTERVAL => 'SYSDATE + (<NUMBER OF MINUTES BETWEEN SAMPLES>/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

----------

BEGIN

DBMS_JOB.ISUBMIT(JOB => 20,

WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;',

NEXT_DATE => SYSDATE,

INTERVAL => 'SYSDATE + (5/1440)');

COMMIT;

END;

-- Periodically query your monitoring table <temporary tablespace name to monitor>_TEMP_SEG_USAGE.

-- Also monitor space usage of the table as it could grow very fast depending on job interval; delete rows or truncate table as appropriate.

select * from <temporary tablespace name to monitor>_TEMP_SEG_USAGE;

select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb

from dba_segments

where segment_name='<temporary tablespace name to monitor>_TEMP_SEG_USAGE;';

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.

############3

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';

###############3

SQL*Loader Fils on Load with ORA-1653 (文档 ID 205296.1)

Fact(s)~~~~~~~~Oracle RDBMS ServerOracle SQL*LoaderSymptom(s)~~~~~~~~~~SQL*Loader fails on small load with   ORA-1653: unable to extend table 
              
               .
               
                 by 
                
                  in tablespace 
                 
                  Loads on other tables work without problem. There is sufficient space within thetablespace to accomodate the load.Change(s)~~~~~~~~~~N/ACause~~~~~~~NEXT extent requested by the table is larger than the largest available extentwithin the tablespace.It is possible that PCTINCREASE is set to some non-zero value and each requestfor extents getslarger and larger.Fix~~~~Possible resolutions include:1. Coalesce the tablespace:   SQL> alter tablespace 
                  
                    coalesce;2. Add a datafile to the tablespace:   SQL> alter tablespace 
                   
                     add datafile 'path/name' 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 
                     
                       storage (next 
                      
                       ); 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 = '
                       
                        '; 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 = '
                        
                         ' group by tablespace_name;