SQL> select sum(bytes) from dba_free_space where tablespace_name='TEMPORARY_DATA';
I have increased the initial and next extent to 1MB and also made the pctincrease to 0.
Even then I am getting the error
SQL> analyze table test compute statistics;
analyze table test compute statistics
ERROR at line 1:
ORA-01652: unable to extend temp segment by 512 in tablespace TEMPORARY_DATA.
SQL> select tablespace_name, max(bytes) "largest free extent" from dba_free_space
2 group by tablespace_name
TABLESPACE_NAME largest free extent
Also please note that Now I have assigned the TEMP tablespace to the user and its largest free extent is 1054720.
I am still getting the error.
BTW whats the best way to size the temporary tablespace rather than to keep on increasing its size every now and then. There should be some way where I can predict the temporary tablespace size requirements by doing some calculations.
I increased the datafile size of the temporary table space to 300 MB and set the maximum extents values for the temporary table space to unlimited and it worked.
In the OEM it shows me that used is 261 MB.
It needs this much of space only while querying one particular table which has millions of rows in it. This is the only table in that schema.
Is it OK if I assign a specific temporary table space to it which no other user uses and define the storage parameters as Initial = 300MB so that it does not have to assign extents and will inturn improve performAnce.