ORA-01119 and ORA-27086

This was tested on Oracle 11.2. If you get the messages above, it could be that you are trying to add a file from 1 instance to another. In the example below, I tried to add a tempfile currently in use by instance ANDREW01 while I was connected to a different instance:

 

SQL> l

  1  alter tablespace temp add tempfile

  2  '/database/Andrew/ANDREW01/temp01.dbf'

  3* reuse

SQL> /

alter tablespace temp add tempfile

*

ERROR at line 1:

ORA-01119: error in creating database file

'/database/Andrew/ANDREW01/temp01.dbf'

ORA-27086: unable to lock file - already in use

SVR4 Error: 11: Resource temporarily unavailable

Additional information: 8

Additional information: 23210

Additional information: 16409

 

SQL>

ORA-01537

This was tested on Oracle 11.2. You get the message above if you try to add a file to an instance more than once:

 

SQL> l

  1* select file_name from dba_temp_files

SQL> /

 

FILE_NAME

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

/database/Andrew/ANDREW01/temp01.dbf

 

SQL> alter tablespace temp add tempfile

  2  '/database/Andrew/ANDREW01/temp01.dbf'

  3  reuse

  4  /

alter tablespace temp add tempfile

*

ERROR at line 1:

ORA-01537: cannot add file '/database/Andrew/ANDREW01/temp01.dbf' - file

already part of database

 

SQL>

How to See the Name of Your Database

This example was tested on Oracle 11.2. It shows how a user can see his database name with only the CREATE SESSION privilege. First I created a user:

SQL> create user fred identified by bloggs

  2  /

User created.

SQL> grant create session to fred

  2  /

Grant succeeded.

SQL>

Then I tried to create the user again. This failed. So if the user had existed, perhaps with extra privileges, before I started this example, I would have seen an error when I ran the preceding step:

SQL> create user fred identified by bloggs

  2  /

create user fred identified by bloggs

            *

ERROR at line 1:

ORA-01920: user name 'FRED' conflicts with another

user or role name

SQL>

Finally, I connected as the new user and displayed the name of the database I was connected to:

SQL> conn fred/bloggs

Connected.

SQL> select sys_context('USERENV', 'DB_NAME')

  2   DATABASE from dual

  3  /

DATABASE

----------

ANDREW01

SQL>

Hit Ratio (tested on Oracle 11.2)

Back in the days of Oracle 7, DBA's used to calculate a database’s hit ratio. It was supposed to be greater than 90%. If it was regularly less than 70%, they would increase the value of the DB_BLOCK_BUFFERS initialisation parameter. I don’t know if people do this any more but, if you are ever asked to calculate a database’s hit ratio, you can do it as follows:

SQL> SELECT TRUNC

  2  ((1-(physical_reads/(db_block_gets+consistent_gets)))*100,1)||'%'

  3  Hit_Ratio FROM

  4  (SELECT value physical_reads FROM v$sysstat

  5   WHERE name = 'physical reads'),

  6  (SELECT value db_block_gets FROM v$sysstat

  7   WHERE name = 'db block gets'),

  8  (SELECT value consistent_gets FROM v$sysstat

  9   WHERE name = 'consistent gets');

 

HIT_RATIO

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

94.1%

 

SQL>

ORA-02392

This example shows how you can limit the amount of CPU time consumed by a user session. It was tested on Oracle 11.2. First I created a profile which limits CPU per session to 50 hundredths of a second i.e. 0.5 seconds:

 

SQL> CONN / AS SYSDBA

Connected.

SQL> CREATE PROFILE FOR_ANDREW

  2  LIMIT CPU_PER_SESSION 50

  3  /

 

Profile created.

 

SQL>

 

Next, I created a user and assigned him the profile I had just created:

 

SQL> CREATE USER ANDREW

  2  IDENTIFIED BY REID

  3  PROFILE FOR_ANDREW

  4  /

 

User created.

 

SQL> GRANT CREATE SESSION,

  2        SELECT ANY TABLE,

  3        SELECT ANY DICTIONARY TO ANDREW

  4  /

 

Grant succeeded.

 

SQL>

 

Then I set the RESOURCE LIMIT initialisation parameter to TRUE:

 

SQL> COL RESOURCE_LIMIT FORMAT A30

