Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2008
    Posts
    24

    Unhappy Unanswered: what happens after a DML command...

    Hi i am new to oracle and have just started learning.... i have following doubts...

    1 what does datafiles do, what i understand is they will contain user tables etc.... now my question is when does this data is written to datafiles, i mean the conditions.
    2 when i perform some DML operation on a table (suppose some updation in couple of rows)then what changes occur in undo segment, online redo log files, datafiles. i want to know how things flow..


    Thanks

    ankur

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    The first things that I have to ask is
    1. Have you read the documentation?
    2. Have you searched the web for such things?
    3. If not why not? There is a MASSIVE amount of info on such topics available.
    4. If you have done this, exactly what are your confusions.
    I'm not trying to be nasty here, you will develop you skills far faster (and better) if you put some of your own effort in, you will also get far more respect and assistance if other posters think that you are actually willing to put effort in and don't just expect to be spoonfed (I'm sure that you don't)
    A couple of links to get you started :
    Documentation
    Read the concepts guide (all of it) and the the relevant sections in the Admin Guide.
    a post from just a couple of days ago

  3. #3
    Join Date
    Jan 2008
    Posts
    24
    Quote Originally Posted by pablolee
    The first things that I have to ask is
    1. Have you read the documentation?
    2. Have you searched the web for such things?
    3. If not why not? There is a MASSIVE amount of info on such topics available.
    4. If you have done this, exactly what are your confusions.
    I'm not trying to be nasty here, you will develop you skills far faster (and better) if you put some of your own effort in, you will also get far more respect and assistance if other posters think that you are actually willing to put effort in and don't just expect to be spoonfed (I'm sure that you don't)
    A couple of links to get you started :
    Documentation
    Read the concepts guide (all of it) and the the relevant sections in the Admin Guide.
    a post from just a couple of days ago

    actually i have read lots of stuff... but confusion is regarding that when we update some record and commit it.... is it immediately written to datafile as well beside redo log files?

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by ankurk
    actually i have read lots of stuff... but confusion is regarding that when we update some record and commit it.... is it immediately written to datafile as well beside redo log files?
    OK, and what did you glean from the link to the post from a couple of days ago that I posted?

  5. #5
    Join Date
    Jan 2008
    Posts
    24
    Hi thanks for thinking that i am not reading and asking for spoon feeding..


    see the thing is that i read that even uncommited transactions are written to datafiles(Oracle9i RMAN Backup & Recovery By Robert G. Freeman, Matthew Hart page 23 chapter 1 link is http://books.google.com/books?id=Hok...iO0Yw#PPA23,M1 )


    so that's why confusion arised and hence wanted to clear all the steps that occur after we commit....



    i hope u understand now..


    wating for answer

    ankurk

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    Hi thanks for thinking that i am not reading and asking for spoon feeding..
    remember that tone cannot be conveyed in the written word and can easily be misconstrued.
    see the thing is that i read that even uncommited transactions are written to datafiles,
    This is absolutely correct, DBWR will write 'uncommitted blocks' to disk. A scenario.
    I have a buffer cache that has 10 blocks.
    Transaction A performs an update on 2 rows. These 2 rows happen to reside in 2 separate blocks but the same segment.
    2 Blocks are used. But a third block is required, the UNDO block, to hold the before images of the rows that have just been updated. (Let's not overthink things with RC blocks just now)
    OK, so we now have 3 blocks used and 7 available. (Transaction A has not been committed yet)
    Another sesssion (obviously) issues an update statement (Transaction B)that updates say 100 rows. These 100 rows are spread across 3 blocks and we need 2 UNDO blocks to handle the before images. 8 blocks down, 2 left and some other bugger decides that he too wants to issue an update (Transaction C). He needs 3 blocks but we only have 2 left... so Oracle has to write some blocks in the buffer_cache down to disk to make room. No probs. Let's say Oracle writes the blocks from transaction A (remember all this is a somewhat simplified process) down to disk, the rows involved still have locks associated with them to say that the are not committed. Transaction C gets the blocks it requires to 'do its stuff'. Transaction A gets committed. The blocks involved in Transaction A are brought back into memory and their locks released (actually this can be a delayed reaction and the locks are released the next time the block is brought into memory during 'normal' operation but don't worry too much about that for the overall picture just yet).
    Now when a commit is issued 2 things definitely happen.
    1. LGWR writes the contents of the Redo_Log_Buffer to disk.
    2. The rows that were locked by the now committed transaction, are released.
    As for when blocks are written to datafiles, Do a search on the DBWR (or DBW0 as it can be called) process as ONLY the DBWR process writes buffers to datafiles.
    I, once again, urge you to read the documentation found at the link that I provided.
    HTH
    Jim

  7. #7
    Join Date
    Jan 2008
    Posts
    24
    Thanks a lot........


    i have one more question that when uncomitted data has been written to datafile because of shortage of data buffer.....

    suppose user now disconnects(closes window without commit) then the uncommited data that has been written to datafile.. what will happen to that...? will that be deleted immediately? using undo segment( or some other procedure)?

  8. #8
    Join Date
    Dec 2007
    Posts
    253
    It will be marked for rollback. It will eventually be rolled back (rollbacked?) and yes it will use undo blocks to do so.

  9. #9
    Join Date
    Jan 2008
    Posts
    24

    Talking

    ok got it........ so we cannot know when does it happens.... but it happens .... right.?

  10. #10
    Join Date
    Jan 2008
    Posts
    24

    Talking

    ok got it........ so we cannot know when does it happens.... but it happens .... right?
    Last edited by ankurk; 02-14-08 at 05:39.

  11. #11
    Join Date
    Dec 2007
    Posts
    253
    It'll happen the next time a transaction needs that block. Oracle will see that a row has a lock on it, search for the relevant undo , see that the undo has also been unlocked (if it even exists anymore and has not been overwritten, and realise that the row is set for rollback.

  12. #12
    Join Date
    Jan 2008
    Posts
    24
    ok.......... got it........ Thanks a lot..........


    How many years of experience do u have?

  13. #13
    Join Date
    Dec 2007
    Posts
    253
    'bout 2.5 weeks.

    I jest of course
    I've been working with Oracle for about 5 years.

  14. #14
    Join Date
    Jan 2008
    Posts
    24
    Great keep helping young DBAs

    will revert back soon with new stupid questions(ha ha)

  15. #15
    Join Date
    Dec 2007
    Posts
    253
    LOL. Absolutely NOT a stupid question. (I'm not saying that there are NO stupid questions...) Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •