[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Resize datafile problem

Posted on 2002-07-04
3
Medium Priority
?
1,066 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:rgcomm
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 7129742
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
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 7129746
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
 

Expert Comment

by:JeanaM
ID: 7135041
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question