Quote:
Originally posted by grjohnson
Except Oracle isn't smart enought to clean up the sys.ts$ table when a TABLESPACE has been dropped. So in the sys.ts$ table you'll find ALL tablespace that have been created in your database was created... even if they currently do not exist.
SELECT filetab.FILE#, filetab.ts#, ts.ts#, ts.name FROM sys.FILE$ filetab, sys.ts$ ts
WHERE filetab.ts# (+) = ts.ts#
AND filetab.FILE# IS NULL
ORDER BY ts.name
You'll find the current tablespace in your database with NO associated datafiles.
Cheers,
Yes and no.