Re: index creation fails because temp tablespace is too small

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 06 May 2005 18:32:34 -0700
Message-ID: <1115429306.369741@yasure>

Jonathan Lewis wrote:

>>Am I missing something? Hopefully not something too obvious.

>
> Try running a query that requires some temp space.
> The column is not reported if none of the rows
> have populated it. (The default is null, not zero).
>
> rem 9.2.0.6
> rem optimizer_mode = all_rows
> rem system stats have been gathered
>
> explain plan for
> select source from sys.source$ order by source;
>
> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> -----------------
>
> ---------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> (%CPU)|
> ---------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1324K| 2528M| | 724K
> (1)|
> | 1 | SORT ORDER BY | | 1324K| 2528M| 5172M| 724K
> (1)|
> | 2 | TABLE ACCESS FULL | SOURCE$ | 1324K| 2528M| | 4128
> (2)|
> ---------------------------------------------------------------------------------
>
> 8 rows selected.

Well I'm the one that wrote: "Hopefully not something too obvious."

SQL*Plus: Release 10.1.0.4.0 - Production on Fri May 6 18:19:12 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> set linesize 121
SQL> explain plan for

2 select source from sys.source$ order by source;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 995087943

| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost 
(%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |   342K|    42M|       | 12285 
(1)| 00:02:28 |
|   1 |  SORT ORDER BY     |         |   342K|    42M|    92M| 12285 
(1)| 00:02:28 |
|   2 |   TABLE ACCESS FULL| SOURCE$ |   342K|    42M|       |  2197

(1)| 00:00:27 |

9 rows selected.

SQL> Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri May 06 2005 - 20:32:34 CDT