I have a table which seems to be taking up more space than it needs to. I have been advised to copy the data to a new table and rename the new table and old table to swap them. How can I confirm if a current table is actually fragmented? How can I estimate or calculate the new size of the fresh table containing the same data?
If your stats are up to date, this should give a decent indication if tables have lots more blocks than the volume of row data suggests.
select table_name, round((num_rows * avg_row_len) /(8*1024)), blocks
from user_tables where ....
This space would be used for future inserts, so isn't necessarily a problem. If you've done a large archive or delete of data, it may be worth reclaiming space (especially if you do lots of full table scans). [Note: I've assumed 8k blocks, which are the default.]
If you do a CREATE/DROP/RENAME you'll lose any indexes, constraints, grants (plus table comments if you use them).
You are better off checking the current tablespace (look in USER_SEGMENTS) and doing an ALTER TABLE tablename MOVE current_tablespace;
You will need to rebuild indexes aftwards too. Pick them from USER_INDEXES and do an ALTER INDEX ... REBUILD;