ORA-01502

This example was tested on Oracle 11.2.0.2.7. It looks at why an index might become unusable. First I created a table in the SYSTEM tablespace by mistake:

SQL> create table andrews_table

  2  tablespace system

  3  as select * from dba_tables

  4  /

 

Table created.

 

SQL>

I created an index on the table and checked that it was VALID:

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  /

 

Index created.

 

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

VALID

 

SQL>

Then I made sure that Oracle would tell me if it tried to use an UNUSABLE index:

SQL> alter session

  2  set skip_unusable_indexes = false

  3  /

 

Session altered.

 

SQL>

I ran some SQL which would use the index and it worked OK:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>

Then I moved the table to the correct tablespace:

SQL> alter table andrews_table

  2  move tablespace users

  3  /

 

Table altered.

 

SQL>

This invalidated the index:

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

UNUSABLE

 

SQL>

So when I tried to use it I got an ORA-01502:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

select count(*) from andrews_table

*

ERROR at line 1:

ORA-01502: index 'OPS$ORACLE.ANDREWS_INDEX' or

partition of such index is in unusable state

 

SQL>

To fix this, I rebuilt the index:

SQL> alter index andrews_index rebuild

  2  /

 

Index altered.

 

SQL>

This made the index VALID:

SQL> select status from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

 

STATUS

--------

VALID

 

SQL>

... and I was able to use it again:

SQL> select count(*) from andrews_table

  2  where table_name = 'BLAH'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>

NOSORT and ORA-01409

When you create an index, Oracle usually does a sort. I read about the NOSORT option recently. This allows Oracle to create an index without doing a sort. I decided to give it a try on an Oracle 9.2.0.7.0 database. First I created a table, counted the number of sorts my session had done, created an index on the table, counted the number of sorts again and saw that it had increased by 1:

 

SQL> create table andrews_table

  2  as select owner, table_name

  3  from dba_tables

  4  /

 

Table created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

-------------------- ----------

sorts (memory)               17

sorts (disk)                  0

sorts (rows)              11527

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  /

 

Index created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

-------------------- ----------

sorts (memory)               18

sorts (disk)                  0

sorts (rows)              13069

 

SQL>

 

Then I dropped the index and tried to recreate it with the NOSORT option. I expected this to fail as the table was not ordered on the indexed column:

 

SQL> drop index andrews_index

  2  /

 

Index dropped.

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  nosort

  4  /

on andrews_table(table_name)

   *

ERROR at line 2:

ORA-01409: NOSORT option may not be used; rows are not

in ascending order

 

SQL>

 

Finally, I dropped and recreated the table in table_name order, counted the number of sorts my session had done, created an index with the NOSORT option on the sorted table_name column, counted the number of sorts again and saw that it had not increased:

 

SQL> drop table andrews_table

  2  /

 

Table dropped.

 

SQL> create table andrews_table

  2  as select owner, table_name

  3  from dba_tables

  4  order by table_name

  5  /

 

Table created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

-------------------- ----------

sorts (memory)               35

sorts (disk)                  0

sorts (rows)              26085

 

SQL> create index andrews_index

  2  on andrews_table(table_name)

  3  nosort

  4  /

 

Index created.

 

SQL> select a.name, b.value

  2  from v$sysstat a, v$mystat b

  3  where a.statistic# = b.statistic#

  4  and a.name like '%sorts%'

  5  /

 

NAME                      VALUE

-------------------- ----------

sorts (memory)               35

sorts (disk)                  0

sorts (rows)              26085

 

SQL>

V$SGASTAT

You can use the query below to show the amount of free memory in your shared pool. It was run on an Oracle 11.1.0.6.0 database:

 

SQL> l

  1  select bytes from v$sgastat

  2  where pool = 'shared pool'

  3* and name = 'free memory'

SQL> /

 

     BYTES

----------

390505176

 

SQL>

 

If this figure remains high and you have insufficient physical memory on your server, you could consider reducing the size of your shared pool. This would allow you to reuse the memory elsewhere.

The Curious Case of the Missing PL/SQL Procedure

I have to run files of SQL on databases for developers almost every day. These files often create or recreate PL/SQL packages. Any errors usually go back to the developers for correction as I am not a PL/SQL expert. Yesterday I ran some of these files then a tester asked me why a screen had stopped working. The screen was calling a PL/SQL package which I had just recreated for the developer. I will call it package_b. It had compilation errors and these were causing the screen to fail. The developer was on leave so I decided to have a look at the problem. I listed the compilation errors and could see that they happened when package_b tried to call a new procedure in another package. I will call the other package package_a and I will call the new procedure procedure_a2. I looked for compilation errors in package_a but there were none. I looked at the description for package_a but procedure_a2 was not mentioned. This was because the developer had provided a new body for package_a but no new header. The new header was found and package_a was recompiled. It was then possible to recompile package_b and the tester’s screen started to work again. I have reproduced this problem below on Oracle 11.2.0.2.7:

 

