Saturday, November 11, 2006

Some books might make you think wrong

It is amazing how confusable some of the books could be.
You better check any information you think is different than you know.
Currently I am reading a book that is suppose to prepare me for OCP exam.
There are some questions and answers after each Chapter. I found few answers that I think are not correct.
Don’t get me wrong I still think that the book I read is a good one.

One Example: (I changed usernames and sequence name used in the original question):
Question:

The following SQL statement will allow user test_usr which operations on sequence scott.test_sq?
GRANT ALL ON scott.test_sq to test_usr;


A) Select the next value from scott.test_sq
B) Alter sequence scott.test_sq to change the next value
C) Change the number of sequence numbers that will be cached in memory
D) Both A and C
E) All of the above

The answer provided in the book is D.

But, I think it is E. Why?


SQL> conn scott/*****
Connected.
scott@ora10g> create sequence test_sq
2 start with 1
3 increment by 1;

Sequence created.

scott@ora10g> select test_sq.nextval from dual;

NEXTVAL
----------
1
scott@ora10g> conn sys as sysdba
Connected.
sys@ora10g> create user test_usr identified by test_usr
2 default tablespace users
3 temporary tablespace temp;

User created.

sys@ora10g> grant connect, resource to test_usr;

Grant succeeded.

sys@ora10g> grant all on scott.test_sq to test_usr;

Grant succeeded.

sys@ora10g> conn test_usr/test_usr
Connected.
test_usr@ora10g> select scott.test_sq.nextval from dual;

NEXTVAL
----------
2

test_usr@ora10g> alter sequence scott.test_sq
2 increment by 10;

Sequence altered.

test_usr@ora10g> select scott.test_sq.nextval from dual;

NEXTVAL
----------
12

test_usr@ora10g> alter sequence scott.test_sq
2 increment by 1;

Sequence altered.

test_usr@ora10g> conn scott/*****
Connected.
scott@ora10g> select test_sq.nextval from dual;

NEXTVAL
----------
13

scott@ora10g>