Printable View
Hi
I am a OCP DBA :-) and i do not know the answers to this questions..all of you are welcome to cricize me :-) for askin this simple question..but i have serached the docs before askin this ?
here goes my 2 questions
1)when is the SCN assigned to a transaction..immediately after a commit is issued or after the contents of the redolog buffer are flushed to the disk
2)when you have row migration why does oracle keep a pointer in the block to the row where it has moved now ?
regards
Hrishy
Whenever a transaction is committed, LGWR writes the transaction’s redo records from the redo log buffer of the SGA to an online redo log file, and a system change number (SCN) is assigned to identify the redo records for each committed transaction.Quote:
Originally posted by hrishy
1)when is the SCN assigned to a transaction..immediately after a commit is issued or after the contents of the redolog buffer are flushed to the disk
Only reason i think is to preserve the rowid. The rowid never changes for a row :)Quote:
Originally posted by hrishy
2)when you have row migration why does oracle keep a pointer in the block to the row where it has moved now ?
HTH
Hi Amar.Quote:
Originally posted by adewri
Only reason i think is to preserve the rowid. The rowid never changes for a row :)
HTH
Dude thats a cool answer...even i couldnt think of any other answer..
regards
Hrishy
It's especially essential if the row is in a table that is a Master table for a materilized view (using ROWID), otherwise, every time you had a row migration, you'd have to rebuild the materialized view.Quote:
Originally posted by hrishy
Hi
2)when you have row migration why does oracle keep a pointer in the block to the row where it has moved now ?
regards
Hrishy
Just one example... :)
Don't worry about asking questions, even DBA's with 10+ years experince don't know everything. If they did.. it'd be a pretty boring job.
Hi Jhonson
Thats cool..now next time probably i would get a job..:-)..
regards
Hrishy
Presumably if the rowid did change, all the indexes pointing to it would have to be updated too.Quote:
Originally posted by adewri
Only reason i think is to preserve the rowid. The rowid never changes for a row
Exactly as greg said,
You can trap the best of the dba's with some nice tricky question.
and trust me answering questions on the net is easy, ask me anything, i'd hunt the net and articulate my ass out of it.
I think dba/developer interviews should be more of having the candidate sit behind a simple box and gradually answer the questions on the machine.
That way you know if you want a junior, intermediate or a senior dba/developer.
That way both the parties know what they've got.
Another sign of a good DBA: Might not know the answer, but knows where to find it. Very important...Quote:
Originally posted by Tarry
Exactly as greg said,
You can trap the best of the dba's with some nice tricky question.
and trust me answering questions on the net is easy, ask me anything, i'd hunt the net and articulate my ass out of it.
I sometimes (ahem, often) see questions posted on this board that I don't know the answer to, but I become curious and wonder what the answer is myself, so I usually go searching for it via Metalink, or books. Once I find the answer I post it here. So the person asking the question gets their answer and I've learnt something new, my employer gains more recognition having a knowledgable employee, the client is more satisfied with a staff member with more expert knowledge. We all win... :D
Well said grjohnson. The ability to research and obtain the answer is essential to the success of anyone that is technical (Not only a DBA).
Regarding
2)when you have row migration ?
A row is placed in datablock initially and if it gets updated lateron,
then the rowlength might increase.
When it tries to expand,and if the space reserved for pctfree (For updates pctfree is reserved) has also been consumed.
In that case Oracle will migrate the entire row to another block where it can fit that row
And it maintains pointer to the new block containing the migrated row.
Generally performance issue comesup if row is migrated /chained as
it has to scan more than 1 block for getting that row.
Yeah, I'm pretty sure hrishy is aware of what Chaining/Migration is and it's effects. The questions was more, Why was a pointer maintained in the original block. Hence the discussion.
Cheers,
This is perhaphs the number one skill that i would look for in any DBAQuote:
Originally posted by grjohnson
Another sign of a good DBA: Might not know the answer, but knows where to find it. Very important...
:D
regards
Hrishy
Yeah .
I mistook it as a separate Q.(when row migration takes place)
Cheers
I belive this reason cud be the one of the reasons....Quote:
Originally posted by hrishy
Hi
2)when you have row migration why does oracle keep a pointer in the block to the row where it has moved now ?
regards
Hrishy
after Insert of data....a pointer pointing to memoray location is assinged to it.
after deletion of data....the pointer is dropped and not actual data is deleted, as writing 0 on disk takes too much time & unnecesssary use of resources.....
So if we think logically, and i AFAIK Oracle is wrriten in C to handle the memory allocation and deallocation part, Pointers to memory location can be assigned or dropped and can be updated but requires complex logic.......instead Pointer to Pointer wud be more flexible and easy....so when a row is migrated...a new pointer is assigned......
Correct me if i am wrong.......
Abhay.
No worries... :D I do it sometimes as well, skim the question and then answer. :pQuote:
Originally posted by k.chithra
Yeah .
I mistook it as a separate Q.(when row migration takes place)
Cheers