SQL> SELECT VALUE RESOURCE_LIMIT

  2  FROM V$PARAMETER

  3  WHERE NAME = 'resource_limit'

  4  /

 

RESOURCE_LIMIT

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

FALSE

 

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE

  2  /

 

System altered.

 

SQL>

 

Finally, I connected as the user and ran some SQL. Once the allotted amount of CPU time had been used, the user was given an ORA-02392 and logged off:

 

SQL> CONN ANDREW/REID

Connected.

SQL> SELECT COUNT(*) FROM DBA_TABLES

  2  /

 

  COUNT(*)

----------

      2724

 

SQL> SELECT COUNT(*) FROM DBA_INDEXES

  2  /

SELECT COUNT(*) FROM DBA_INDEXES

                     *

ERROR at line 1:

ORA-02392: exceeded session limit on CPU usage, you

are being logged off

 

SQL>

Reply to: Reply to Andrew Reid

Send

ORA-00937

This was tested on Oracle 11.2. You might think that, if you wanted to see details from a row containing some maximum value, you could do it as follows:

 

SQL> select owner, segment_name, max(bytes)

  2  from dba_segments

  3  /

select owner, segment_name, max(bytes)

       *

ERROR at line 1:

ORA-00937: not a single-group group function

 

SQL>

 

... but you get an ORA-00937. You have to do it like this:

 

SQL> l

  1  select owner, segment_name, bytes

  2  from dba_segments

  3  where bytes =

  4* (select max(bytes) from dba_segments)

SQL> /

 

OWNER           SEGMENT_NAME         BYTES

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

SYS             IDL_UB1$         251658240

 

SQL>

Reply to: Reply to Andrew Reid

Send

SQL ANY Clause

The SQL ANY clause allows you to compare a series of columns with some other value. In this example, the other value is a literal. I tested it on Oracle 11.1.0.6.0. First I created a table showing what I ate for each meal on a given day:

SQL> create table meals
  2  (day       varchar2(3),
  3   breakfast varchar2(10),
  4   lunch varchar2(10),
  5   dinner    varchar2(10))
  6  /

Table created.

SQL>

