Problem with Temporary Table space.

[EM1107]

Good day everyone!

I hope you guys can help me on that one!

I have to create an index on a table that containe over 10,000.000 rows and every time I start to create the index the temporary table space get full before I can complete the creation of the index.

I would like to know if there is a way I can flush the content of the temporary table space or stop the information from going in the temporary table space while creating the index?

 

[SantaMufasa]

EM,

Index creations require sorting. If the sorting requires more space than is available in your System Global Area's (SGA) SORTAREA, then Oracle must use the space in the Temporary Table Space. Therefore, there is no alternative to, or turning off of, the Temporary Tablespace.

To better advise you on how to resolve your need, we must understand whether your are using "conventional" temporary tablespace or "automatic" temporary tablespace. To assist in determining which style you are using, please report the results of the following code (presuming that your temporary-tablespace datafile names contain the string 'TEMP'):

set linesize 100
col name format a70
select file_name name from dba_data_files
 where upper(file_name) like '%TEMP%';
select name from v$tempfile;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]