版本11.2.0.4
set linesize 170
set pagesize 200
col file_name for a60
此脚本解决tom脚本调用DBA_FREE_SPACE,无法显示已经写满的数据文件的问题。
select
a.file_id "file_id",
a.file_name "file_name",
(a.bytes/1048576) "curr_MB",
nvl(((a.bytes-b.fbytes)/1048576),(a.bytes/1048576)) "used_MB",
nvl2((b.fbytes),b.fbytes/1048576,0) "free_MB",
(a.MAXBYTES/1024/1024) maxsize,
a.AUTOEXTENSIBLE
from dba_data_files a
left join
(select file_id,sum(bytes) fbytes from DBA_FREE_SPACE group by file_id) b
on a.file_id=b.file_id
order by 1;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
左连接执行计划实验
SQL> create table jt as select file_id,sum(bytes) fbytes from DBA_FREE_SPACE group by file_id; //原语句的b表
SQL> select * from jt order by 1;
FILE_ID FBYTES
---------- ----------
1 9502720
2 33292288
3 85786624
4 786432
5 37289984
6 20905984
6 rows selected.
SQL> select file_id,file_name from dba_data_files; //原语句的a表
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
4 +DATA/dbm01/datafile/users.259.956474535
3 +DATA/dbm01/datafile/undotbs1.258.956474535
2 +DATA/dbm01/datafile/sysaux.257.956474533
1 +DATA/dbm01/datafile/system.256.956474533
5 +DATA/dbm01/datafile/example.264.956474661
6 +DATA/dbm01/datafile/undotbs2.265.956474955
6 rows selected.
select /* +moniotr*/
a.file_id "file_id",
a.file_name "file_name",
b.FBYTES
from dba_data_files a
left join jt b on a.file_id=b.file_id
order by 1;
select /*+ noparallel */ dbms_sqltune.report_sql_monitor (sql_id=>'d8dpu3441kvy1', report_level=>'ALL', type=>'ACTIVE',base_path=>'http://www.ly/smon') from dual;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29903445/viewspace-2217986/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29903445/viewspace-2217986/