ORA-01078

If you get an ORA-01078 when trying to open a database, this means there is an invalid value in the database’s parameter file:

 

Oracle 9: sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Dec 27 17:21:19 2013

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00110: syntax error at 'Andrew'

SQL>

 

When this happens, you should normally locate the line concerned in the parameter file, correct it then try again. However, on this occasion, I closed the database and added the invalid value myself. I then started copying the database to another server. The line I added was as follows, right at the start of the parameter file (although you can add it wherever you like):

 

Oracle 9: cat initORCL.ora

Andrew is copying this so it is down.

Etc

db_name=’ORCL’

Etc

Oracle 9:

 

I did this to stop another DBA opening the database by mistake. Once the copy has completed, I will remove the offending line from the parameter file and open the database again.

ORA-00360

Oracle returns an ORA-00360 if you try to drop a redo logfile member which does not exist: 

SQL> select group#, member from v$logfile

  2  order by 1, 2;

 

    GROUP# MEMBER

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

         1 /disk1/redo/log1a.rdo

         1 /disk2/redo/log1b.rdo

         2 /disk1/redo/log2a.rdo

 

SQL> alter database drop logfile member

  2  '/disk2/redo/log2b.rdo';

alter database drop logfile member

*

ERROR at line 1:

ORA-00360: not a logfile member: /disk2/redo/log2b.rdo

 

SQL>

Library Cache Pin and Library Cache Lock Wait Events

A developer was trying to compile a procedure without success recently so I decided to reproduce the problem for future reference. I did this in an Oracle 11.2 database. First I created a user then I used it to login, display my SID, create a long running procedure and kick it off. You can see this in the red session below:

 

SQL> conn / as sysdba

Connected.

SQL> create user andrew identified by reid

  2  /

 

User created.

 

SQL> grant create session, create procedure,

  2  select any dictionary to andrew

  3  /

 

Grant succeeded.

 

SQL> grant execute on dbms_lock to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

       296

 

SQL> create procedure lcp

  2  is

  3  begin

  4  sys.dbms_lock.sleep(3600);

  5  end;

  6  /

 

Procedure created.

 

SQL> exec lcp;

 

While this was happening, I logged in with the same user and tried to compile the procedure in the blue session below. You cannot compile a procedure while another session is running it so the compilation had to wait:

 

SQL> conn andrew/reid

Connected.

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

       490

 

SQL> alter procedure lcp compile

  2  /

 

I connected to the database as SYS in the green session below and used the SID to determine that the blue session was waiting for a library cache pin:

 

SQL> conn / as sysdba

Connected.

SQL> undefine sid

SQL> select time_waited/100

  2  from v$session_event

  3  where event = 'library cache pin'

  4  and sid = '&&sid'

  5  /

Enter value for sid: 490

old   4: and sid = '&&sid'

new   4: and sid = '490'

 

TIME_WAITED/100

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

          30.07

 

SQL> exec sys.dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select time_waited/100

  2  from v$session_event

  3  where event = 'library cache pin'

  4  and sid = '&&sid'

  5  /

old   4: and sid = '&&sid'

new   4: and sid = '490'

 

TIME_WAITED/100

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

          39.08

 

SQL>

 

I logged in as ANDREW and tried to compile the procedure again in the purple session which follows:

 

SQL> conn andrew/reid

Connected.

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

       394

 

SQL> alter procedure lcp compile

  2  /

 

I connected as an administrator then used the SID to determine that the purple session was waiting for a library cache lock. This happens when more than one session tries to compile the same procedure simultaneously. You can see this in the green session which follows:

 

SQL> undefine sid

SQL> select time_waited/100

  2  from v$session_event

  3  where event = 'library cache lock'

  4  and sid = '&&sid'

  5  /

Enter value for sid: 394

old   4: and sid = '&&sid'

new   4: and sid = '394'

 

TIME_WAITED/100

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

          24.05

 

SQL> exec sys.dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select time_waited/100

  2  from v$session_event

  3  where event = 'library cache lock'

  4  and sid = '&&sid'

  5  /

old   4: and sid = '&&sid'

new   4: and sid = '394'

 

TIME_WAITED/100

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

          33.07

 

SQL>

 

Finally, I used the script below to locate the session causing the problem. I gave it the SID of the session waiting for the library cache pin. It showed that SID 296 was holding a lock and SID 490 was waiting for one. Teminating SID 296 would allow both compilations to run:

 

SQL> conn / as sysdba

Connected.

SQL> select s.sid, kglpnmod "Mode", kglpnreq "Req"

  2  from x$kglpn p, v$session s, v$session_wait w

  3  where p.kglpnuse = s.saddr

  4  and p.kglpnhdl = w.p1raw

  5  and w.sid = &sid

  6  /

