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
  • 30. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    899401 Newbie
    Currently Being Moderated
    result is
    TEMP
  • 31. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    899401 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,
    2nd is for oracle application
    tahnks
  • 37. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    899401 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    that i too not understanding
  • 42. Re: ORA-1652: unable to extend temp segment by 128 in tablespace
    Niket Kumar Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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....

Legend

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