ORA-14306 and ORA-14400

Oracle apparently introduced list partitioning in Oracle 9. I decided to try it out in an Oracle 11 database.

In this simple example I created a table to store the names of towns in Buckinghamshire, together with the people living in each one:

SQL> create table buckinghamshire_people
  2  (town varchar2(30),
  3   first_name varchar2(30),
  4   last_name varchar2(30))
  5  partition by list (town)
  6  (partition town1 values ('AYLESBURY'),
  7   partition town2 values ('AMERSHAM'))
  8  /

Table created.

SQL>

I inserted a valid row into the table:

SQL> insert into buckinghamshire_people
  2  (town, first_name, last_name)
  3  values
  4  ('AYLESBURY', 'FRED', 'BLOGGS')
  5  /

1 row created.

SQL>

...then I inserted a row for somebody from Slough. It used to be in Buckinghamshire but is now in Berkshire so the table did not have a partition for it. This failed with an ORA-14400:

SQL> insert into buckinghamshire_people
  2  (town, first_name, last_name)
  3  values
  4  ('SLOUGH', 'JOHN', 'SMITH')
  5  /
insert into buckinghamshire_people
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any
partition
 
SQL>

I tried to recreate the table with three partitions but the first and third stored rows for the same town. Oracle would not have known where to put rows for people from Aylesbury so it returned an ORA-14306:

SQL> drop table buckinghamshire_people
  2  /

Table dropped.

SQL> create table buckinghamshire_people
  2  (town varchar2(30),
  3   first_name varchar2(30),
  4   last_name varchar2(30))
  5  partition by list (town)
  6  (partition town1 values ('AYLESBURY'),
  7   partition town2 values ('AMERSHAM'),
  8   partition town3 values ('AYLESBURY'))
  9  /
create table buckinghamshire_people
*
ERROR at line 1:
ORA-14306: List value ''AYLESBURY'' specified twice in
partitions 'TOWN1', 'TOWN3'

SQL>

Online Rebuild of Bitmap Indexes

I was reading an old Oracle 9 performance tuning book (as you do) and it said that you could not do online rebuilds of bitmap indexes.
I did not have access to an Oracle 9 database so I tried it out in an Oracle 10 one instead and it worked:

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

Table created.

SQL> create bitmap index ind1 on tab1(col1)
  2  /

Index created.

SQL> alter index ind1 rebuild online
  2  /

Index altered.

SQL>

I then remembered that we had the Oracle 11.2 version of the same book by the same author in the cupboard.
It still claimed that you could not do online rebuilds of bitmap indexes.
Remember, if you read something in a book, be sure to check it yourself.

How to Calculate pi

You can use the following series to calculate pi:

4/1 - 4/3 + 4/5 - 4/7 + 4/9 - 4/11 + ...

I decided to try this out using PL/SQL. The example I created is shown below. It works OK but the series converges very slowly so you have to work out several million terms just to get pi accurate to 6 decimal places:

SQL> set timing on
SQL> declare
  2  pi          number  := 0;
  3  numerator   number  := 4;
  4  denominator number  := 1;
  5  dp          number  := 0;
  6  pi1         number;
  7  pi2         number;
  8  counter     number  := 0;
  9  finished    boolean := false;
 10  begin
 11  while not finished
 12  loop
 13   pi          := pi + (numerator / denominator);
 14   pi1         := round (pi,dp);
 15   denominator := denominator + 2;
 16   pi          := pi - (numerator / denominator);
 17   pi2         := round (pi,dp);
 18   denominator := denominator + 2;
 19   counter     := counter + 1;
 20   if pi1 = pi2 then
 21    dbms_output.put_line ('Counter = '||counter);
 22    if dp = 1 then
 23     dbms_output.put_line
 24     ('Accurate to 1 decimal place:');
 25    else
 26     dbms_output.put_line
 27     ('Accurate to '||dp||' decimal places:');
 28    end if;
 29    dbms_output.put_line ('Pi = '||pi1);
 30    dbms_output.put_line ('**********');
 31    dp := dp + 1;
 32    if dp > 6 then
 33     finished := true;
 34    end if;
 35   end if;
 36  end loop;
 37  end;
 38  /
