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.

ORA-01090

The first screen print below shows 3 sessions on the same UNIX machine.

 

In the first session, I connected to ORCL as an externally identified user at 14:10:46.

 

In the second session, I connected to ORCL as SYS at 14:12:07 and issued a shutdown command. There are several ways to do this e.g. shutdown normal, shutdown transactional, shutdown immediate, shutdown abort and shutdown timeout. The default, if none of these options are specified, is shutdown normal. This causes the shutdown command to wait until all other sessions have logged out and this is what happened here.

 

Once you have run a shutdown command, Oracle does not allow other users to connect to the database. In the third session below, I tried to connect to database ORCL as an externally identified user at 14:14:13. This connection failed with an ORA-01090. As usual, click on the images to enlarge them and bring them into focus:

   

The second screen print below shows sessions 1 and 2 above a little later. The externally identified user logged out of the database in session 1 at 14:18:51. This allowed the shutdown command in session 2 to complete:

 

Swapping

You can see if any swapping has taken place since a UNIX server was started by running vmstat –s. If you see values other than zero on the swap ins or swap outs lines, then swapping has occurred:

Solaris > vmstat -s

     4123 swap ins

      118 swap outs

    12369 pages swapped in

    35254 pages swapped out

93593074720 total address trans. faults taken

16175338065 page ins

67075526 page outs

167117261315 pages paged in

153673896 pages paged out

157281194355 total reclaims

157268182842 reclaims from free list

        0 micro (hat) faults

93593074720 minor (as) faults

12433082155 major faults

22790008329 copy-on-write faults

30659757855 zero fill page faults

228888590 pages examined by the clock daemon

      112 revolutions of the clock hand

146623357 pages freed by the clock daemon

251218086 forks

  8106297 vforks

197058709 execs

994409866209 cpu context switches

584512421794 device interrupts

135390836607 traps

2614749602197 system calls

328390806433 total name lookups (cache hits 99%)

7844654930 user   cpu

8057496148 system cpu

25835471029 idle   cpu

        0 wait   cpu

Solaris >

How to See the Version Number of an Oracle Client

If you want to find out the version number of an Oracle client from Windows, open a Command Prompt, type tnsping and press Enter / Return. No parameters are required. You will see the Oracle version number (10.2.0.4.0 in this case) and whether it is 32-bit or 64-bit. As usual, click on the image to enlarge it and bring it into focus:

Another option, suggested by Laurent Schneider in the first comment at the end, is to use sqlplus -v:

Tru64 > sqlplus -v

SQL*Plus: Release 9.2.0.7.0 - Production

Tru64 >

H:\>sqlplus -v

SQL*Plus: Release 10.2.0.4.0 - Production

H:\> 

Solaris > sqlplus -v

SQL*Plus: Release 11.2.0.2.0 Production 

Solaris >

%NOTFOUND

This simple example was tested on Oracle 11.2. It uses SQL*Plus to list the tables owned by a user and counts the rows in each one:

SQL> select table_name

  2  from dba_tables

  3  where owner = 'ANDREW'

  4  /

 

TABLE_NAME

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

T1

T2

T3

 

SQL> select count(*) from andrew.t1

  2  /

 

  COUNT(*)

----------

      3082

 

SQL> select count(*) from andrew.t2

  2  /

 

  COUNT(*)

----------

      4851

 

SQL> select count(*) from andrew.t3

  2  /

 

  COUNT(*)

----------

     73942

 

SQL>

Then it does the same thing in PL/SQL. Note the use of %NOTFOUND to jump out of the loop when there is nothing more to return from the cursor:

SQL> DECLARE

  2   CURSOR c1 is

  3    SELECT owner||'.'||table_name fqn

  4    FROM dba_tables

  5    WHERE owner = 'ANDREW';

  6   c1_rec c1%ROWTYPE;

  7   row_count NUMBER;

  8   sql_statement VARCHAR2(200);

  9  BEGIN

10   OPEN c1;

11    LOOP

12     FETCH c1 INTO c1_rec;

13     EXIT WHEN c1%NOTFOUND;

14     sql_statement :=

15      'SELECT COUNT(*) FROM '||c1_rec.fqn;

16     DBMS_OUTPUT.PUT_LINE(sql_statement);

17     EXECUTE IMMEDIATE sql_statement

18      INTO row_count;

19     DBMS_OUTPUT.PUT_LINE(c1_rec.fqn||

20      ' has '||row_count||' rows');

21    END LOOP;

22   CLOSE c1;

23  END;

24  /

SELECT COUNT(*) FROM ANDREW.T1

ANDREW.T1 has 3082 rows

SELECT COUNT(*) FROM ANDREW.T2

ANDREW.T2 has 4851 rows

SELECT COUNT(*) FROM ANDREW.T3

ANDREW.T3 has 73942 rows

 

PL/SQL procedure successfully completed.

 

SQL>