Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Use of TEMP tablespace other than sorting
DA Morgan wrote:
>
> Rick Denoire wrote:
>
> > Using an SQL query with a join over six tables, the query breaks with
> > the message "ORA-1652: unable to extend temp segment by 512
> > in tablespace TEMP". This query does not do any sort operation, at
> > least not explicitly.
> >
> > After having expanded TEMP four times consecutively up to 48 GB, I am
> > still getting the same error. So I wonder what TEMP is used for beside
> > sort operations. The tablespace is locally managed, uses uniform
> > extents of 4 MB each. The sort area size is set to this size as well.
> >
> > Is there a way to monitor the use of space in TEMP by a particular
> > session?
> >
> > Oracle 8.1.7 / Solaris 2.7
> >
> > Any hint would be appreciated.
> >
> > Rick Denoire
>
> I'd suggest a couple of things. The first is the you run explain plan to
> see what is happening. The second is that you post the query and give us
> some idea of how many rows are in the tables. At first blush it appears
> that something is wrong. But without more background I wouldn't want to
> speculate as to what.
>
> BTW: CBO or RBO and are statistics with DBMS_STATS current?
>
> Daniel Morgan
The following query will show you who is using temp and how much they are using.
-- SET heading ON SET linesize 140 SET pagesize 40 COLUMN user FORMAT a20 HEADING 'User' COLUMN username FORMAT a10 HEADING 'Username' COLUMN osuser FORMAT a12 HEADING 'OS User' COLUMN sid_ser FORMAT a10 HEADING 'SID,SER' COLUMN blk_mb FORMAT 99,999 HEADING 'Temp MB' COLUMN sorts FORMAT 9,999 HEADING 'Sorts' COLUMN sql_text FORMAT a40 word_wrapped HEADING 'SQL Text' COLUMN process FORMAT a35 word_wrapped HEADING 'Module:Action' COLUMN tablespace NOPRINT new_value ts_name BREAK ON sid_ser SKIP 1 DUPLICATES BREAK ON tablespace - ON REPORT COMPUTE SUM OF blk_mb on tablespace report TTITLE Center 'Current Sort Usage' - skip Center '~~~~~~~~~~~~~~~~~~' - skip 1 Left 'Temp Tablespace: ' ts_name - skip 2 - SELECT /*+ ORDERED */ u.tablespace, s.sid || ',' || s.serial# as sid_ser, s.username, s.osuser, (SUM(u.blocks)*TO_NUMBER(a.value))/1048576 as blk_mb, x.sql_text, s.module || ':' || chr(10) ||'. ' || s.action as process FROM v$sort_usage u ,v$session S ,v$sql X ,v$parameter a WHERE s.saddr = u.session_addr AND s.sql_address = x.address AND s.sql_hash_value = x.hash_value AND a.name = 'db_block_size' GROUP BY u.tablespace , s.sid , s.serial# , s.username , s.osuser , a.value , x.sql_text , s.module , s.action ORDER BY u.tablespace , s.sid / TTITLE off clear computes clear breaks -- Andrew AllenReceived on Wed Feb 26 2003 - 21:32:47 CST