1 2 3 4 5 Previous Next 61 Replies Latest reply: Jan 22, 2013 5:23 AM by 899401 Go to original post RSS
      • 31. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
        899401
        output is

        Microsoft Windows [Version 6.0.6002]
        Copyright (c) 2006 Microsoft Corporation. All rights reserved.

        C:\Users\295373>sql
        'sql' is not recognized as an internal or external command,
        operable program or batch file.

        C:\Users\295373>sqlplus

        SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 21 16:55:36 2013

        Copyright (c) 1982, 2007, Oracle. All rights reserved.

        Enter user-name: sys as sysdba
        Enter password:

        Connected to:
        Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options

        SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

        TABLESPACE_NAME BYTES_USED BYTES_FREE
        ------------------------------ ---------- ----------
        TEMP 114294784 0
        TEMP 1048576 103809024
        DEV_IAS_TEMP 1048576 103809024
        DEV1_IAS_TEMP 1048576 103809024

        SQL> select tablespace_name, used_blocks, free_blocks from v$sort_segment;

        TABLESPACE_NAME USED_BLOCKS FREE_BLOCKS
        ------------------------------- ----------- -----------
        TEMP 256 13568

        SQL> select * from v$sort_usage;

        USERNAME USER SESSION_
        ------------------------------ ------------------------------ --------
        SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE
        ----------- -------- ---------- ------------- -------------------------------
        CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
        --------- --------- ---------- ---------- ---------- ---------- ----------
        ADMIN1 ADMIN1 2F04CD90
        25 26B2DAC0 1928208520 8k0hyvttfw848 TEMP
        TEMPORARY DATA 201 4233 1 128 1

        DAC_MES1 DAC_MES1 2F06D250
        2 27AAF804 3205555760 g5sp4jfzj1tjh TEMP
        TEMPORARY LOB_DATA 201 3593 1 128 1

        USERNAME USER SESSION_
        ------------------------------ ------------------------------ --------
        SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE
        ----------- -------- ---------- ------------- -------------------------------
        CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
        --------- --------- ---------- ---------- ---------- ---------- ----------


        SQL> select * from dba_temp_files;

        FILE_NAME
        --------------------------------------------------------------------------------

        FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
        ---------- ------------------------------ ---------- ---------- ---------
        RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
        ------------ --- ---------- ---------- ------------ ---------- -----------
        C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP01.DBF
        1 TEMP 114294784 13952 AVAILABLE
        1 YES 3.4360E+10 4194302 80 113246208 13824

        C:\APP\VSHADMIN\ORADATA\ORCL01\DEV_IASTEMP.DBF
        2 DEV_IAS_TEMP 104857600 12800 AVAILABLE
        1 NO 0 0 0 103809024 12672

        FILE_NAME
        --------------------------------------------------------------------------------

        FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
        ---------- ------------------------------ ---------- ---------- ---------
        RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
        ------------ --- ---------- ---------- ------------ ---------- -----------

        C:\APP\VSHADMIN\ORADATA\ORCL01\DEV1_IASTEMP.DBF
        3 DEV1_IAS_TEMP 104857600 12800 AVAILABLE
        1 NO 0 0 0 103809024 12672

        C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF
        4 TEMP 104857600 12800 AVAILABLE

        FILE_NAME
        --------------------------------------------------------------------------------

        FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
        ---------- ------------------------------ ---------- ---------- ---------
        RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
        ------------ --- ---------- ---------- ------------ ---------- -----------
        2 YES 3.4360E+10 4194302 1 103809024 12672


        SQL>c
        • 32. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
          Fran
          please try to post the result with {} option to get a easy view of data.

          Also post result of:

          sql> SELECT * FROM database_properties WHERE property_name like '%TABLESPACE';

          And you forgot post :

          C:\Users\295373> dir C:\
          • 33. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
            Dafo
            Niket is right,

            check your user's temporary tablespace settings in dba_users, you probably have some user(s) with not existing temporary tablespace. This occurs when you drop one TEMPORARY tableaspace but doesn't correct temporary_tablespace parameter of database users.

            select username,temporary_tablespace from dba_users where upper(temporary_tablespace)='TEMP2';
            then correct user parameter with:
            alter user XXX temporary tablespace YYY;
            • 34. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
              899401
              OUPUT IS

              Microsoft Windows [Version 6.0.6002]
              Copyright (c) 2006 Microsoft Corporation. All rights reserved.

              C:\Users\295373>SQLPLUS

              SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 21 17:14:10 2013

              Copyright (c) 1982, 2007, Oracle. All rights reserved.

              Enter user-name: SYS AS SYSDBA
              Enter password:

              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options

              SQL> SELECT * FROM database_properties WHERE property_name like '%TABLESPACE';

              PROPERTY_NAME
              ------------------------------
              PROPERTY_VALUE
              --------------------------------------------------------------------------------

              DESCRIPTION
              --------------------------------------------------------------------------------

              DEFAULT_TEMP_TABLESPACE
              TEMP
              Name of default temporary tablespace

              DEFAULT_PERMANENT_TABLESPACE
              USERS
              Name of default permanent tablespace

              PROPERTY_NAME
              ------------------------------
              PROPERTY_VALUE
              --------------------------------------------------------------------------------

              DESCRIPTION
              --------------------------------------------------------------------------------



              SQL>
              • 35. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                Fran
                could we know why do you have two temporary tablespaces?
                TEMP and DEV_IAS_TEMP

                default tablespace is TEMP but dev_ias_temp is for test purpose?

                anyway you forgot 2 times to post the result:

                dir C:\

                *at os level, no sqlplus                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                • 36. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                  899401
                  Hi,
                  2nd is for oracle application
                  tahnks
                  • 37. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                    899401
                    Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                    still getting same error message it may be due to some of data loaded in TEMP and as it is full can not load further


                    thanks
                    • 38. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                      sb92075
                      896398 wrote:
                      Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                      still getting same error message it may be due to some of data loaded in TEMP and as it is full can not load further


                      thanks
                      01652, 00000, "unable to extend temp segment by %s in tablespace %s"
                      // *Cause:  Failed to allocate an extent of the required number of blocks for
                      //          a temporary segment in the tablespace indicated.
                      // *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
                      //          files to the tablespace indicated.
                      • 39. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                        899401
                        already done

                        ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' SIZE 100M autoextend on;

                        thanks
                        • 40. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                          sb92075
                          896398 wrote:
                          already done

                          ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' SIZE 100M autoextend on;

                          thanks
                          why does error exist?
                          • 41. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                            899401
                            that i too not understanding
                            • 42. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                              Niket Kumar
                              alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\DEV_IASTEMP.DBF'  autoextend on;
                              alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\DEV1_IASTEMP.DBF'  autoextend on;
                              run above also may be your error get resolved.....
                              • 43. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                                899401
                                these belongs to oracle apps application it may go down

                                thanks
                                • 44. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                                  Niket Kumar
                                  no man the command will not making them goes down....
                                  its just making autoextend on......
                                  it doesn't have any effect but start using your disk space....