Home » SQL & PL/SQL » SQL & PL/SQL » General Question on altering table without locking it (Oracle 11gR2)
General Question on altering table without locking it [message #673551] Fri, 23 November 2018 08:47 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

We have an application which access a table 24/7. We are trying to add new columns to the table and populate the data, it has 9 million data. I need to do this operation with zero downtime.Can you suggest any new functionality introduced in 11gR2 which add columns and populates the value offline without impacting or locking the table.

Normally we follow below options:
1. Create a copy of table add new columns and rename the table, this has two disadvantage as the data will be live i may miss few data and renaming of 10 million records may take more than 1 minute

Your suggestions are appreciated

Thanks,
SRK
Re: General Question on altering table without locking it [message #673554 is a reply to message #673551] Fri, 23 November 2018 09:15 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Use dbms_redefinition.
Re: General Question on altering table without locking it [message #673558 is a reply to message #673554] Fri, 23 November 2018 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And for the future, study and use editions.

Re: General Question on altering table without locking it [message #673560 is a reply to message #673554] Fri, 23 November 2018 11:28 Go to previous message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

I have used the dbms_redefinition, but used to move the normal table to partition and so. Let me do some reading on it for this case.

Thanks,
SRK
Previous Topic: MV Refresh on Partitioned Materialized views
Next Topic: HAVING ISSUES UNDERSTANDING HOW TO SOLVE THIS ORACLE QUESTION
Goto Forum:
  


Current Time: Fri Mar 29 02:45:01 CDT 2024