930109 wrote:Hi Team,
I have created the temporary tablespace TEMP2 and assigned it to tablespace group and made it as default temporary tablespace.
After that I am getting this error
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Before that I have only one tablespace TEMP and no group and I was not facing this error. After creating TEMP2 and tablespace group I am facing this error. Please let me know how to solve this issue. I have got lot of space in first TEMP tablespace.
SQL> SELECT * FROM dba_tablespace_groups;
Sharma .....please refer below link ....u may get the answer for your question...root cause might be any thing....when ever you got the ORA-1652 ...increase the table-space size is the only solution i hope...plz tell me if any other solution ...i am interested to learn...
The second cause of ORA-01652 may have to do with the local temp segment not being able to extent space even though there is space in other instances.This is what I read from your link mentioned which contradicts you completely.
root cause might be any thing....when ever you got the ORA-1652 ...increase the table-space size is the only solution i hope...plz tell me if any other solution ...i am interested to learn...You said abpve that "increase the tablespace size is the *ONLY*solution" which is wrong according to your mentioned link. So you are not even reading what you are suggesting.
TSharma wrote:how can you say temporary tablespace allocation is good....and due to bad query causing this issue.?....OP has mentioned any bad query running on his database..?
Your temporary tablespace creation and allocation looks good. The other reason could be the bad query running using temporary datafiles. Can you re-produce the error? if this error is caused by certain query, you should tune the query so that the sort operations are done in memory and not on the disk.
If you think it is caused by a particular query,post the query so other users can take a look and try tune your query. Increasing datafile size is one solution but you should fix the root cause if there is any.