Home » RDBMS Server » Server Administration » ORA-03297: Problem
ORA-03297: Problem [message #273762] Thu, 11 October 2007 10:27 Go to next message
Messages: 144
Registered: March 2007
Senior Member

Hello, i am using oracle 10g, and i have a problem trying to resize a datafile.
i was checking the datafile size and the used blocks and i see that the datafile size is 14gb but the used space is 1.2 gb aprox. (File_id 4 as for example)
   d.bytes/1024/1024 total,
   round(sum(x.bytes)/1024/1024) used
   dba_data_files d,
   dba_extents x
   group by d.file_id, d.bytes
   order by file_id;

and i get
   FILE_ID      TOTAL       USED
---------- ---------- ----------
         1       2150        185
         2        250        232
         3        190        189
         4      14600       1254
         5       2150       2102
         6        200        170
         7       1200        228
         8        500        472
         9       1350       1344
        10        450        400
        11      11000      10390

I was trying to resize the datafile but i get
ORA-03297: file contains used data beyond requested RESIZE value

i mean that i must reorganize the used blocks

Actually i have non free spaces in the tables but i was deleting indexes and moving tables to another tablespace. That's why i can't shrink tables but i want to know if exist a way to reorganize blocks to resize the datafile
Re: ORA-03297: Problem [message #273768 is a reply to message #273762] Thu, 11 October 2007 10:57 Go to previous message
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member

First check the High water mark in datafile

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
and a.tablespace_name='&Tablespace_Name'

1. Check for fragmentation.
2. Try moving objects within the tablespace or if needed to another tablespace & move them back after resize is complete.

Previous Topic: init.ora
Next Topic: ora-12560:TNS: protocol adapter error
Goto Forum:

Current Time: Tue Aug 22 04:25:55 CDT 2017

Total time taken to generate the page: 0.14929 seconds