The tables I can move, but I have INDEX, LOBSEGMENTS, LOBINDEX in there also
Can I move these over too or do I have to rebuild them ? And if so, how..
Another way to see what is in a tablespace is the "tablespace map" option in dbconsole. It can be slow, 6G might push your patience. 1500 objects are better handled with scripts, but the map option can be real handy when you've moved everything and Oracle insists something is still there. Be sure and purge recylebin first.
Sorry to bring this back up again but could I export the tablespace (EXPDP), create a new one (with only a couple of datafiles) and import the dump file into this newly created tablespace ?
You can use the REMAP_TABLESPACE option to do that
Export the data.
DROP the tablespace.
(re)CREATE the tablespace with fewer datafiles.
Import the data (back to the same tablespace name)
This requires downtime.
Hemant K Chitale