Solved

Resize datafile problem

Posted on 2002-07-04
3
1,052 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now