Re: Problems resizing a datafile

Hi Roy,
This is because there is data beyond the 75M you specified. Look at dba_extents, depending on the block size of your database you should be able to find which segments are causing the problem. Obviously your 50M is scattered.
I am afraid you will end up exporting all segments in this tablespace, drop them and import them back (or drop and recreate the tablespace and import them back)
Got this situation before, so let's shake hands...

Hth,

Sybrand Bakker, Oracle DBA

rspeaker_at_my-dejanews.com wrote in message <7gpldj$j0s$1_at_nnrp1.dejanews.com>...
>Hello group,
>
>I am having problems resizing a datafile in 1 or 2 of my tablespaces. Here
>are the details (some of the names have been changed to protect the
>innocent):
>
>Oracle 8.0.4.3.1 running on AIX v 4.2.1
>
>existing tablespace DATA contains 3 tables, A, B, & C.
>
>tablespace storage parameters from user_tablespaces are as follows:
>
>TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
>------------------------------ -------------- ----------- -----------
>DATA 512000 512000 1
>
>
>table storage parameters from user_tables are as follows:
>
>TABLE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
>------------------------------ -------------- ----------- -----------
>A 47185920 4194304 1
>B 3145728 1048576 1
>C 24576 4096 1
>
>
>finally, allocated segment information from dba_segments is as follows:
>
>SEGMENT_NAME BLOCKS BYTES
>-------------------- ---------- ----------
>A 11520 47185920
>B 770 3153920
>C 10 40960
>
>
>now, the existing size of my DATA datafile is 400M. I am trying to resize
it
>down to 75M, but get the following error:
>
>SQL> alter database datafile '/u03/oradata/PROD/DATA01.dbf' resize 75M;
>alter database datafile '/u03/oradata/PROD/DATA01.dbf' resize 75M
>*
>ERROR at line 1:
>ORA-03297: file contains 12300 blocks of data beyond requested RESIZE value
>
>
>but no matter what size I try to resize the file to, I get the exact same
>message, with the same number of blocks (12300). I have even tried
resizing
>it down from 400M to 200M, same result.
>
>How can this be? What is wrong here? From what I can tell, there is only
>about 50M of space allocated to all the tables in this tablespace, so why
>can't I size it to 75M ?
>
>Thanks in advance for your help.
>
>Roy
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed May 05 1999 - 12:22:57 CDT