熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle數據庫管理員職責(四)

2013-11-13 15:55:09  來源: Oracle 

   analyze_compsql


  analyze_compsql

         BEGIN
  SYSDBMS_UTILITYANALYZE_SCHEMA ( &OWNERCOMPUTE);
  END;
  /

   pop_volsql


  pop_volsql 

       insert into utl_vol_facts
  select table_name
   NVL ( num_rows ) as num_rows
   trunc ( last_analyzed ) as meas_dt
  from all_tables


    or just user_tables 

         where owner in (&OWNER)


  or a commaseparated list of owners  

         /
  commit
  /

  C每周處理程序

   nextextsql


  nextextsql
  To find tables that dont 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 tablespaces
  default value for NEXT
  his tells us what the setting for NEXT is for these objects today
    //  

         SELECT segment_name segment_type dsnext_extent as Actual_Next
   dttablespace_name dtnext_extent as Default_Next
  FROM dba_tablespaces dt dba_segments ds
  WHERE dttablespace_name = dstablespace_name
  AND dtnext_extent !=dsnext_extent
  AND dsowner = UPPER ( &OWNER )
  ORDER BY tablespace_name segment_type segment_name;

   existextsql 


  existextsql
  To check existing extents
  This tells us how many of each objects extents differ in size from
   the tablespaces 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
  // 

         SELECT segment_name segment_type
   count(*) as nr_exts
   sum ( DECODE ( dxbytesdtnext_extent) ) as nr_illsized_exts
   dttablespace_name dtnext_extent as dflt_ext_size
  FROM dba_tablespaces dt dba_extents dx
  WHERE dttablespace_name = dxtablespace_name
  AND dxowner = &OWNER
  GROUP BY segment_name segment_type dttablespace_name dtnext_extent;

   No_pksql


  no_pksql
  To find tables without PK constraint
  //  

         SELECT table_name
  FROM all_tables
  WHERE wner = &OWNER
  MINUS
  SELECT table_name
  FROM all_constraints
  WHERE wner = &&OWNER
  AND constraint_type = P ;

   disPKsql


  disPKsql
  To find out which primary keys are disabled
    // 

         SELECT owner constraint_name table_name status
  FROM all_constraints
  WHERE wner = &OWNER AND status = DISABLED AND constraint_type = P;

   nonuPKsql


  nonuPKsql
  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    

    //  

         SELECT index_name table_name uniqueness
  FROM all_indexes
  WHERE index_name like &PKNAME%
  AND wner = &OWNER AND uniqueness = NONUNIQUE
  SELECT nstraint_name itablespace_name iuniqueness
  FROM all_constraints c  all_indexes i
  WHERE cowner = UPPER ( &OWNER ) AND iuniqueness = NONUNIQUE
  AND nstraint_type = P AND iindex_name = nstraint_name

   mkrebuild_idxsql


  mkrebuild_idxsql
      Rebuild indexes to have correct storage parameters
  // 

         SELECT alter index  || index_name ||  rebuild 
   tablespace INDEXES storage 
  ||  ( initial  K next  K pctincrease  ) ; 
  FROM all_indexes
  WHERE ( tablespace_name != INDEXES
  OR next_extent != (  *  )
  )
  AND wner = &OWNER
  /

   datatypesql


  datatypesql
  To check datatype consistency between two environments
  // 

         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 = &OWNER
  order by table_name column_name

   obj_coordsql


  obj_coordsql
  To find out any difference in objects between two instances
  //        

         SELECT object_name object_type
  FROM user_objects
  MINUS
  SELECT object_name object_type
  FROM user_objects@&my_db_link


From:http://tw.wingwit.com/Article/program/Oracle/201311/17515.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.