OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » ORA-03297: Problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-03297: Problem [message #273762]

Thu, 11 October 2007 10:27

thorin666
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)

  select
   d.file_id,
   d.bytes/1024/1024 total,
   round(sum(x.bytes)/1024/1024) used
 from
   dba_data_files d,
   dba_extents x
 where
   d.file_id=x.file_id
   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

Report message to a moderator

Re: ORA-03297: Problem [message #273768 is a reply to message #273762]

Thu, 11 October 2007 10:57

girish.rohini
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.

--Girish

Report message to a moderator

Previous Topic:	Trace database operations
Next Topic:	need to know in how many table this type of relation exists.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 07:43:29 CST 2024