Originally posted by Manf
I need to reduce a tablespace, due a lot of data it used to contain, was erased, and I need also to reduce backup time.
I need to do it ONLINE, is there any command to do it?
I forgot something important: the tablespace contains 6 dbf, and all of that contains data.
You first need to reduce the size of the original tables (if they still exist)
You can alter table xxx deallocate unused keep x - up to the
highwater mark of the tables. You can rebuild the indexes smaller and
into another tablespace (online).
coalesce the tablespace
find the highest block number in each datafile that contains data and
resize the datafiles up to that point ...
To shrink tables you can also use 'alter table ... move ' to another tablespace, then coalesce the tablespace and then move the tables back. Dont forget to rebuild any indexes related to the tables though as they become invalid after a table move.
If youve got a large number of tables & indexes write a sql query to write your table moves and index rebuilds by querying dba_tables and dba_indexes.