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)

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 and above but I tested it on Oracle 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...)


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


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

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


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 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  /


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
SQL> create table tab1(col1 number)
  2  (more...)

DBMS_STATS Causes ORA-00600 [15851]

| Apr 3, 2013
I noticed this in an Oracle 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: