Data Dictionary Cache Hit Ratio

Every Oracle database has a data dictionary, which is owned by the SYS user and stored in the SYSTEM tablespace. This consists of read-only tables, which record information about the database e.g. table definitions, details of integrity constraints, usernames and the roles and privileges granted to them etc. You can read about this in detail in Oracle's own documentation.

So, for example, if user SCOTT tries to read a table, Oracle checks the data (more...)


If a session does ALTER SESSION DISABLE COMMIT IN PROCEDURE then a procedure tries to do a COMMIT or ROLLBACK, Oracle returns an ORA-00034. You can see what I mean in the example below, which was tested on Oracle 11.2: 

SQL> create or replace procedure commit_proc as
  2  begin
  3  commit;
  4  end;
  5  /
Procedure created.
SQL> create or replace procedure rollback_proc as
  2  (more...)

Justify Left, Centre and Right

This was tested on Oracle 11.2. NUMBER items and their column headings are right justified by default:
SQL> set numwidth 15
SQL> select count(*) table_count from dba_tables
  2  /
If you want to see the heading in the centre of the column, you can do so with justify centre:
SQL> col table_count justify centre
SQL> select count(*) table_count from dba_tables


I noticed this recently on an Oracle database.
If you create a table, add a trigger then add a column, it all works:
SQL> create table tab1 (col1 varchar2(1))
  2  /
Table created.
SQL> create trigger trig1
  2  after update on tab1
  3  begin
  4  null;
  5  end;
  6  /
Trigger created.
SQL> alter table tab1
  2  (more...)

Permissions Required to Create a Materialized View

The idea for this post came from a problem, which I saw on Javier Morales Carreras' blog here.

This example was tested on Oracle 11.2. It shows the permissions required to create a materialized view. First I created a user:
SQL> conn / as sysdba
SQL> create user andrew identified by reid
  2  /
User created.
SQL> grant create session to andrew
  2  /
Grant succeeded.


This example shows how to kill a user session. You can view sessions in your database as follows:

SQL> col username format a10
SQL> l
  1  select username, sid, serial#, status
  2  from v$session
  3* where username = 'ANDREW'
SQL> /

---------- ---------- ---------- --------
ANDREW            143         79 INACTIVE


You can then use the SID and SERIAL# displayed to kill a session as shown below. The username (more...)

User Commits, Log File Parallel Write and Log File Sync

I ran this example on Oracle 11.2. If an application spends too much time waiting on log file parallel write and/or log file sync, it may be doing too many user commits. To illustrate this, I created the SQL*Plus script below. It accepts a parameter and uses this to create a unique SPOOL file name and table name. It then sees how many user commits the database has done and looks to see (more...)

Logons Cumulative

The logons cumulative statistic in V$SYSSTAT shows how many sessions have connected since the database was opened. If this value is too high, there could be shell scripts looping round and connecting then disconnecting from the database. This can have a detrimental effect on performance.

SQL> col name format a20
SQL> select * from v$sysstat where name = 'logons cumulative'
  2  /

STATISTIC# NAME                 CLASS      VALUE      STAT_ID
---------- -------------------- ---------- ---------- ----------
0          logons (more...)


I tested this on an Oracle 10 database running on Linux. If you try to make the SYSAUX tablespace READ ONLY, you get an error. There is no way round this, you cannot do it: 

SQL> alter tablespace sysaux read only
  2  /
alter tablespace sysaux read only
ERROR at line 1:
ORA-13505: SYSAUX tablespace can not be made read only


DROP TYPE Statement Hangs

This happened in an Oracle 11 database. I had an INACTIVE session in the database:

SQL> select status from v$session
  2  where sid = 112;
I tried to use this session to drop a type but it did not seem to work:
SQL> drop type ppc_day_rec
  2  /

… but the status of the session had changed to ACTIVE so I assumed it was doing (more...)


If you are running a SQL script and you see the message below: 

SP2-0308: cannot close spool file

... it could be that the disk the SPOOL file is on is full (as usual, click on the image to enlarge it):

If you clear some space, future SPOOL files should be created OK but, if my experience is anything to go by, the output you were SPOOLing before the SP2-0308 occurred will be lost forever.


I had ORA-12500 errors each time I tried to connect to a database on a particular server:
C:\>sqlplus system@livsost1
SQL*Plus: Release Production on Fri Feb 7 11:05:18 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password:
ORA-12500: TNS:listener failed to start a dedicated server process
Enter user-name: system@ebesost1
Enter password:
ORA-12500: TNS:listener failed to start a dedicated server process

ORA-00604, ORA-04092, ORA-06512 and ORA-00942

I saw an ORA-00604 in a database recently and decided to see what might have caused it. The test below was run in an Oracle 9 database and shows one possible reason for an ORA-00604. Strangely enough, when I repeated the test in Oracle 10 and Oracle 11 databases, the ORA-00604 did not appear. If / when I find out why, I will update this post accordingly. First I ensured that _system_trig_enabled was set to (more...)


I discovered a new error today while working on an Oracle 11 database. If you try to use alter table without any options, you get an ORA-02210. If you add a valid option, the error does not appear.

SQL> alter table srce.go_tpr_nhh_ppc
  2  /
alter table srce.go_tpr_nhh_ppc
ERROR at line 1:
ORA-02210: no options specified for ALTER TABLE
SQL> alter table srce.go_tpr_nhh_ppc enable all triggers
  2  /
Table altered.

DBMS_STATS Causes ORA-00600 [15851]

| Apr 3, 2013
I noticed this in an Oracle 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: