FAST Refresh of Materialized View Returns ORA-12004

I tried to do a FAST refresh of a materialized view in Oracle 11.2 but it failed with an ORA-12004:
 
SQL> begin
  2  dbms_mview.refresh('ebase.m_gridpoint2',method=>'F');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view
"EBASE"."M_GRIDPOINT2"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 2

GROUP BY and ORDER BY

In 1997, I was sent on a 1 day Introduction to Oracle course. The lecturer said that if you used a GROUP BY, you could not assume that the results would be returned in order. If you wanted to make sure, you should include an ORDER BY as well. From a theoretical point of view, he was correct as an RDBMS is not obliged to return results in any particular order unless you tell (more...)

ORA-00361

This was tested on an Oracle 9 database. 

One way to relocate redo log groups is to drop members in one location and recreate them in another. However, if your redo log group only has one member and you want to relocate it, you need to create the new member first. If you don’t do this, you get an ORA-00361 as a redo log group must always have at least one member:
 

How I Copied Triggers from one Database to Another

(This happened on Oracle 11.2.) In an earlier example, I started to look at doing a full expdp and impdp. I'm not sure why, but the Data Pump import produced a number of errors. There were well over 200 like this: 

ORA-39112: Dependent object type TRIGGER:"SRCE"."ADDRESS_BIU" skipped, base object type TABLE:"SRCE"."ADDRESS" creation failed 

This message suggested that the SRCE.ADDRESS table had not been created in the output (more...)

sqlplus -prelim

If all the available processes in a database are used up, you will get an ORA-00020 when you try to login. The best way round this is to get some people to log out. However, this may not be possible e.g. if people have logged in from some application then killed it, leaving their sessions alive in the database but with no way to get back to them and end them tidily.
 
Oracle (more...)

Soundex

I found a booklet called Introduction to SQL Version 1.0, printed in 1985 and 1989. It was written by a certain Lawrence Ellison and described a function called SOUNDEX, which I had never heard of before so I tried it out on an Oracle 9 database.

SOUNDEX represents the sound of a word by a 4 character alphanumeric code. Words which sound alike should have the same code. This allows you to (more...)

ORA-00065

For those of you still  on Oracle 9, here is a summary of bug 3368245. It caught me out on a few occasions until I looked it up on Metalink. If you are using a server parameter file, setting fixed_date to none causes an ORA-00065 the next time you open the database:

TEST9 > sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Feb 11 08:40:19 2011

Copyright (c) (more...)

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

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