SELECT ANY SEQUENCE

I used to think that a user with SELECT ANY TABLE and SELECT ANY DICTIONARY could see anything in a database. I found out today that these 2 privileges do not allow you to SELECT from another user’s sequence. You can see what I mean in the example below, which I tested in an Oracle 11.1 database. First I created a user to own a sequence:
 
SQL> conn / as sysdba
Connected.

UK OUG Conference 2015

I went to the UK OUG Conference for the first time around 12 years ago, in 2003 if I remember correctly. I enjoyed all the presentations but the two which stuck in my mind were by Jonathan Lewis and Connor McDonald.

This year I will be chairing 8 sessions there, which means I will be introducing the speaker and making sure nothing goes wrong.

Two of these sessions are on 6th December 2015, which is (more...)

AUTHID CURRENT_USER

If USERA creates a function or procedure and allows USERB to run it, USERB does so with USERA’s permissions. However, if USERA adds the AUTHID CURRENT_USER clause to the code, USERB runs it with its own permissions. You can see what I mean in the example below, which I tested in an Oracle 11.1 database:
 
I created a user called USERB and allowed it to login to the database:
 
SQL> create user (more...)

O7_DICTIONARY_ACCESSIBILITY

Long ago, in Oracle 7 I believe, a user with the SELECT ANY TABLE privilege could access tables and views owned by SYS. Also, a user with the EXECUTE ANY  PROCEDURE privilege could run code owned by SYS. Nowadays, this behaviour is controlled by the O7_DICTIONARY_ACCESSIBILITY initialisation parameter. The default value for this is FALSE, as you can see in the query below, which I ran in an Oracle 11.1 database:
 

db_writer_processes

I wrote the first part of this example in 2012.
 
The database writer copies data blocks from the buffer cache onto disk. The db_writer_processes initialization parameter determines how many processes will do this task. Its default value is 1 or cpu_count / 8, whichever is greater. I found an Oracle 9 database on a Tru64 server with cpu_count set to 1:
 
SQL> l
  1  select value from v$parameter
  2* where name (more...)

DBMS_STATS Causes ORA-00600 [15851]

Uncategorized
| Apr 3, 2013
I noticed this in an Oracle 11.2.0.1.0 database. DBMS_STATS failed with an ORA-00600 and the first argument was [15851]. On investigation, it seemed to have something to do with the fact that the table had a function based index:
 
SQL> SELECT COUNT(*) FROM DBA_INDEXES