Hello,
Oracle Database 11g
RHEL 5
I got a error in alert log file that TEMP tablespace was not able to grow , when i checked the status of temp tablespace it had 768 MB of free space. So i started to focus in different angel.
I figured out that the index when used is not getting the proper extent in TEMP tablespace and few other tablespaces in my DB. So i wanted to extend the extent management in those tablespaces .... but i got the following error ..
SQL> alter tablespace TEMP default storage (maxextents unlimited);
alter tablespace TEMP default storage (maxextents unlimited)
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
SQL> alter tablespace TBSP1 default storage (maxextents unlimited);
alter tablespace TBSP1 default storage (maxextents unlimited)
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy
I checked on net and I found that the tablespace with Uniform allocation type cannot be changed.
select t.tablespace_name,t.allocation_type from dba_tablespaces t
where tablespace_name in ('TEMP03','TBSP1');
1 TBSP1 SYSTEM
2 TEMP03 UNIFORM
As TEMP tablespace is UNIFORM , can i change it to something different so that i can set the extent management. What about TBSP1 tablespace ?? what is the problem with this query.
The major question here is " How should i be able to manage the extent management for these tablespace " ???
Thanks in Advance .....