How to See the Height of an Index

This example shows where to find the height of an index. I tested it on Oracle 11.2. First I deleted the index's statistics:

SQL> exec dbms_stats.delete_index_stats -
> ('uimsmgr','ubbchst_serv_index');
 
PL/SQL procedure successfully completed.
 
SQL>
 
Then I checked its BLEVEL was null:

SQL> select nvl(blevel,999)
  2  from dba_indexes
  3  where owner = 'UIMSMGR'
  4  and index_name = 'UBBCHST_SERV_INDEX'
  5  /
 
NVL(BLEVEL,999)
---------------
            999
 
SQL>

Bug 8477973

I ran the following query on an Oracle 11.1.0.6 database but it failed with an ORA-02020:
 
SQL> SELECT ppc.sttlmnt_dt day_date
  2  FROM   vrm_d18_ppc   ppc,
  3         meter_nhh     mtr,
  4         mtd_registers reg
  5  WHERE  ppc.tm_pttrn_rgm = reg.tpr
  6  AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id
  7  AND    ppc.prfl_clss_id = mtr.profile_class_id
  8  AND    ppc.gsp_grp_id = mtr.gsp_group_id
  9  AND    reg. (more...)

Worked Examples with the SET ROLE Command

Before I start, I wonder if anybody can help me. Some time ago, I saw several adverts on the Internet similar to the one below:

I am looking for an Oracle DBA (French speaking) - Hampshire (South Coast of England)

Languages: ENGLISH and FRENCH


We are recruiting for a major blue chip company based in the South of English (Hampshire area), where we seek a proven Oracle Database Administrator (HPUX, Linux, and AIX) who can (more...)

ORA-02391

I tested this on an Oracle 11.1 database.
 
Oracle profiles control how certain database resources are allocated to a user session. They also define some security rules. When you create a user, it is assigned a profile and, if you do not specify it explicitly, the DEFAULT profile will be used:
 
SQL> grant create session to andrew
  2  identified by reid
  3  /
 
Grant succeeded.
 
SQL> select profile (more...)

Bug 5497611

I used Oracle Enterprise Manager to look at the execution plan for some SQL in an Oracle 10.2.0.3 database. (The SQL shown is just an example done later for the purposes of this blog post. As usual, click on the image to enlarge it and bring it into focus if necessary.):


This produced the following ORA-00600 message several times in the alert log:
 
Wed Oct  1 18:13:21 2014
Errors in (more...)

OPTIMIZER_MODE = FIRST_ROWS_N

I have known about the first_rows optimizer mode for some time. This tells Oracle to use an execution path which will return the first few rows as quickly as possible. However, I recently read about the first_rows_n optimizer mode, which apparently appeared first in Oracle 9. This tells Oracle to use an execution path which will return the first n rows quickly, where n can be 1, 10, 100 or 1000. I decided to try (more...)

PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME

I tested this on Oracle 11.2. I created a profile called FOR_ANDREW with PASSWORD_REUSE_MAX set to 1. This meant that I could not reuse a password until I had used one other password first:
 
SQL> conn / as sysdba
Connected.
 
SQL> create profile for_andrew
  2  limit password_reuse_max 1
  3  /
 
Profile created.
 
SQL>
 
I created a user called ANDREW and gave him the FOR_ANDREW profile:
 

ORA-01123

I was reading through an old book recently and it said that you could not put a tablespace into hot backup mode if the database was in NOARCHIVELOG mode. This seemed reasonable to me but I wondered what might happen if you tried to do this so I ran the following SQL in an Oracle 11.2 database:  

SQL> l
  1* alter tablespace users begin backup
SQL> /
alter tablespace users (more...)

How to See When Your Database Was Opened

This was tested on Oracle 11.2: 

SQL> l
  1* select startup_time from v$instance
SQL> /
 
STARTUP_TIME
------------
24-MAY-12
 
SQL>


FIXED_DATE and LAST_ANALYZED

I tested this on Oracle 11.2.0.1. You need to be careful when looking at the LAST_ANALYZED column in USER_TABLES. I created a table and removed the FIXED_DATE parameter from the database:
 
SQL> create table t1 (c1 number)
  2  /
 
Table created.
 
SQL> alter system set fixed_date = none
  2  /
 
System altered.
 
SQL>
 
…then I used DBMS_STATS.GATHER_TABLE_STATS and the old ANALYZE command to (more...)

ORA-01460 on PL/SQL Developer

An Oracle 9 database had the following character set:
 
SQL> l
  1  select sys_context('userenv','LANGUAGE')
  2* from dual
SQL> /
 
SYS_CONTEXT('USERENV','LANGUAGE')
----------------------------------------
ENGLISH_UNITED KINGDOM.WE8ISO8859P15
 
SQL>
 
I used PL/SQL Developer to look at the code in the database (as usual, click on the image to enlarge it and bring it into focus):

 
Somebody typed the following command in the database by mistake:
 
SQL> l
  1* create database (more...)

A Simple Example with Indexes

I did this worked example on Oracle 11.2. First I created a table:

SQL> create table t1
  2  as select * from dba_segments
  3  /
 
Table created.
 
SQL>
 
…then I made sure it contained enough data:
 
SQL> begin
  2  for a in 1..8 loop
  3  insert into t1 select * from t1;
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure (more...)

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.