Counter = 2
Accurate to 0 decimal places:
Pi = 3
**********
Counter = 60
Accurate to 1 decimal place:
Pi = 3.1
**********
Counter = 148
Accurate to 2 decimal places:
Pi = 3.14
**********
Counter = 5397
Accurate to 3 decimal places:
Pi = 3.142
**********
Counter = 11723
Accurate to 4 decimal places:
Pi = 3.1416
**********
Counter = 213092
Accurate to 5 decimal places:
Pi = 3.14159
**********
Counter = 3255425
Accurate to 6 decimal places:
Pi = 3.141593
**********

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.53
SQL>

ORA-00439

I created a tablespace in an Oracle 12 Enterprise Edition database.

I should have used ASM but I put its datafile in ordinary disk space by mistake:

SQL> col banner format a47
SQL> l
  1  select banner from v$version
  2* where banner like 'Oracle Database%'
SQL> /

BANNER
-----------------------------------------------
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'ANDREW'
  3  /

FILE_NAME
--------------------------------------------------
/home/oracle/andrew/datafile1.dbf

SQL>

I moved the datafile to ASM as follows:

SQL> alter database move datafile
  2  '/home/oracle/andrew/datafile1.dbf'
  3  to '+DATA'
  4  /

Database altered.

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'ANDREW'
  3  /

FILE_NAME
--------------------------------------------------
+DATA/NLPEST1_LHR1S2/7976D62027536974E0530600000A7
C7B/DATAFILE/andrew.301.1036667943

SQL>

I tried the same thing in an Oracle 12 Standard Edition database on a different server but Oracle returned an ORA-00439:

SQL> col banner format a45
SQL> l
  1  select banner from v$version
  2* where banner like 'Oracle Database%'
SQL> /

BANNER
---------------------------------------------
Oracle Database 12c Standard Edition Release
12.2.0.1.0 - 64bit Production

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'ANDREWS_TABLESPACE'
  3  /

FILE_NAME
----------------------------------------------
/home/oracle/andrew/andrews_tablespace.dbf

SQL> alter database move datafile
  2  '/home/oracle/andrew/andrews_tablespace.dbf'
  3  to '+DATA'
  4  /
alter database move datafile
*
ERROR at line 1:
ORA-00439: feature not enabled: online move datafile

SQL>

ORA-12954

I went to Techfest 2019 in Brighton. While I was there I saw a presentation by somebody from Pythian. He said that Oracle Database Express Edition is now very similar to Enterprise Edition, it just has certain resource limits applied. This makes it ideal for testing out new features. One of the resource limits is on database size, which is limited to 12 gigabytes. I wondered what would happen if I tried to exceed this. 

I checked the name of the datafile for the USERS tablespace:

SQL> l
  1  select file_name from dba_data_files
  2* where tablespace_name = 'USERS'
SQL> /

FILE_NAME
---------------------------------------------------
C:\APP\ADMIN\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF

SQL>

I checked its size:

SQL> select bytes from dba_data_files
  2  where tablespace_name = 'USERS'
  3  /

     BYTES
----------
   5242880

SQL>

...then I tried to resize it:

SQL> l
  1  alter database datafile
  2  'C:\APP\ADMIN\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF'
  3* resize 10g
SQL> /

Database altered.

SQL>

As soon as it looked like the total amount of data would exceed 12 gigabytes, Oracle returned an ORA-12954:

SQL> l
  1  alter database datafile
  2  'C:\APP\ADMIN\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF'
  3* resize 11g
SQL> /
alter database datafile
*
ERROR at line 1:
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

SQL>

It is not obvious how this calculation was done but I assume some allowance was made for data in the SYSAUX, UNDOTBS1 or TEMP tablespaces. 

How to Create a 3 by 3 Puzzle Using Oracle Database Express Edition

In 2012 I wrote a C program to create sudoku puzzles. I took a break from writing about Oracle to set up a blog for these puzzles. You can see it here. I also created a group on Facebook called Sudoku Puzzles. At the time of writing this post it has over 10,000 members.

I recently saw a new kind of puzzle in a UK newspaper. The UK agents for this puzzle are Puzzler and their offices are only a few hundred yards from my own place of work. I paid them a visit one lunchtime and they told me that the name used for these puzzles is copyright so I cannot use it here. However, they said that there is nothing to stop me creating my own puzzles as long as I call them something different. You can see the first one I made here. I used Oracle 18 Express Edition to create it. First I ran the PL/SQL below:

