Hi Tom,

Thank you for the answer. But it is showing no rows selected.

SQL> select s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
from sys.obj$ o, sys.tab$ t, sys.user$ u, sys.snap$ s , sys.ts$ p
2 3 where t.ts#=p.ts# and p.name='GOFLX'
4 and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname
5 order by 1,2;

no rows selected

SQL> select o.owner, o.object_name index_name, o.object_type, sq.mv_owner,sq.mv_name,sq.tablespace
2 from dba_objects o,
3 (select i.obj#,s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
4 from sys.obj$ o, sys.tab$ t, sys.ind$ i, sys.user$ u, sys.snap$ s,sys.ts$ p where i.ts#=p.ts# and p.name='GOFLX'
5 and i.bo#=t.obj# and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname ) sq
6 where sq.obj#=o.object_id
7 order by 1,2,3;

no rows selected

Thank You
Shiva

Maybe try emptying the recyclebin?

Hi Siva,

You tried to drop tablespace GOLFX, but then you executed the query against DBA_SEGMENTS using GOFLX tablespace. You also executed Connor's query passing the same GOFLX but not GOLFX, which you tried to drop.
Perhaps you made a typo.
Please substitute the correct tablespace in Connor's query and let us know how it works.