Unable to extend Temp tablespace.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Unable to extend Temp tablespace.

  1. #1
    Join Date
    Apr 2001
    Posts
    5

    Red face

    I am creating a table using a Select Statment. I get an error saying "Unable to extend segments 64 for the tabllespace TEMP"

    When i see the free space in the Temp Tablespace from the SQL Navigator, it shows as 224k whereas the our Administrator says that it's about 2 Gig in size.

    Last friday, it showed me the free space in Temp Tablespace as 100 Meg and after running the statment, it now shows me as only 224k.

    Does'nt Oracle free up the Temp table space after its usage or we have to use any external command to free the used space.

    I am using Oracle 8.1.7.0.

    Your suggesstions most welcome.
    Jr Datla

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Sometimes Oracle performs some sorting in the temp ts. This makes the ts look in use all the time. It's a good idea to set Autoextend On for the temp ts, so that queries dont fail. Bouncing the db will free up the space used by Oracle's sorting in the temp ts.

  3. #3
    Join Date
    Apr 2001
    Posts
    5

    Talking

    Thank you for your reply.

    I have the AutoExtend ON on Temp TS, but still i get the following error when i create a table using the select statment. Select statment returns around 1/2 million records.

    Error:
    ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
    Jr Datla

  4. #4
    Join Date
    Apr 2001
    Posts
    5

    Talking

    I am herewith putting down the Statment I am using for creation of the Table:

    CREATE Table TEST
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    TABLESPACE MART_DATA01
    STORAGE (
    INITIAL 131072
    NEXT 131072
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    )
    as
    Select Statment..........................................................

    Am I using anything wrong in the above statment.
    Jr Datla

  5. #5
    I have had these troubles too, It seems like Oracle can't reuse (sometimes) the temporary space.
    I have read Oracle don't waste its time freeing extents from temporary tablespaces, but they have to get better their implementation.
    Ramon Caballero, DBA, rcaballe@yahoo.com

  6. #6
    Please put the select statement because your problem is on TEMP tablespace then it's because sorting/grouping, the temporary segments that will be the TABLE are actually in the tablespace the TABLE will be on.
    The only way I have been able to free space on TEMP has been bouncing the instance (shutdown/startup)
    Ramon Caballero, DBA, rcaballe@yahoo.com

  7. #7
    Join Date
    Apr 2001
    Posts
    5

    Talking

    Given below is the statment for creation of the table:

    CREATE Table TEST
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    TABLESPACE MART_DATA01
    STORAGE (
    INITIAL 131072
    NEXT 131072
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    )
    as
    select
    m.id,
    ip.inv,
    ip.agency,
    ip.category_name ,
    r.remit,
    i.bank ,
    s.state,
    m.Acq,
    DECODE((m.automatic_draft_ind),0,'No Coupons',1,'Coupon Cards',4,'Bills',6,'Drafting',
    7,'Monthly Billing statement',8,'Bill and Receipt',9,'Bill and Receipt/late charge','Others') payment_type,
    t.month_name,
    o.description,
    f.description,
    DECODE(NVL(h.first_principal_bal,0),0,0,1),
    DECODE(NVL(h.previous_upb,0),0,0,h.previous_upb),
    a.Esest,
    h.esunt,
    h.esce,
    a.esings,
    a.serFee,
    a.guFee,
    a.lendeLMPI,
    a.piings,
    a.pings,
    a.pinings,
    a.buyings,
    a.susings,
    a.resAmt,
    a.totice,
    a.forCost,
    a.esst,
    a.pist,
    a.rest,
    a.thirdCost,
    a.ppayrest,
    a.unrest,
    a.epbofit,
    a.nsfee,
    a.lateted,
    a.anclcome,
    a.optunt,
    a.paycome,
    a.origAmt,
    a.hoount,
    a.aecome
    from a.mortan m, a.loist h, a.actlow a, a.geo g, a.perty p, a.vestor i, a.dule r, a.rogra ip, a.orig o, a.stat s, a.tim t,
    b.fico f
    where m.loan_id = h.loan_id
    and h.time_dt = t.lin_mon
    and m.mrtg = a.mrtg
    and a.time_dt = t.lin_mon
    and m.propid = p.propid
    and p.geokey = g.geokey
    and m.inveid = i.inveid
    and i.invprog = ip.invprog
    and i.remid = r.remid
    and g.state = s.state
    and m.orig = o.orig
    and m.origital = f.origre
    and t.time_dt = '31-JUL-00'
    Jr Datla

  8. #8
    Join Date
    Apr 2001
    Posts
    5

    Smile

    We can bounce the Instance, But what's the way out if its a High Availability database where i cannot bring down my Instance.

    Any Suggesstions????
    Jr Datla

  9. #9
    Join Date
    Feb 2001
    Posts
    389
    1) Temporarily, add another datafile.

    2) Later,

    a) Drop the temporary tablespace
    Create temporary tablespace
    1) Dictionary managed , specify temporary keyword and make initial extent a big one may be 1GB.

    2) Locally managed temporary tablespace.

    b) Fine tune your query, increase sort_area_size ( need to verify whether sorting is causing any Disk level i/o).

    c) Why are you not using view.


    You must have some way (disabling monitoring/remove node ) to bring Database in maintainenc mode

    Take Care
    GP

  10. #10
    Join Date
    Oct 2000
    Posts
    211
    Hi,
    Just another suggestion. It is better to have two equally sized temporary tablespaces in some situations, so that when one gets highly fragmented, it can be recreated while the second one stays online.
    Coming back to the present situation, check up the fragmentation, and if that is the culprit then create another temporary tablespace of required size, assign the users to this,and then drop/recreate the earlier as per your requirement.
    HTH
    manjunath

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