SQL> create table nine_digit_numbers
  2  (col1 varchar2(9))
  3  /

Table created.

SQL> declare
  2   valid_switch boolean;
  3  begin
  4  for a in 123456789..987654321 loop
  5   valid_switch := true;
  6   for b in 2..9 loop
  7    for c in 1..b-1 loop
  8     if substr(a,c,1) = substr(a,b,1) then
  9      valid_switch := false;
 10     end if;
 11    end loop;
 12   end loop;
 13   for d in 1..9 loop
 14    if substr(a,d,1) = 0 then
 15     valid_switch := false;
 16    end if;
 17   end loop;
 18   if valid_switch then
 19    insert into nine_digit_numbers(col1) values(a);
 20    commit;
 21   end if;
 22  end loop;
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL>

It ran for a couple of days on my home PC. I'm well aware that it is not efficient and at some point I hope to use it as the basis for a post entitled Your Poor Performance Could Well Be In Your Application or words to that effect. Eventually it produced a table of all possible numbers containing each of the digits 1 through 9 exactly once. There should be 9 factorial in all. I checked the first few values:

SQL> l
  1  select * from
  2  (select col1 from nine_digit_numbers
  3   order by 1 asc)
  4* where rownum <= 5
SQL> /

COL1
---------
123456789
123456798
123456879
123456897
123456978


SQL>

I checked the last few values:

SQL> l
  1  select * from
  2  (select col1 from nine_digit_numbers
  3   order by 1 desc)
  4* where rownum <= 5
SQL> /

COL1
---------
987654321
987654312
987654231
987654213
987654132


SQL>

...and I checked the number of rows:

SQL> l
  1* select count(*) from nine_digit_numbers
SQL> /

  COUNT(*)
----------
    362880


SQL>

I chose a solution at random (719368524) and decided by trial and error which extra number I would provide as a clue. Then I ran the following SQL to ensure the puzzle would only have one solution:

SQL> l
  1  select col1 from nine_digit_numbers
  2  where
  3  substr(col1,1,1) + substr(col1,2,1) +
  4  substr(col1,4,1) + substr(col1,5,1) = 17
  5  and
  6  substr(col1,2,1) + substr(col1,3,1) +
  7  substr(col1,5,1) + substr(col1,6,1) = 24
  8  and
  9  substr(col1,4,1) + substr(col1,5,1) +
 10  substr(col1,7,1) + substr(col1,8,1) = 16
 11  and
 12  substr(col1,5,1) + substr(col1,6,1) +
 13  substr(col1,8,1) + substr(col1,9,1) = 20
 14  and
 15* substr(col1,2,1) = 1
SQL> /

COL1
---------
719368524

SQL>

I put the puzzle away for a couple of days so that I would forget the answer then I solved it myself to ensure the solution could be reached using human logic.

Finally, I used Microsoft Paint to create the puzzle and the solution.

ORA-16005

I did a shutdown abort in an Oracle 19c database:

C:\Users\Admin>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 9 20:52:49 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shutdown abort
ORACLE instance shut down.
SQL>

A shutdown abort blocks new connections and kills current sessions whether they are doing anything or not. It does not roll back terminated transactions either. As a result, Oracle has some recovery to do when the instance is restarted to make the database consistent again.

I tried to open the database in read only mode:

SQL> startup mount
ORACLE instance started.

Total System Global Area 7717518448 bytes
Fixed Size                  9284720 bytes
Variable Size            1258291200 bytes
Database Buffers         6442450944 bytes
Redo Buffers                7491584 bytes
Database mounted.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

SQL>

This would have prevented the recovery from taking place so Oracle returned an ORA-16005.

I recovered the database manually:

SQL> recover database
Media recovery complete.
SQL>

I thought this might allow me to open the database in read only mode but it didn't:

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

SQL>

To open the database in read only mode, I had to open it normally, close it then reopen it as shown below:

SQL> alter database open;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 7717518448 bytes
Fixed Size                  9284720 bytes
Variable Size            1258291200 bytes
Database Buffers         6442450944 bytes
Redo Buffers                7491584 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL>