Re: Basis Question on update of Large table

The checkpoint is complete when all dirty buffers (whether committed or not) are written to disk (datafiles). So, you could have 500MB Redo Logs and a 10TB update -- if the database is running ARCHIVELOG and you do not have enough space for the ArchiveLogs, the operation (update) will halt but not fail.

If the undo tablespace is not large enough (i.e. the undo segment that Oracle chooses to hold the undo for that transaction) to contain the undo for the UPDATE, the transaction will fail with an allocation error on Undo.

Hemant K Chitale

Hemant K Chitale

On Sat, Jan 20, 2018 at 12:03 PM, nilesh kumar <nileshkum_at_gmail.com> wrote:

> Forwarding it to correct group.
>
> ---------- Forwarded message ----------
> From: nilesh kumar <nileshkum_at_gmail.com>
> Date: Sat, Jan 20, 2018 at 1:13 AM
> Subject: Basis Question on update of Large table
> To: oracle-l-admins_at_freelists.org
>
>
> Hello Gurus,
>
> I have a Basic question on How an update operation work on a very large
> table in Oracle.
>
> Lets say for example, my table size is 10TB, and my database buffercache
> size is 10GB.
> I have fired an update on the 10 TB size table to update all the rows.
>
> I know that the entire 10TB table cannot be brought into the Database
> Buffer cache in one short as the Buffer is small, so does Oracle brings the
> blocks in batches ?
>
> Secondly lets say one batch of buffer blocks are updated and commit has
> not fired, to bring the next set of buffer blocks into the buffer it has to
> be pushed to the disk after the redo buffer has written the redolog files,
> but if the checkpoint is not completed then the online redologs cannot be
> reused right ?
>
> Thirdly, once the database buffer blocks are pushed to disk and commit has
> not happened and my undo tablespace size is lets say is 5GB, from where we
> would get the read consistency of the data in case a another user fires a
> select on the table ?
>
> could you please clarify my question on the update on large table.
> Sorry for asking a basic question.
>
>
>
> --
> Thanks & Regards
> Nilesh
> Oracle DBA (sqlplus "/as sysdba" is my world)
>
> "As our circle of knowledge expands, so does the circumference of darkness
> surrounding it."--Einstein
> "All men die, few men ever truly live" -- Braveheart
> "You can get help from teachers, but you are going to have to learn a lot
> by yourself, sitting alone in a room." --Dr. Seuss
> "Know me for what I do and for not what I am. Judge me for what I can do
> and not for what I have done."-Unknown
> "Don't walk in front of me, I may not follow. Don't walk behind me, I may
> not lead. Just walk beside me and be my friend."- Albert Camus
> "Never argue with an idiot. They drag you down to their level and then
> beat you with experience." - Unknown
>
>
>
> --
> Thanks & Regards
> Nilesh
> Oracle DBA (sqlplus "/as sysdba" is my world)
>
> "As our circle of knowledge expands, so does the circumference of darkness
> surrounding it."--Einstein
> "All men die, few men ever truly live" -- Braveheart
> "You can get help from teachers, but you are going to have to learn a lot
> by yourself, sitting alone in a room." --Dr. Seuss
> "Know me for what I do and for not what I am. Judge me for what I can do
> and not for what I have done."-Unknown
> "Don't walk in front of me, I may not follow. Don't walk behind me, I may
> not lead. Just walk beside me and be my friend."- Albert Camus
> "Never argue with an idiot. They drag you down to their level and then
> beat you with experience." - Unknown
>

--
http://www.freelists.org/webpage/oracle-l