Datafile resize

[ams-jamali]

Hi all,

In my ProductionDB i have truncated table which contails 4.7GB.The table present in ISHDATA tablespace with datafiles
'/mnt/db/oracle/oradata/ISPRO/ishdata01.dbf' upto 10 files are there in Ishdata tablespace.

After truncating i tried to resize all the 10 datafiles, to show free space at OS level.(MD wants to see at OS level.)

So i tried as ..
SQL> alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata01.dbf' resize 1900m;
alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata01.dbf' resize 1900m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
...

SQL> alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata10.dbf' resize 4000m;
alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata10.dbf' resize 4000m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

spaces at datafiles reduces at different level ie., for one datafile 250MB released another 1800MB released.

So,please help me on this regard.

Thanks in advance.

[davey23uk]

you have other tables which are at the end of the file so the datafiles cant be resized

[slimdave]

Query DBA_EXTENTS to find the segments at the high end of the data files.

[ams-jamali]

So..,If i want to rezise i have to drop entire tablespace and recreate with new size.So i have to take backup of that tablespace(ishdata).

ok., In no way i can play in production.

Thanks to all.

[PAVB]

So..,If i want to rezise i have to drop entire tablespace and recreate with new size.

Nope.
You have to move offending objects to a different place, resize tablespace, move objects back.

[Bore]

PAVB, isn't it easyer, after the objects have been moved, just to drop the empty tablespace?
OK, say you do not have to drop it, but generally yes, that's the easyest way

[ams-jamali]

Resizing tablespace... can't follow.

I can try as

alter tablespace [tbs] coalesce;

then i can resize datafile (am i right...)

[Bore]

Yes, I belive that is what PAVB means

[PAVB]

Yes, then resize datafile. Sorry, I'd a senior moment.

[ams-jamali]

It doesn't help me to release even 1GB.

I found free space using dba_data_files & dba_extents it shows only 154MB.
So instead of resizing i left that..,

Thanks to all.