Simple Example with REPLACE

The REPLACE function allows you to change a string of characters to another string of characters and can accept three parameters:
 
(1)    Input column name.
(2)    Old string value.
(3)    New string value.
 
You can see what I mean in the example below, which I tested on Oracle 11.2:
 
SQL> create table directory_name
  2  (location varchar2(30))
  3  /
 
Table created.
 
SQL> insert into directory_name

ORA-01749

This was tested on an Oracle 11.2 database. You cannot GRANT or REVOKE object privileges to or from yourself:
 
SQL> show user
USER is "ANDREW"
SQL> grant select on blah to john
  2  /
 
Grant succeeded.
 
SQL> grant select on blah to andrew
  2  /
grant select on blah to andrew
                        *
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from
yourself
 
SQL> revoke select (more...)

How to Rename a Table

I tested this example in Oracle 12.1. First I created a table:

SQL> create table fred
  2  as select * from user_synonyms
  3  where 1 = 2
  4  /
 
Table created.

SQL>

Then I checked its object_id for later:

SQL> select object_id
  2  from user_objects
  3  where object_name = 'FRED'
  4  /
 
OBJECT_ID
----------
     92212

SQL>

... and described it:

SQL> desc fred
Name                       Null?    (more...)

ORA-12899

This was tested on Oracle 11.2. If you try to update a column with a value which is too long, you will get an ORA-12899:
 
SQL> conn andrew/reid
Connected.
SQL> create table tab1 (first_name varchar2(10))
  2  /
 
Table created.
 
SQL> insert into tab1 values ('Christopher')
  2  /
insert into tab1 values ('Christopher')
                         *
