点击(此处)折叠或打开
- --tablespace size
- select a.tablespace_name,
- round(a.bytes_alloc / 1024 / 1024 /1024,2) Size_G,
- round(nvl(b.bytes_free, 0) / 1024 / 1024 /1024,2) Free_G,
- round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024 /1024,2) Used_G,
- round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Free_Percent,
- 100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Usage_Percent,
- round(maxbytes/ 1024 / 1024 /1024) Max_G
- from ( select f.tablespace_name,
- sum(f.bytes) bytes_alloc,
- sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
- from dba_data_files f
- group by tablespace_name) a,
- ( select f.tablespace_name,
- sum(f.bytes) bytes_free
- from dba_free_space f
- group by tablespace_name) b
- where a.tablespace_name = b.tablespace_name (+)
- union all
- select h.tablespace_name,
- round(sum(h.bytes_free + h.bytes_used) / 1024 / 1024 /1024,2) Size_G,
- round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024 /1024,2) Free_G,
- round(sum(nvl(p.bytes_used, 0))/ 1024 / 1024 /1024,2) Used_G,
- round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
- 100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
- round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1024 / 1024 /1024),2) Max_G
- from sys.v_$TEMP_SPACE_HEADER h,
- sys.v_$Temp_extent_pool p,
- dba_temp_files f
- where p.file_id(+) = h.file_id
- and p.tablespace_name(+) = h.tablespace_name
- and f.file_id = h.file_id
- and f.tablespace_name = h.tablespace_name
- group by h.tablespace_name
- ORDER BY 1;
-
- --tablespace can resize
- select fs.tablespace_name, fs.file_id, round(nvl(sum(fs.bytes), 0) / 1048576) can_shrink_by
- from dba_free_space fs,
- (
- select e1.tablespace_name, e1.file_id, e1.block_id + e1.blocks last_block
- from dba_extents e1,
- (Select tablespace_name, file_id, max(block_id) block_id
- from dba_extents
- group by tablespace_name, file_id) e2
- where e1.tablespace_name = e2.tablespace_name
- and e1.file_id = e2.file_id
- and e1.block_id = e2.block_id
- ) e
- where fs.tablespace_name = e.tablespace_name
- and fs.file_id = e.file_id
- and fs.block_id >= e.last_block
- group by fs.tablespace_name, fs.file_id;
-
-
- --datafile size
- SELECT t.tablespace_name, 'Datafile' file_type,
- t.status tablespace_status, d.status file_status,
- round((d.bytes - NVL(f.sum_bytes, 0)) / 1024 / 1024 /1024,2) used_G,
- round(NVL(f.sum_bytes, 0) / 1024 / 1024 /1024,2) free_G,
- t.initial_extent,t.next_extent, t.min_extents, t.max_extents, t.pct_increase,
- d.file_name, d.file_id, d.autoextensible, d.maxblocks,
- round(d.maxbytes / 1024 / 1024 /1024,2) max_G,
- nvl(d.increment_by,0) increment_by, t.block_size
- FROM (SELECT tablespace_name, file_id, SUM(bytes) sum_bytes
- FROM DBA_FREE_SPACE
- GROUP BY tablespace_name, file_id) f,
- DBA_DATA_FILES d,
- DBA_TABLESPACES t
- WHERE t.tablespace_name = d.tablespace_name
- AND f.tablespace_name(+) = d.tablespace_name
- AND f.file_id(+) = d.file_id
- GROUP BY t.tablespace_name, d.file_name, d.file_id, t.initial_extent,
- t.next_extent, t.min_extents, t.max_extents,
- t.pct_increase, t.status, d.bytes, f.sum_bytes, d.status,
- d.AutoExtensible, d.maxblocks, d.maxbytes, d.increment_by, t.block_size
- UNION ALL
- SELECT h.tablespace_name,
- 'Tempfile',
- ts.status,
- t.status,
- round(SUM(NVL(p.bytes_used, 0)) / 1024 / 1024 /1024,2) used_G,
- round(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1024 / 1024 /1024,2) free_G,
- -1,
- -1,
- -1,
- -1,
- -1,
- t.file_name,
- t.file_id,
- t.autoextensible, t.maxblocks,
- round(t.maxbytes / 1024 / 1024 /1024,2) max_G,
- nvl(t.increment_by, 0) increment_by, ts.block_size
- FROM sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p, sys.DBA_TEMP_FILES t, sys.dba_tablespaces ts
- WHERE p.file_id(+) = h.file_id
- AND p.tablespace_name(+) = h.tablespace_name
- AND h.file_id = t.file_id
- AND h.tablespace_name = t.tablespace_name
- and ts.tablespace_name = h.tablespace_name
- GROUP BY h.tablespace_name, t.status, t.file_name, t.file_id, ts.status,
- t.autoextensible, t.maxblocks, t.maxbytes, t.increment_by, ts.block_size
- ORDER BY 1, 5 DESC;
-
- --datafile can resize
- Select sum(bytes)/1024/1024 Can_Resize_M
- from dba_free_space
- where file_id = 4
- and block_id >= nvl((Select (block_id + (bytes/8192))
- from dba_extents
- where block_id = (Select max(block_id)
- from dba_extents
- where file_id = 4
- )
- and file_id = 4), 0);
- --tempfile can resize
- Select round(sum(bytes) / 1048576) Can_Resize_M
- from sys.v_$TEMP_EXTENT_MAP
- where file_id = 1
- and block_id >= nvl((Select block_id + (bytes / 4096)
- from sys.v_$TEMP_EXTENT_MAP
- where block_id = (Select max(block_id)
- from sys.v_$TEMP_EXTENT_MAP
- where file_id = 1
- and owner <> 0)
- and file_id = 1), 0);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-2114594/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28539951/viewspace-2114594/