监视表空间、段和索引.doc

? ? ? ? ? ? ? ? 监视表空间、段和索引 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 监视表空间、段和索引 监视表空间 select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES) UNDOTBS1 8978432 SYSAUXAAA 983040 R 2031616 USERSDATAWWWSYSTEM 8716288 A 4546560 B各表空间的剩余空间 第 31 页 col metrics_name format a30 col warning_value format a10 col critical_value format a10 select metrics_name,warning_operator,warning_value,critical_operator,critical_value,consecutive_occurrences from dba_thresholds where metrics_name=Tablespace Space Usage; METRICS_NAME WARNING_OPER WARNING_VA CRITICAL_OPE CRITICAL_V CONSECUTIVE_OCCURRENCES Tablespace Space Usage GE 85 GE 97 1 这是一个表空间使用的阀值，当空间使用超过 85 时警告，超过 97 时达到危险，每次都产生报警（DBA_OUTSTANDING_ALERTS）。 create tablespace tbs datafile E:OracleFilesDatabaseFilesWWWTBS01.DBF size 1M; create table t (s char(2000)) tablespace tbs; begin for i in 1..600 loop insert into t values(A); end loop; end; / col object_name format a20 col object_type format a10 col reason format a40 col host_nw_addr format a20 col time_suggested format a40 select object_name,object_type,reason,time_suggested,host_nw_addr from dba_outstanding_alerts; OBJECT_NAME OBJECT_TYP REASON TIME_SUGGESTED HOST_NW_ADDR TBS TABLESPACE 表空间 [TBS] 已占用 [93 percent] 02-7 月 -08 003000 下午 +08:00 9 TBS TABLESPACE 表空间 [TBS] 只有 [0 megabytes] 空闲空间 02-7 月 -08 018000 下午 +08:00 9 出现警报，列出了对象名、对象类型、警报理由、最后一次修改时间、ip 地址等信息 当警报解除后，会把此报警记入 DBA_ALERT_HISTORY。 监视段 select segment_name,bytes from dba_segments where rownum = 10 order by bytes desc; SEGMENT_NAME BYTES OBJ$ 5242880 SYN$ 983040 OBJAUTH$ 655360 CON$ 196608 UGROUP$ 65536 BOOTSTRAP$ 65536 FILE$ 65536 PROXY_ROLE_DATA$ 65536 UNDO$ 65536 PROXY_DATA$ 65536 获取空间占用最大的段 create table t as select * from dba_objects; select bytes from dba_segments where segment_name=T; delete from t where rownum = 20000; commit; select bytes from dba_segments where segment_name=T; SQL select bytes from dba_segments w