Oracle Database Discussions

Hi,
I have a partitioned table which have 90 partitions and 64 subpartitions in each partitions.This each 64 subpartitions are placed in the 64 tablespace.

my table structure
CREATE TABLE CDR (
SERVICE_TYPE NUMBER(20,0) NOT NULL,
CDR_CATEGORY NUMBER(20,0) DEFAULT 1 NOT NULL,
IS_COMPLETE NUMBER(20) NOT NULL,
IS_ATTEMPTED NUMBER(20) NOT NULL,
SERVICE NUMBER(20,0) DEFAULT 2047,
PHONE_NUMBER VARCHAR2(40),
DAY_OF_YEAR NUMBER(20,0),
TELESERVICE_CODE VARCHAR2(20),
BEARERSERVICE_CODE VARCHAR2(20),
SUPPLEMENTARY_SERVICE VARCHAR2(20)
)
INITRANS 4 STORAGE(FREELISTS 16)
PARTITION BY RANGE(DAY_OF_YEAR)
SUBPARTITION BY HASH (PHONE_NUMBER)
(
partition p001 values less than (2)
(
subpartition p001_sp01 tablespace TS_CDR01,
subpartition p001_sp02 tablespace TS_CDR02,
subpartition p001_sp03 tablespace TS_CDR03,
subpartition p001_sp04 tablespace TS_CDR04,
subpartition p001_sp05 tablespace TS_CDR05,
subpartition p001_sp06 tablespace TS_CDR06,
:::::::::::::::
:::::::::::::::
subpartition p001_sp64 tablespace TS_CDR64
),


These tablespace's were initially created with maxsize 37GB. Now the size of each tablespace is around 37GB but the used space is only 14G; I am trying to resize the tablespace to 20G but it throws error as "file contains used data beyond requested RESIZE value" . So I am sure there was a fragmentation in the tablespaces. In order to defragment I decided to move all the subpartition within same tablespace.
like

alter table CDR move subpartition p001_sp01;
similarly for remaining 63 subpartitions


what I want to know is will this help me in defragmenting and resizing the datafile in the tablespace?

OS:HP_UX
ORACLE VERSION:9.2.0.7.0

Shrinking the datafile size from 37GB to 20GB

SQL> alter database datafile '/disk03/oradata/CDR27.dbf' resize 20000m;
alter database datafile '/disk03/oradata/CDR27.dbf' resize 20000m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Alter table CDR move subpartition p001_sp01;

will this work?

With Regards
Boo