I created package_a and showed that its description contained procedure_a1:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  end package_a;

  4  /

 

Package created.

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  end package_a;

  7  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

I created package_b containing procedure_b1. This procedure called package_a.procedure_a1. Then I ran package_b.procedure_b1 successfully:

 

SQL> create or replace package package_b is

  2  procedure procedure_b1;

  3  end package_b;

  4  /

 

Package created.

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  end procedure_b1;

  6  end package_b;

  7  /

 

Package body created.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

 

I added procedure_a2 to package_a but did not change its header. Then I described it but could only see procedure_a1:

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  procedure procedure_a2 is

  7  begin

  8  null;

  9  end procedure_a2;

10  end package_a;

11  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

Then I changed package_b to call package_a.procedure_a2 but it failed to compile:

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  package_a.procedure_a2;

  6  end procedure_b1;

  7  end package_b;

  8  /

 

Warning: Package Body created with compilation errors.

 

SQL> show errors

Errors for PACKAGE BODY PACKAGE_B:

 

LINE/COL

-------------------------------------------------------

ERROR

-------------------------------------------------------

5/1

PL/SQL: Statement ignored

 

5/11

PLS-00302: component 'PROCEDURE_A2' must be declared

 

SQL>

 

I changed the header for package_a. Then its description included procedure_a2:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  procedure procedure_a2;

  4  end package_a;

  5  /

 

Package created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

PROCEDURE PROCEDURE_A2

 

SQL>

 

This allowed me to compile package_b and run package_b.procedure_b1 again:

 

SQL> alter package package_b compile

  2  /

 

Package altered.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

How to Move System Tablespace Datafiles

In an earlier post I explained how to move datafiles. The method I used then is NOT suitable for datafiles belonging to the system, undo or temporary tablespaces as they cannot be taken offline. I said I would publish a post about this in due course and here it is.

I created a database using dbca recently. I was in a hurry and forgot to put a slash at the end of the directory name where I wanted to put the datafiles so they ended up like this:

SQL> l
  1  select file_id, file_name
  2  from dba_data_files
  3* order by 1
SQL> /

   FILE_ID FILE_NAME
---------- ------------------------------------------
         1 /agasprd/qcsprod/qcs_systemsystem01.dbf
         2 /agasprd/qcsprod/qcs_systemsysaux01.dbf
         3 /agasprd/qcsprod/qcs_systemundotbs01.dbf
         4 /agasprd/qcsprod/qcs_systemusers01.dbf
         5 /agasprd/qcsprod/qcs_data/qc_data_a.dbf

SQL>

Files 1 to 4 were in the wrong place. File 5, which was created afterwards, is OK. I closed the database and backed it up. Then I renamed the files in UNIX:

UNIX > pwd
/agasprd/qcsprod
UNIX > mv qcs_systemsystem01.dbf qcs_system/system01.dbf
UNIX > mv qcs_systemsysaux01.dbf qcs_system/sysaux01.dbf
UNIX > mv qcs_systemundotbs01.dbf qcs_system/undotbs01.dbf
UNIX > mv qcs_systemusers01.dbf qcs_system/users01.dbf
UNIX >

I mounted the database:

UNIX > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 6 14:12:20 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             390075096 bytes
Database Buffers          121634816 bytes
Redo Buffers                8232960 bytes
Database mounted.

SQL>

... and renamed the files in the database 1 by 1:

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemsystem01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/system01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemsysaux01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/sysaux01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemundotbs01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/undotbs01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemusers01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/users01.dbf'
SQL> /

Database altered.

SQL>

Finally, I opened the database:

SQL> alter database open;

Database altered.

SQL> select file_id, file_name
  2  from dba_data_files
  3  order by 1;

   FILE_ID FILE_NAME
---------- ---------------------------------------------
        1 /agasprd/qcsprod/qcs_system/system01.dbf
        2 /agasprd/qcsprod/qcs_system/sysaux01.dbf
        3 /agasprd/qcsprod/qcs_system/undotbs01.dbf
        4 /agasprd/qcsprod/qcs_system/users01.dbf
        5 /agasprd/qcsprod/qcs_data/qc_data_a.dbf

SQL>

The tempfile was in the wrong place too but I dealt with that by recreating the temporary tablespace.