TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN status FORMAT a9 HEADING 'Status'
- COLUMN name FORMAT a30 HEADING 'TS. Name'
- COLUMN type FORMAT a15 HEADING 'TS Type'
- COLUMN extent_mgt FORMAT a10 HEADING 'Ext.Mgt.'
- COLUMN segment_mgt FORMAT a10 HEADING 'Seg.Mgt.'
- COLUMN ts_size FORMAT 999,999,999 HEADING 'TS.Size (MB)'
- COLUMN used FORMAT 999,999,999 HEADING 'Used (MB)'
- COLUMN free FORMAT 999,999,999 HEADING 'Free (MB)'
- COLUMN pct_used FORMAT 999 HEADING 'Pct.Used'
- COLUMN pct_free FORMAT 999 HEADING 'Pct.FREE'
- BREAK ON report
- COMPUTE count OF type ON report
- COMPUTE sum OF ts_size ON report
- COMPUTE sum OF used ON report
- SELECT
- d.tablespace_name name
- , NVL(a.bytes, 0)/1024/1024 ts_size
- , NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 used
- , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used
- FROM
- sys.dba_tablespaces d
- , ( select tablespace_name, sum(bytes) bytes
- from dba_data_files
- group by tablespace_name
- ) a
- , ( select tablespace_name, sum(bytes) bytes
- from dba_free_space
- group by tablespace_name
- ) f
- WHERE
- d.tablespace_name = a.tablespace_name(+)
- AND d.tablespace_name = f.tablespace_name(+)
- AND NOT (
- d.extent_management like 'LOCAL'
- AND
- d.contents like 'TEMPORARY'
- )
- UNION ALL
- SELECT
- d.tablespace_name name
- , NVL(a.bytes, 0)/1024/1024 ts_size
- , NVL(t.bytes, 0)/1024/1024 used
- , NVL(t.bytes / a.bytes * 100, 0) pct_used
- FROM
- sys.dba_tablespaces d
- , ( select tablespace_name, sum(bytes) bytes
- from dba_temp_files
- group by tablespace_name
- ) a
- , ( select tablespace_name, sum(bytes_cached) bytes
- from v$temp_extent_pool
- group by tablespace_name
- ) t
- WHERE
- d.tablespace_name = a.tablespace_name(+)
- AND d.tablespace_name = t.tablespace_name(+)
- AND d.extent_management like 'LOCAL'
- AND d.contents like 'TEMPORARY'
- ORDER BY
- 4 desc
- /
复制代码
|
|