Enter value for sid: 490

old   5: and w.sid = &sid

new   5: and w.sid = 490

 

       SID       Mode        Req

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

       490          0          3

       296          2          0

 

SQL>

ORA-00068

This example, tested on an Oracle 9 database, shows the largest permitted value for the max_enabled_roles parameter:

SQL> alter system set max_enabled_roles = 150 scope = spfile
  2  /
alter system set max_enabled_roles = 150 scope = spfile
*
ERROR at line 1:
ORA-00068: invalid value 150 for parameter max_enabled_roles, must be between 1 and 148

SQL> alter system set max_enabled_roles = 148 scope = spfile
  2  /

System altered.

SQL>

Recursive Procedure Compilation

This was tested on Oracle 11.2. You cannot compile a procedure if anybody is running it so, if you write a procedure which compiles itself, it hangs:

This appears in V$SESSION_EVENT as a library cache pin:

… and, if you look in x$kglpn, you see that SID 296 is blocking and waiting simultaneously: 

SQL MINUS Operator

If you join two SELECT statements together with a MINUS, the output from the second SELECT is removed from the output from the first SELECT before the results are displayed. As always, it is easier to explain by using an example. First create a table for odd and even numbers and another table just for odd numbers:

SQL> create table odd_and_even
  2  (col1 number)
  3  /

Table created.

SQL> create table odd
  2  (col1 number)
  3  /

Table created.

SQL>

Now add some odd and even numbers to the first table then insert the contents again so that each number is stored twice (you will see why later):

SQL> insert into odd_and_even values (1);

1 row created.

SQL> insert into odd_and_even values (2);

1 row created.

SQL> insert into odd_and_even values (3);

1 row created.

SQL> insert into odd_and_even values (4);

1 row created.

SQL> insert into odd_and_even select * from odd_and_even;

4 rows created.

SQL> select * from odd_and_even

  2  /

      COL1

----------

         1

         2

         3

         4

         1

         2

         3

         4

8 rows selected.

SQL>

Now insert some odd numbers into the second table but do not replicate the values:

SQL> insert into odd values (1);

1 row created.

SQL> insert into odd values (3);

1 row created.

SQL> select * from odd

  2  /

      COL1

----------

         1

         3

SQL>

Now display the contents of the first table but before displaying the results, use the MINUS statement to remove any values which appear in the second table:

SQL> select col1 from odd_and_even

  2  minus

  3  select col1 from odd

  4  /

      COL1

----------

         2

         4

SQL>
  

Note that there were four odd numbers in the first table i.e. 1, 3, 1, 3 but only two odd numbers in the second table i.e. 1, 3. Even so, all the odd numbers have been subtracted from the output. This is equivalent to either of the following two SQL statements: 

SQL> select distinct col1 from odd_and_even

  2  where col1 not in

  3  (select col1 from odd)

  4  /

      COL1

----------

         2

         4

SQL> select distinct col1 from odd_and_even x

  2  where not exists

  3  (select col1 from odd

  4   where col1 = x.col1)

  5  /

      COL1

----------

         2

         4

SQL>

redo log space wait time, redo buffer allocation retries, redo blocks written and log_buffer

The redo log space wait time statistic records the total amount of time spent (since the instance was started) waiting for space in the redo log buffer. It is recorded in hundredths of a second:

 

SQL> select to_number(value)/100 "Seconds Waited"

  2  from v$sysstat

  3  where name = 'redo log space wait time'

  4  /

 

Seconds Waited

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

         96.63

 

SQL>

 

The redo buffer allocation retries statistic shows the total number of times a user process has had to wait for space in the redo log buffer (since instance startup again):

 

SQL> select value from v$sysstat

  2  where name = 'redo buffer allocation retries'

  3  /

 

VALUE

------

2463

 

SQL>

 

The lower these two statistics are, the better. However, you need to look at them in relation to the length of time the instance has been running. The figures above would be bad in an instance started 10 minutes ago but this Oracle 10.2.0.1.0 one was started in June:

 

SQL> select startup_time from v$instance

  2  /

 

STARTUP_TIME

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

15-JUN-13

 

SQL>

 

…and it is December now so the instance has been up for almost six months:

 

SQL> select sysdate from dual

  2  /

 

SYSDATE

---------

13-DEC-13

 

SQL>

 

You also need to compare them with the amount of redo activity. This instance has had its fair share so the wait statistics start to look even better:

 

SQL> select value from v$sysstat

  2  where name = 'redo blocks written'

  3  /

 

VALUE

----------

167877393

 

SQL>

 

Finally, you need to check whether the statistics are currently increasing. If they are high but stable right now, they may have caused a performance problem in the past but they are not causing one at present.

 

