Skip to Main Content
  • Questions

  • ORA-03297 - when trying to shrink a datafile

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Muhammad Riaz.

Asked: October 03, 2015 - 11:02 pm UTC

Last updated: October 04, 2015 - 8:29 am UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Dear Team,

I am using the maxshrink script provided on this site to try to reduce the size of the data file. Here is the output for the specific data file:


SQL> set verify off
SQL> column file_name format a50 word_wrapped
SQL> column smallest format 999,990 heading "Smallest|Size|Poss."
SQL> column currsize format 999,990 heading "Current|Size"
SQL> column savings  format 999,990 heading "Poss.|Savings"
SQL> break on report
SQL> compute sum of savings on report
SQL> column value new_val blksize
SQL> select value from v$parameter where name = 'db_block_size'
  2  /

VALUE
--------------------------------------------------------------------------------
8192

SQL> ed
Wrote file d:\afiedt.buf

  1  select file_name,
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
  3         ceil( blocks*&&blksize/1024/1024) currsize,
  4         ceil( blocks*&&blksize/1024/1024) -
  5         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
  6  from dba_data_files a,
  7       ( select file_id, max(block_id+blocks-1) hwm
  8           from dba_extents
  9          group by file_id ) b
 10  where a.file_id = b.file_id(+)
 11* and a.file_id = 38
SQL> /

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
<Data File Name>              1   16,587   16,586

                                                                     --------
sum                                                                    16,586

SQL> column cmd format a75 word_wrapped
SQL> select 'alter database datafile ''' || file_name || ''' resize ' ||
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
  3  from dba_data_files a,
  4       ( select file_id, max(block_id+blocks-1) hwm
  5           from dba_extents
  6          group by file_id ) b
  7  where a.file_id = b.file_id(+)
  8    and ceil( blocks*&&blksize/1024/1024) -
  9        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
 10  and a.file_id = 38;

CMD
---------------------------------------------------------------------------
alter database datafile
'<Data File Name>' resize 1m;


SQL> alter database datafile
  2  '<Data File Name>' resize 1m;
alter database datafile
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


SQL> ed
Wrote file d:\afiedt.buf

  1  alter database datafile
  2* '<Data File Name>' resize 1g
SQL> /
alter database datafile
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> SELECT sum(bytes)/1024/1024 from dba_free_space where file_id = 38;

SUM(BYTES)/1024/1024
--------------------
               16586

SQL> SELECT count(*) FROM dba_extents where file_id = 38;

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


Can you please help me to solve this mystery?

Regards,

Muhammad Riaz

and Connor said...

Things that often catch me out when I'm trying to shrink a file are:

1) A file needs at least a small portion to hold some extent management information, so you cannot shrink it to a size below that. Also, (if memory serves), the file itself can also have this information at the tail end of the file.

2) Do you have a temporary segment in there ?

3) Do you have recyclebin objects in there ?


In 12c (perhaps 11.2.0.4?) , you can drop a datafile that is empty without impacting the tablespace

SQL> create tablespace demo datafile 'C:\ORACLE\ORADATA\NP12\DEMO1.DBF' size 10m;

Tablespace created.

SQL> alter tablespace demo add datafile 'C:\ORACLE\ORADATA\NP12\DEMO2.DBF' size 10m;

Tablespace altered.

SQL> create table X ( y int ) tablespace demo;

Table created.

SQL> insert into x values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dba_extents
  2  where tablespace_name = 'DEMO';

OWNER
------------------------------------------------------------------------------------------------------------------------
SEGMENT_NAME
----------------------------------------
PARTITION_NAME
------------------------------------------------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME           EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------ ------------------------ ---------- ---------- ---------- ---------- ---------- ------------
MCDONAC
X

TABLE              DEMO                              0          8        128      65536          8         8


SQL> select * from dba_data_files
  2  where tablespace_name = 'DEMO';

FILE_NAME                                                 FILE_ID TABLESPACE_NAME               BYTES     BLOCKS
------------------------------------------------------ ---------- ------------------------ ---------- ----------
STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
--------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
C:\ORACLE\ORADATA\NP12\DEMO1.DBF                                8 DEMO                       10485760       1280
AVAILABLE            8 NO           0          0            0    9437184        1152 ONLINE

C:\ORACLE\ORADATA\NP12\DEMO2.DBF                                9 DEMO                       10485760       1280
AVAILABLE            9 NO           0          0            0    9437184        1152 ONLINE


SQL> alter tablespace demo drop datafile 9;

Tablespace altered.

SQL> select TABLESPACE_NAME, STATUS from dba_tablespaces where tablespace_name = 'DEMO';

TABLESPACE_NAME          STATUS 
------------------------ ----------
DEMO                     ONLINE    


SQL>


Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Still not able to get

Muhammad Riaz, October 04, 2015 - 5:11 am UTC

Thank you very much for your reply.

(1) I have tried to reduce the size mentioning different sizes (1g, 5g, 10g, 15g etc.) but I still get the same error so I believe we are already accommodating the space required to maintain extent management information.
(2) We don't have temporary segment
(3) There are no recylebin objects

I am still not able to find out the cause for the error. How to find if below is applicable to us and If applicable, how to resolve this (restart all instances of RAC)?

<Quote>
Also, (if memory serves), the file itself can also have this information at the tail end of the file.
</Quote>

Thanks.

Mystery Solved

Muhammad Riaz, October 04, 2015 - 6:08 am UTC

I stay corrected; there were objects in the recycle. I was using "purge recyclebin" command instead of "purge dba_recyclebin".

Regards,
Connor McDonald
October 04, 2015 - 8:29 am UTC

glad you found the cause
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.