?
Solved

Resize datafile problem

Posted on 2002-07-04
3
Medium Priority
?
1,070 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

589 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