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

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