This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Mar 18, 2013 11:05 PM by TSharma-Oracle RSS

ORA-1652: unable to extend temp segment by 128 in tablespace

User416084 Newbie
Currently Being Moderated
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;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2
  • 1. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    sb92075 Guru
    Currently Being Moderated
    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;

    GROUP_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    TEMP_TS_GROUP TEMP
    TEMP_TS_GROUP TEMP2
    >
    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;

    GROUP_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    TEMP_TS_GROUP TEMP
    TEMP_TS_GROUP TEMP2

    what is total size of both TEMP & TEMP2?

    why did you create TEMP2?
  • 2. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    Pavan DBA Expert
    Currently Being Moderated
    you said you have made TEMP2 as default temp tablespace. rather than that you should make entire temp tablespace group as default one. then oracle will use both TEMP tablespaces and you might not have faced this error.

    Now either you need to add space to TEMP2 tablespace or make entire group as default rather than only TEMP2.
  • 3. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    TSharma-Oracle Guru
    Currently Being Moderated
    Post the result of the following:

    SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
    select tablespace_name, sum(bytes/1024/1024) from dba_temp_files group by tablespace_name;
  • 4. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    606331 Explorer
    Currently Being Moderated
    increase the temp tablespace size..it will solve ur issue....

    thanks,
    DBC,
    Sr DBA.
  • 5. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    TSharma-Oracle Guru
    Currently Being Moderated
    @dbc001 : How can be so sure that your suggestion will resolve his issue? Do you know the root cause?
  • 6. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    User416084 Newbie
    Currently Being Moderated
    Default temp tablespace is my tablespace group and below is the details.


    SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

    PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
    -------------------------------- -------------------------------- ------------------------
    DEFAULT_TEMP_TABLESPACE TEMP_TS_GROUP ID of default temporary tablespace


    SQL> select tablespace_name, sum(bytes/1024/1024) from dba_temp_files group by tablespace_name;

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    -------------------------------- ----------------------------------------
    TEMP2 5000
    TEMP 60000
  • 7. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    TSharma-Oracle Guru
    Currently Being Moderated
    Please post these results too:

    select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

    select username,temporary_tablespace from dba_users;
  • 8. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    606331 Explorer
    Currently Being Moderated
    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...

    http://www.dba-oracle.com/t_ora_1652_unable_extend_tips.htm



    thanks,
    DBC,
    Sr DBA,
    OCE.
  • 9. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    TSharma-Oracle Guru
    Currently Being Moderated
    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...

    http://www.dba-oracle.com/t_ora_1652_unable_extend_tips.htm

    thanks,
    DBC,
    Sr DBA,
    OCE. >



    @DBC: So you are trying to say the if any BAD QUERY is asking for 1 TB of temporary space , we should just give 1 TB of space rather than fixing the query? Have you read and tried to understand what OP is trying to resolve?
    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.

    I have seen your other posts and you really should think before you give suggestions to other. The way you give suggestions without understanding could be disastrous to others. You have been mentioned this before by other users in this forum too. And last stop bragging yourself by writing Sr DBA and OCE etc etc. Nobody does that here.
  • 10. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    User416084 Newbie
    Currently Being Moderated
    I have posted this result earlier

    SQL> select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

    TABLESPACE_NAME GROUP_NAME
    ------------------------------ ------------------------------
    TEMP TEMP_TS_GROUP
    TEMP2 TEMP_TS_GROUP

    I have modified your second query as there are 258 rows are there

    SQL> select temporary_tablespace,count(username) from dba_users where TEMPORARY_TABLESPACE='TEMP_TS_GROUP'
    group by temporary_tablespace;

    TEMPORARY_TABLESPACE COUNT(USERNAME)
    ------------------------------ ---------------
    TEMP_TS_GROUP 258
  • 11. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    606331 Explorer
    Currently Being Moderated
    Sharma…its ridiculous....you dont know the cause....i have given just example.....just understand.....

    select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

    select username,temporary_tablespace from dba_users;


    what does above querys will gives ?... what you will do with those results ..it wasting of time of OP........is it above select statement results will resolve the OP's issue

    And last stop bragging yourself by writing Sr DBA and OCE etc etc. Nobody does that here.
    its none of your business......


    thanks,
    DBC,
    Sr DBA,
    OCE

    Edited by: dbc001 on Mar 18, 2013 10:36 PM
  • 12. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    TSharma-Oracle Guru
    Currently Being Moderated
    @dbc: Yes I don't know the cause and that is I am trying to understand by asking more questions to OP. This is a discussion forum where you discuss and try to understand the problem first and give your suggestions.

    If you call yourself a Sr DBA, you should know this but it doesn;t seem like you know this. You have been told by many users in this forum that think before you write. It is just not only me. What I am trying to say is just do not throw your suggestions without understanding the whole situation.

    Sr DBA tag is just not enough, you should start behaving like a Senior DBA.
    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.
  • 13. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    TSharma-Oracle Guru
    Currently Being Moderated
    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.
  • 14. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    606331 Explorer
    Currently Being Moderated
    TSharma wrote:
    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.
    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..?
    did he mentioned any query causing this issue..? or..did you got any Phantasm to give the solution........?


    what he mentioned ......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........ Sharma try to understand OP post 1st then after understand... comment others...

    how can you say bad query cause this rather than space constraint...please explain.....

    dont comment any other...you don't have rights to comments to other...comment the posts......hope you have good experience on oracle but lack of good behavior.....try to learn good behavior first then lecture to others... ...


    thanks,
    DBC,
    Sr DBA,
    OCE.

    Edited by: dbc001 on Mar 19, 2013 8:03 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points