I have a tablespace that is 1.5gb in size and I find it half filled. But I dont find any objects in the tablespace. I have checked for the presence of tables,indexes etc in that table but not have found any. Also none of the users have it as their default tablespace. I need to move the contents of this tablespace to some other tablespace and then drop it. Could someone throw light on how to go ahead. Also could someone throw light on how to find out what the tablespace contains.
It is half filled means definitely it consists of objects.If check all the users or the table in their schema it will tell which tables are using this tablespace.
If u want to move any users to this tablespace u can use Alter command.But be careful that other than Oracle 9i when u will move the objects to other tablespace u need to rebuild the indexes.
Thank you for your timely and usefull suggestion. I could locate two LOB indexes in those tablespace and could be located by querying dba_segments with your query. My mind was completely locked and to be frank, I queried all other things except dba_segments. I finally recreated those two tables containing lob indexes to the tablespace required.