-
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;
-
How to find out tablespace is full or not? - Urgent(Temporary tablespace)
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|