oracle查看某个表空间数据增长量,查看oracle中哪些SQL语句在执行和给定表空间数据量增长...

--查看oracle中哪些SQL语句在执行

sELECT s.username,

s.sid,

pr.PID,

s.OSUSER,

s.MACHINE,

s.PROGRAM,

rs.segment_id,

r.usn,

rs.segment_name,

r.rssize/1024/1024,

sq.sql_text

FROM v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs ,v$sqltext  sq,v$process pr

WHERE s.saddr = t.ses_addr

AND t.xidusn = r.usn

AND rs.segment_id = t.xidusn

AND s.sql_address=sq.address

AND s.sql_hash_value = sq.hash_value

AND s.PADDR=pr.ADDR

ORDER BY t.used_ublk DESC ,sq.PIECE;

--表空间增长

SELECT   a.tablespace_name,          ROUND (a.total_size) "total_size(MB)",          ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",          ROUND (b.free_size, 3) "free_size(MB)",          ROUND (b.free_size / total_size * 100, 2) || '%' free_rate,          ROUND ( (a.total_size - b.free_size) / total_size * 100, 2) || '%' used_rate,          sysdate            FROM   (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 total_size               FROM   dba_data_files           GROUP BY   tablespace_name) a,          (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 free_size               FROM   dba_free_space           GROUP BY   tablespace_name) b  WHERE   a.tablespace_name = b.tablespace_name(+)  and (a.tablespace_name = 'UNDOTBS1' or a.tablespace_name = 'NETSPLATDATA' )