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

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;

alter tablespace <temp> permanent;

This should cause PMON to clear down the temporary segments fairly swiftly after which you can issue the command;

alter tablespace <temp> temporary;

to prevent people from creating 'real' segments in that tablespace.

Hope this helps,

Ian Received on Tue Dec 16 1997 - 00:00:00 CST