Oracle数据库管理员职责(四)

zzj11119999999

zzj11119999999

2016-02-19 18:46

下面图老师小编要向大家介绍下Oracle数据库管理员职责(四),看起来复杂实则是简单的,掌握好技巧就OK,喜欢就赶紧收藏起来吧!

  2. analyze_comp.sql

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)

  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 

  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 

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)

   SELECTsegment_name,segment_type
  ,count(*)asnr_exts
  ,sum(DECODE(dx.bytes,dt.next_extent,0,1))asnr_illsized_exts
  ,dt.tablespace_name,dt.next_extentasdflt_ext_size
  FROMdba_tablespacesdt,dba_extentsdx
  WHEREdt.tablespace_name=dx.tablespace_name
  ANDdx.owner='&OWNER'
  GROUPBYsegment_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  

   SELECTtable_name
  FROMall_tables
  WHEREwner='&OWNER'
  MINUS
  SELECTtable_name
  FROMall_constraints
  WHEREwner='&&OWNER'
  ANDconstraint_type='P';

  4. disPK.sql

  disPK.sql

  To find out which primary keys are disabled

  11/30/98 

   SELECTowner,constraint_name,table_name,status
  FROMall_constraints
  WHEREwner='&OWNER'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

展开更多 50%)
分享

猜你喜欢

Oracle数据库管理员职责(四)

编程语言 网络编程
Oracle数据库管理员职责(四)

Oracle数据库管理员职责(三)

编程语言 网络编程
Oracle数据库管理员职责(三)

s8lol主宰符文怎么配

英雄联盟 网络游戏
s8lol主宰符文怎么配

Oracle数据库管理员职责(二)

编程语言 网络编程
Oracle数据库管理员职责(二)

Oracle数据库管理员职责(一)

编程语言 网络编程
Oracle数据库管理员职责(一)

lol偷钱流符文搭配推荐

英雄联盟 网络游戏
lol偷钱流符文搭配推荐

Oracle数据库管理员的利器EXP、IMP使用简介

编程语言 网络编程
Oracle数据库管理员的利器EXP、IMP使用简介

MySQL将为数据库管理员减负

编程语言 网络编程
MySQL将为数据库管理员减负

lolAD刺客新符文搭配推荐

英雄联盟
lolAD刺客新符文搭配推荐

AJAX是否能够取代桌面应用程序

AJAX是否能够取代桌面应用程序

Windows系统崩溃后恢复Oracle 9i数据库

Windows系统崩溃后恢复Oracle 9i数据库
下拉加载更多内容 ↓