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 220.127.116.11.
Your suggesstions most welcome.
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.
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.
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
I am herewith putting down the Statment I am using for creation of the Table:
CREATE Table TEST
Am I using anything wrong in the above statment.
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.
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)
Given below is the statment for creation of the table:
CREATE Table TEST
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,
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,
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'
We can bounce the Instance, But what's the way out if its a High Availability database where i cannot bring down my Instance.
1) Temporarily, add another datafile.
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
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.
Click Here to Expand Forum to Full Width