Oracle Database Discussions

Hi all,

Oracle 10.2.0.3.0 on HP-UX

I have restored cold backup of size 1.5 TB. All the tables are present in a single schema.

SQL> SELECT SUM(bytes)/1024/1024/1024 "GB" FROM user_segments;

GB
----------
1496.96539

Application user has dropped all the unnecessary rows from the tables.

SQL> SELECT SUM(bytes)/1024/1024/1024 "GB" FROM user_segments;

GB
--------
677.87667

After dropping the tables, I try to shrink the tables to reduce HWM. For that I have the below query on all tables.

alter table table_name enable row movement;

alter table table_name shrink space compact;

I check with a script available in metalink to scan the datafiles that how far i can reduce size of the datafile manually

After executing script. I found there is no shink of datafile can be done. When I try to reduce the datafile using

sql> altere database <datafile> resize <size>;

it throws error like

ORA-03297: file contains used data beyond requested RESIZE value

When I check my size of the database It is the same as I checked before shrinking tables ie 1.5TB.

Could you please suggest what would be the problem?

Thanks
KSG

Edited by: KSG on Mar 15, 2010 2:01 PM