Oracle数据库管理员职责（三）

核心提示： 2. analyze5pct.sql-- analyze5pct.sql -- To analyze tables and indexes quickly, using a 5% sample size -- (do not use this script. if you are perf

　2. analyze5pct.sql

-- analyze5pct.sql

-- To analyze tables and indexes quickly, using a 5% sample size

-- (do not use this script. if you are performing the overnight collection of volumetric data)

-- 11/30/98　　

　　　BEGIN
　　DBMS_UTILITY.ANALYZE_SCHEMA('&OWNER','ESTIMATE',NULL,5);
　　END;
　　/

3. nr_extents.sql

-- nr_extents.sql

-- To find out any object reaching

-- extents, and manually upgrade it to allow unlimited

-- max_extents (thus only objects we *expect* to be big

-- are allowed to become big)

-- 11/30/98

　　　SELECTe.owner,e.segment_type,e.segment_name,count(*)asnr_extents,s.max_extents
　　,to_char(sum(e.bytes)/(1024*1024),'999,999.90')asMB
　　FROMdba_extentse,dba_segmentss
　　WHEREe.segment_name=s.segment_name
　　GROUPBYe.owner,e.segment_type,e.segment_name,s.max_extents
　　HAVINGcount(*)>&THRESHOLD
　　OR((s.max_extents-count(*))<&&THRESHOLD)
　　ORDERBYcount(*)desc;

4. spacebound.sql

spacebound.sql

To identify space-bound objects. If all is well, no rows are returned.

f any space-bound objects are found, look at value of NEXT extent

size to figure out what happened.

Then use coalesce (alter tablespace coalesce .

Lastly, add another datafile to the tablespace if needed.

11/30/98

　　　SELECTa.table_name,a.next_extent,a.tablespace_name
　　FROMall_tablesa,
　　(SELECTtablespace_name,max(bytes)asbig_chunk
　　FROMdba_free_space
　　GROUPBYtablespace_name)f
　　WHEREf.tablespace_name=a.tablespace_name
　　ANDa.next_extent>f.big_chunk;

B.每晚处理程序

1. mk_volfact.sql

mk_volfact.sql (only run this once to set it up; do not run it nightly!)

Table UTL_VOL_FACTS

　　　CREATETABLEutl_vol_facts(
　　table_nameVARCHAR2(30),
　　num_rowsNUMBER,
　　meas_dtDATE)
　　TABLESPACEplatab
　　STORAGE(
　　INITIAL128k
　　NEXT128k
　　PCTINCREASE0
　　MINEXTENTS1
　　MAXEXTENTSunlimited
　　)
　　/
　　--PublicSynonym
　　CREATEPUBLICSYNONYMutl_vol_factsFOR&OWNER..utl_vol_facts
　　/

Grants for UTL_VOL_FACTS　

　　　GRANTSELECTONutl_vol_factsTOpublic