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...)

LOGICAL_READS_PER_SESSION

One of the resources you can limit in a profile is called logical_reads_per_session. According to the Oracle documentation, it is used to:
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. I decided to try it out in an Oracle 11.1 database.
 
I created a profile called for_andrew, which would limit logical_reads_per_session to 10000:

SQL> conn / as sysdba
Connected.
SQL> create profile (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