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

    thanks
  • 46. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    899401 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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