Then I added some imaginary data (I don't really eat nut roast):

SQL> insert into meals values
  2  ('MON', 'WEETABIX', 'PIZZA', 'KIPPERS')
  3  /

1 row created.

SQL> insert into meals values
  2  ('TUE', 'CORNFLAKES', 'LASAGNE', 'BEEF')
  3  /

1 row created.

SQL> insert into meals values
  2  ('WED', 'PORRIDGE', 'LAMB', 'NUT ROAST')
  3  /

1 row created.

SQL> select * from meals
  2  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
MON WEETABIX   PIZZA      KIPPERS
TUE CORNFLAKES LASAGNE    BEEF
WED PORRIDGE   LAMB       NUT ROAST

SQL>

Finally, I used the ANY clause to check whether I ate a particular food for breakfast, lunch or dinner:

SQL> select * from meals
  2  where 'WEETABIX' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
MON WEETABIX   PIZZA      KIPPERS

SQL> select * from meals
  2  where 'LASAGNE' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
TUE CORNFLAKES LASAGNE    BEEF

SQL> select * from meals
  2  where 'NUT ROAST' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
WED PORRIDGE   LAMB       NUT ROAST

SQL>

Cosines and Sines

When I was at school, I learnt that the square of the cosine of an angle plus the square of the sine of that angle equals 1. I decided to try this out in Oracle 11.1.0.6.0 running on Windows XP. The results were quite close. The cosine and sine functions both work on angles in radians. To convert an angle in degrees into radians,  you must multiply it by pi/180:

SQL> set serveroutput on
SQL> declare
  2   cos_squared_i number;
  3   sin_squared_i number;
  4   csi_plus_ssi number;
  5  begin
  6   for i in 0..90 loop
  7    cos_squared_i := power(cos(i*3.14159/180),2);
  8    sin_squared_i := power(sin(i*3.14159/180),2);
  9    csi_plus_ssi := cos_squared_i + sin_squared_i;
 10    dbms_output.put_line('Angle = '||i);
 11    dbms_output.put_line('Result = '||csi_plus_ssi);
 12   end loop;
 13  end;
 14  /
Angle = 0
Result = 1
Angle = 1
Result = .9999999999999999999999999999999999999899
Angle = 2
Result = .9999999999999999999999999999999999999883
Angle = 3
Result = .9999999999999999999999999999999999999958
Angle = 4
Result = 1.00000000000000000000000000000000000002
Angle = 5
Result = 1.00000000000000000000000000000000000001
Angle = 6
Result = .9999999999999999999999999999999999999871
Angle = 7
Result = .9999999999999999999999999999999999999983
Angle = 8
Result = 1
Angle = 9
Result = .9999999999999999999999999999999999999975
Angle = 10
Result = 1
Angle = 11
Result = 1.00000000000000000000000000000000000001
Angle = 12
Result = .9999999999999999999999999999999999999964
Angle = 13
Result = .9999999999999999999999999999999999999963
Angle = 14
Result = 1.00000000000000000000000000000000000001
Angle = 15
Result = 1
Angle = 16
Result = 1.00000000000000000000000000000000000001
Angle = 17
Result = .9999999999999999999999999999999999999943
Angle = 18
Result = 1.00000000000000000000000000000000000001
Angle = 19
Result = .9999999999999999999999999999999999999953
Angle = 20
Result = 1.00000000000000000000000000000000000001
Angle = 21
Result = .9999999999999999999999999999999999999816
Angle = 22
Result = .9999999999999999999999999999999999999673
Angle = 23
Result = .9999999999999999999999999999999999999744
Angle = 24
Result = .9999999999999999999999999999999999999816
Angle = 25
Result = 1.00000000000000000000000000000000000002
Angle = 26
Result = 1
Angle = 27
Result = 1.00000000000000000000000000000000000001
Angle = 28
Result = 1.00000000000000000000000000000000000001
Angle = 29
Result = 1
Angle = 30
Result = 1
Angle = 31
Result = .9999999999999999999999999999999999999967
Angle = 32
Result = 1.00000000000000000000000000000000000002
Angle = 33
Result = .9999999999999999999999999999999999999992
Angle = 34
Result = .9999999999999999999999999999999999999992
Angle = 35
Result = 1.00000000000000000000000000000000000001
Angle = 36
Result = .9999999999999999999999999999999999999982
Angle = 37
Result = .9999999999999999999999999999999999999923
Angle = 38
Result = .9999999999999999999999999999999999999978
Angle = 39
Result = .9999999999999999999999999999999999999915
Angle = 40
Result = .9999999999999999999999999999999999999894
Angle = 41
Result = .9999999999999999999999999999999999999978
Angle = 42
Result = 1.00000000000000000000000000000000000002
Angle = 43
Result = 1.00000000000000000000000000000000000001
Angle = 44
Result = 1
Angle = 45
Result = 1
Angle = 46
Result = .9999999999999999999999999999999999999995
Angle = 47
Result = 1.00000000000000000000000000000000000003
Angle = 48
Result = 1
Angle = 49
Result = .9999999999999999999999999999999999999896
Angle = 50
Result = .9999999999999999999999999999999999999852
Angle = 51
Result = 1
Angle = 52
Result = .9999999999999999999999999999999999999984
Angle = 53
Result = 1.00000000000000000000000000000000000001
Angle = 54
Result = .9999999999999999999999999999999999999962
Angle = 55
Result = 1.00000000000000000000000000000000000001
Angle = 56
Result = 1.00000000000000000000000000000000000001
Angle = 57
Result = .9999999999999999999999999999999999999891
Angle = 58
Result = 1.00000000000000000000000000000000000001
Angle = 59
Result = .9999999999999999999999999999999999999965
Angle = 60
Result = 1.00000000000000000000000000000000000002
Angle = 61
Result = 1.00000000000000000000000000000000000001
Angle = 62
Result = 1.00000000000000000000000000000000000001
Angle = 63
Result = 1.00000000000000000000000000000000000001
Angle = 64
Result = .9999999999999999999999999999999999999912
Angle = 65
Result = .9999999999999999999999999999999999999862
Angle = 66
Result = .9999999999999999999999999999999999999832
Angle = 67
Result = 1.00000000000000000000000000000000000001
Angle = 68
Result = 1.00000000000000000000000000000000000004
Angle = 69
Result = 1
Angle = 70
Result = .9999999999999999999999999999999999999986
Angle = 71
Result = 1.00000000000000000000000000000000000002
Angle = 72
Result = 1.00000000000000000000000000000000000002
Angle = 73
Result = 1
Angle = 74
Result = 1.00000000000000000000000000000000000001
Angle = 75
Result = .9999999999999999999999999999999999999819
Angle = 76
Result = .9999999999999999999999999999999999999742
Angle = 77
Result = .9999999999999999999999999999999999999867
Angle = 78
Result = .999999999999999999999999999999999999991
Angle = 79
Result = .9999999999999999999999999999999999999907
Angle = 80
Result = 1
Angle = 81
Result = 1
Angle = 82
Result = 1
Angle = 83
Result = .9999999999999999999999999999999999999998
Angle = 84
Result = .9999999999999999999999999999999999999874
Angle = 85
Result = .9999999999999999999999999999999999999965
Angle = 86
Result = .9999999999999999999999999999999999999691
Angle = 87
Result = .9999999999999999999999999999999999999966
Angle = 88
Result = 1.00000000000000000000000000000000000001
Angle = 89
Result = 1.00000000000000000000000000000000000003
Angle = 90
Result = .9999999999999999999999999999999999999968

PL/SQL procedure successfully completed.

SQL>

ORA-01758 and ORA-12988

I read that you could not add a NOT NULL column to a table which already contained rows. It was not something I had ever thought about but it seemed reasonable. If you were allowed to do it, the table would end up with NULLS in a NOT NULL column after you had added the column. I decided to give it a try on Oracle 11.1.0.6.0 running on Windows XP. First I created a table:

SQL> create table people
  2  (first_name varchar2(10))
  3  /

Table created.

SQL>

Then I added a NOT NULL column while the table was still empty. This worked OK:

SQL> alter table people add
  2  (age number not null)
  3  /

Table altered.

SQL>

Next I tried to drop the column but got an ORA-12988 as I was logged in as SYS:

SQL> alter table people
  2  drop column age
  3  /
alter table people
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

SQL>

I dropped the table instead and recreated it:

SQL> drop table people
  2  /

Table dropped.

SQL> create table people
  2  (first_name varchar2(10))
  3  /

Table created.

SQL>

Then I inserted a row and tried to add a NOT NULL column. This failed with an ORA-01758:

SQL> insert into people values ('ANDREW')
  2  /

1 row created.

SQL> alter table people add
  2  (age number not null)
  3  /
alter table people add
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

SQL>

There are a few ways round this:

(1) You could empty the table, add the NOT NULL column then reinsert the data. I don’t think much of that idea.
(2) You could add the column without the NOT NULL constraint, put a value in the column in every row then add the NOT NULL constraint. I think this is the best suggestion.
(3) You could include a DEFAULT value as shown below then update it afterwards if necessary:

SQL> l
  1  alter table people add
  2* (age number default 50 not null)
SQL> /

Table altered.

SQL> select * from people
  2  /

FIRST_NAME        AGE
---------- ----------
ANDREW             50

SQL>

rman Backup and Recovery - example 3

This was tested on Oracle 11.2. The Oracle rman user guide says:  If a database runs in ARCHIVELOG mode, then you can back up the database while it is open. This suggests that if your database is running in NOARCHIVELOG mode, you cannot back it up while it is open. I decided to try this out. First I checked that the database was still in NOARCHIVELOG mode:

 

SQL> select log_mode, open_mode from v$database;

 

LOG_MODE     OPEN_MODE

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

NOARCHIVELOG READ WRITE

 

SQL>

 

Then I tried to back it up with rman:

 

RMAN> backup database;

 

Starting backup at 23-AUG-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=586 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/23/2012 18:42:22

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

continuing other job steps, job failed will not be re-run

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 23-AUG-12

channel ORA_DISK_1: finished piece 1 at 23-AUG-12

piece handle=/database/Andrew/ANDREW01/backupset/2012_08_23/o1_mf_ncnnf_TAG20120823T184221_83dtw1pb_.bkp tag=TAG20120823T184221 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

 

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/23/2012 18:42:22

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

 

RMAN>

 

It failed, as you might expect, so, if you see these messages yourself, you should know what to do. I will pick this up in future examples.

Reply to: Reply to Andrew Reid

Send

rman Backup and Recovery - example 2

Recovery Manager stores backups at the location specified by the db_recovery_file_dest initialisation parameter. At the start of this example, which I tested on Oracle 11.2, my database had no db_recovery_file_dest details:

SQL> l

  1  select name, nvl(value,'NULL')

  2  from v$parameter

  3* where name like 'db_recovery_file_dest%'

SQL> /

NAME                           NVL(VALUE,'NULL')

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

db_recovery_file_dest          NULL

db_recovery_file_dest_size     0

SQL>

So, when I used rman to do a backup, Oracle put the files it produced in $ORACLE_HOME/dbs instead. This has the potential to fill up $ORACLE_HOME so you must not do it. Notice how you can use rman to shutdown and startup mount the database:

Solaris > rman nocatalog target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 17 14:48:59 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ANDREW01 (DBID=385736687)

using target database control file instead of recovery catalog

RMAN> shutdown

database closed

database dismounted

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     417669120 bytes

Fixed Size                     2148672 bytes

Variable Size                314578624 bytes

Database Buffers              96468992 bytes

Redo Buffers                   4472832 bytes

RMAN> backup database;

Starting backup at 17-AUG-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/database/Andrew/ANDREW01/system01.dbf

input datafile file number=00002 name=/database/Andrew/ANDREW01/sysaux01.dbf

input datafile file number=00005 name=/database/Andrew/ANDREW01/users2.dbf

input datafile file number=00003 name=/database/Andrew/ANDREW01/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 17-AUG-12

channel ORA_DISK_1: finished piece 1 at 17-AUG-12

piece handle=/oracle/app/oracle/product/11.2.0/dbs/03nisji9_1_1 tag=TAG20120817T145048 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 17-AUG-12

channel ORA_DISK_1: finished piece 1 at 17-AUG-12

piece handle=/oracle/app/oracle/product/11.2.0/dbs/04nisjjc_1_1 tag=TAG20120817T145048 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-AUG-12

RMAN>

I tried to set db_recovery_file_dest by itself but failed:

SQL> l

  1  alter system set

  2* db_recovery_file_dest = '/database/Andrew'

SQL> /

alter system set

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because

specified value is invalid

ORA-19802: cannot use DB_RECOVERY_FILE_DEST without

DB_RECOVERY_FILE_DEST_SIZE

SQL>

The ORA-02097 message is slightly misleading as the parameter supplied is not invalid. The real problem is shown by the ORA-19802 message. I set db_recovery_file_dest_size to an appropriate value:

SQL> l

  1  alter system

  2* set db_recovery_file_dest_size = 4g

SQL> /

System altered.

SQL>

Then I was able to set db_recovery_file_dest successfully:

SQL> l

  1  alter system set

  2* db_recovery_file_dest = '/database/Andrew'

SQL> /

System altered.

SQL>

ORA-01033

Why might you get an ORA-01033? Yesterday I was moving a test database from one file system on one UNIX server to a new file system on a different UNIX server. I closed the database on the old server and un-mounted the file system. Then I recreated the database on the new server and amended the developers’ Windows tnsnames.ora file to point to the new database. Unbeknown to me, a developer had a login to the old server and said there was still a pmon process for the database there. There was but it was created by an automatic job, which I did not know about, which tried to open the database. This job appears to have run at 2300 hours yesterday evening:

 

Solaris > ps -ef|grep TESTDB01|grep -v grep

  oracle 22385     1   0 23:00:05 ?           0:02 ora_mman_TESTDB01

  oracle 22405     1   0 23:00:06 ?           0:00 ora_reco_TESTDB01

  oracle 22397     1   0 23:00:06 ?           0:03 ora_ckpt_TESTDB01

  oracle 22374     1   0 23:00:05 ?           0:18 ora_pmon_TESTDB01

  oracle 22414     1   0 23:00:06 ?           0:02 ora_mmnl_TESTDB01

  oracle 22401     1   0 23:00:06 ?           0:01 ora_smon_TESTDB01

  oracle 22410     1   0 23:00:06 ?           0:03 ora_mmon_TESTDB01

  oracle 22381     1   0 23:00:05 ?           0:02 ora_psp0_TESTDB01

  oracle 22389     1   0 23:00:06 ?           0:02 ora_dbw0_TESTDB01

  oracle 22393     1   0 23:00:06 ?           0:03 ora_lgwr_TESTDB01

Solaris >

 

When I tried to access the database, I got an ORA-01033:

 

Solaris > sqlplus system/manager

 

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 16 16:41:18 2012

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

 

Enter user-name: