Friday, October 31, 2014

ORA-02205

I found some notes from a course I took in 1990. They said that it was only possible to GRANT ALTER or GRANT SELECT on a sequence. This seemed reasonable to me but I wanted to check if it was still the case. I did this test on Oracle 12.1. First I created a user who would own a sequence:

SQL> create user u1 identified by pw1
  2  /
 
User created.
 
SQL> grant create session, create sequence to u1
  2  /
 
Grant succeeded.

SQL>

Then I created a user who would be granted access to the sequence:

SQL> create user u2 identified by pw2
  2  /
 
User created.

SQL>

The first user created a sequence then did a GRANT ALL on it to the second user:

SQL> conn u1/pw1
Connected.
SQL> create sequence s1
  2  /
 
Sequence created.
 
SQL> grant all on s1 to u2
  2  /
 
Grant succeeded.

SQL>

I looked for the privileges which had been given to the second user but only found ALTER and SELECT. This confirmed what I had read in my notes:

SQL> select privilege from all_tab_privs
  2  where grantor = 'U1'
  3  and grantee = 'U2'
  4  and table_name = 'S1'
  5  /
 
PRIVILEGE                                             
----------------------------------------              
ALTER                                                 
SELECT

SQL>

Finally I tried to GRANT UPDATE on the sequence but this failed with an ORA-02205:

SQL> grant update on s1 to u2
  2  /
grant update on s1 to u2
                *
ERROR at line 1:
ORA-02205: only SELECT and ALTER privileges are valid
for sequences
 

Tuesday, October 21, 2014

How to See the Height of an Index

This example shows where to find the height of an index. I tested it on Oracle 11.2. First I deleted the index's statistics:

SQL> exec dbms_stats.delete_index_stats -
> ('uimsmgr','ubbchst_serv_index');
 
PL/SQL procedure successfully completed.
 
SQL>
 
Then I checked its BLEVEL was null:

SQL> select nvl(blevel,999)
  2  from dba_indexes
  3  where owner = 'UIMSMGR'
  4  and index_name = 'UBBCHST_SERV_INDEX'
  5  /
 
NVL(BLEVEL,999)
---------------
            999
 
SQL>
 
I recalculated the index's statistics:
 
SQL> exec dbms_stats.gather_index_stats -
> ('uimsmgr','ubbchst_serv_index');
 
PL/SQL procedure successfully completed.
 
SQL>
 
After that, the BLEVEL column in DBA_INDEXES showed the index’s height:

SQL> select nvl(blevel,999)
  2  from dba_indexes
  3  where owner = 'UIMSMGR'
  4  and index_name = 'UBBCHST_SERV_INDEX'
  5  /
 
NVL(BLEVEL,999)
---------------
              3
 
SQL>
 
I used to calculate an index's height by analyzing it then looking in INDEX_STATS. First I checked that INDEX_STATS was empty (you will see why I showed the username later):

SQL> select name, height from index_stats
  2  /
 
no rows selected
 
SQL> show user
USER is "ORACLE"
SQL>
 
Then I analyzed the index:

SQL> analyze index uimsmgr.ubbchst_serv_index
  2  validate structure
  3  /
 
Index analyzed.
 
SQL>
 
... and checked its height in INDEX_STATS again. This height comes out as BLEVEL + 1. I guess Oracle starts to count it from a different place as this happened in Oracle 9 too:
 
SQL> select name, height from index_stats
  2  /
 
NAME                               HEIGHT
------------------------------ ----------
UBBCHST_SERV_INDEX                      4
 
SQL>
 
INDEX_STATS is a public synonym for the SYS.INDEX_STATS view. It is far too complicated for me to follow but I know that it never has more than one row, which holds details for the index you analyzed most recently. It is also session specific so, if you start a new session, it will be empty again:
 
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select name, height from index_stats
  2  /
 
no rows selected
 
SQL>
 
You can read more about this starting on page 69 of the book advertised below: