Solved

Resize datafile problem

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

895 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