Re: Help! Urgent! Database incredibly grown.

We came across the same problem too. The strange thing is that although we assigned the temporary tablespace to a user who made the changes of the original tablespace (using SQL*Loader) Oracle creates some temporary segment in this original tablespace (NOT TEMPORARY!). What is worse is that Oracle doesn't drop these segments after the operation (loading data) ends.

Any suggestions how to avoid that?

Regards,
Arkady

In article <35DEE1F9.B4BF0394_at_sybrandb.demon.nl>, Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

> Danil,
>
> I'm more or less guessing now, you need to check out a few things before
> something more definitive can be said. The guess is
> big sort going, sort results in a so called temporary segment
> temporary segment will be created in any tablespace, preferably in it's own
> tablespace. The tablespace which will get the temporary segment is determined
> by the users temporary tablespace setting.
> Secondly, the tablespace where this happened in is an autoextend tablespace.
> Then the strange thing: 'old-style' temporary segments are dropped
> automatically. Of course the extra storage will not be released. In this case
> your remark about a temporary segment is strange. If the temporary segments
> are in a tablespace designated as a temporary tablespace, temporary segments
> are created in more or less the same fashion as rollback segments.
> Now at least we will need you to issue the following select:
> select segment_name, tablespace_name from dba_segments
> where segment_type = 'TEMPORARY'
> Hopefully this is the default tablespace called TEMP. If this is true, simply
> drop it, and create a new smaller one, and disable autoextend. If this is not
> true, you will need to run queries on dba_tablespaces first to determine
> where the storage was allocated and respond.
>
> Hope this helps,
>
> Sybrand Bakker
>
> Danil Krasnov wrote:
>
> > Hello, gurus!
> >
> > Usually my database takes about 270 Mbytes of diskspace.
> > Today something happend (don't know what) and database grown up to 700
> > MBytes.
> >
> > Tablespace manager shows that about 500 Mb takes sys's extent of
> > 'temporary' type.
> >
> > What could be a reason and what I have to do now to reduce database size?
> >
> > Please help..
> >
> > Thanks in advance.
> >
> > Danil Krasnov
> > danil_at_maginfo.net
Received on Mon Aug 24 1998 - 00:00:00 CDT