Oracle实用技巧
1.找出无用索引:
DML 性能低下，其中最严重的原因之一是无用索引的存在。所有SQL的插入，更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。许多Oracle 管理人员只要看见在一个SQL 查询的WHERE语句出现了一列的话就会为它分配索引。虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle 索引使得数据库管理人员有可能在数据表的行上过度分配索引。过度分配索引会严重影响关键Oracle 数据表的性能。
在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。Oracle9i有一个工具能够让你使用ALTER INDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。
下面是一段脚本，它能够打开一个系统中所有索引的监视功能:
spool run_monitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off;
@run_monitor
你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。
select index_name,table_name,mon,used
from v$object_usage;
在下面，我们可以看见V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。
SQL> select * from v$object_usage where rownum < 10;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
ASD DIM_ACCT_ITEM_TYPE_TEMP YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_ACCESSORY_TARIFF1 ACCOUNT_ACCESSORY_TARIFF YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_QUOTA_LOG1 ACCOUNT_QUOTA_LOG YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_SYSTEM_PARAMETERS1 ACCOUNT_SYSTEM_PARAMETERS YES NO 01/15/2004 13:50:59
IDX_ACCT2 ACCT YES NO 01/15/2004 13:50:59
IDX_ACCT3 ACCT YES NO 01/15/2004 13:51:00
IDX_ACCT4 ACCT YES NO 01/15/2004 13:51:00
IDX_ACCT_BIND_DISCT1 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
IDX_ACCT_BIND_DISCT2 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
2.查看一个很长的操作已经做了多少:
v$session_longops视图可以使Oracle专家减少运行时间很长的DDL和DML语句的运行时间。例如在数据仓库环境中，即使使用并行索引创建技术，构建一个很多G字节大的索引需要耗费很多个小时。这里你就可以查询v$session_longops视图快速找出一个特定的DDL语句已经完成了多少。其实v$session_longops视图也可以用于任何运行时间很长的操作，包括运行时间很长的更新操作。
下面的脚本将显示一个状态信息，说明了运行时间很长的DDL操作已经使用的时间。注意你必须从v$session中取得SID并将其插入到下面的SQL语句中:
select sid,start_time,elapsed_seconds,message
from v$session_longops
where sid = 13
order by start_time;
这里是一个输出的例子，显示了运行时间很长的CREATE INDEX语句的运行过程。
SID MESSAGE
--- ---------------------------------------------------------------
11 Table Scan: CUST.PK_IDX: 732 out of 243260 Blocks done
3.用set transaction 命令解决ORA-01555错误
在执行大事务时,有时oracle会报出如下的错误:
ORA-01555:snapshot too old (rollback segment too small)
这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如
set transaction use rollback segment roll_abc;
delete from table_name where ... ;
commit;
提交结束后ORACLE会自动释放对 roll_abc 的指定。
4.删除表中重复记录
方法原理:
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 file ‘d:\oracle\oradata\orcl\control02.ctl’ version 57452.
根据以上分析，我们试着修改参数文件。将参数文件中的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;
数据库已更改。
立即关闭数据库，并进行数据库的冷备份，将数据库的数据完整地保存下来。
DML 性能低下，其中最严重的原因之一是无用索引的存在。所有SQL的插入，更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。许多Oracle 管理人员只要看见在一个SQL 查询的WHERE语句出现了一列的话就会为它分配索引。虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle 索引使得数据库管理人员有可能在数据表的行上过度分配索引。过度分配索引会严重影响关键Oracle 数据表的性能。
在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。Oracle9i有一个工具能够让你使用ALTER INDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。
下面是一段脚本，它能够打开一个系统中所有索引的监视功能:
spool run_monitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off;
@run_monitor
你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。
select index_name,table_name,mon,used
from v$object_usage;
在下面，我们可以看见V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。
SQL> select * from v$object_usage where rownum < 10;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
ASD DIM_ACCT_ITEM_TYPE_TEMP YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_ACCESSORY_TARIFF1 ACCOUNT_ACCESSORY_TARIFF YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_QUOTA_LOG1 ACCOUNT_QUOTA_LOG YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_SYSTEM_PARAMETERS1 ACCOUNT_SYSTEM_PARAMETERS YES NO 01/15/2004 13:50:59
IDX_ACCT2 ACCT YES NO 01/15/2004 13:50:59
IDX_ACCT3 ACCT YES NO 01/15/2004 13:51:00
IDX_ACCT4 ACCT YES NO 01/15/2004 13:51:00
IDX_ACCT_BIND_DISCT1 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
IDX_ACCT_BIND_DISCT2 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
2.查看一个很长的操作已经做了多少:
v$session_longops视图可以使Oracle专家减少运行时间很长的DDL和DML语句的运行时间。例如在数据仓库环境中，即使使用并行索引创建技术，构建一个很多G字节大的索引需要耗费很多个小时。这里你就可以查询v$session_longops视图快速找出一个特定的DDL语句已经完成了多少。其实v$session_longops视图也可以用于任何运行时间很长的操作，包括运行时间很长的更新操作。
下面的脚本将显示一个状态信息，说明了运行时间很长的DDL操作已经使用的时间。注意你必须从v$session中取得SID并将其插入到下面的SQL语句中:
select sid,start_time,elapsed_seconds,message
from v$session_longops
where sid = 13
order by start_time;
这里是一个输出的例子，显示了运行时间很长的CREATE INDEX语句的运行过程。
SID MESSAGE
--- ---------------------------------------------------------------
11 Table Scan: CUST.PK_IDX: 732 out of 243260 Blocks done
3.用set transaction 命令解决ORA-01555错误
在执行大事务时,有时oracle会报出如下的错误:
ORA-01555:snapshot too old (rollback segment too small)
这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如
set transaction use rollback segment roll_abc;
delete from table_name where ... ;
commit;
提交结束后ORACLE会自动释放对 roll_abc 的指定。
4.删除表中重复记录
方法原理:
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 file ‘d:\oracle\oradata\orcl\control02.ctl’ version 57452.
根据以上分析，我们试着修改参数文件。将参数文件中的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;
数据库已更改。
立即关闭数据库，并进行数据库的冷备份，将数据库的数据完整地保存下来。