amp;THRESHOLD OR ( ( s.max_extents - count(*) ) < &&THRESHOLD ) ORDER BY count(*) desc 4. spacebound.sql -- spacebound.sql -- To identify space-bound objects. If all is well, no rows are returned. -- If any space-bound objects are found, look at value of NEXT extent -- size to figure out what happened. -- Then use coalesce (alter tablespace <foo> coalesce. -- Lastly, add another datafile to the tablespace if needed. -- 11/30/98 SELECT a.table_name, a.next_extent, a.tablespace_name FROM all_tables a, ( SELECT tablespace_name, max(bytes) as big_chunk FROM dba_free_space GROUP BY tablespace_name ) f WHERE f.tablespace_name = a.tablespace_name AND a.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 CREATE TABLE utl_vol_facts ( table_name VARCHAR2(30), num_rows NUMBER, meas_dt DATE ) TABLESPACE platab STORAGE ( INITIAL 128k NEXT 128k PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS unlimited ) / -- Public Synonym CREATE PUBLIC SYNONYM utl_vol_facts FOR &OWNER..utl_vol_facts / -- Grants for UTL_VOL_FACTS GRANT SELECT ON utl_vol_facts TO public / 2. analyze_comp.sql -- -- analyze_comp.sql -- BEGIN sys.dbms_utility.analyze_schema ( ''''&OWNER'''',''''COMPUTE''''); END ; / 3. pop_vol.sql -- -- pop_vol.sql -- insert into utl_vol_facts select table_name , NVL ( num_rows, 0) as num_rows , trunc ( last_analyzed ) as meas_dt from all_tables -- or just user_tables where owner in (''''&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. -- -- This tells us what the setting for NEXT is for these objects today. -- -- 11/30/98 SELECT segment_name, segment_type, ds.next_extent as Actual_Next , dt.tablespace_name, dt.next_extent as Default_Next FROM dba_tablespaces dt, dba_segments ds WHERE dt.tablespace_name = ds.tablespace_name AND dt.next_extent !=ds.next_extent AND ds.owner = UPPER ( ''''&OWNER'''' ) ORDER BY tablespace_name, segment_type, segment_name 2. existext.sql -- -- existext.sql -- -- To check existing extents -- -- This tells us how many of each object''''s extents differ in size from -- the tablespace''''s default size. If this report shows a lot of different -- sized extents, your free space is likely to become fragmented. If so, -- this tablespace is a candidate for reorganizing. -- -- 12/15/98 SELECT segment_name, segment_type , count(*) as nr_exts , sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts , dt.tablespace_name, dt.next_extent as dflt_ext_size FROM dba_tablespaces dt, dba_extents dx WHERE dt.tablespace_name = dx.tablespace_name AND dx.owner = ''''&OWNER'''' GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent 3. No_pk.sql -- -- no_pk.sql -- -- To find tables without PK constraint -- -- 11/2/98 SELECT table_name FROM all_tables WHERE owner = ''''&OWNER'''' MINUS SELECT table_name FROM all_constraints WHERE owner = ''''&&OWNER'''' AND constraint_type = ''''P'''' 4. disPK.sql -- -- disPK.sql -- -- To find out which primary keys are disabled -- -- 11/30/98 SELECT owner, constraint_name, table_name, status FROM all_constraints WHERE owner = ''''&OWNER'''' AND status = ''''DISABLED’ AND constraint_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 SELECT index_name, table_name, uniqueness FROM all_indexes WHERE index_name like ''''&PKNAME%'''' AND owner = ''''&OWNER'''' AND uniqueness = ''''NONUNIQUE'''' SELECT c.constraint_name, i.tablespace_name, i.uniqueness FROM all_constraints c , all_indexes i WHERE c.owner = UPPER ( ''''&OWNER'''' ) AND i.uniqueness = ''''NONUNIQUE'''' AND c.constraint_type = ''''P'''' AND i.index_name = c.constraint_name 6. mkrebuild_idx.sql -- -- mkrebuild_idx.sql -- -- Rebuild indexes to have correct storage parameters -- -- 11/2/98 SELECT ''''alter index '''' || index_name || '''' rebuild '''' , ''''tablespace INDEXES storage '''' || '''' ( initial 256 K next 256 K pctincrease 0 ) ; '''' FROM all_indexes WHERE ( tablespace_name != ''''INDEXES'''' OR next_extent != ( 256 * 1024 ) ) AND owner = ''''&OWNER'''' / 7. datatype.sql -- -- datatype.sql -- -- To check datatype consistency between two environments -- -- 11/30/98 SELECT table_name, column_name, data_type, data_length, data_precision, data_scale, nullable FROM all_tab_columns -- first environment WHERE owner = ''''&OWNER'''' MINUS SELECT table_name, column_name, data_type, data_length, data_precision, data_scale, nullable FROM all_tab_columns@&my_db_link -- second environment WHERE owner = ''''&OWNER2'''' order by table_name, column_name 8. obj_coord.sql -- -- obj_coord.sql -- -- To find out any difference in objects between two instances -- -- 12/08/98 SELECT object_name, object_type FROM user_objects MINUS SELECT object_name, object_type FROM user_objects@&my_db_link 六. 参考文献 1. Loney, Kevin Oracle8 DBA Handbook 2. Cook, David Database Management from Crisis to Confidence [http://www.orapub.com/] 3. Cox, Thomas B. The Database Administration Maturity Model
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
