Resize datafile problem

I have 2 datafiles that have under 100mb used, but have 1gig allocated. When I try to resize I get: "ORA-03297: file contains used data beyond requested RESIZE value". One of the datafiles is from the SYSTEM Tablespace and the other is from the RBS Tablespace.
rgcommAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
When you try to RESIZE, which is the new size you try to allocate? Can you post your SQL?
Which is your version of Oracle (this could make the default unit be different (KB / MB) )?
Cheers
0
 
p_yaroslavCommented:
Hi!

You can't decrease size of this datafiles.

You may look at map of this datafiles (tablespaces) and see data distribution in datafiles.

I give you example tablespace map select:
____________________________________
select 'free space' Owner,
' ' Object,File_ID,Block_ID,Blocks,Bytes
from DBA_FREE_SPACE
where Tablespace_Name=UPPER('&1')
union
select substr(Owner,1,20),substr(Segment_name,1,32),File_ID,Block_ID,Blocks,Bytes
from DBA_EXTENTS
where Tablespace_name=UPPER('&1')
order by 3,4
____________________________________
You can resize other tablespaces (not SYSTEM or RBS) You may defragment them and after decrease there size. But in your case it's impossible (for SYS and RBS).
Though, you can decrease RBS if you firstly drop all rollback segments from RBS, decrease RBS datafiles and after that create rollback segments anew.

Best regards,
Yaroslav.
0
 
JeanaMCommented:
I agree with Yaroslav's method, with an additional suggestion.  When you run the query, take a look at the segments in the one file that you are trying to resize.  You can list the datafiles to find out what the fileid is for that file.

For example, say you want to resize one of your datafiles for RBS from 1 gig to 500 meg. You do not have to drop all of the rollback segments in RBS, just those that are located in blocks above the 500 meg limit in that particular file.  

I have successfully resized RBS using this method. I have only resized SYSTEM to make it bigger, not smaller.  I would never attempt to drop segments from SYSTEM for fear of dropping something that is needed for database integrity.  If you really want to decrease the size of SYSTEM and be really safe, you could export the entire database, recreate it with a smaller SYSTEM tablespace, and then reimport everything back in.  This is not a fun thing to do however and may take a lot of time if your database is big and has lots of data.
0
All Courses

From novice to tech pro — start learning today.