1 2 Previous Next 17 Replies Latest reply: Mar 19, 2013 1:05 AM by TSharma-Oracle RSS

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

    User416084
      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
          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
            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
              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
                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
                  @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
                    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
                      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
                        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
                          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
                            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
                              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
                                @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
                                  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
                                    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