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

        thanks
        • 46. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
          899401
          actually,some tables are pointing to temp only as data loaded through that if i can can able to extend that then this problem will may resolve
          errpr message



          thanks
          • 47. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
            899401
            exact error message

            Severity     Timestamp     Node     Thread     Message Code     Message
            ERROR     1/22/2013 12:00:07 PM     node01_Infva05175     READER_1_1_1     RR_4035     SQL Error [
            ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2

            Database driver error...
            Function Name : Execute
            SQL Stmt : SELECT DISTINCT
            DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,
            DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,
            AELINE.ACCOUNTING_CLASS_CODE,
            GLIMPREF.JE_HEADER_ID JE_HEADER_ID,
            GLIMPREF.JE_LINE_NUM JE_LINE_NUM,
            AELINE.AE_HEADER_ID AE_HEADER_ID,
            AELINE.AE_LINE_NUM AE_LINE_NUM,
            T.LEDGER_ID LEDGER_ID,
            T.LEDGER_CATEGORY_CODE LEDGER_TYPE,
            JBATCH.NAME BATCH_NAME,
            JHEADER.NAME HEADER_NAME,
            PER.END_DATE,
            AELINE.CODE_COMBINATION_ID
            FROM XLA_DISTRIBUTION_LINKS DLINK
            , GL_IMPORT_REFERENCES GLIMPREF
            , XLA_AE_LINES AELINE
            , GL_JE_HEADERS JHEADER
            , GL_JE_BATCHES JBATCH
            , GL_LEDGERS T
            , GL_PERIODS PER
            WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN
            ( 'AR_DISTRIBUTIONS_ALL'
            , 'RA_CUST_TRX_LINE_GL_DIST_ALL')
            AND DLINK.APPLICATION_ID = 222
            AND AELINE.APPLICATION_ID = 222
            AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE
            AND AELINE.GL_SL_LINK_ID = GLIMPREF.GL_SL_LINK_ID
            AND AELINE.AE_HEADER_ID = DLINK.AE_HEADER_ID
            AND AELINE.AE_LINE_NUM = DLINK.AE_LINE_NUM
            AND GLIMPREF.JE_HEADER_ID = JHEADER.JE_HEADER_ID
            AND JHEADER.JE_BATCH_ID = JBATCH.JE_BATCH_ID
            AND JHEADER.LEDGER_ID = T.LEDGER_ID
            AND JHEADER.STATUS = 'P'
            AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME
            AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME
            AND JHEADER.CREATION_DATE >=
            TO_DATE('01/01/1970 00:00:00'
            , 'MM/DD/YYYY HH24:MI:SS' )
            AND DECODE('N', 'Y', T.LEDGER_ID, 1) IN (1)
            AND DECODE('N', 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ('NONE')
            Oracle Fatal Error
            Database driver error...
            Function Name : Execute
            SQL Stmt : SELECT DISTINCT
            DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,
            DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,
            AELINE.ACCOUNTING_CLASS_CODE,
            GLIMPREF.JE_HEADER_ID JE_HEADER_ID,
            GLIMPREF.JE_LINE_NUM JE_LINE_NUM,
            AELINE.AE_HEADER_ID AE_HEADER_ID,
            AELINE.AE_LINE_NUM AE_LINE_NUM,
            T.LEDGER_ID LEDGER_ID,
            T.LEDGER_CATEGORY_CODE LEDGER_TYPE,
            JBATCH.NAME BATCH_NAME,
            JHEADER.NAME HEADER_NAME,
            PER.END_DATE,
            AELINE.CODE_COMBINATION_ID
            FROM XLA_DISTRIBUTION_LINKS DLINK
            , GL_IMPORT_REFERENCES GLIMPREF
            , XLA_AE_LINES AELINE
            , GL_JE_HEADERS JHEADER
            , GL_JE_BATCHES JBATCH
            , GL_LEDGERS T
            , GL_PERIODS PER
            WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN
            ( 'AR_DISTRIBUTIONS_ALL'
            , 'RA_CUST_TRX_LINE_GL_DIST_ALL')
            AND DLINK.APPLICATION_ID = 222
            AND AELINE.APPLICATION_ID = 222
            AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE
            AND AELINE.GL_SL_LINK_ID = GLIMPREF.GL_SL_LINK_ID
            AND AELINE.AE_HEADER_ID = DLINK.AE_HEADER_ID
            AND AELINE.AE_LINE_NUM = DLINK.AE_LINE_NUM
            AND GLIMPREF.JE_HEADER_ID = JHEADER.JE_HEADER_ID
            AND JHEADER.JE_BATCH_ID = JBATCH.JE_BATCH_ID
            AND JHEADER.LEDGER_ID = T.LEDGER_ID
            AND JHEADER.STATUS = 'P'
            AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME
            AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME
            AND JHEADER.CREATION_DATE >=
            TO_DATE('01/01/1970 00:00:00'
            , 'MM/DD/YYYY HH24:MI:SS' )
            AND DECODE('N', 'Y', T.LEDGER_ID, 1) IN (1)
            AND DECODE('N', 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ('NONE')
            Oracle Fatal Error].
            • 48. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
              Niket Kumar
              are you able to login into database where these queries are running.....
              because the database you have provided doesn't contains TEMP2 tablespace.....
              • 49. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                899401
                please see previous posts it is nothing but temp
                thanks
                • 50. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                  Niket Kumar
                  you have not provided alert log file yet.....Please provide 200 lines of alert log file....
                  check on google how to find alert log file of database....
                  • 51. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                    899401
                    race file c:\app\vshadmin\diag\rdbms\orcl01\orcl01\trace\orcl01_m000_9696.trc
                    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    Windows NT Version V6.0 Service Pack 2
                    CPU : 4 - type 586, 2 Physical Cores
                    Process Affinity : 0x00000000
                    Memory (Avail/Total): Ph:306M/4095M, Ph+PgF:2656M/8453M, VA:1143M/2047M
                    Instance name: orcl01
                    Redo thread mounted by this instance: 1
                    Oracle process number: 63
                    Windows thread id: 9696, image: ORACLE.EXE (m000)


                    i got this trace file in 'show parameter background_dump_dest
                    *** 2013-01-22 12:30:34.188
                    *** SESSION ID:(90.2960) 2013-01-22 12:30:34.188
                    *** CLIENT ID:() 2013-01-22 12:30:34.188
                    *** SERVICE NAME:(SYS$BACKGROUND) 2013-01-22 12:30:34.188
                    *** MODULE NAME:(MMON_SLAVE) 2013-01-22 12:30:34.188
                    *** ACTION NAME:(Auto-Flush Slave Action) 2013-01-22 12:30:34.188

                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    qerfxGCol:KQFDTTIM - Error converting to LdiDateArray
                    thanks
                    • 52. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                      899401
                      i got the alert log file but it is very huge

                      can u joins this session so that i can show you
                      https://indiafreetrial.webex.com/indiafreetrial/e.php?AT=WMI&EventID=207824787&PW=59e5c3042425135f555c54&RT=MiM0MQ%3D%3D

                      thanks
                      • 53. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                        899401
                        Hi,

                        can someone accept the meeting to understand the issue
                        thanks
                        • 54. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                          Fran
                          NO, if you don't get any answer open a SR.

                          You won't get any correct answer if you don't cooperate. We told you for information that you didn't show like "dir C:\", post alert.log. Both information is necessary.

                          when Niket Kumar told you for alert.log you post trace (why?), when i told you about dir c:\ you ignored me....
                          • 55. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                            899401
                            Please find alert log file
                            thanks

                            Mon Jan 21 10:27:49 2013
                            Thread 1 advanced to log sequence 2104
                            Current log# 1 seq# 2104 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Mon Jan 21 10:31:05 2013
                            Thread 1 advanced to log sequence 2105
                            Current log# 2 seq# 2105 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Mon Jan 21 10:35:27 2013
                            Thread 1 advanced to log sequence 2106
                            Current log# 3 seq# 2106 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Mon Jan 21 10:36:37 2013
                            Thread 1 advanced to log sequence 2107
                            Current log# 1 seq# 2107 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Mon Jan 21 10:37:16 2013
                            Thread 1 advanced to log sequence 2108
                            Current log# 2 seq# 2108 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Mon Jan 21 10:39:01 2013
                            Thread 1 advanced to log sequence 2109
                            Current log# 3 seq# 2109 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Mon Jan 21 10:45:24 2013
                            Thread 1 advanced to log sequence 2110
                            Current log# 1 seq# 2110 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Mon Jan 21 10:52:57 2013
                            Thread 1 advanced to log sequence 2111
                            Current log# 2 seq# 2111 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Mon Jan 21 11:00:25 2013
                            Thread 1 advanced to log sequence 2112
                            Current log# 3 seq# 2112 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Mon Jan 21 11:01:31 2013
                            Thread 1 advanced to log sequence 2113
                            Current log# 1 seq# 2113 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Thread 1 cannot allocate new log, sequence 2114
                            Private strand flush not complete
                            Current log# 1 seq# 2113 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Thread 1 advanced to log sequence 2114
                            Current log# 2 seq# 2114 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Mon Jan 21 11:01:46 2013
                            Thread 1 advanced to log sequence 2115
                            Current log# 3 seq# 2115 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Mon Jan 21 11:08:26 2013
                            Thread 1 advanced to log sequence 2116
                            Current log# 1 seq# 2116 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Mon Jan 21 11:16:33 2013
                            Thread 1 advanced to log sequence 2117
                            Current log# 2 seq# 2117 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Thread 1 advanced to log sequence 2118
                            Current log# 3 seq# 2118 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Mon Jan 21 11:16:44 2013
                            Thread 1 advanced to log sequence 2119
                            Current log# 1 seq# 2119 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Mon Jan 21 11:25:53 2013
                            Thread 1 advanced to log sequence 2120
                            Current log# 2 seq# 2120 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Mon Jan 21 12:10:00 2013
                            Thread 1 advanced to log sequence 2121
                            Current log# 3 seq# 2121 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Mon Jan 21 12:56:51 2013
                            alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP01.DBF' autoextend on
                            Completed: alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP01.DBF' autoextend on
                            Mon Jan 21 12:58:04 2013
                            Thread 1 advanced to log sequence 2122
                            Current log# 1 seq# 2122 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Mon Jan 21 15:07:56 2013
                            Thread 1 advanced to log sequence 2123
                            Current log# 2 seq# 2123 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Mon Jan 21 15:30:00 2013
                            Clearing Resource Manager plan via parameter
                            Mon Jan 21 15:36:58 2013
                            ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' SIZE 100M autoextend on
                            Completed: ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' SIZE 100M autoextend on
                            Mon Jan 21 16:06:42 2013
                            Thread 1 advanced to log sequence 2124
                            Current log# 3 seq# 2124 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Mon Jan 21 16:58:44 2013
                            Thread 1 advanced to log sequence 2125
                            Current log# 1 seq# 2125 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Mon Jan 21 17:24:58 2013
                            Thread 1 advanced to log sequence 2126
                            Current log# 2 seq# 2126 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Mon Jan 21 18:30:49 2013
                            Thread 1 advanced to log sequence 2127
                            Current log# 3 seq# 2127 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Tue Jan 22 05:29:46 2013
                            Thread 1 advanced to log sequence 2128
                            Current log# 1 seq# 2128 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            Tue Jan 22 11:30:00 2013
                            Setting Resource Manager plan SCHEDULER[0x2C09]:DEFAULT_MAINTENANCE_PLAN via scheduler window
                            Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
                            Tue Jan 22 11:30:03 2013
                            Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
                            Tue Jan 22 11:30:05 2013
                            Tue Jan 22 11:30:05 2013
                            Logminer Bld: Lockdown Complete. DB_TXN_SCN is UnwindToSCN (LockdownSCN) is 49925591
                            Tue Jan 22 11:31:33 2013
                            Thread 1 advanced to log sequence 2129
                            Current log# 2 seq# 2129 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO02.LOG
                            Tue Jan 22 11:37:36 2013
                            End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
                            Tue Jan 22 11:58:00 2013
                            alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\DEV_IASTEMP.DBF' autoextend on
                            Completed: alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\DEV_IASTEMP.DBF' autoextend on
                            Tue Jan 22 12:05:44 2013
                            Thread 1 advanced to log sequence 2130
                            Current log# 3 seq# 2130 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO03.LOG
                            Tue Jan 22 12:17:58 2013
                            alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP01.DBF' autoextend on
                            Completed: alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP01.DBF' autoextend on
                            alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP01.DBF' autoextend on
                            Completed: alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP01.DBF' autoextend on
                            Tue Jan 22 12:18:12 2013
                            alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' autoextend on
                            Completed: alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' autoextend on
                            Tue Jan 22 12:18:29 2013
                            ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' SIZE 1000M autoextend on
                            ORA-1537 signalled during: ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' SIZE 1000M autoextend on...
                            Tue Jan 22 12:20:31 2013
                            ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' SIZE 1000M autoextend on
                            ORA-1537 signalled during: ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' SIZE 1000M autoextend on...
                            Tue Jan 22 12:20:49 2013
                            alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' autoextend on
                            Completed: alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' autoextend on
                            alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' autoextend on
                            Completed: alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' autoextend on
                            alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' autoextend on
                            Completed: alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP02.DBF' autoextend on
                            Tue Jan 22 12:48:07 2013
                            Thread 1 advanced to log sequence 2131
                            Current log# 1 seq# 2131 mem# 0: C:\APP\VSHADMIN\ORADATA\ORCL01\REDO01.LOG
                            • 56. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                              Fran
                              ORA-01537
                              Error Description: cannot add file string – file already part of database
                              Error Cause: During CREATE or ALTER TABLESPACE, a file being added is already part of the database.
                              How to Solve: Use a different file name.

                              You can't use the same name for an existing datafile
                              • 57. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                                899401
                                is it ok
                                alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\TEMP03.DBF' autoextend on;

                                but when i fired the statement

                                select TEMPORARY_TABLESPACE from dba_users where username='DATAWAREHOUSE';

                                output
                                temp
                                only
                                atlest it should point to temp02,03 etc

                                how to assign temp02 to datawarehouse?



                                thanks

                                Edited by: 896398 on Jan 22, 2013 1:29 AM

                                Edited by: 896398 on Jan 22, 2013 1:30 AM
                                • 58. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                                  Fran
                                  you assign a temporary tablespace, you can't assign a datafile.

                                  tablespace_name = TEMP
                                  datafiles= TEMP01.DBF, TEMP02.DBF and TEMP03.DBF
                                  • 59. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
                                    899401
                                    Hi,

                                    can u accept this requst to analyze
                                    https://indiafreetrial.webex.com/indiafreetrial/e.php?AT=WMI&EventID=207824787&PW=59e5c3042425135f555c54&RT=MiM0MQ%3D%3D