-- 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

2. analyze_comp.sql

　　analyze_comp.sql

　　　BEGIN
　　SYS.DBMS_UTILITY.ANALYZE_SCHEMA('&OWNER','COMPUTE');
　　END;
　　/

　　3. pop_vol.sql

　　pop_vol.sql　

　　insertintoutl_vol_facts
　　selecttable_name
　　,NVL(num_rows,0)asnum_rows
　　,trunc(last_analyzed)asmeas_dt
　　fromall_tables

　　or just user_tables　

　　　whereownerin('&OWNER')

　　or a comma-separated list of owners　　

　　　/
　　commit
　　/

　　C.每周处理程序

　　1. nextext.sql

　　nextext.sql

　　To find tables that don't match the tablespace default for NEXT extent.

　　The implicit rule here is that every table in a given tablespace should

　　use the exact same value for NEXT, which should also be the tablespace's

　　default value for NEXT.

　　his tells us what the setting for NEXT is for these objects today.

　　11/30/98　　

　　　SELECTsegment_name,segment_type,ds.next_extentasActual_Next
　　,dt.tablespace_name,dt.next_extentasDefault_Next
　　FROMdba_tablespacesdt,dba_segmentsds
　　WHEREdt.tablespace_name=ds.tablespace_name
　　ANDdt.next_extent!=ds.next_extent
　　ANDds.owner=UPPER('&OWNER')
　　ORDERBYtablespace_name,segment_type,segment_name;

　　2. existext.sql　

which primary keys are disabled

　　11/30/98　

　　　SELECTowner,constraint_name,table_name,status
　　FROMall_constraints
　　WHEREwner='&OWNERwhich primary keys are disabled

　　11/30/98　

　　　SELECTowner,constraint_name,table_name,status
　　FROMall_constraints
　　WHEREwner='&OWNER'ANDstatus='DISABLED’ANDconstraint_type='P';

'ANDstatus='DISABLED’ANDconstraint_type='P';

　5. nonuPK.sql

　　nonuPK.sql

　　To find tables with nonunique PK indexes. Requires that PK names

　　follow a naming convention. An alternative query follows that

　　does not have this requirement, but runs more slowly.　　　　　　

　　11/2/98　　

　　　SELECTindex_name,table_name,uniqueness
　　FROMall_indexes
　　WHEREindex_namelike'&PKNAME%'
　　ANDwner='&OWNER'ANDuniqueness='NONUNIQUE'
　　SELECTc.constraint_name,i.tablespace_name,i.uniqueness
　　FROMall_constraintsc,all_indexesi
　　WHEREc.owner=UPPER('&OWNER')ANDi.uniqueness='NONUNIQUE'
　　ANDc.constraint_type='P'ANDi.index_name=c.constraint_name

　　6. mkrebuild_idx.sql

　　mkrebuild_idx.sql

　　Rebuild indexes to have correct storage parameters

　　11/2/98　

　　　SELECT'alterindex'||index_name||'rebuild'
　　,'tablespaceINDEXESstorage'
　　||'(initial256Knext256Kpctincrease0);'
　　FROMall_indexes
　　WHERE(tablespace_name!='INDEXES'
　　ORnext_extent!=(256*1024)
　　)
　　ANDwner='&OWNER'
　　/

　　7. datatype.sql

　　datatype.sql

　　To check datatype consistency between two environments

　　11/30/98　

　　　SELECTtable_name,column_name,data_type,data_length,data_precision,data_scale,nullable
　　FROMall_tab_columns--firstenvironment
　　WHEREwner='&OWNER'
　　MINUS
　　SELECTtable_name,column_name,data_type,data_length,data_precision,data_scale,nullable
　　FROMall_tab_columns@&my_db_link--secondenvironment
　　WHEREwner='&OWNER2'
　　orderbytable_name,column_name

　　8. obj_coord.sql

　　obj_coord.sql

　　To find out any difference in objects between two instances

　　12/08/98　　　　　

　　　SELECTobject_name,object_type
　　FROMuser_objects
　　MINUS
　　SELECTobject_name,object_type
　　FROMuser_objects@&my_db_link