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

ORA-01427

This was tested on Oracle 11.2. If you want to compare a column value with the result of a subquery, that subquery must only return 1 row.  You can see what I mean in the example below. First, create an employee table and insert some data:

SQL> create table emp

  2  (emp_no   varchar2(4) unique,

  3   emp_name varchar2(10),

  4   dept     varchar2(10))

  5  /

Table created.

SQL> insert into emp values

  2  ('0001','Andrew','IT')

  3  /

1 row created.

SQL> insert into emp values

  2  ('0002','Brian','IT')

  3  /

1 row created.

SQL> insert into emp values

  2  ('0003','Colin','Sales')

  3  /

1 row created.

SQL> insert into emp values

  2  ('0004','David','Sales')

  3  /

1 row created.

SQL> select * from emp

  2  /

EMP_ EMP_NAME   DEPT

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

0001 Andrew     IT

0002 Brian      IT

0003 Colin      Sales

0004 David      Sales

SQL>

 

Next, run a query to list all the employees in the same department as another employee - in this case, Andrew. It works, as all the rows in the EMP table have different names:

SQL> select emp_name from emp

  2  where dept =

  3  (select dept from emp

  4   where emp_name = 'Andrew')

  5  /

 

EMP_NAME

----------

Andrew

Brian

 

SQL>

However, if you have more than 1 employee called Andrew:

SQL> insert into emp values

  2  ('0005','Andrew','Personnel')

  3  /

1 row created.

SQL> select * from emp

  2  /

EMP_ EMP_NAME   DEPT

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

0001 Andrew     IT

0002 Brian      IT

0003 Colin      Sales

0004 David      Sales

0005 Andrew     Personnel

SQL>

 

... the query returns an ORA-01427 as Oracle does not know which department to check against:

SQL> select emp_name from emp

  2  where dept =

  3  (select dept from emp

  4   where emp_name = 'Andrew')

  5  /

(select dept from emp

*

ERROR at line 3:

ORA-01427: single-row subquery returns more than one

row

SQL>

 

You get an ORA-01427 because of dodgy data or, as in this case, poorly written SQL. If you change the subquery to select from a column with a unique constraint, the SQL runs successfully again:

SQL> select emp_name from emp

  2  where dept =

  3  (select dept from emp

  4   where emp_no = '0001')

  5  /

EMP_NAME

----------

Andrew

Brian

SQL>

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 LOG and APPEND commands

This example was tested on Oracle 11.2 and shows how to create a log of an rman session. First display your $ORACLE_SID to show you are pointing to a database:

 

Solaris > echo $ORACLE_SID

ANDREW01

Solaris >

 

Show that you are already in the directory containing the log file:

 

Solaris > pwd

/database/Andrew/ANDREW01

Solaris >

 

Show the existing contents of the log file:

 

Solaris > cat rman.log

If this works, the output from my rman session will appear below.

Solaris >

 

Now start an rman session, which will append its output to the log file. Notice that no output appears on the screen:

 

Solaris > rman target / LOG //database/Andrew/ANDREW01/rman.log APPEND

RMAN> list backup;

RMAN> exit

Solaris >

 

Finally, display the new contents of the log file to show the output from the list backup command:

 

Solaris > cat rman.log

If this works, the output from my rman session will appear below.

 

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Aug 23 17:51:55 2012

 

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

 

connected to target database: ANDREW01 (DBID=385736687)

 

RMAN>

using target database control file instead of recovery catalog

 

List of Backup Sets

===================

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

3       Full    943.45M    DISK        00:00:27     22-AUG-12

        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20120822T155255

        Piece Name: /database/Andrew/ANDREW01/backupset/2012_08_22/o1_mf_nnndf_TAG20120822T155255_839wl8dj_.bkp

  List of Datafiles in backup set 3

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 947201     22-AUG-12 /database/Andrew/ANDREW01/system01.dbf

  2       Full 947201     22-AUG-12 /database/Andrew/ANDREW01/sysaux01.dbf

  3       Full 947201     22-AUG-12 /database/Andrew/ANDREW01/undotbs01.dbf

  4       Full 947201     22-AUG-12 /database/Andrew/ANDREW01/users01.dbf

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4       Full    9.33M      DISK        00:00:00     22-AUG-12

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20120822T155255

        Piece Name: /database/Andrew/ANDREW01/backupset/2012_08_22/o1_mf_ncnnf_TAG20120822T155255_839wmdo5_.bkp

  Control File Included: Ckp SCN: 947201       Ckp time: 22-AUG-12

 

RMAN>

 

Recovery Manager complete.

Solaris >

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