Urgent Issue on temporary tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Urgent Issue on temporary tablespace

  1. #1
    Join Date
    May 2001
    Posts
    57
    Hi All,
    One of my developers was trying to run his stored proc from the backend and the stored proc was just taking a lot of time and just gang indefinitely. Using OEM I found that this particular session from the developer was running a hash join and categorised it as Long Operation. The same stored procedure runs excellent in one test environment as well as production. This happens only in the development environment. The error I got is
    ORA-03232: unable to allocate an extent of 58 blocks from tablespace 3
    ORA-06512: at "IDBOWNER.SOPVCCONFIG", line 185
    ORA-06512: at line 1

    I then thought of increasing hash_multiblock_io_count and increased it with the statement
    alter system set hash_multiblock_io_count = 1;

    But it again bombed with
    ORA-01652: unable to extend temp segment by 32 in tablespace TEMP
    ORA-06512: at "IDBOWNER.SOPVCCONFIG", line 185
    ORA-06512: at line 1

    I do have enough free space on my tablespace 3 named temp.
    Can any one of you here please tell me how to go about this situation. Can you also provide me with literature on practical aspects of tuning. I tried to read the documentation but didnot make much use for me. Please advice or comment or suggest.

  2. #2
    Join Date
    Dec 2001
    Posts
    221
    trace your procedure, explain plan your queries, see whether proper indexes are created. otherwise increase the size of temp tablespace
    Santosh Jadhav
    8i OCP DBA

  3. #3
    Maybe the question looks stupid, but I'll ask it:
    Are you sure tablespace 3 is the temp tablespace, not the UNDO tablespace?


    [Edited by ovidius on 04-05-2002 at 07:47 AM]
    ovidius over!

  4. #4
    Join Date
    May 2001
    Posts
    57

    Wink

    I can bet on it. Below is the output of the query select * from v$tablespace; is

    TS# NAME
    ---------- ------------------------------
    0 SYSTEM
    6 IDB
    2 RBS
    3 TEMP
    5 AUD
    4 INDX

    6 rows selected.

    Does it make sense atleast by now.


  5. #5
    So, the two errors was on the same tablespace, TEMP.
    Next question:
    What do you need, to solve the problem, to understand what happens(, or both)?
    I think incresing the tablespace will solve the problem punctually. But if this even happens, never knows when it will occur again, neither why.

    Hey, guys, a helping hand - brain - is needed here, gurus... I'm not able to think and realise theoretically why is this happening... Sorry!
    ovidius over!

  6. #6
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Hi guys I too got the similar problem in my tempfile research.
    Well I solved it by increasing the space in temp tablespace.
    Well,Your tablespace temp is a temporary tablespace or the tablespace used for temporary.Hope you have understood the
    diff between temporary tablespace and tablespace temporary.
    It was one of my research in tempfile concept.Increase the size it will solve your problem.
    Thanigaivasan.

  7. #7
    Join Date
    May 2001
    Posts
    57

    Angry

    I have my temp tablespace sized to 1Gb. The instance is running on solaris 2.6 on ultra sparc, oracle 8.1.6. Iam still getting the same error even after getting increasing the size of the temp tablespace. I think the moderator's hand is needed in this. Can anyone of you throw some light on this!!!!!
    ERROR at line 1:
    ORA-03232: unable to allocate an extent of 58 blocks from tablespace 3
    ORA-06512: at "APPOWNER.PROCNAME", line 185
    ORA-06512: at line 1

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by dba_akram
    I have my temp tablespace sized to 1Gb. The instance is running on solaris 2.6 on ultra sparc, oracle 8.1.6. Iam still getting the same error even after getting increasing the size of the temp tablespace. I think the moderator's hand is needed in this. Can anyone of you throw some light on this!!!!!
    ERROR at line 1:
    ORA-03232: unable to allocate an extent of 58 blocks from tablespace 3
    ORA-06512: at "APPOWNER.PROCNAME", line 185
    ORA-06512: at line 1
    Do one of the following, your choice:

    1. Decrease the value of HASH_MULTIBLOCK_IO_COUNT. What is it set to now?

    2. Increase NEXT for the TEMP tablespace. Run:

    Code:
    ALTER TABLESPACE TEMP DEFAULT STORAGE(NEXT ...);
    MTS or not?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    May 2001
    Posts
    57
    The current setting for hash_multiblock io_count is 0.
    SQL> show parameter hash

    NAME TYPE VALUE
    ------------------------------------ ------- ------------------------------
    hash_area_size integer 6291456
    hash_join_enabled boolean TRUE
    hash_multiblock_io_count integer 0

    The following query gives you more info:
    SQL> select initial_extent, next_extent, min_extents, max_extents, pct_increase,
    2 min_extlen, status
    3 from dba_tablespaces where tablespace_name='TEMP';

    INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS
    -------------- ----------- ----------- ----------- ------------ ---------- ---------
    262144 262144 2 5 262144 ONLINE

    This satisfies the condition already that NEXT > hash_multiblock_io_count
    I think it is MTS. Can you tell me how to confirm it?

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Oracle computes the value of hash_multiblock io_count for every query. If Oracle does the automatic computation of hash_multiblock io_count , then the value is 0 in V$PARAMETER.

    I suggest you recreate the TEMP tablespace with a bit bigger INITIAL and NEXT. Drop TEMP and run:

    Code:
    CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/datadg/disk22/temp_2.dbf' 
    SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    so that you replace '/datadg/disk22/temp_2.dbf' with your own directory.


    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width