Database Administration (MOSC)

MOSC Banner

Best way to replenish the data in a table

edited Jun 8, 2009 7:16AM in Database Administration (MOSC) 6 commentsAnswered
 Here is the situation:

We have a table in one database that needs to be copied nightly in another database.  This table is over 1.5 million rows.  The current methodology is to truncate the table and then, using a database link, inserts the rows from the first database into the second database.  We have placed this table in a nologging tablespace and use the /*+ APPEND */ hint to eliminate the generation of redo information.

The issue is that this takes 1.3 minutes to perform this task.  If someone tries to access this table during the 1.3 minutes, there are 0 rows in the select.  We perform this at 3:00 a.m..  The application developers are saying that this is not acceptable. I have tried to use a delete from table statement to remove the rows, which allows for availability to data, but the performance degradation (10 minutes to perform the task, a select statement takes minutes, instead of a couple of seconds) during the delete/insert plus the generation of redo/archive files (from the delete) way

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center