What are the most important skills a senior Oracle developer and a junior DBA must have? Can I hire this DBA/developer or not? Should I prefer to hire a high school graduate with 5 years of experience or a college graduate with 1 year of experience and an OCP (Oracle Certified Practice)? Those are kind of questions recruiters are faced to when they are in a process of hiring oracle consultant specialized in data base administrator and PLSQL developments.
Tom Kyte in his famous website emphasize that he considers people skills as the most important things he look for in a process of hiring. The ability to communicate, to keep an open mind and to realize that things change over time, he said, are at least as important as technical skills. How do we not agree with such assumption? This is a perfect first step hiring process.
The second step in the hiring process would be to test the candidate technical skills. Here below is a snapshot of questions that might serve to distinguish between a good, a bad or an indifferent candidate.
Question 1: Could you explain ORA-01555 rollback segment snapshot too old error?
If you understand the principle of oracle multi-versioning then you can easily explain ora-01555 error. Oracle multi-versioning is the ability of oracle to maintain simultaneously a multiple version of the same data. Let’s consider that and end user starts a query (q1) that selects tab.ides on table tab at time t1. At time t1+1 a transaction starts and updates tab.ides that are being selected by query q1. Suppose that the query q1 is still running and is still getting new tab.ides at time t1+1, what values of tab.ides query q1 will be retrieving? Values of tab.ides at time t1 or values of tab.ides at time t1+1?
The answer is that query q1 will be retrieving values of tab.ides at the time of the beginning of the query i.e t1. That is at time t1+1 oracle has two versions of the same data tab.ides values at time t1 and values at time t1+1. This is the multi-versioning process in oracle.
Then, what is the relation between multi-versioning and ora-01555 rollback segment snapshot too old error?
When query q1 starts oracle did not copy the content of this query in any place but instead when a modification occurs in data queried by query q1 oracle will preserve a copy of this data before the modification happen in a place called rollback segment. Oracle uses this rollback segment to reconstruct the read-consistent snapshot of the data. The data provided by query q1 is reconstructed from rollback segment and presented as it was when the query q1 began at time t1.
Consequently when you (or other session) are actively updating information you are reading through cursor or through sql query, oracle will be actively using its rollback segments in order to store those modifications aiming to provide you with a data read consistency. When oracle is not anymore able to restore a consistent data then you will receive the famous ORA-1555 rollback segment snapshot too old.
Question 2: What solutions you will advise in order to avoid ORA-01555 error?
In addition to what has been recommended here how to avoid ora-01555 you may also consider this
1. increase size of rollback segment
2 make your query run very quickly
3. make your transaction (or job) restartable so that when an ora-01555 error occurs you can re-run your job
4 schedule your job at time where there is less update activity on the data base.
More questions next time….