ERROR at line 1:
ORA-12899: value too large for column
"ANDREW"."TAB1"."FIRST_NAME" (actual: (more...)

enq: SQ – contention

This example shows how to deal with the enq: SQ – contention wait event. It was tested on Oracle 11.2. First I created a sequence and queried the amount of time already spent on this wait event:
 
SQL> create sequence seq1
  2  /
 
Sequence created.
 
SQL> select time_waited/100
  2  from v$system_event
  3  where event = 'enq: SQ - contention'
  4  /
 
no rows selected
 
SQL>

ORA-12985

If you try to drop a column from a table in a read only tablespace, you get an ORA-12985. If you really need to drop the column, you must put the tablespace back into read write mode first. You can see what I mean in the example below, which I tested in an Oracle 9 database:
 
SQL> create tablespace andrews_ts
  2  datafile '/tmp/andrew.dbf'
  3  size 1m
  4  /
 
Tablespace (more...)

PLSQL_OPTIMIZE_LEVEL

This parameter controls how much optimization Oracle does to PL/SQL code when it is compiled.
 
In Oracle 10, valid values were 0, 1 and 2:
 
Oracle 10: sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Apr 3 17:41:02 2014
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit (more...)

ORA-29339

I discovered a new error today. I was using Data Pump to copy a database from Sun SPARC to X86 and from Oracle 11.1.0.6.0 to 11.2.0.4.0 for testing purposes. I extracted the tablespace creation SQL from the old database like this. (This is only some of it.):
 
SQL> set lines 32000
SQL> set pages 50
SQL> exec dbms_metadata.set_transform_param(-
> DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
 
PL/SQL procedure (more...)

How to Diagnose a Locking Issue

A user ran an update statement and noticed that it did not seem to be doing anything:
 
SQL> conn gordon/bennett
Connected.
SQL> update andrew.emp
  2  set ename = 'COLIN'
  3  where ename = 'BRIAN'
  4  /
 
I looked up his SID in V$SESSION:
 
SQL> select sid from v$session
  2  where username = 'GORDON'
  3  /
 
       SID
----------
      393
 
SQL>

I ran the following (more...)

ORA-00957

I tested this on Oracle 11.2. If you try to use a column name more than once in an Oracle table, you get an ORA-00957

SQL> create table tab1
  2  (col1 number,
  3   col1 number)
  4  /
col1 number)
*
ERROR at line 3:
ORA-00957: duplicate column name
 
SQL>

Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list
  2  as select * from dba_objects
  3  /

Table created.

SQL>

When you create a table it has no statistics so (more...)

ORA-01442

This was tested on Oracle 11.2. If you define a column as NOT NULL then try to modify it to NOT NULL again, you get an ORA-01442:

SQL> create table tab1 (col1 number not null)
  2  /
 
Table created.
 
SQL> alter table tab1 modify (col1 number not null)
  2  /
alter table tab1 modify (col1 number not null)
                         *
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is
already NOT NULL
 
SQL>

INVALID Materialized View with COMPILATION_ERROR

In this example, Andrew grants access on a table to a role then grants this role to John. John creates a materialized view on Andrew’s table. Andrew updates his table, making John’s materialized view INVALID. John then tries to compile his materialized view, which gives it a COMPILE_STATE of COMPILATION_ERROR. Andrew then grants access to his table directly to John and the problem goes away. It was tested on Oracle 11.2:
 
First, user (more...)

ORA-12983

If you try to drop all columns in a table, you get an ORA-12983 as you cannot have a table with no columns. You can see what I mean in the examples below, which I ran in an Oracle 11.2 database:

SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> alter table tab1 drop column col1
  2  /
alter table tab1 drop column col1
*
ERROR (more...)

How to Automatically Trace a User’s Sessions

This example shows how you can use a logon trigger to automatically trace all sessions for a given user. This can be useful where an application is launched from the desktop and then fails before the DBA has had time to identify the SID and SERIAL# and start tracing it. First create the user:

SQL> conn system/manager@adhoc
Connected.
SQL> create user andrew identified by reid
  2  /

User created.

SQL> grant create session,
  (more...)

ORA-10636

I tested this in an Oracle 11.2.0.1.0 database. I created a table with lots of data:
 
SQL> create table andrews_table
  2  as select * from dba_tables
  3  /
 
Table created.
 
SQL> begin
  2  for a in 1..5 loop
  3  insert into andrews_table select * from andrews_table;
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL>

Using RMAN to Clone a Database into a Different Database on the Same Server

This was tested on Oracle 11.2. I had an input or target database as follows:
 
SQL> select name from v$database
  2  /
 
NAME
---------
ORCL1
 
SQL> l
  1  select tablespace_name, file_name
  2* from dba_data_files order by 1
SQL> /
 
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------
SYSAUX          /database/RMAN_test/ORCL1/sysaux1.dbf
SYSTEM          /database/RMAN_test/ORCL1/system1.dbf
UNDOTBS1        /database/RMAN_test/ORCL1/undo1.dbf
 
SQL>
 
I wanted to create an output or auxiliary or clone database called (more...)

ORA-01921

This was tested on Oracle 11.2. If you try to create a role which already exists, you get an ORA-01921:
 
SQL> create role dba
  2  /
create role dba
            *
ERROR at line 1:
ORA-01921: role name 'DBA' conflicts with another user
or role name
 
SQL>
 
You can check the names of existing roles by looking in DBA_ROLES something like this:
 
SQL> select count(*) from dba_roles

INVALID Materialized View NEEDS_COMPILE

This example was tested on Oracle 11.2. It shows how an on demand materialized view becomes invalid following DML on the underlying table. First I created a table:

SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL>

Then I inserted the value 1 into it: 

SQL> insert into tab1 values (1)
  2  /
 
1 row created.
 
SQL>

I created an on demand materialized view on (more...)

ORA-13504

This was tested on Oracle 11.2.0.4. If you try to create a database like this i.e. without specifying a SYSAUX datafile clause:
 
create database extent management local
datafile '/database/NBAPERF/nba_system/system1.dbf'
size 100m autoextend on
logfile
group 1 ('/database/NBAPERF/nba_redo1/log1a.rdo',
         '/database/NBAPERF/nba_redo2/log1b.rdo') size 50m,
group 2 ('/database/NBAPERF/nba_redo1/log2a.rdo',
         '/database/NBAPERF/nba_redo2/log2b.rdo') size 50m
maxlogfiles 3
maxlogmembers 3
user sys identified by secret_password
user system identified by secret_password
default temporary tablespace (more...)