Monday, December 31, 2012

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>

In Spanish / en español 

Thursday, December 27, 2012

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>

Monday, December 24, 2012

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.

In Spanish / en español  

Tuesday, December 18, 2012

Please Answer my Poll

Another year is nearly over. For many of us, this means an annual appraisal and, if we are very lucky, details of the training we will receive next year.

I decided to look and see what is available. I found a company called Firebrand and looked at their OCA 11g course.

This gave me the idea for the poll to the right of this post. It will run until I have had 100 replies then I will publish the results.

Now have a go at the three simple questions below. They were tested on Oracle 11.2.0.2.7. I made them up myself so I cannot guarantee that they will come up in an exam. The answers and explanations are all within this post. If they prove popular, I will publish some more with the answers at a different URL:
 
Question 1:

Which of the statements below creates a view which allows data to be selected but not updated?
 
(a)
create or replace view my_view
as select * from my_table
where last_analyzed > '01-JAN-2012'
with check option
/
 
(b)
create or replace view my_view
as select * from my_table
where last_analyzed > '01-JAN-2012'
with read only
/
 
(c)
create or replace view my_view
as select * from my_table
where last_analyzed > '01-JAN-2012'
no update
/
 
(d)
None of the above.
 
Answer to question 1:

 
Option (a) is a valid SQL statement but it is incorrect as it does not stop you updating values via the view:

SQL> create or replace view my_view
  2  as select * from my_table
  3  where last_analyzed > '01-JAN-2012'
  4  with check option
  5  /
 
View created.
 
SQL> update my_view set owner = 'ANDREW'
  2  /
 
238 rows updated.
 
SQL>

I have already covered with check option here:

Option (b) is correct. If you create a view with read only and try to use it to update the underlying data, you get an ORA-42399:

SQL> create or replace view my_view
  2  as select * from my_table
  3  where last_analyzed > '01-JAN-2012'
  4  with read only
  5  /
 
View created.
 
SQL> update my_view set owner = 'ANDREW'
  2  /
update my_view set owner = 'ANDREW'
                   *
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a
read-only view
 
SQL>

Option (c) is not a valid SQL statement:

SQL> create or replace view my_view
  2  as select * from my_table
  3  where last_analyzed > '01-JAN-2012'
  4  no update
  5  /
no update
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
 
SQL>

Question 2:
 
Choose the only 2 system privileges from the list below:
 
(a) CREATE TABLE
(b) DROP TABLE
(c) CREATE INDEX
(d) MANAGE TABLESPACE
(e) ASSIGN USER
 
Answer to question 2:
 
The correct options are (a) and (d). The others are not valid:
 
SQL> grant create table to andrew
  2  /
 
Grant succeeded.
 
SQL> grant drop table to andrew
  2  /
grant drop table to andrew
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege
 
SQL> grant create index to andrew
  2  /
grant create index to andrew
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege
 
SQL> grant manage tablespace to andrew
  2  /
 
Grant succeeded.
 
SQL> grant assign user to andrew
  2  /
grant assign user to andrew
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege
 
SQL>

Question 3:
 
You want to create a tablespace using Oracle Managed Files but you get the following error:

SQL> create tablespace andrew;
create tablespace andrew
                       *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
 
SQL>

What should you do?

(a)
Make the tablespace locally managed using the following syntax:
create tablespace andrew extent management local;

(b)
Specify a file size like this:
create tablespace andrew datafile size 25m;
 
(c)
Download and apply the appropriate patch as this is a well known Oracle 11 bug.

(d)
Use the ALTER SYSTEM command to set the db_create_file_dest parameter and try again.
 
Answer to Question 3:
 
Option (a) results in the same error:


  1* create tablespace andrew extent management local
SQL> /
create tablespace andrew extent management local
                                               *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

Option (b) gives an ORA-02236:

  1* create tablespace andrew datafile size 25m
SQL> /
create tablespace andrew datafile size 25m
                                  *
ERROR at line 1:
ORA-02236: invalid file name

I haven’t checked option (c) carefully but I made it up myself and I am not aware of such a bug.
 
Option (d) is correct as you can see below:
 
  1  alter system set db_create_file_dest
  2*  = '/export/home/oracle/andrew'
SQL> /
 
System altered.
 
SQL> create tablespace andrew;
 
Tablespace created.
 
SQL>

Thursday, December 13, 2012

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>

Saturday, December 08, 2012

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.