Default Size of a CHAR Column

If you do not specify a size for a CHAR column, the default is 1. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table t1
  2  (c1 char,
  3   c2 char(1))
  4  /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  CHAR(1)
C2                                  CHAR(1)
 
SQL> 

However, if you rely on defaults like (more...)

VARCHAR and VARCHAR2

If you create a table with a VARCHAR column in Oracle 11.2, Oracle sets it up as a VARCHAR2:

SQL> l
  1  create table t1
  2  (c1 varchar(1),
  3*  c2 varchar2(1))
SQL> /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  VARCHAR2(1)
C2                                  VARCHAR2(1)
 
SQL>

According to a book I am working through, this has been the case since Oracle 8. However, (more...)

Oracle "read by other session" Wait Event

When a session needs to read data from disk into the Oracle buffer cache, it may have to wait for another session to finish doing the same thing. Time spent doing this is recorded as a read by other session event. I decided to reproduce this in an Oracle 12 database. First, in session 1, in red, I set up a user called Fred to create a table:

SQL> conn / as sysdba
Connected.

How Many Values Can You Have in an IN List?

I have often wondered how many values you could have following an IN and I have just found out. I loaded some new data into a name and address table in an Oracle 11 database over the weekend. On Monday, a user sent me an Excel spreadsheet containing a list of almost 18000 meter point references to search for in the table. I exported them into a file, copied it to the server and used (more...)

ORA-00600: internal error code, arguments: [kzdlk_zt2 err]

This example is based on an error which a colleague showed me recently. I logged into an Oracle 11.1 database, reset its SYSTEM password and checked the encrypted value: 

Oracle11 > sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 4 18:10:38 2015
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1. (more...)

ORA-01114 Not Recorded Correctly in Alert Log

A user reported an ORA-01114 and an ORA-27069 in a 3rd party application running against an Oracle 11.1 database:

ERROR 28/01/2015 10:32:37  INF2 LKPDP_10:READER_1_1  RR_4035    SQL Error [
ORA-01114: IO error writing block to file 201 (block # 524367)
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 524367
Additional information: 7
Additional information: 524289
ORA-01114: IO error writing block to file 201 (block # 524367)
ORA-27069: attempt to (more...)

A Difference Between SQL*Plus and SQL Developer

A third-party supplier delivered some SQL today but it did not work in SQL*Plus. We asked the supplier about this and it turned that the code had been tested in SQL Developer. The reason for the failure was as follows. If you end a line of SQL with a semi-colon then add a comment afterwards, SQL*Plus rejects it with an ORA-00911

SQL> @test1
SQL> set echo on
SQL> select 'Comment->' from dual; /*Andrew was (more...)

SUCCESS and FAILURE Columns in DBA_STMT_AUDIT_OPTS

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
 
SQL>
 
You can audit successful connections as follows:
 
SQL> audit create session by system whenever successful
  2  /
 
Audit succeeded.
 
SQL>
 
This will then (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