Since I no longer have access to Oracle Enterprise Manager, I wrote the following queries to pull back inofrmation about the tablespaces and table.
I would appreciate any constructive critisism and suggestions that people have. So far I haven't found an existing query that gives me the information that I want.
Thanks in advance.
Code:COL "Table Name" FORMAT A20
COL "TS Name" FORMAT A10
SELECT dba_tablespaces.tablespace_name "TS Name",
dba_tablespaces.extent_management "TS Type",
ROUND(dba_tablespaces.initial_extent/1024, 2) "Init (KB)",
ROUND(dba_tablespaces.min_extlen/1024, 2) "Min (KB)",
dba_segments.KBUsed "Used (KB)",
NVL(dba_data_files.FreeKBytes, 0) "Free (KB)",
dba_data_files.MaxKBytes "Max (KB)",
dba_tablespaces.pct_increase "% Increase"
FROM dba_tablespaces,
( SELECT tablespace_name,
ROUND(SUM(bytes)/1024, 2) KBUsed
FROM dba_segments
GROUP BY tablespace_name ) dba_segments,
( SELECT dba_data_files.tablespace_name,
ROUND(SUM(maxbytes)/1024, 2) MaxKBytes,
ROUND(SUM(user_free_space.bytes)/1024, 2) FreeKBytes
FROM dba_data_files
LEFT OUTER JOIN
( SELECT tablespace_name, bytes
FROM dba_free_space ) user_free_space ON
dba_data_files.tablespace_name =
user_free_space.tablespace_name
GROUP BY dba_data_files.tablespace_name ) dba_data_files
WHERE dba_tablespaces.tablespace_name = dba_segments.tablespace_name AND
dba_tablespaces.tablespace_name = dba_data_files.tablespace_name
ORDER BY dba_tablespaces.tablespace_name;
SELECT all_tables.table_name "Table Name",
all_tables.tablespace_name "TS Name",
all_tables.pct_free "% Free",
all_tables.pct_used "% Used",
init_extents.InitKB "Init (KB)",
all_tables.next_extent "Next (KB)",
dba_extents.KBUsed "KB Used",
dba_extents.KBFree "KB Free",
dba_extents.TotalExtents "Total Extents",
all_tables.max_extents "Max Extents"
FROM all_tables,
( SELECT segment_name, ROUND(SUM(bytes)/1024, 2) InitKB
FROM dba_extents
WHERE segment_type = 'TABLE' AND
owner = 'FIN' AND
(segment_name, extent_id) IN
(SELECT segment_name, MIN(extent_id)
FROM dba_extents
WHERE segment_type = 'TABLE' AND
owner = 'FIN'
GROUP BY segment_name)
GROUP BY segment_name ) init_extents,
( SELECT segment_name,
ROUND(SUM(dba_extents.bytes)/1024, 2) KBUsed,
ROUND(SUM(user_free_space.bytes)/1024, 2) KBFree,
COUNT(*) TotalExtents
FROM dba_extents
LEFT OUTER JOIN
( SELECT block_id, bytes
FROM dba_free_space ) user_free_space ON
dba_extents.block_id =
user_free_space.block_id
WHERE segment_type = 'TABLE' AND
owner = 'FIN'
GROUP BY dba_extents.segment_name ) dba_extents
WHERE all_tables.table_name = init_extents.segment_name AND
all_tables.table_name = dba_extents.segment_name;