How to find out Tablespace full or not? - URGENT..

[iyyappan]

Hi,

In which data dictionary view and dynamic performance view
find out whether tablespace is full or not?

In specific data dictionary view or dynamic performance view
is there.

Thanks,
Iyyappan.M

[mattias]

Hi, run this query. It will show tablespace, total mb, free mb, free % and largest segment size. Largest segment size will indicate how fragmented your tablespace is and how big the largest available chunk of space is.

SELECT a.tablespace_name tablespace,
ROUND(SUM(a.total1)/1024/1024, 1) "Total Mb",
ROUND(SUM(a.total1)/1024/1024, 1)-ROUND(SUM(a.sum1)/1024/1024, 1) "Used Mb",
ROUND(SUM(a.sum1)/1024/1024, 1) "Free Mb",
ROUND(SUM(a.sum1)/1024/1024,1)*100/ROUND(SUM(a.total1)/1024/1024,1) "Free %",
ROUND(SUM(a.maxb)/1024/1024, 1) "Largest Segment Size Mb"
FROM (SELECT tablespace_name,
0 total1,
SUM(bytes) sum1,
MAX(bytes) maxb,
COUNT(bytes) cnt
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name,
SUM(bytes) total1,
0,
0,
0
FROM dba_data_files
GROUP BY tablespace_name) a
GROUP BY a.tablespace_name;

[iyyappan]

Hi,

How to find out the temporary tablespaces whether full or not?

In v$temp_space_header are other views are there.

Awaiting ur reply,

Thanks,
Iyyappan.M

[Sonia]

hi folks,

there is an easy way to find out the free space and used space in a tablespace.

query sm$ts_free to find out % free space in a tablespace
sm$ts_used to find out % used space in a tablespace

[pando]

sys@DSIM>select * from V$TEMP_EXTENT_POOL;

TABLESPACE_NAME                   FILE_ID EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
------------------------------ ---------- -------------- ------------ ------------- ----------- ------------ ---------- ------------
TEMP                                    1          15999           28        255984         448   2097020928    3670016            1

[pando]

select * from v$sort_segment;