Problems resizing a datafile

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 - 09:42:59 CDT