kgxgncin: CLSS init failed with status 3

At the end of September, I will be walking 26.2 miles (the marathon distance) in aid of Great Ormond Street Hospital If you find the post below useful and you have any money to spare, you might like to click on the Just Giving link on the right of this page and sponsor me.

I had a problem with an Oracle 11.1.0.6.0 database producing trace files every few minutes. Here (more...)

DBA_FEATURE_USAGE_STATISTICS

Oracle introduced this view in version 10. It looks like this in version 11:
 
SQL> desc dba_feature_usage_statistics
Name                       Null?    Type
-------------------------- -------- ------------------
DBID                       NOT NULL NUMBER
NAME                       NOT NULL VARCHAR2(64)
VERSION                    NOT NULL VARCHAR2(17)
DETECTED_USAGES            NOT NULL NUMBER
TOTAL_SAMPLES              NOT NULL NUMBER
CURRENTLY_USED                      VARCHAR2(5)
FIRST_USAGE_DATE                    DATE
LAST_USAGE_DATE                     DATE
AUX_COUNT                           NUMBER
FEATURE_INFO                        CLOB
LAST_SAMPLE_DATE                    DATE
LAST_SAMPLE_PERIOD                  NUMBER
SAMPLE_INTERVAL                     NUMBER
DESCRIPTION                         VARCHAR2(128)
 
SQL>
 
As its name suggests, it allows you to see (more...)

1 + NULL = NULL

If you try to add a null to a number, the result is a null. You can see what I mean in the example below, which I tested in Oracle 10:
 
SQL> select 1 from dual
  2  /
 
         1
----------
         1
 
SQL> select nvl(null,'null') from dual
  2  /
 
NVL(NULL,'NULL')
----------------
null
 
SQL> select nvl(to_char(1+null),'null') from dual
  2  /
 
NVL(TO_CHAR(1+NULL),'NULL')
---------------------------
null
 
SQL>

MDSYS.SDO_COORD_AXES

This was tested on Oracle 11.2. If you need to look at the ORDER column in the above-mentioned table, there only seems to be one way to do it i.e. in upper case and surrounded by double quotes as shown below. I guess this is because ORDER is a reserved word:
 
SQL> desc mdsys.sdo_coord_axes
Name                       Null?    Type
-------------------------- -------- ------------------
COORD_SYS_ID               NOT NULL NUMBER(10)
COORD_AXIS_NAME_ID                  NUMBER(10)
COORD_AXIS_ORIENTATION              VARCHAR2(24)
COORD_AXIS_ABBREVIATION             VARCHAR2(24)

Integer Arithmetic

I was looking at the problem of the 3rd party application which is generating ORA-01426 errors when I found the following on My Oracle Support. According to Oracle, this issue affects version 11.2.0.4 and above but I tested it on Oracle 11.2.0.1. Since version 10g, Oracle has used integer arithmetic when it can. The largest integer result it can hold is not very long so you can easily get (more...)

DBMS_MONITOR.SESSION_TRACE_ENABLE

This happened on Oracle 11.2. I had a problem with a 3rd party application hosted in the UK with users in another country. The front-end application was failing regularly with ORA-01426. I needed to know which SQL was causing these errors. First I logged into a test database as user ORACLE:
 
SQL> show user
USER is "ORACLE"
SQL>
 
Then I started a trace of this session as follows:
 

Read Only Tables

Oracle 11 introduced a new ALTER TABLE syntax, which allows you to make a table READ ONLY, so I decided to try it out. First I created a table:

SQL> create table table_list
  2  as select table_name from dba_tables
  3  /
 
Table created.

SQL>

... then I made it READ ONLY:
 
SQL> alter table table_list read only
  2  /
 
Table altered.

... so when I tried to update it, (more...)

V$RECOVERY_FILE_DEST

The V$RECOVERY_FILE_DEST view shows details about the flash recovery area. In the example below, it is queried to see the maximum size. This is then increased from 4 gigabytes to 5 gigabytes and the view is queried again to see the change: 

ORCL /export/home/oracle > sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 18 16:08:48 2013
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Taking a Datafile Offline in NOARCHIVELOG Mode

I was reading a colleague’s copy of Oracle RMAN 11g Backup and Recovery by Robert Freeman. It said that you could not take a datafile offline if the database was in NOARCHIVELOG mode. I don’t know much about taking datafiles offline but this seemed reasonable so I gave it a try in Oracle 12.1. First I checked the database’s LOG_MODE: 

SQL> select log_mode from v$database
  2  /
 
LOG_MODE
------------
NOARCHIVELOG

SHUTDOWN TRANSACTIONAL

If you run this command, Oracle waits until all users have committed or rolled back any outstanding transactions before closing the database. To test this out in Oracle 12.1, I tried to close a database from a session with an outstanding transaction. This failed with an ORA-01097. Once I had committed the transaction, I was able to close the database successfully:

SQL> show user
USER is "SYS"
SQL> create table tab1(col1 number)
  2  (more...)

Wrap Utility

This is an example using the wrap utility, which allows you to hide stored code. Software suppliers can use it to prevent customers stealing their PL/SQL. First I created a table:
 
SQL> create table name_list
  2  as select 'ANDREW' name from dual
  3  /
 
Table created.
 
SQL> select * from name_list
  2  /
 
NAME
------
ANDREW
 
SQL>
 
Then I created a procedure to add names to (more...)

The USER Keyword

Here are a couple of examples with the USER keyword, which I tested on Oracle 11.2. You can use it after the SHOW command or in a SELECT statement as follows. It returns the name of the user running the current session: 

SQL> conn fred/bloggs
Connected.
SQL> show user
USER is "FRED"
SQL> select user from dual
  2  /
 
USER
------------------------------
FRED

SQL>

You can also include it in a WHERE (more...)

ORA-00031, ORA-03113 and ORA-03114

This was tested on Oracle 11.2. I had a session in the middle of a long-running operation: 

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(3600);
 
I tried to kill it like this but Oracle gave me an ORA-00031 instead:
 
SQL> select sid, serial# from v$session
  2  where username = 'USER1'
  3  /
 
       SID    SERIAL#
---------- ----------
         7         62
 
SQL> alter system kill session '7,62'

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