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 wner = '&OWNER' 

 MINUS 

 SELECT table_name 

 FROM all_constraints 

 WHERE wner = '&&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 wner = '&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 wner = '&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 wner = '&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 wner = '&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 wner = '&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