I tested this in Oracle 10.2. DBA_STMT_AUDIT_OPTS records auditing options which are currently in effect. If no auditing has been requested, it will be empty:
SQL> select user_name, audit_option, success, failure
  2  from dba_stmt_audit_opts
  3  /
no rows selected
You can audit successful connections as follows:
SQL> audit create session by system whenever successful
  2  /
Audit succeeded.
This will then (more...)

Segment Creation Deferred and ORA-02266

If you try to truncate a table with a primary key which is referenced by an enabled foreign key, you usually get an ORA-02266 error. This happens straight away if the table is set up with segment creation immediate. However, if the table is set up with segment creation deferred, the error is not reported until the segment has been created. You can see what I mean in the example below, which I tested (more...)

Case Sensitive Passwords in Oracle 11

In version 11, Oracle passwords became case sensitive. You can see what I mean in the example below:

SQL> conn / as sysdba
SQL> alter user system identified by manager
  2  /
User altered.
SQL> conn system/manager
SQL> conn system/MANAGER
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
The DBA_USERS view no longer contains the encrypted password, except when the user (more...)

Rollback to Savepoint Does Not Release Locks

I read that rolling back to a savepoint releases locks. This sounded reasonable so I decided to check it out in an Oracle 11.2 database. I logged in as user John (in blue) and noted my SID for future reference. Then I created a table, inserted a row, committed the change and created a savepoint. Finally I updated the row but did not commit the change, thus setting up a lock:


I read about this in the book advertised at the end of this post. It was tested on Oracle 11.2. V$SESSION_EVENT holds similar information to V$SYSTEM_EVENT but it is broken down by session (only currently logged in sessions appear - there is no history). There is a MAX_WAIT column which shows the maximum time a session has had to wait for a particular event.There is no timestamp on this so you cannot tell (more...)

ORA-01951, ORA-01952 and ORA-01045

I saw a strange question on a forum and decided to reproduce it in an Oracle 12.1 database. First I created a user:
SQL> conn / as sysdba
SQL> create user a identified by b
  2  /
User created.
Then I found that the user could apparently login without the CREATE SESSION privilege:
SQL> conn a/b
After logging in, the user had a role (more...)


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.

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

Bug 8477973

I ran the following query on an Oracle database but it failed with an ORA-02020:
SQL> SELECT ppc.sttlmnt_dt day_date
  2  FROM   vrm_d18_ppc   ppc,
  3         meter_nhh     mtr,
  4         mtd_registers reg
  5  WHERE  ppc.tm_pttrn_rgm = reg.tpr
  6  AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id
  7  AND    ppc.prfl_clss_id = mtr.profile_class_id
  8  AND    ppc.gsp_grp_id = mtr.gsp_group_id
  9  AND    reg. (more...)

Worked Examples with the SET ROLE Command

Before I start, I wonder if anybody can help me. Some time ago, I saw several adverts on the Internet similar to the one below:

I am looking for an Oracle DBA (French speaking) - Hampshire (South Coast of England)

Languages: ENGLISH and FRENCH

We are recruiting for a major blue chip company based in the South of English (Hampshire area), where we seek a proven Oracle Database Administrator (HPUX, Linux, and AIX) who can (more...)


I tested this on an Oracle 11.1 database.
Oracle profiles control how certain database resources are allocated to a user session. They also define some security rules. When you create a user, it is assigned a profile and, if you do not specify it explicitly, the DEFAULT profile will be used:
SQL> grant create session to andrew
  2  identified by reid
  3  /
Grant succeeded.
SQL> select profile (more...)

Bug 5497611

I used Oracle Enterprise Manager to look at the execution plan for some SQL in an Oracle database. (The SQL shown is just an example done later for the purposes of this blog post. As usual, click on the image to enlarge it and bring it into focus if necessary.):

This produced the following ORA-00600 message several times in the alert log:
Wed Oct  1 18:13:21 2014
Errors in (more...)


I have known about the first_rows optimizer mode for some time. This tells Oracle to use an execution path which will return the first few rows as quickly as possible. However, I recently read about the first_rows_n optimizer mode, which apparently appeared first in Oracle 9. This tells Oracle to use an execution path which will return the first n rows quickly, where n can be 1, 10, 100 or 1000. I decided to try (more...)


I tested this on Oracle 11.2. I created a profile called FOR_ANDREW with PASSWORD_REUSE_MAX set to 1. This meant that I could not reuse a password until I had used one other password first:
SQL> conn / as sysdba
SQL> create profile for_andrew
  2  limit password_reuse_max 1
  3  /
Profile created.
I created a user called ANDREW and gave him the FOR_ANDREW profile:


I was reading through an old book recently and it said that you could not put a tablespace into hot backup mode if the database was in NOARCHIVELOG mode. This seemed reasonable to me but I wondered what might happen if you tried to do this so I ran the following SQL in an Oracle 11.2 database:  

SQL> l
  1* alter tablespace users begin backup
SQL> /
alter tablespace users (more...)

How to See When Your Database Was Opened

This was tested on Oracle 11.2: 

SQL> l
  1* select startup_time from v$instance
SQL> /


I tested this on Oracle You need to be careful when looking at the LAST_ANALYZED column in USER_TABLES. I created a table and removed the FIXED_DATE parameter from the database:
SQL> create table t1 (c1 number)
  2  /
Table created.
SQL> alter system set fixed_date = none
  2  /
System altered.
…then I used DBMS_STATS.GATHER_TABLE_STATS and the old ANALYZE command to (more...)

ORA-01460 on PL/SQL Developer

An Oracle 9 database had the following character set:
SQL> l
  1  select sys_context('userenv','LANGUAGE')
  2* from dual
SQL> /
I used PL/SQL Developer to look at the code in the database (as usual, click on the image to enlarge it and bring it into focus):

Somebody typed the following command in the database by mistake:
SQL> l
  1* create database (more...)

A Simple Example with Indexes

I did this worked example on Oracle 11.2. First I created a table:

SQL> create table t1
  2  as select * from dba_segments
  3  /
Table created.
…then I made sure it contained enough data:
SQL> begin
  2  for a in 1..8 loop
  3  insert into t1 select * from t1;
  4  end loop;
  5  end;
  6  /
PL/SQL procedure (more...)

kgxgncin: CLSS init failed with status 3

At the end of September, I will be walking 26.2 miles (the marathon distance) in aid of Great Ormond Street Hospital If you find the post below useful and you have any money to spare, you might like to click on the Just Giving link on the right of this page and sponsor me.

I had a problem with an Oracle database producing trace files every few minutes. Here (more...)