ORA 3297 when resizing a datafile

ORA 3297 when resizing a datafile
December 2, 2009 · Leave a Comment · Edit This

Sometimes we want to shrink a datafile and we get the ORA-3297 error, what? but if there is enough space!!
Well , there is an explanation for that, the problem is that there is at least one segment’s extent that resides in blocks that are beyond the target size.
I will do an example with the tablespace users, it is empty and it has only one datafile of 50MB with autoextend on (my datafiles are ASM, but the example works with regular files too)

Now I will create and populate 2 tables, the second much bigger than the first:

SQL> create table map1 (col1 number, col2 varchar2(100 char)) tablespace users;

Table created.

begin
for i in 1..20000 loop
insert into map1 values ( i,rpad('X',100,'X'));
end loop;
commit;
end;
/

SQL> create table map2 (col1 number, col2 varchar2(4000 char),col3 varchar2(4000 char)) tablespace users;

Table created.

begin
for i in 1..10000 loop
insert into map2 values ( i,rpad('X',4000,'X'),rpad('X',4000,'X'));
end loop;
commit;
end;
/ 

Now I create an index on the table MAP1.

SQL> create index idx_map1_col1 on map1(col1) tablespace users;
Index created. 

lets check the sizes of our segments:

col segment_name format a30
set lines 120
SQL> select segment_name,segment_type, bytes/1024/1024 MB from user_segments;
SEGMENT_NAME                       SEGMENT_TYPE     MB
 ------------------------------ ------------------ ----------
 MAP2                               TABLE                  160
 MAP1                               TABLE                  3
 IDX_MAP1_COL1                      INDEX                 .4375

Note: The tablespace has grown according to the new segments on it.

I have modified for this example a query from Tom Kyte (original query here)
that shows a tablespace map so we can see the segments’ extents and their location in the file.

SQL Plus is going to ask for the datafile ID and the block size of it, lets find it out.

SQL> show parameter db_block_size
NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_block_size integer 8192

SQL> select file_id from dba_data_files where file_name ='+DATA/rac/datafile/users.267.681841043';
FILE_ID
 ----------
 5

For this example, block size is 8 and the file_id is 5

Note: If you want to check more than one tablespace remember to undefine the variables before running the script.
undefine dbblocksize
undefine fileid

col name format a30
set pages 200
undefine dbblocksize
undefine fileid
SQL>select file_id,block_id , blocks*&&dbblocksize/1024 MB,
 owner||'.'||segment_name "Name",block_id*&&dbblocksize/1024 "Position MB"
 from sys.dba_extents
 where file_id = &&fileid
 UNION
 select file_id, block_id, blocks*&&dbblocksize/1024, 'Free' "Name",block_id*&&dbblocksize/1024 "Position MB"
 from sys.dba_free_space
 where file_id = &&fileid
 order by 1,2,3;

FILE_ID    BLOCK_ID   MB         Name                   Position MB
 ---------- ---------- ---------- ---------------------- -----------
 5          9          .0625      CACOSTA.MAP1           .0703125
 5          17         .0625      CACOSTA.MAP1           .1328125

 (...) continous space used by MAP1 table

 5          137        1          CACOSTA.MAP1           1.0703125
 5          265        1          CACOSTA.MAP1           2.0703125
 5          393        .0625      CACOSTA.MAP2           3.0703125
 5          401        .0625      CACOSTA.MAP2           3.1328125
 (...) continous space used by MAP2 table
 5          18825      8          CACOSTA.MAP2           147.070313
 5          19849      8          CACOSTA.MAP2           155.070313
 5          20873      .0625      CACOSTA.IDX_MAP1_COL1  163.070313
 5          20881      .0625      CACOSTA.IDX_MAP1_COL1  163.132813
 (...)
 5          20913      .0625      CACOSTA.IDX_MAP1_COL1  163.382813
 5          20921      .0625      CACOSTA.IDX_MAP1_COL1  163.445313
 5          20929      .25        Free                   163.507813

We can see that at the beginning are the extents for the MAP1 table, then the extents for MAP2 table and at the end the extents for the IDX_MAP1_COL1 index.

Now, lets drop the MAP2 table:

SQL> drop table map2 purge;
Table dropped.
SQL> select segment_name,segment_type, bytes/1024/1024 MB from user_segments;
SEGMENT_NAME SEGMENT_TYPE MB
 ------------------------------ ------------------ ----------
 MAP1 TABLE 3
 IDX_MAP1_COL1 INDEX .4375

Here we can see that our tablespace is used by 2 objects but they don’t reach 4MB in size together,

Here is the datafile map again:

FILE_ID     BLOCK_ID   MB         Name                   Position MB
 ---------- ---------- ---------- ---------------------- -----------
 5          9          .0625      CACOSTA.MAP1            .0703125
 5          17         .0625      CACOSTA.MAP1            .1328125
 (...)
 5          137        1          CACOSTA.MAP1            1.0703125
 5          265        1          CACOSTA.MAP1            2.0703125
 5          393        160        Free                    3.0703125
 5          20873      .0625      CACOSTA.IDX_MAP1_COL1   163.070313
 5          20881      .0625      CACOSTA.IDX_MAP1_COL1   163.132813
 5          20889      .0625      CACOSTA.IDX_MAP1_COL1   163.195313
 5          20897      .0625      CACOSTA.IDX_MAP1_COL1   163.257813
 5          20905      .0625      CACOSTA.IDX_MAP1_COL1   163.320313
 5          20913      .0625      CACOSTA.IDX_MAP1_COL1   163.382813
 5          20921      .0625      CACOSTA.IDX_MAP1_COL1   163.445313
 5          20929      .25        Free                    163.507813

We can see the free space obtained from the MAP2 dropped table (bold) .
So even if the tablespace has only 2 objects that are less than 5 Megs I cannot shrink it by more than .25Megs

SQL> alter database datafile '+DATA/rac/datafile/users.267.681841043' resize 163m;
 alter database datafile '+DATA/rac/datafile/users.267.681841043' resize 163m
 *
 ERROR at line 1:
 ORA-03297: file contains used data beyond requested RESIZE value

The solution is to move those objects that resides beyond our target size, then we can resize and move the objects back.

NOTE: for this example I am showing the whole datafile map, but if you want to keep it simple use the following script, it will ask for the target datafile size and will show you those segments that needs to be moved out in order to achieve the shrink.

select segment_name, segment_type, bytes/1024/1024/1024 GB from dba_segments
where segment_name in(
select distinct segment_name
from sys.dba_extents
where file_id = &&fileid
and block_id*&dbblocksize/1024 > &target_size_mb)
/ 

Lets create a new tablespace and move out the index to it.

SQL> create tablespace users2 datafile '+data' size 5m;

Tablespace created.

SQL> alter index CACOSTA.IDX_MAP1_COL1 rebuild tablespace users2; 

we should now be able to resize the datafile to 10MB

SQL> alter database datafile '+DATA/rac/datafile/users.267.681841043' resize 10m;
Database altered. 

Now lets move back our index:

SQL> alter index CACOSTA.IDX_MAP1_COL1 rebuild tablespace users;
Index altered.
SQL> drop tablespace users2;
Tablespace dropped. 

And that’s it, in this example we managed to identify those objects that are beyond certain size in a datafile so we can move only those really needed for shrinking a datafile.