oracle的一些使用技巧1

亦凉 2022-05-16 14:14 221阅读 0赞

1.使用创建临时表

![20181031090413696.png][]

![20181031090431796.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NTU3MA_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NTU3MA_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NTU3MA_size_16_color_FFFFFF_t_70 2][]

说明：wm\_concat与listagg的差别

①wm\_concat依赖wmsys用户

②listagg是oracle11g2提供的函数

③listagg可以指定需要使用的分隔符，wm\_concat不能指定，但是可以通过下面方式达到相同效果

listagg(ts.sid, '|') with group(order by ts.sid) as c\_sid与

replace(wm\_concat(ts.sid), ',', '|') as c\_sid

参考链接：聚合函数listagg，wm\_concat函数用法，listagg和wm\_concat

（2）统计各班级的学生人数  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NTU3MA_size_16_color_FFFFFF_t_70 3][]

**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’

[20181031090413696.png]: /images/20220503/469de24829e64df3be8b4a193860ecbf.png
[20181031090431796.png]: /images/20220503/d254781d24bf405aba9313f228f8092a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NTU3MA_size_16_color_FFFFFF_t_70]: /images/20220503/a9d187228fca4084b387f7c1b7a74ed2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NTU3MA_size_16_color_FFFFFF_t_70 1]: /images/20220503/961f1f319b0f462ead6bffee31158fc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NTU3MA_size_16_color_FFFFFF_t_70 2]: /images/20220503/2402c2c7fed04b78a2f94e99b1abfc28.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NTU3MA_size_16_color_FFFFFF_t_70 3]: /images/20220503/5104ef3cf32847318e1e11c4f59b08b6.png