Using NOT = in a WHERE Clause

I ran this test in Oracle 12.1. First I created a table, added some data to it and made sure that the bytes column was set to zero in every row:
 
SQL> conn system/manager
Connected.
SQL> alter session set optimizer_mode = first_rows
  2  /
 
Session altered.
 
SQL> create table t1
  2  tablespace users
  3  as select segment_name, bytes
  4  from dba_segments
  5  /
 
Table created.

ORA-04000

I created a table in an Oracle 11.2 database. I did not specify a pctfree or pctused so it was given the defaults of 10% and 40% respectively:

SQL> conn system/manager
Connected.
SQL> create table t1 (c1 number)
  2  /

Table created.

SQL> select pct_free, pct_used
  2  from user_tables
  3  where table_name = 'T1'
  4  /

  PCT_FREE   PCT_USED
---------- ----------
        10         40 




SQL>

When Oracle inserts rows into (more...)

FULL and NO_INDEX Hints

I was reading about hints and decided to try out a couple on an Oracle 11.2 database. First I created a table, added some data and created an index:

SQL> create table t1 (c1 varchar2(30))
  2  /
 
Table created.
 
SQL> insert into t1 select table_name from dba_tables
  2  /
 
3159 rows created.
 
SQL> create index i1 on t1(c1)
  2  /
 
Index created.
 
SQL>

I ran (more...)

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

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
Connected.
SQL> alter user system identified by manager
  2  /
 
User altered.
 
SQL> conn system/manager
Connected.
SQL> conn system/MANAGER
ERROR:
ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.
SQL>
 
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:
 

DBMS_SYSTEM.KCFRMS

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
Connected.
SQL> create user a identified by b
  2  /
 
User created.
 
SQL>
 
Then I found that the user could apparently login without the CREATE SESSION privilege:
 
SQL> conn a/b
Connected.
SQL>
 
After logging in, the user had a role (more...)

ORA-02205

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.
 
SQL>
 
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  /
 
NVL(BLEVEL,999)
---------------
            999
 
SQL>

Bug 8477973

I ran the following query on an Oracle 11.1.0.6 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...)