2. analyze_comp.sql 

  -- 

  -- analyze_comp.sql 

  -- 

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

  / 

  -- 

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

  /

每周处理程序

       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; 

  -- 

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

  -- 

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

  -- 

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

  -- 

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

  -- 

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