Take a look at table user_extents (or dba_extents). For each segment it
shows the actual blocks used. From this you can see which segment is
located 'high' on the tablespace (i.e. beyond the 200M mark).
--
Terry Dykstra
Canadian Forest Oil Ltd.
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 - 13:24:03 CDT
