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.
 

Allocate Extent

This post, which was tested on a version 9 database, shows how you can allocate extents to a table or index manually as opposed to allowing Oracle to create them automatically. Start by creating a table and looking at the size of its first extent:

 
SQL> create table andrews_table
  2  (col1 varchar2(10))
  3  tablespace user_data
  4  /

Table created.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480

SQL> 

Now do the same with an index:

SQL> create index andrews_index
  2  on andrews_table (col1)
  3  tablespace user_data
  4  /

Index created.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_INDEX'
  3  /

EXTENT_ID      BYTES
---------- ----------
        0      20480

SQL>

When you add an extent manually, you can allow Oracle to decide its size: 

SQL> alter table andrews_table allocate extent
  2  /

Table altered.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  order by 1
  4  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480
         1      20480

SQL>

... or you can specify it yourself like this: 

SQL> alter index andrews_index allocate extent
  2  (size 40k)
  3  /

Index altered.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_INDEX'
  3  order by 1
  4  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480
         1      40960

SQL>

You can even tell Oracle which datafile to put the extent in. However, in this example it makes no difference as the tablespace only has 1 datafile:

SQL> alter table andrews_table allocate extent
  2  (size 60k
  3  datafile '/datafiles/user_data.dbf')
  4  /

Table altered.

SQL> select extent_id, file_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  order by 1
  4  /

 EXTENT_ID    FILE_ID      BYTES
---------- ---------- ----------
         0         22      20480
         1         22      20480
         2         22      61440

SQL>

But, if you specify a datafile which does not exist or, as in this case, belongs to another tablespace, you get an ORA-03283:

SQL> alter index andrews_index allocate extent
  2  (datafile '/datafiles/system.dbf')
  3  /
alter index andrews_index allocate extent
*
ERROR at line 1:
ORA-03283: specified datafile /datafiles/system.dbf does not exist

SQL>

Truncate (Part 1)

This post shows the effect of the TRUNCATE statement. First create a table:

SQL> create table truncate_example
  2  as select * from dba_tables
  3  /

Table created.

SQL>

Count the lines in the table:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
      3023

SQL>

And the number of extents:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Truncate the table with the REUSE STORAGE option:

SQL> truncate table truncate_example reuse storage
  2  /

Table truncated.

SQL>

Now count the number of rows in the table. There will be none:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Truncate does not produce any undo information so if you do a rollback, the deleted lines will not reappear:

SQL> rollback;

Rollback complete.

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Now count the number of extents. It will be the same as before because you included the REUSE STORAGE clause:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Now reinsert the deleted rows from the source table:

SQL> insert into truncate_example
  2  select * from dba_tables
  3  /

3024 rows created.

SQL>

Truncate the table again but this time, add the DROP STORAGE clause, which is the default:

SQL> truncate table truncate_example
  2  drop storage
  3  /

Table truncated.

SQL>

Count the number of rows in the table again. There will be none, as before:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

And count the number of extents. By adding the DROP STORAGE clause, the table’s extents have been deallocated. The number of extents remaining is determined by the table’s MINEXTENTS value:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
         1

SQL>