一.查看表空间剩余百分比

SELECT tablespace_name, TO_CHAR

(100*sum_free_blocks/sum_alloc_blocks, '999.99')

AS percent_free

FROM

(SELECT tablespace_name, SUM(blocks)

AS sum_alloc_blocks

FROM dba_data_files

GROUP BY tablespace_name),

(SELECT tablespace_name

AS fs_ts_name,MAX(blocks)

AS max_blocks,

COUNT(blocks)

AS count_blocks, SUM(blocks) AS sum_free_blocks

FROM dba_free_space

GROUP BY tablespace_name)

WHERE tablespace_name = fs_ts_name

ORDER BY percent_free DESC;

?

涉及的视图有：dba_free_space? dba_data_files

?

二.查看当前sessions的sid,pid,以及serial#等

select p.pid, p.spid, s.sid, s.serial# from v$session s,v$process p

where s.sid = (select sid from v$mystat where rownum = 1) and p.addr = s.paddr

?

涉及的视图有：v$session v$process v$mystat

?

三.查看回滚段与数据段信息的一些SQL

1.系统实际UNDO表空间占用率可以使用如下语句来计算。

select? ((select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name ='UNDOTBS1'? and status in ('ACTIVE','UNEXPIRED')) *100) / (select sum(bytes)? from dba_data_files? where tablespace_name='UNDOTBS1')? "PCT_INUSE"? from dual;

PCT_INUSE

----------

??? 27.575

将UNEXPIRED，ACTIVE两种状态的回滚段一起算上，实际现场的UNDO占用率为27.575%。

?

2.查看表空间的使用情况

SELECT A.TABLESPACE_NAME,A.BYTES/1024/1024 TOTAL,B.BYTES/1024/1024 USED, C.BYTES/1024/1024 FREE,

?(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

?FROM SYS.S