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>