Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to shrink data file

Re: How to shrink data file

From: <jdorlon_at_my-deja.com>
Date: Thu, 01 Feb 2001 20:38:48 GMT
Message-ID: <95chgm$agh$1@nnrp1.deja.com>

> alter database datafile '/oracle/u01/oradata/wapdb/system01.rbf'
 resize 800M;
>
> But the following error message was returned:
>
> ORA-03297: file contains used data beyond requested RESIZE value
>

When you resize a datafile, Oracle trims only off the END of the file. So if you have objects which are located at the end of the file then you must move them before you can shrink the file down beyond that point. I do NOT recommend trying to move anything in the SYSTEM tablespace, unless they are user objects (ie, not owned by SYS or SYSTEM!) For the Rollback, you can clean this up by dropping the rollback segments which are at the end of your file, then shrinking your file, then creating your rollback segments again.

The following query will produce a pretty map of where things are in your tablespace :

Select owner segment_owner, Segment_name, partition_name, Block_ID, File_id, Blocks, segment_type
from sys.DBA_Extents
where tablespace_name = 'your_tablespace' union
Select null, 'Free Space', null, Block_ID, File_id, Blocks, null from sys.DBA_FREE_SPACE
where tablespace_name ='your_tablespace' order by 5,4

Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 01 2001 - 14:38:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US