Printable View
How can I make the temp tablespace free up.
User ran a query last night and has taken 80 % of tablespace. but temp tablespace has not been freed up. If you restart the database the temp tablespace gets freed up, but is there any other way how a tablespace can free up.
Bet you're looking at the temp space through OEM aren't you.
Adding to above, if the user runs one more create table as selct query, it would take 60% more. User is going to get an Error when the temp tablespace is filled. I want to free up the temp tablespace. is there any way I can do that??
When i issue below command I see free space 100%
select SUBSTR(SYSDATE,12) "TIME",Current_Users, Total_Blocks, Used_Blocks, Free_Blocks,
Round(100 * free_blocks / total_blocks,2) "% FREE"
from V$SORT_SEGMENT;
But if my freespace was 20% then what would be solution. I have my PCTINCREASE As 100 and initial and next 8mb.
I am looking at temp space through TOAD.
create a new one and drop the old.
create a new one and drop the old.
That cannot be solution every time and I guess thats not correct practice.
twmp will always show as full
are you getting unable to extend errors?
I am getting unable to extentd error
ORA-1652: unable to extend temp segment by 1024 in tablespace TEMP
How big is tablespace TEMP? Can you increase it's size?
Has the users CTAS sql got a cartesian join in it?
Tablespace is 8g.
User is issuing below query.
create table wilson2a
as select cpolicypfx, cpolicyno, criskstate, cpecode, nclaimoffc, nclaimno, nclaimsuff,
nrtline, NOCCURDATE, NCMADEDATE, CMINORCO, cmajorline,CBORISCODE, CFILLER1, creinsco, caccountid,
sum(nvl(npayment,0)) npayment,sum(nvl(nreserve,0)) nreserve,sum(nvl(npaidtodat,0)) npaidtodat,
sum(nvl(nopencount,0)) nopencount, sum(nvl(ncwpcount,0)) ncwpcount,
sum(nvl(ncnpcount,0)) ncnpcount
from homesupp a, homemain b where cpolicyno in
('1951726','5969327','9086977','9130806','9131869','9170162','9170171','9170223','9195863','9196064' ,
'9196103','9259702','9259743','9259750','9259783','9259786','9259795','9259859','9259865','9259926',
'9259965','9260213','9260516','9260517','9260521','9260529','9260563','9260570','9260577','9260700',
'9260719','9260762','9260770','9260771','9260810','9260816','9260841','9260853','9260857','9260886',
'C148574','C148579','C148581','C148582','C148588','C148596','C148597','C148601','C148611','C148642')
and a.nrecnum=b.nrecnum
group by cpolicypfx, cpolicyno, criskstate, cpecode, nclaimoffc, nclaimno, nclaimsuff,
nrtline, NOCCURDATE, NCMADEDATE, CMINORCO, cmajorline, CBORISCODE, CFILLER1, creinsco, caccountid;
Do you have an estimate of how large the wilson2a table is going to be?
Also, post an explain plan for the query, and don't forget to use code tags ...
[ code ]
place code here
[ /code ]
(Take out the spaces to get the tags to work)
Also, "sum(nvl(npayment,0))" is probably better as "Nvl(sum(npayment),0)"
What is oracle release?
How big the tables are?
Since, 16 columns in group by clause, it may need a huge temp space.
Increase sort_area_size at the session level to match the extent size of temp tablespace. This may solve your problem.
You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed.
Tamil
The table is going to be with 6000 rows. HOMEMAIN and HOMESUPP tables has 60 millions rows each.
[CODE ]
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION
-------------------------------------------------------
CREATE TABLE STATEMENT ~ ~ 0 ~ 460400
LOAD AS SELECT ~ ~ 1 0 1
SORT GROUP BY ~ 2 1 1
MERGE JOIN ~ ~ 3 2 1
SORT JOIN ~ 4 3 1
TABLE ACCESS FULL HOMESUPP 5 4 1
SORT JOIN ~ 6 3 2
TABLE ACCESS FULL HOMEMAIN 7 6 1
[ END CODE]
ORACLE 8.1.7.4
upgradeed from 7.3 using export/ import.
You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed.
Did you do this?
Tamil
create a new one and drop the old.
That cannot be solution every time and I guess thats not correct practice.
create a new one and drop the old.
This is the only solution and best practice.
Temperary tablespace is not allowed to get shrinked because oracle has to do extra processing to allocate extents everytime. It is made this way so that oracle don need to deaalocate and reallocate extent everytime.
==
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION
-------------------------------------------------------
CREATE TABLE STATEMENT ~ ~ 0 ~ 460400
LOAD AS SELECT ~ ~ 1 0 1
SORT GROUP BY ~ 2 1 1
MERGE JOIN ~ ~ 3 2 1
SORT JOIN ~ 4 3 1
TABLE ACCESS FULL HOMESUPP 5 4 1
SORT JOIN ~ 6 3 2
TABLE ACCESS FULL HOMEMAIN 7 6 1
==
The problem is with MERGE JOIN.
You need to create indexes on the column cpolicyno.
Also another index on nrecnum.
Post the explain plan here.
The plan should not go for MERGE JOIN.
Tamil
I ran same explain plan again and I did not see Merge operation.
Both tables have indexes on nrecnum.
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION
------------------------ ---------- ---------- ----------
CREATE TABLE STATEMENT ~ ~ 0 ~ 18086
LOAD AS SELECT ~ ~ 1 0 1
SORT GROUP BY ~ 2 1 1
NESTED LOOPS ~ ~ 3 2 1
TABLE ACCESS FULL HOMESUPP 4 3 1
TABLE ACCESS BY INDEX ROWID HOMEMAIN 5 3 2
INDEX NIQUE SCAN I_HOMEMAIN_NRECNUM 6 5 1
You need to create indexes on the column cpolicyno of HOMESUPP table.
Tamil
You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed
select shows 5075 rows wihtout group by. It took 30 minutes to run the select statement.
Do you have an index on the column cpolicyno of HOMESUPP table?
Tamil
I am creating index now.
Thanks
I don't think so -- the space requirement is related more to the size of the output from the grouping than it is to the number of ungrouped rows.Quote:
Originally posted by tamilselvan
You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed.
For example,
... is going to require very little sort space, right?Code:Select Sum(my_num)
From table_with_million_rows
Slimdave,
It is not the group by clause causing the problem b/c the result set after joining 2 big tables is small. MERGE JOIN operation is causing real problem of filling out the temp space. I am trying to eliminate MERGE JOIN by adding an INDEX.
Tamil
OK I see ... well I'd think that a hash join would be worth trying also.Quote:
Originally posted by tamilselvan
Slimdave,
It is not the group by clause causing the problem b/c the result set after joining 2 big tables is small. MERGE JOIN operation is causing real problem of filling out the temp space. I am trying to eliminate MERGE JOIN by adding an INDEX.
Tamil
srt, do you have hash joins enabled in your db?
ok, why dont you run the sql statement alone and see how it performs.
For starters, you seem to be using
but you dont seem to be using this reference a or b for the column names in question.Code:from homesupp a, homemain b where cpolicyno
Worked fine with creating an index on the column.
Assuming that none of the extents are currently used, you can issue the "alter tablespace" command below to clean up temp.
alter tablespace temp
default storage (pctincrease 0);
This will tell smon to clean up the segments in the tablespace that are not used.
Change your temp tablespace to a locally managed tablespace