每天檢查正式Server的一個指令碼

yhj20041128001發表於2011-10-11

Server指令碼

source /home/oracle/.bash_profile

echo "

sqlplus -S '/as sysdba' <

col OWNER format a10;

col DB_LINK format a10;

col USERNAME format a10;

col HOST format a10;

select OWNER,DB_LINK,USERNAME,HOST from dba_db_links;

exit;

echo "

sqlplus -S '/as sysdba' <

col OWNER format a20;

col OBJECT_NAME format a20;

col OBJECT_TYPE format a20;

select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where status='INVALID' and object_type <> 'SYNONYM';

exit;

echo "

sqlplus -S '/as sysdba' <

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

col SCHEMA_USER format a20;

col LAST_DATE format a20;

SELECT JOB,SCHEMA_USER OWNER,LAST_DATE,NEXT_DATE,BROKEN FROM DBA_JOBS;

exit;

echo "

sqlplus -S '/as sysdba' <

SELECT INST_ID,SESSIONS_CURRENT,SESSIONS_HIGHWATER FROM GV\$LICENSE;

exit

echo "

sqlplus -S '/as sysdba' <

col TABLESPACE format a10;

SELECT UPPER(F.TABLESPACE_NAME) "TABLESPACE",

D.TOT_GROOTTE_MB "Total(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "Used(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,0), '990')||'%' "Percentage",

F.TOTAL_BYTES "Free(M)",

F.MAX_BYTES "Max Size(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 4 DESC;

exit

echo "

sqlplus -S '/as sysdba' <

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select thread#,max(first_time),max(sequence#) from v\$archived_log group by thread#;

exit

echo "

sqlplus -S '/as sysdba' <

select round(sum(a.bytes_alloc)/1024/1024/1024,2) "TBS_ALLOCT_TOTAL(G)",

round( (sum(a.bytes_alloc)-sum(nvl(b.bytes_free, 0)))/1024/1024/1024,3) "TBS_USED_TOTAL(G)"

from ( select f.tablespace_name,

sum(f.bytes) bytes_alloc

from dba_data_files f

group by tablespace_name) a,

( select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name (+);

exit

全部腳印不留腳印留下腳印:

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23757700/viewspace-708926/，如需轉載，請註明出處，否則將追究法律責任。

下一篇：恢復update,delete表資料錯誤的語句

請登入後發表評論登入

全部評論

yhj20041128001

註冊時間：2010-09-17

博文量
162
訪問量

353367

每天檢查正式Server的一個指令碼

最新文章

相關文章