学习 Oracle过程中几个常见问题的总结

来源：互联网发布：idl软件下载win8 编辑：程序博客网时间：2024/05/07 23:51

1.找出无用索引:

DML 性能低下，其中最严重的原因之一是无用索引的存在。所有SQL的插入，更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。许多 Oracle 管理人员只要看见在一个SQL 查询的WHERE语句出现了一列的话就会为它分配索引。虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle 索引使得数据库管理人员有可能在数据表的行上过度分配索引。过度分配索引会严重影响关键Oracle 数据表的性能。

在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。Oracle9i有一个工具能够让你使用ALTER INDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。

下面是一段脚本，它能够打开一个系统中所有索引的监视功能:

　
select ind.owner,
       ind.user_stats,
       ind.index_name,
       ind.status,
       ind.table_name,
       ind.dropped
        　from dba_indexes ind 　　
       where owner = ('PYMZ')

你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。

select index_name,table_name,usedfrom v$object_usage;

在下面，我们可以看见V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。

2 删除表中重复记录

方法原理:

1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。

2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。

实现方法:

SQL> create table a(bm char(4),mc varchar2(20));　　Table created　　SQL> insert into a values(’1111’,’aaaa’);　　SQL> insert into a values(’1112’,’aaaa’);　　SQL> insert into a values(’1113’,’aaaa’);　　SQL> insert into a values(’1114’,’aaaa’);　　SQL> insert into a select * from a;　　4 rows inserted　　SQL> commit;　　Commit complete　　SQL> select rowid,bm,mc from a;　　ROWID BM MC　　------------------ ---- --------------------　　AAAIRIAAQAAAAJqAAA 1111 aaaa　　AAAIRIAAQAAAAJqAAB 1112 aaaa　　AAAIRIAAQAAAAJqAAC 1113 aaaa　　AAAIRIAAQAAAAJqAAD 1114 aaaa　　AAAIRIAAQAAAAJqAAE 1111 aaaa　　AAAIRIAAQAAAAJqAAF 1112 aaaa　　AAAIRIAAQAAAAJqAAG 1113 aaaa　　AAAIRIAAQAAAAJqAAH 1114 aaaa　　8 rows selected

查出重复记录

SQL> select rowid,bm,mc from a where a.    rowid!=(select max(rowid) from a b where a.bm=b.bm and     a.mc=b.mc);　　ROWID BM MC　　------------------ ---- ------------------　　AAAIRIAAQAAAAJqAAA 1111 aaaa　　AAAIRIAAQAAAAJqAAB 1112 aaaa　　AAAIRIAAQAAAAJqAAC 1113 aaaa　　AAAIRIAAQAAAAJqAAD 1114 aaaa

删除重复记录

　　SQL> delete from a a where a.rowid!=    (select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);　　删除4个记录.　　SQL> select rowid,bm,mc from a;　　ROWID BM MC　　------------------ ---- --------------------　　AAAIRIAAQAAAAJqAAE 1111 aaaa　　AAAIRIAAQAAAAJqAAF 1112 aaaa　　AAAIRIAAQAAAAJqAAG 1113 aaaa　　AAAIRIAAQAAAAJqAAH 1114 aaaa

5.控制文件损坏时的恢复

根据如下错误信息，我们发现数据库只能启动实例，读控制文件时发生错误。在数据库设计的过程中，从安全的角度考虑，系统使用了三个镜像的控制文件，现在三个控制文件version号不一致。

　　SVRMGRL>startup　　oracle instance started　　total system global area 222323980 bytes　　fixed size 70924 bytes　　variable size 78667776 bytes　　database buffers 143507456 bytes　　redo buffers 77824 bytes　　ORA-00214: controlfile ‘d:/oracle/oradata/    orcl/control01.ctl’ version 57460 inconsistent     with fit

根据以上分析，我们试着修改参数文件。将参数文件中的control_file参数修改为一个控制文件，分别使用control01、control02、control03。但数据库都无法启动，说明三个控制文件都已损坏。

由于没有控制文件的备份，我们只能采取重建控制文件的做法。

　　D:/>svrmgrl　　Oracle Server Manager Release 3.1.6.0.0 - Production　　版权所有 (c) 1997，1999，Oracle Corporation。保留所有权利。　　Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production　　With the Partitioning option　　JServer Release 8.1.6.0.0 - Production　　SVRMGR> connect internal　　连接成功。　　SVRMGR> shutdowm abort　　已关闭 ORACLE 实例。　　SVRMGR> startup nomount　　已启动 ORACLE 实例。　　系统全局区域合计有 108475660个字节　　Fixed Size 70924个字节　　Variable Size 46116864个字节　　Database Buffers 62210048个字节　　Redo Buffers 77824个字节　　SVRMGR>create controlfile reuse database orcl    noresetlogs archivelog　　Logfile group 1 ‘d:/oracle/oradata/orcl/redo01.log’,　　group 2 ‘d:/oracle/oradata/orcl/redo02.log’,　　group 3 ‘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>recover datafile ‘    d:/oracle/oradata/orcl/system01.dbf’;　　介质已恢复。　　SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/users0101.dbf’;　　介质已恢复。　　SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/temp01.dbf’;　　介质已恢复。　　SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/tools01.dbf’;　　介质已恢复。　　SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/indx01.dbf’;　　介质已恢复。　　SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/dr01.dbf’;　　介质已恢复。　　SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/rbs01.dbf’;　　介质已恢复。

介质恢复后，重新打开数据库，提示日志文件也需恢复。

　SVRMGR> recover database until cancel;　　日志已恢复。

控制文件、数据文件、日志文件全部恢复后，将三种文件同步，并打开数据库，成功地完成了数据库的恢复工作。

　SVRMGR> alter database open resetlogs;　　数据库已更改。