Best option to defragment a database ORA-03297
I am running Oracle Db 11.2.0.3 on Windows 2008 Server 64 bit.
I would like to know how to find out if the db is fragmented,is it possible to get a script to check this?
I want to reduce size of datafiles but I get error ORA-03297: file contains used data beyond requested RESIZE value
Also,I would like to get the best option to de-fragment the database.I have seen several options below
1)alter table xxx deallocate unused space;
2)alter table xxx coalesce;
alter index xxx coalesce;
3)alter tablespace xxx coalesce;
Please advise on the best option out of these or any other options available.