Is their any way of identifying which user/process is holding onto a temporary segment. I've got a 50Mb temporary tablespace and there is a temporary segment which is 47Mb which is not being freed up by SMON.
And, if i can't identify who is holding onto this TEMP segment, is their a way in whichI
Title: RE: Oracle TEMPORARY SEGMENT
Hi,
This is what I use to clean up a temp tablespace. The tablespace is named 'temp' and it is normally set to PCTINCREASE of 10. You will need to insert the correct values for your temp tablespace. Altering the tablespace a little wakes us SMON which
Whittle Jerome Contr NCI,
I think your sql should not work on a really TEMPORARY tablespace, and
temporary tablespace do not need to be cleaned either.
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(China Oracle User Group)
=== 2003-02-11 06:49:00
Hi Jerry, I tried your suggestion of altering the pctincrease to a non-zero value, although the original temp segment has disappeared from the dba_segments. I now have a different temp segment which is lingering around in dba_segments.
Zabair
Whittle Jerome Contr NCI [EMAIL PROTECTED] wrote:
Hi,
Hello Zabair,
Tuesday, February 11, 2003, 6:03:42 AM, you wrote:
ZA Is their any way of identifying which user/process is holding onto a temporary
segment.
Try this: http://www.geocities.com/oraperf/tempseg/
--
Best regards,
Sergeymailto:[EMAIL PROTECTED]
--
Title: RE: RE: Oracle TEMPORARY SEGMENT
Hi,
It does work on temporary (that are truly temporary and not permanent) tablespaces in 7.3.4 and 8.1.7. I use it when I don't want to wait for SMON to clean things up. Just yesterday one our 'power users' ran SQL that selected from two tables
Jerry,
Your sql will never run on Temp tablespace 'that are truly temporary' in
8.1.7
Just see the results
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
SQL alter tablespace temp default
Title: RE: RE: Oracle TEMPORARY SEGMENT
Rafiq,
I'm not using locally managed tablespaces just yet. If the definition of 'truly temporary' includes locally managed tablespace, then you are correct.
Thanks for keeping me on the straight and narrow.
Jerry Whittle
ASIFICS DBA
NCI
Jerry,
It was just for update. True temporary tablespace cannot be created using
datafile(dictionery managed) but tempfile which is allowable by locally
managed tablespace. I just tried to create it with datafile but it gave me
error.
Here, on this list we are learning everyday something new
Whittle Jerome Contr NCI,
1.Temporary tablespace in oracle8i+ does not need coalesce, and Oracle do not
deallocate used sort extents. This is a feature.
2.There is no pctincrease parameter in TEMPORARY tablespace.
SQL /
create temporary tablespace test_tmp tempfile
10 matches
Mail list logo