Re: tempspace usage
- From: Yongping Yao <yaoyongping@xxxxxxxxx>
- To: jame tong <jametong@xxxxxxxxx>
- Date: Wed, 20 Apr 2005 23:31:23 +0800
I've used partition though every partition occupies about 12GB. And I
subpartitioned :(
Online switch the undo tablespace is a good idea. i agree with you
that SMON cleans UNDO until some other transactions request undo
space. But I still got "cannot extend" error once. It cleaned too
slow?
BTW, I know that when the transaction is running I can link the undo
objects to its SQL using V$ views. But during the transaction
committed and the UNDO space cleaned, how can I get the SQL? I mean
how can I name which transaction or session used up the UNDO and
thougth it has been committed SMON hasn't clean it out.
On 4/20/05, jame tong <jametong@xxxxxxxxx> wrote:
> comment in line.
>=20
> On 4/20/05, Yongping Yao <yaoyongping@xxxxxxxxx> wrote:
> > Well, some statements cannot be tuned more. In our design, these kind
> > of operations are rare (delete the duplicate rows and due to the load
> > speed we do not use unique index). But I did use them these days since
> > the application did something wrong :(
> if your table is very big , then use partition table will be a must.
> then you can use exchange partition to minimize the undo with it.
> delete from a big table is a bad idea.
>=20
> > And by the way, SMON clean the undo tablespace? It also has some
> > delay. Some transanctions are commited and they still occupy the undo
> > space. Can it be solved? Or just add more space to the undo
> > tablespace?
>=20
> if you have a undo tablespace that extended beyond your control. then
> create a new undo tablespace . and change the undo tablespace online
> may be a better solution than to wait the SMON to clean then undo
> tablespace.
>=20
> within my technical scope. if there were no other transaction request
> the undo from the undo tablespace , oracle will not release this undo
> segment.
>=20
> > On 4/20/05, jame tong <jametong@xxxxxxxxx> wrote:
> > > I think you tune the sql statement to minimize the temp space usage.
> > >
> > >
> > > On 4/20/05, Yongping Yao <yaoyongping@xxxxxxxxx> wrote:
> > > > I have a related question. After I run a statement consuming the
> > > > temporary tablespace a lot and commit the transaction, the tablespa=
ce
> > > > is not cleaned soon. Then other sessions got an error something lik=
e
> > > > "can't extend ...in ..." which means the temporary tablespace is no=
t
> > > > enough. Is there something wrong with the background process?
> > > > Do I have to use a separate temporary tablespace or set the tempora=
ry
> > > > tablespace autoextend (which I think is not safe and hard to contro=
l
> > > > the datafile size)?
> > > >
> > > > --
> > > > Yao Yongping
> > > > Learning Oracle, UNIX/Linux...
> > > > Love Reading, Classical Music, Philosophy, Economics etc.
> > > > Blog: http://blog.csdn.net/
> > > >
> > >
> >
> > --
> > Yao Yongping
> > Learning Oracle, UNIX/Linux...
> > Love Reading, Classical Music, Philosophy, Economics etc.
> > Blog: http://blog.csdn.net/
> >
>=20
--=20
Yao Yongping
Learning Oracle, UNIX/Linux...
Love Reading, Classical Music, Philosophy, Economics etc.
Blog: http://blog.csdn.net/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
