How can I see what objects under specific tablespaces
28348Oct 11 2009 — edited Oct 13 2009Dear all,
I run the following scripts to see all the space available for each tablespace.
But I don't know how to see what objects are under each tablespace.
--- All tablesapces size available ---
SELECT /*+ all_rows */ Total.name "Tablespace Name",
NVL(Free_space, 0) Free_space,
NVL(total_space-Free_space, 0) Used_space,
total_space
FROM
(SELECT tablespace_name, SUM(bytes/1024/1024) Free_Space
FROM sys.dba_free_space
GROUP BY tablespace_name
) Free,
(SELECT b.name, SUM(bytes/1024/1024) TOTAL_SPACE
FROM sys.v_$datafile a, sys.v_$tablespace B
WHERE a.ts# = b.ts#
GROUP BY b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
Please advice,
Amy