Quick overview of the state of an Oracle database

最新推荐文章于 2019-07-09 09:37:52 发布

zhoul77777

最新推荐文章于 2019-07-09 09:37:52 发布

阅读量102

点赞数

分类专栏： ORACLE管理文章标签： Oracle F# 脚本

本文链接：https://blog.csdn.net/zhoul77777/article/details/83611305

版权

ORACLE管理专栏收录该内容

275 篇文章 1 订阅

订阅专栏

有时候我们需要快速浏览数据库的大致情况，如表空间大小，数据库参数，表格等。以下脚本引自www.orafaq.com，个人觉得还不错。摘录如下
[quote]SET LINESIZE 1000
SET PAGESIZE 1000
spool report.txt
prompt You can leave this script copied at $ORACLE_HOME\rdbms\admin\
prompt - Remember to use also statspack o bstats depending on the Oracle version
prompt - Review alert.log
prompt
prompt
prompt TABLESPACE STATUS
Select t.tablespace_name "Tablespace", t.status "Estado",
ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE 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, t.pct_increase, t.status ORDER BY 1,3 DESC;
prompt
prompt
prompt INSTALLED PRODUCTS AND ITS VERSION
select * from product_component_version;
prompt
prompt
prompt ORACLE PARAMETERS DIFFERENT TO DEFAULT
select name, value from v$parameter where isdefault = 'FALSE';
prompt
prompt
prompt STATUS OF TABLES
select OWNER,TABLE_NAME,PCT_FREE,PCT_USED,AVG_SPACE,PARTITIONED,LAST_ANALYZED from DBA_TABLES ORDER BY OWNER,AVG_SPACE;
spool off;
quit
[/quote]