OraFAQ Forum: Performance Tuning » Undo Tablespace Issue

Home » RDBMS Server » Performance Tuning » Undo Tablespace Issue - Archiving Case

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Undo Tablespace Issue - Archiving Case [message #111270]

Tue, 15 March 2005 03:27

kumarsz
Messages: 180
Registered: March 2005
Location: canada

Senior Member

Hi,
we are trying to archive data from our production server to archive server
The retention period is 24 months.

these are basically payment claims for servicing of parts
we refer group of claims as 'batches'

1 batch contains almost 50,000 claims
the tables that will be archived are 36 nos.

the procedure we are follwing is..
select data from production server
insert data in archive server
delete the data from production server

now the problem is that
1)if we 'commit' in between , it may lead inconsisterncy
2) if we treat all as single transaction we fall short in undo tablespace

please suggest on this!(can anybody give correct logic for this)

also my further queries are
Q 1.) in 9i with undo tablespace can we create and assign rollback segment for particular transaction
how to estimate the required rollback segment size if answer for above question is 'yes'
Q 2.) how to estimate undo tablespace required for specific transaction
Q 3.) can we handle Exception when we run short of Undo tablespace
Q 4.) inserting in archive server and deleting from production server will use undo space in production server only, right?

Thanks in Advance
Pratap

Report message to a moderator

Re: Undo Tablespace Issue - Archiving Case [message #111300 is a reply to message #111270]

Tue, 15 March 2005 08:28

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please refer the url.
this gives a decent account on UNDO management.

http://www.dbasupport.com/oracle/ora9i/undo.shtml

You need to consider UNDO retention time.

Report message to a moderator

Re: Undo Tablespace Issue - Archiving Case [message #111307 is a reply to message #111300]

Tue, 15 March 2005 09:24

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

undo information is information as of before the changes are made.
Redo information is information as of after the changes are made (committed and uncommitted).

by using CTAS ( create table new_table as select * from old_table) you can avoid undo
and instead of deleting you can truncate.

>delete the data from production server
is this some kind of staging area?

Report message to a moderator

Re: Undo Tablespace Issue - Archiving Case [message #111445 is a reply to message #111270]

Wed, 16 March 2005 08:05

skmishra
Messages: 2
Registered: March 2005
Location: New Delhi

Junior Member

Hi,

As you are Trasfering Data from between two Oracle Database, so you can use APPEND/PARALLEL with NOARCHIVE option while transfering data.

Thanks
S.K.Mishra
DBA

[Updated on: Wed, 16 March 2005 08:07]

Report message to a moderator

Re: Undo Tablespace Issue - Archiving Case [message #111464 is a reply to message #111445]

Wed, 16 March 2005 09:44

kumarsz
Messages: 180
Registered: March 2005
Location: canada

Senior Member

Hi,
yes, transferring between databases .
can you please describe about the options you have mentioned
i.e. APPEND/PARALLEL
major problem we are facing is with deleting 50,000 records from source (production) database after inserting in destination i.e. archive database.
the delete is not completed even after 12 hours.!!! pls suggest here
(in fact what i was thinking append works for sqlloader only during direct path)

Thanks in Advance
Pratap

Report message to a moderator

Re: Undo Tablespace Issue - Archiving Case [message #111666 is a reply to message #111270]

Fri, 18 March 2005 09:10

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

How about use some partitioning and transportable tablespaces?

Report message to a moderator

Previous Topic:	Want sql executed session wise
Next Topic:	Tunning - urgent

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 00:03:47 CST 2024