Oracle DBA Daily Checklist
发布时间：2023-02-17 09:22:34 所属栏目：Oracle 来源：互联网
导读：There are several routine checklist and tasks to do in Oracle database by DBA ( Database Administrator ). This checklist and tasks are as follows. All scripts are valid for Single and RAC Database and Exadata. If you dont use RAC,then you
|
MOUNTED HIGH Y 512 512 4096 4194304 1056964608 214081832 14680064 66467256 2 Y DATA/ MOUNTED HIGH N 512 512 4096 4194304 134507520 101014692 1868160 33048844 0 N RECO/ ASMCMD> 4- Check the Tablespaces for objects to extend if required. Use the following script to check Tablespace usage set pagesize 1000 linesize 180 tti 'Tablespace Usage Status' col "TOTAL(MB)" for 99,999,999.999 col "USAGE(MB)" for 99,999.999 col "FREE(MB)" for 99,999.999 col "EXTENSIBLE(MB)" for 99,999.999 col "FREE PCT %" for 999.99 col "USED PCT OF MAX %" for 999.99 col "NOTO" for 9999 col "OTO" for 999 select d.tablespace_name "NAME",d.contents "TYPE",nvl(a.bytes /1024/1024,0) "TOTAL(MB)",nvl(a.bytes - nvl(f.bytes,0),0)/1024/1024 "USAGE(MB)",nvl(f.bytes,0)/1024/1024 "FREE(MB)",nvl((a.bytes - nvl(f.bytes,0))/a.bytes * 100,0) "FREE PCT %",nvl(a.ARTACAK,0)/1024/1024 "EXTENSIBLE(MB)",0))/ (a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %",a.NOTO,a.OTO from sys.dba_tablespaces d,(select tablespace_name,sum(bytes) bytes,sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK,count(decode(autoextensible,'NO',0)) NOTO,0)) OTO from dba_data_files group by tablespace_name) a,sum(bytes) bytes from dba_free_space group by tablespace_name) f where d.tablespace_name = a.tablespace_name(+) and d.tablespace_name = f.tablespace_name(+) and NOT (d.extent_management like 'LOCAL'and d.contents like 'TEMPORARY') UNION ALL select d.tablespace_name "NAME",nvl(t.bytes,nvl(a.bytes - nvl(t.bytes,nvl(t.bytes/a.bytes * 100,nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,0)) OTO from dba_temp_files group by tablespace_name) a,sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t where d.tablespace_name = a.tablespace_name(+) and d.tablespace_name = t.tablespace_name(+) and d.extent_management like 'LOCAL' and d.contents like 'TEMPORARY%' order by 3 desc; exit; Result of script is as follows. sql> set pagesize 1000 linesize 180 sql> tti 'Tablespace Usage Status' sql> col "TOTAL(MB)" for 99,999.999 sql> col "USAGE(MB)" for 99,999.999 sql> col "FREE(MB)" for 99,999.999 sql> col "EXTENSIBLE(MB)" for 99,999.999 sql> col "FREE PCT %" for 999.99 sql> col "USED PCT OF MAX %" for 999.99 sql> col "NOTO" for 9999 sql> col "OTO" for 999 sql> select d.tablespace_name "NAME",2 d.contents "TYPE",3 nvl(a.bytes /1024/1024,4 nvl(a.bytes - nvl(f.bytes,5 nvl(f.bytes,6 nvl((a.bytes - nvl(f.bytes,7 nvl(a.ARTACAK,8 nvl((a.bytes - nvl(f.bytes,9 a.NOTO,a.OTO 10 from sys.dba_tablespaces d,11 (select tablespace_name,12 sum(decode(autoextensible,13 count(decode(autoextensible,14 count(decode(autoextensible,0)) OTO 15 from dba_data_files 16 group by tablespace_name) a,17 (select tablespace_name,sum(bytes) bytes 18 from dba_free_space 19 group by tablespace_name) f 20 where d.tablespace_name = a.tablespace_name(+) 21 and d.tablespace_name = f.tablespace_name(+) 22 and NOT (d.extent_management like 'LOCAL'and d.contents like 'TEMPORARY') 23 UNION ALL 24 select d.tablespace_name "NAME",25 d.contents "TYPE",26 nvl(a.bytes /1024/1024,27 nvl(t.bytes,28 nvl(a.bytes - nvl(t.bytes,29 nvl(t.bytes/a.bytes * 100,30 nvl(a.ARTACAK,31 nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,a.OTO 32 from sys.dba_tablespaces d,33 (select tablespace_name,34 sum(decode(autoextensible,35 count(decode(autoextensible,36 count(decode(autoextensible,0)) OTO 37 from dba_temp_files 38 group by tablespace_name) a,39 (select tablespace_name,sum(bytes_used) bytes 40 from v$temp_extent_pool 41 group by tablespace_name) t 42 where d.tablespace_name = a.tablespace_name(+) 43 and d.tablespace_name = t.tablespace_name(+) 44 and d.extent_management like 'LOCAL' 45 and d.contents like 'TEMPORARY%' 46 order by 3 desc; exit; Tue Mar 10 page 1 Tablespace Usage Status NAME TYPE TOTAL(MB) USAGE(MB) FREE(MB) FREE PCT % EXTENSIBLE(MB) USED PCT OF MAX % NOTO OTO ------------------------------ --------------------- --------------- --------------- --------------- ---------- --------------- ----------------- ----- ---- USERS PERMANENT 625,384.391 621,953.953 3,430.438 99.45 83,223.281 87.77 1 21 MSD_TBS PERMANENT 550,324.313 277,077.188 273,247.125 50.35 33,004,107.664 .83 0 1 DEFAULT_TBS1 PERMANENT 410,624.000 4,623.438 406,000.563 1.13 31,046,656.000 .01 0 1 （编辑：甘南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
