rcle实用技巧.docx
《rcle实用技巧.docx》由会员分享，可在线阅读，更多相关《rcle实用技巧.docx（8页珍藏版）》请在冰豆网上搜索。
rcle实用技巧
Oracle实用技巧
1.找出无用索引:
DML性能低下，其中最严重的原因之一是无用索引的存在。
所有SQL的插入，更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。
许多Oracle管理人员只要看见在一个SQL查询的WHERE语句出现了一列的话就会为它分配索引。
虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle索引使得数据库管理人员有可能在数据表的行上过度分配索引。
过度分配索引会严重影响关键Oracle数据表的性能。
在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。
Oracle9i有一个工具能够让你使用ALTERINDEX命令监视索引的使用。
然后你可以查找这些没有使用的索引并从数据库里删除它们。
下面是一段脚本，它能够打开一个系统中所有索引的监视功能:
spoolrun_monitor.sql
select'alterindex'||owner||'.'||index_name||'monitoringusage;'
fromdba_indexes
whereownernotin（'SYS','SYSTEM'）;
spooloff;
@run_monitor
你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。
selectindex_name,table_name,mon,used
fromv$object_usage;
在下面，我们可以看见V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。
它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。
SQL>select*fromv$object_usagewhererownum<10;
INDEX_NAMETABLE_NAMEMONITORINGUSEDSTART_MONITORINGEND_MONITORING
----------------------------------------------------------------------------------------------------------------
ASDDIM_ACCT_ITEM_TYPE_TEMPYESNO01/15/200413:
50:
59
IDX_ACCOUNT_ACCESSORY_TARIFF1ACCOUNT_ACCESSORY_TARIFFYESNO01/15/200413:
50:
59
IDX_ACCOUNT_QUOTA_LOG1ACCOUNT_QUOTA_LOGYESNO01/15/200413:
50:
59
IDX_ACCOUNT_SYSTEM_PARAMETERS1ACCOUNT_SYSTEM_PARAMETERSYESNO01/15/200413:
50:
59
IDX_ACCT2ACCTYESNO01/15/200413:
50:
59
IDX_ACCT3ACCTYESNO01/15/200413:
51:
00
IDX_ACCT4ACCTYESNO01/15/200413:
51:
00
IDX_ACCT_BIND_DISCT1ACCT_BIND_DISCTYESNO01/15/200413:
51:
00
IDX_ACCT_BIND_DISCT2ACCT_BIND_DISCTYESNO01/15/200413:
51:
00
2.查看一个很长的操作已经做了多少:
v$session_longops视图可以使Oracle专家减少运行时间很长的DDL和DML语句的运行时间。
例如在数据仓库环境中，即使使用并行索引创建技术，构建一个很多G字节大的索引需要耗费很多个小时。
这里你就可以查询v$session_longops视图快速找出一个特定的DDL语句已经完成了多少。
其实v$session_longops视图也可以用于任何运行时间很长的操作，包括运行时间很长的更新操作。
下面的脚本将显示一个状态信息，说明了运行时间很长的DDL操作已经使用的时间。
注意你必须从v$session中取得SID并将其插入到下面的SQL语句中:
selectsid,start_time,elapsed_seconds,message
fromv$session_longops
wheresid=13
orderbystart_time;
这里是一个输出的例子，显示了运行时间很长的CREATEINDEX语句的运行过程。
SIDMESSAGE
------------------------------------------------------------------
11TableScan:
CUST.PK_IDX:
732outof243260Blocksdone
3.用settransaction命令解决ORA-01555错误
在执行大事务时,有时oracle会报出如下的错误:
ORA-01555:
snapshottooold（rollbacksegmenttoosmall）
这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如
settransactionuserollbacksegmentroll_abc;
deletefromtable_namewhere...;
commit;
提交结束后ORACLE会自动释放对roll_abc的指定。
4.删除表中重复记录
方法原理:
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的， rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。
实现方法:
SQL>createtablea（bmchar（4）,mcvarchar2（20））;
Tablecreated
SQL>insertintoavalues（'1111','aaaa'）;
SQL>insertintoavalues（'1112','aaaa'）;
SQL>insertintoavalues（'1113','aaaa'）;
SQL>insertintoavalues（'1114','aaaa'）;
SQL>insertintoaselect*froma;
4rowsinserted
SQL>commit;
Commitcomplete
SQL>selectrowid,bm,mcfroma;
ROWIDBMMC
------------------------------------------
AAAIRIAAQAAAAJqAAA1111aaaa
AAAIRIAAQAAAAJqAAB1112aaaa
AAAIRIAAQAAAAJqAAC1113aaaa
AAAIRIAAQAAAAJqAAD1114aaaa
AAAIRIAAQAAAAJqAAE1111aaaa
AAAIRIAAQAAAAJqAAF1112aaaa
AAAIRIAAQAAAAJqAAG1113aaaa
AAAIRIAAQAAAAJqAAH1114aaaa
8rowsselected
查出重复记录
SQL>selectrowid,bm,mcfromawherea.rowid!
=（selectmax（rowid）fromabwherea.bm=b.bmanda.mc=b.mc）;
ROWIDBMMC
------------------------------------------
AAAIRIAAQAAAAJqAAA1111aaaa
AAAIRIAAQAAAAJqAAB1112aaaa
AAAIRIAAQAAAAJqAAC1113aaaa
AAAIRIAAQAAAAJqAAD1114aaaa
删除重复记录
SQL>deletefromaawherea.rowid!
=（selectmax（rowid）fromabwherea.bm=b.bmanda.mc=b.mc）;
删除4个记录.
SQL>selectrowid,bm,mcfroma;
ROWIDBMMC
------------------------------------------
AAAIRIAAQAAAAJqAAE1111aaaa
AAAIRIAAQAAAAJqAAF1112aaaa
AAAIRIAAQAAAAJqAAG1113aaaa
AAAIRIAAQAAAAJqAAH1114aaaa
5.控制文件损坏时的恢复
根据如下错误信息，我们发现数据库只能启动实例，读控制文件时发生错误。
在数据库设计的过程中，从安全的角度考虑，系统使用了三个镜像的控制文件，现在三个控制文件version号不一致。
SVRMGRL>startup
oracleinstancestarted
totalsystemglobalarea222323980bytes
fixedsize70924bytes
variablesize78667776bytes
databasebuffers143507456bytes
redobuffers77824bytes
ORA-00214:
controlfile‘d:
\oracle\oradata\orcl\control01.ctl’version57460inconsistentwithfile‘d:
\oracle\oradata\orcl\control02.ctl’version57452.
根据以上分析，我们试着修改参数文件。
将参数文件中的control_file参数修改为一个控制文件，分别使用control01、control02、control03。
但数据库都无法启动，说明三个控制文件都已损坏。
由于没有控制文件的备份，我们只能采取重建控制文件的做法。
D:
\>svrmgrl
OracleServerManagerRelease3.1.6.0.0-Production
版权所有（c）1997，1999，OracleCorporation。
保留所有权利。
Oracle8iEnterpriseEditionRelease8.1.6.0.0-Production
WiththePartitioningoption
JServerRelease8.1.6.0.0-Production
SVRMGR>connectinternal
连接成功。
SVRMGR>shutdowmabort
已关闭ORACLE实例。
SVRMGR>startupnomount
已启动ORACLE实例。
系统全局区域合计有108475660个字节
FixedSize70924个字节
VariableSize46116864个字节
DatabaseBuffers62210048个字节
RedoBuffers77824个字节
SVRMGR>createcontrolfilereusedatabaseorclnoresetlogsarchivelog
Logfilegroup1‘d:
\oracle\oradata\orcl\redo01.log’,
group2‘d:
\oracle\oradata\orcl\redo02.log’,
group3‘d:
\oracle\oradata\orcl\redo03.log’
datafile‘d:
\oracle\oradata\orcl\system01.dbf’,
‘d:
\oracle\oradata\orcl\users01.dbf’,
‘d:
\oracle\oradata\orcl\temp01.dbf’,
‘d:
\oracle\oradata\orcl\tools01.dbf’,
‘d:
\oracle\oradata\orcl\indx01.dbf’,
‘d:
\oracle\oradata\orcl\dr01.dbf’,
‘d:
\oracle\oradata\orcl\rbs01.dbf’;
语句已处理。
成功地重建控制文件后，我们尝试着打开数据库，但系统报错，提示需要进行介质恢复。
SVRMGR>recoverdatafile‘d:
\oracle\oradata\orcl\system01.dbf’;
介质已恢复。
SVRMGR>recoverdatafile‘d:
\oracle\oradata\orcl\users0101.dbf’;
介质已恢复。
SVRMGR>recoverdatafile‘d:
\oracle\oradata\orcl\temp01.dbf’;
介质已恢复。
SVRMGR>recoverdatafile‘d:
\oracle\oradata\orcl\tools01.dbf’;
介质已恢复。
SVRMGR>recoverdatafile‘d:
\oracle\oradata\orcl\indx01.dbf’;
介质已恢复。
SVRMGR>recoverdatafile‘d:
\oracle\oradata\orcl\dr01.dbf’;
介质已恢复。
SVRMGR>recoverdatafile‘d:
\oracle\oradata\orcl\rbs01.dbf’;
介质已恢复。
介质恢复后，重新打开数据库，提示日志文件也需恢复。
SVRMGR>recoverdatabaseuntilcancel;
日志已恢复。
控制文件、数据文件、日志文件全部恢复后，将三种文件同步，并打开数据库，成功地完成了数据库的恢复工作。
SVRMGR>alterdatabaseopenresetlogs;
数据库已更改。
立即关闭数据库，并进行数据库的冷备份，将数据库的数据完整地保存下来
升级会员 