ORA-01166

We have some jobs which copy the datafiles from 1 database to another then recreate the control file to give the output database a new name. Some of these jobs are intelligent i.e. they query the input database to dynamically create the SQL to do the rename. This particular job is not. Part of it is shown below and you can see that the MAXDATAFILES parameter was set to 120:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "MRMDPT1" RESETLOGS  NOARCHIVELOG

--  SET STANDBY TO MAXIMIZE PERFORMANCE

    MAXLOGFILES 10

    MAXLOGMEMBERS 3

    MAXDATAFILES 120

    MAXINSTANCES 1

    MAXLOGHISTORY 452

 

Somebody added an extra datafile to the input database so it then had 121:

 

SQL> select count(*) from dba_data_files

  2  /

 

  COUNT(*)

----------

       121

 

SQL>

 

However, he forgot to update the script so the rename failed as follows:

 

ORA-01503: CREATE CONTROLFILE failed

ORA-01166: file number 121 is larger than MAXDATAFILES (120)

ORA-01110: data file 121: '/cisdpt/mrmdpt1/mrm_tables3/sysauxMRMPROD_2.dbf'

 

I updated the script like this, changing MAXDATAFILES to 150:

 

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "MRMDPT1" RESETLOGS  NOARCHIVELOG

--  SET STANDBY TO MAXIMIZE PERFORMANCE

    MAXLOGFILES 10

    MAXLOGMEMBERS 3

    MAXDATAFILES 150

    MAXINSTANCES 1

    MAXLOGHISTORY 452

 

... and when I ran the job again, it worked:

 

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Oct 8 12:16:47 2015

 

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

 

Connected to an idle instance.

 

SQL> ORACLE instance started.

 

Total System Global Area  524288000 bytes

Fixed Size                  2031416 bytes

Variable Size             171966664 bytes

Database Buffers          343932928 bytes

Redo Buffers                6356992 bytes

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80   81   82   83   84   85   86   87   88   89   90   91   92   93   94   95   96   97   98   99  100  101  102  103  104  105  106  107  108  109  110  111  112  113  114  115  116  117  118  119  120  121  122  123  124  125  126  127  128  129  130  131  132  133  134  135  136  137  138  139  140  141  142  143  144

Control file created.

 

SQL>

ORA-03297

This example, which I tested in an Oracle 9 database, explains the cause of this error message. First create a 20 megabyte tablespace with 1 datafile. Put 2 tables in it, each with a single 9 megabyte extent:

SQL> create tablespace andrew
  2  datafile '/database/andrew.dbf'
  3  size 20m
  4  extent management dictionary
  5  /

Tablespace created.

SQL> create table tab1(col1 number)
  2  tablespace andrew
  3  storage (initial 9m)
  4  /

Table created.

SQL> create table tab2(col1 number)
  2  tablespace andrew
  3  storage (initial 9m)
  4  /

Table created.

SQL>

Next create a map of the tablespace. I wrote this SQL in 2002 but it still seems to work. Its output shows the two 9 megabyte tables at the start of the datafile and a 2 megabyte free area at the end:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB1               9441280

        26       2307 TAB2               9441280

        26       4612 FREE               2084864

SQL>

Now drop the first table and redo the tablespace map. This shows a 9 megabyte free area at the start of the datafile where TAB1 used to be:

SQL> drop table tab1

  2  /

Table dropped.

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 FREE               9441280

        26       2307 TAB2               9441280

        26       4612 FREE               2084864

SQL>

Try to resize the datafile to 10 megabytes as it now only contains a 9 megabyte table. This fails as it can only recoup space from the end of the datafile and TAB2 is there already:

SQL> alter database

  2  datafile '/cisdpt/ebedpt1/ebe_tables/andrew.dbf'

  3  resize 10m

  4  /

alter database

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested

RESIZE value

SQL>

You need to do something to free up the space at the end of the  datafile. On this occasion you are in luck. If you move TAB2 within the tablespace, it will take up the space at the start of the datafile:

SQL> alter table tab2 move

  2  /

Table altered.

SQL>

Redo the tablespace map. This shows a large enough area of free space at the end of the datafile to allow it to be resized successfully:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB2               9441280

        26       2307 FREE              11526144

SQL> alter database

  2  datafile '/cisdpt/ebedpt1/ebe_tables/andrew.dbf'

  3  resize 10m

  4  /

Database altered.

SQL>

Redo the tablespace map to show the effect of the resize:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB2               9441280

        26       2307 FREE               1040384

SQL>

Finally, drop the tablespace:

SQL> drop tablespace andrew
  2  including contents and datafiles
  3  /

Tablespace dropped.

SQL>

And now for a history lesson. I started to use Oracle 7 in the late 1990s. I had a development server all to myself and would often use create controlfile to clone databases. On one occasion I made the datafiles smaller beforehand using the resize command shown above. This caused the create controlfile to fail with an actual file size of <num> is smaller than correct size of <num> blocks message. The only workaround I found was to return the files to their original size and try again. For a long time I thought I was doing something wrong but I eventually discovered that this problem was caused by bug 309181, which was apparently fixed in Oracle 7.3.3.