Home » RDBMS Server » Performance Tuning » Undo Tablespace Issue - Archiving Case
Undo Tablespace Issue - Archiving Case [message #111270] Tue, 15 March 2005 03:27 Go to next message
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

Re: Undo Tablespace Issue - Archiving Case [message #111300 is a reply to message #111270] Tue, 15 March 2005 08:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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.
Re: Undo Tablespace Issue - Archiving Case [message #111307 is a reply to message #111300] Tue, 15 March 2005 09:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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?
Re: Undo Tablespace Issue - Archiving Case [message #111445 is a reply to message #111270] Wed, 16 March 2005 08:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Undo Tablespace Issue - Archiving Case [message #111666 is a reply to message #111270] Fri, 18 March 2005 09:10 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
How about use some partitioning and transportable tablespaces?
Previous Topic: Want sql executed session wise
Next Topic: Tunning - urgent
Goto Forum:
  


Current Time: Wed Sep 30 11:57:29 CDT 2020