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
Printable View
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
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;
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
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
Code: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
select * from v$sort_segment;