Re: Is it possible to drop temp tables owned by SYS without restart the database?

On Tue, 16 Dec 1997 13:26:02 +0000, Ian Cary <cary_at_gatwick.geco-prakla.slb.com> wrote:

>Sanjay Mathew wrote:
>
>> David Shi wrote:
>> >
>> > Some times when a job dead, Oracle don't drop those temp tables (table
>> > names like 2.12 etc), I usually follows a book says, just restart the
>> > database, but this is very inconvenient, is there another way to do
>> > that? I tryed something like "drop table 2.12;" which won't work.
>> >
>> > Thanks.
>> Try drop table "2.12;";
>
>I think you'll find that drop table "2.12" also doesn't work because these
>are actually temporary segments and not temporary tables. There have been
>one or two bugs with Oracle not droppping temporary segments correctly over
>the years.
>
>The latest one is on 7.3.3 where temporary segments created in a tablespace
>defined as temporary sometimes do not get cleared down.
>The answer to this one is to issue the command;

It's my understanding now, that that's the way temporary tablespaces are supposed to work. The segments are purposely left out there to reduce activity on seg$, fet$, and uet$. Check out v$sort_segment too. It documents how the temp segment's been used.

Also, it's SMON's job to do this on non-temporary tablespaces. PMON just monitors the other background processes.

--
Chuck Hamilton
chuckh_at_dvol.com

"Therefore the Lord Himself will give you a sign:
Behold, a virgin will be with child, and bear a son,
and she will call His name Immanuel." (Isa 7:14 NASB)