If these statistics caused problems in Oracle 9, you could try increasing the size of the log_buffer initialization parameter. You can check its value as follows:

 

SQL> select value from v$parameter

  2  where name = 'log_buffer'

  3  /

 

VALUE

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

2104320

 

SQL>

 

However, in more recent versions, Oracle calculates the value of this parameter for you. Fortunately, these statistics have never caused a problem in any databases I have had to monitor.

ORA-02289

This was tested on Oracle 11.2. You can create a sequence and select the next value from it like this: 

SQL> create sequence seq1

  2  /

 

Sequence created.

 

SQL> select seq1.nextval from dual

  2  /

 

   NEXTVAL

----------

         1

 

SQL> 

… but if you try to use a sequence which does not exist, you get an ORA-02289: 

SQL> select blah.nextval from dual

  2  /

select blah.nextval from dual

       *

ERROR at line 1:

ORA-02289: sequence does not exist

 

SQL>

How to Provide User Input to PL/SQL

Here are some simple examples tested on Oracle 11.2. You can use a variable beginning with one ampersand:

 

SQL> !cat accept1.sql

begin

dbms_output.put_line('Hello &your_name');

end;

/

 

If you do this, you are asked to input the data again when you rerun the script:

 

SQL> set echo on

SQL> set serveroutput on

SQL> @accept1

SQL> begin

  2  dbms_output.put_line('Hello &your_name');

  3  end;

  4  /

Enter value for your_name: Andrew

old   2: dbms_output.put_line('Hello &your_name');

new   2: dbms_output.put_line('Hello Andrew');

Hello Andrew

 

PL/SQL procedure successfully completed.

 

SQL> @accept1

SQL> begin

  2  dbms_output.put_line('Hello &your_name');

  3  end;

  4  /

Enter value for your_name: Brian

old   2: dbms_output.put_line('Hello &your_name');

new   2: dbms_output.put_line('Hello Brian');

Hello Brian

 

PL/SQL procedure successfully completed.

 

SQL>

 

You can use a variable beginning with two ampersands:

 

SQL> !cat accept2.sql

begin

dbms_output.put_line('Hello &&first_name');

end;

/

 

If you do this, PL/SQL remembers the value of the variable between one execution and the next:

 

SQL> @accept2

SQL> begin

  2  dbms_output.put_line('Hello &&first_name');

  3  end;

  4  /

Enter value for first_name: Colin

old   2: dbms_output.put_line('Hello &&first_name');

new   2: dbms_output.put_line('Hello Colin');

Hello Colin

 

PL/SQL procedure successfully completed.

 

SQL> @accept2

SQL> begin

  2  dbms_output.put_line('Hello &&first_name');

  3  end;

  4  /

old   2: dbms_output.put_line('Hello &&first_name');

new   2: dbms_output.put_line('Hello Colin');

Hello Colin

 

PL/SQL procedure successfully completed.

 

SQL>

 

Later, if you want to provide a new value, you can UNDEFINE the variable between executions:

 

SQL> undefine first_name

SQL> @accept2

SQL> begin

  2  dbms_output.put_line('Hello &&first_name');

  3  end;

  4  /

Enter value for first_name: David

old   2: dbms_output.put_line('Hello &&first_name');

new   2: dbms_output.put_line('Hello David');

Hello David

 

PL/SQL procedure successfully completed.

 

SQL> undefine first_name

SQL> @accept2

SQL> begin

  2  dbms_output.put_line('Hello &&first_name');

  3  end;

  4  /

Enter value for first_name: Elvis

old   2: dbms_output.put_line('Hello &&first_name');

new   2: dbms_output.put_line('Hello Elvis');

Hello Elvis

 

PL/SQL procedure successfully completed.

 

SQL>

 

You can accept the variable beforehand in SQL*Plus as follows:

 

SQL> !cat accept3.sql

accept christian_name prompt "Who are you? "

begin

  dbms_output.put_line('Hello &christian_name');

end;

/ 

SQL> @accept3

SQL> accept christian_name prompt "Who are you? "

Who are you? Finbar

SQL> begin

  2    dbms_output.put_line('Hello &christian_name');

  3  end;

  4  /

old   2:   dbms_output.put_line('Hello &christian_name');

new   2:   dbms_output.put_line('Hello Finbar');

Hello Finbar

 

PL/SQL procedure successfully completed.

 

SQL>

 

If you don’t want Oracle to display the old and new values of the variable, you can stop this happening by running set verify off beforehand:

 

SQL> set verify off

SQL> @accept3

SQL> accept christian_name prompt "Who are you? "

Who are you? Gordon

SQL> begin

  2    dbms_output.put_line('Hello &christian_name');

  3  end;

  4  /

Hello Gordon

 

PL/SQL procedure successfully completed.

 

SQL>