PRAGMA AUTONOMOUS_TRANSACTION

I tested this on Oracle 11.2. If you update a table in SQL*Plus then update it again with a different value in a PL/SQL block, the second update replaces the first:
 
SQL> create table tab1 as
  2  select 1 col1 from dual
  3  /
 
Table created.
 
SQL> update tab1 set col1 = 2
  2  /
 
1 row updated.
 
SQL> begin
  2   update tab1 set col1 (more...)

Global_Names and ORA-02085

This was tested on Oracle 9. I created 2 database links to the same target database:
 
SQL> conn / as sysdba
Connected.
SQL> create database link any_name.world
  2  connect to link_schema
  3  identified by link_schema
  4  using 'REMOTEDB.WORLD'
  5  /
 
Database link created.
 
SQL> create database link remotedb.world
  2  connect to link_schema
  3  identified by link_schema
  4  using 'REMOTEDB.WORLD'
  5  (more...)

TRUNCATE TABLE SYS.AUD$ Gives ORA-00942

I saw that the SYSTEM tablespace in an Oracle 11.2 database was getting quite big so I checked the size of SYS.AUD$:
 
C:\Users\AJ0294094>sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 16 13:20:54 2014
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, (more...)

ORA-01511, ORA-01512 and ORA-27037

This was run on an Oracle 11 database. I used dbca to create a database recently and, by mistake, I put redo03a.log in the wrong directory (ent_red01 with a zero instead of ent_redo1 with an o). dbca had no way of knowing this was wrong and simply created the directory name I specified in the GUI. You can see what I mean in the output below:

In the database: 

  1  (more...)

_single_process Initialization Parameter

The _single_process initialization parameter allows you to start an instance in single process mode. As it begins with an underscore, you should only use it when Oracle tell you to. When I tried it on Oracle 10 and Oracle 11 test databases, they promptly fell over. However, I was able to get it to work on Oracle 9, although it produced an ORA-00600. I then left the SYSDBA session open: 

Oracle 9: sqlplus '/ (more...)

AFTER DELETE Triggers do not Run After a Truncate

In this example, I want to show that Oracle does not execute an AFTER DELETE trigger after doing a TRUNCATE. I tested it on Oracle 11.2. First I created a table:

SQL> create table tab1 (my_name varchar2(10))
  2  /

Table created. 

SQL>

Then I inserted a row into it: 

SQL> insert into tab1 values ('Andrew')
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select * (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