ORACLE DBA常用脚本.ppt

Welcome DBA常用脚本ORACLESUPPORTSERVICE汪珏 Agenda DBA常用脚本 完整性约束检查索引检查数据库锁资源监控数据库对象建立性能监控 权限管理空间管理其他 前言 DBA接手的数据库开发人员移交的数据库文档不完整遗留与生产无关数据前任DBA移交的数据库文档不完善首要任务 建立对数据库的完整认识 完整性约束检查 外键 foreignkey 列表主外键约束对性能的潜在影响 完整性约束检查 Disable完整性约束建立disable脚本select altertable fromdba constraintswhereowner upper owner andtable name upper tab Enable约束类似 完整性约束检查 建立全面的约束依赖关系表对于新DBA非常重要数据库必备档案 完整性约束检查 检查主外键表锁问题脚本结果举例 ChangingdataintableITEMSwilllocktableITEM CATEGORIESCreateanindexonthefollowingcolumnstoremovelockproblemColumn ITEM CAT 1 Column ITEM BUS UNIT 2 ChangingdataintableITEMSwilllocktableITEM CATEGORIESCreateanindexonthefollowingcolumnstoremovelockproblemColumn ITEM CAT 1 Column ITEM BUS UNIT 2 完整性约束检查 另一个显示主外键关联关系的脚本产生下列结果Parent Child ConstraintName DeleteRule JACK DEPTRfdByJACK EMP EMP FOREIGN KEY OnDeleteCASCADE ENABLED JACK DEPTRfdByJACK EMP2 EMP2 FOREIGN KEY DeleteRESTRICT ENABLED 完整性约束检查 产生完全的约束列表的脚本结果在当前目录中产生SQL脚本Altertableaddconstraint 在重整数据库时非常有用 索引检查 描述某个用户名下的索引信息产生如下报告格式 INDEX NAMETABLE NAMEUNIQUENESCOLUMN NAME ICAT PKITEM CATEGORIESUNIQUECAT CATUNIQUEICAT BUS UNITITEM INDEXITEMNONUNIQUEORDIDITEM PKITEMSUNIQUEITEM ITEMUNIQUEITEM ITEM REVUNIQUEITEM BUS UNITI TA DEPTTANONUNIQUEDEPT 索引检查 检查索引碎片情况结果例如 IndexFragmentationStatisticindexnameS EMP USERID UKleafrowsdeleted0leafrowsinuse25indexbadness0 000 索引检查 报告索引统计信息报告例如 IndexStatisticsS EMP USERID UK height1blocks5del lf rows0del lf rows len0distinct keys25most repeated key1btree space1 876used space447pct used24rows per key1blks gets per access2lf rows25br rows0lf blks1br blks0lf rows len447br rows len0lf blk len1 876br blk len0 数据库锁资源监控 查看锁资源等待情况DBA经常用到脚本产生如下结果 USERNAMESIDTYPEHELDREQID1ID2 SYSTEM12TXExclusiveNone1310872328SCOTT7TXNoneExclusive1310872328SCOTT8TXExclusiveNone1310992332SYSTEM10TXNoneExclusive1310992332SYSTEM12TXNoneExclusive1310992332 数据库对象建立 建立数据库所有trigger的脚本结果生成一个脚本 包含一系列 createtrigger 语句 数据库对象建立 建立全部DBLINK的脚本结果产生一个脚本 该脚本建立数据库中全部的DBLINKconnectSCOTT CREATEDATABASELINKahmadCONNECTTOscottIDENTIFIEDBYtigerUSING t otcsun2 V714 CREATEDATABASELINKcrCONNECTTOsurman usIDENTIFIEDBYsc0ttuUSING crprd CREATEDATABASELINKmylinkCONNECTTOscottIDENTIFIEDBYtigerUSING V7223 CREATEDATABASELINKto otcosf v716CONNECTTOscottIDENTIFIEDBYtigerUSING OTCOSF V716 数据库对象建立 建立所有索引结果产生一个脚本 包含建立索引的SQLCREATEUNIQUEINDEXscott s dept name ukONscott s dept name region id PCTFREE10INITRANS2MAXTRANS255TABLESPACESYSTEMSTORAGE INITIAL10240NEXT10240MINEXTENTS1MAXEXTENTS121PCTINCREASE50 CREATEUNIQUEINDEXscott s emp id pkONscott s emp id PCTFREE10INITRANS2MAXTRANS255TABLESPACESYSTEMSTORAGE INITIAL10240NEXT10240MINEXTENTS1MAXEXTENTS121PCTINCREASE50 数据库对象建立 建立所有用户的数据库对象权限结果产生用户授权的SQL脚本结果已经考虑授权依赖关系 用户无需更改用户需要提供数据库schema的口令来完成生成的SQL脚本 数据库对象建立 建立所有数据库表空间结果产生脚本 建立所有表空间例如 CREATETABLESPACErbsDATAFILE u02 oracle V7 1 6 dbs rbs2V716 dbf SIZE52428800REUSE u02 oracle V7 1 6 dbs rbsV716 dbf SIZE8388608REUSEDEFAULTSTORAGE INITIAL131072NEXT131072MINEXTENTS2MAXEXTENTS121PCTINCREASE0 ONLINE 性能监控 检查表存储的有效性报告某个表在物理文件存储行数的分布结果例如 SCOTTEMPRowsperDatafileDatafileID Hexadecimal RowCount 000118 性能监控 查看buffercache中数据块的分布需要sys用户权限产生如下报告ClassNotDirtyDirtyOnDirtyTotal 71001Data219459802792Header350035Rollback37020372 性能监控 执行某个用户名下所有表的分析计算自定义某值以下大小表的统计值估计大于上述值的表的统计值产生统计报告 性能监控 显示某个表的统计值产生如下列表TableNumberEmptyAverageChainAverageNameofRowsBlocksBlocksSpaceCountRowLen NATION16990687066ColumnColumnDistinctNameDetailsValuesDensity CODENUMBER 5 0 NOTNULL1690NATIONCHAR 28 NOTNULL1690CAPITALCHAR 20 1690AREANUMBER 22 0 1680POPULATIONNUMBER 22 0 1670 性能监控 格式化显示plan table的内容在8i中使用 ORACLE HOME rdbms admin utlxpls sql在8 0或更早版本使用本脚本 产生如下内容QueryPlan SELECTSTATEMENTCost 1TABLEACCESSFULLS EMP 权限管理 显示某用户相关的对象权限产生如下报告Enterusertoevaluate sysTableprivilegesGIVEN SELECTONSYS ACCESSIBLE TABLESTOPUBLIC GRANTOPTSELECTONSYS ALL ARGUMENTSTOPUBLIC GRANTOPTSELECTONSYS ALL COL COMMENTSTOPUBLIC GRANTOPTSELECTONSYS ALL COL GRANTS MADTOPUBLICTableprivilegesRECEIVED SELECTONSYSTEM DEF CALLFROMSYSTEM GRANTOPTSELECTONSYSTEM DEF CALLDESTFROMSYSTEM GRANTOPTSELECTONSYSTEM REPCAT REPSCHEFROMSYSTEM GRANTOPTColumnprivilegesGIVEN ColumnprivilegesRECEIVED 权限管理 查询某个用户的系统权限产生如下列表SYSTEMPRIVILEGESMARTYCONNECTALTERSESSIONCREATECLUSTERCREATEDATABASELINKCREATESEQUENCECREATESESSIONCREATESYNONYMCREATETABLECREATEVIEWMARTYDBAEXP FULL DATABASERoleof2privsDBAIMP FULL DATABASERoleof35privsDBADBA role 80privs 空间管理 查看某个用户名下表的平均行长度对于包含long longraw以及类似字段不适用结果产生SQL脚本 用以查看平均行长度 其他 查看表之间的依赖关系输入参数接受用户名 和通配符输入参数接受对象名 和通配符 其他 清理系统中无效的同义词产生SQL脚本 用来完成清除工作 其他 将数据导出到文本文件产生SQL脚本 执行该脚本导出数据文本文件中数据定长同时产生sql loader的控制文件作为数据导入导出的另一种方法 其他 重新编译所有无效存储过程在8i版本 执行 ORACLE HOME rdbms admin utlrp sql在8 0或更早版本 使用本脚本脚本已注意过程之间的调用关系 执行一次即可 其他 重新建立所有用户定义产生脚本 该脚本建立数据库所有用户 SYS SYSTEM除外 本方法只适用8i或更高版本 其他 查看MTS环境中session dispatcher process之间的对应关系 其他 另一个获得所有数据库用户定义的脚本结果产生建立用户的脚本例如CREATEUSERscottIDENTIFIEDBYVALUES F894844C34402B67 DEFAULTTABLESPACETOOLSTEMPORARYTABLESPACETEMPQUOTA 1ONTOOLSQUOTA 1ONUSERSPROFILEDEFAULT 其他 快速查看数据库系统性能指标用于快速获得对数据库性能指标的简单认识包括 SGA信息 buffercache sharedpool logbuffer Latch竞争情况表空间使用情况磁盘I O情况回滚段使用情况 其他 获取数据库全貌包括表空间 数据文件 用户 表 索引 同义词 数据库连接 视图等适用于获得数据库原始全貌数据 其他 删除大量数据时分段提交结果是建立一个存储过程用户自定义处理多少行自动提交一次适用于带条件的delete大量数据极大减轻回滚段压力使用实例 executedelete commit deletemytablewheremycol AAAA 500 每500行提交一次 其他 查看用户名下的表 约束 索引只查看用户的上述信息 其他 查看用户名下所有sequence产生如下报告SequenceValueSummaryOwnerSequenceNameIncrBeginValueLastValue SCOTTCHESS SAVEID111CUSTID11109MAHA SEQ1121MY OWN SEQ111ORDID11622PRODID11200 381 其他 详细描述一个表的信息SatJul27page1TableDescription SpaceDefinitionDate Saturday27thJuly199913 31 47At TFTS TESTUsername SCOTTTableNameS EMPTablespaceNameSYSTEMClusterName Free10 Used40IniTrans1MaxTrans255InitialExtent K 10NextExtent K 10MinExtents1MaxExtents121 Increase50NumberofRows25NumberofBlocks1NumberofEmptyBlocks3A