心得共享：Oracle经验技巧集锦

1、1删除表空间DROP TABLESPACE TableSpaceName INCLUDING CONTENTS AND DATAFILES2删除用户DROP USSER Usser_Naame CAASCADEE3删除表的注注意事项在删除一个表中中的全部数据据时，须使用用TRUNCCATE TTABLE 表名;因为为用DROPP TABLLE，DELLETE * FROMM 表名时，TTABLESSPACE表表空间该表的的占用空间并并未释放，反反复几次DRROP，DEELETE操操作后，该TTABLESSPACE上上百兆的空间间就被耗光了了。4havinng子句的用用法having子子句对gr

2、ooup byy子句所确定定的行组进行行控制，haaving子子句条件中只只允许涉及常常量,聚组函函数或grooup byy 子句中的的列。5外部联接+的用法法外部联接+按其在=的左边或或右边分左联联接和右联接接.若不带+运算符符的表中的一一个行不直接接匹配于带+预算符符的表中的任任何行,则前前者的行与后后者中的一个个空行相匹配配并被返回.若二者均不不带+,则二者中中无法匹配的的均被返回.利用外部联联接+,可以替代效效率十分低下下的 nott in 运运算,大大提提高运行速度度.例如,下下面这条命令令执行起来很很慢 用外联接提高表表连接的查询询速度 在作表连接(常常用于视图)时，常使用用以下方

3、法来来查询数据: SELECT PAY_NNO, PRROJECTT_NAMEE FROM A WHERE AA.PAY_NO NOOT IN (SELEECT PAAY_ NO FROMM B WHHERE VVALUE =1200000); - 但是是若表A有110000条条记录，表BB有100000条记录，则则要用掉300分钟才能查查完，主要因因为NOT IN要进 行一条一条条的比较，共共需要100000*100000次比比较后，才能能得到结果。该该用外联接后后，可以缩短短到1分左右右的时间: SELECT PAY_NNO,PROOJECT_NAMEFROM A,B WHERE AA.P

4、AY_NO=B.PAY_NNO(+) AND B.PPAY_NOO IS NNULL AND B.VVALUE =120000; 6set ttransaactionn命令的用法法 在执行大事务时时,有时orracle会会报出如下的的错误: ORA-015555:snnapshoot tooo old (rolllback segmeent tooo smaall) 这说明orracle给给此事务随机机分配的回滚滚段太小了,这时可以为为它指定一个个足够大的回回滚段,以确确保这个事务务的成功执行行.例如 set traansacttion uuse roollbacck seggment rol

5、l_abc; delete from tablee_namee wherre . commit; 回滚段rooll_abbc被指定给给这个dellete事务务,commmit命令则则在事务结束束之后取消了了回滚段的指指定. 7数据库重建建应注意的问问题 在利用immport进进行数据库重重建过程中,有些视图可可能会带来问问题,因为结结构输入的顺顺序可能造成成视图的输入入先于它低层层次表的输入入,这样建立立视图就会失失败.要解决决这一问题,可采取分两两步走的方法法:首先输入入结构,然后后输入数据.命令举例如如下 (ueesrnamme:jfccl,passswordd:hfjff,hostt s

6、tinng:oraa1,数据文文件:exppdata.dmp): imp jfccl/hfjjforaa1 fille=emppdata.dmp rrows=NN imp jfccl/hfjjforaa1 fille=emppdata.dmp ffull=YY bufffer=644000 commit=Y ignnore=YY 第一条命令令输入所有数数据库结构,但无记录.第二次输入入结构和数据据,640000字节提交交一次.iggnore=Y选项保证证第二次输入入既使对象存存在的情况下下也能成功. select a.emppno frrom emmp a wwhere a.emppno no

7、ot in (seleect emmpno ffrom eemp1 wwhere job=SALE); 倘若利用外外部联接,改改写命令如下下: select a.emppno frrom emmp a ,emp1 b where aa.empnno=b.eempno(+) and b.eempno is nuull and b.jjob=SSALE; 可以发现,运行行速度明显提提高. 8从已知表新新建另一个表表：CREATE TABLEE b AS SELEECT * (可以是表表a中的几列列) FROM a WHERE aa.coluumn = .; 9查找、删除除重复记录: 法一: 用Gr

8、roup bby语句 此此查找很快的的select countt(num), maxx(namee) froom stuudent -查找表表中num列列重复的，列列出重复的记记录数，并列列出他的naame属性group bby numm having countt(num) 1 -按numm分组后找出出表中numm列重复，即即出现次数大大于一次delete from studeent(上面面Selecct的) 这样的话就把所所有重复的都都删除了。-慎慎重 法二:当表比较较大(例如110万条以上上)时,这个个方法的效率率之差令人无无法忍受,需需要另想办法法: - 执行行下面SQLL语句后就可可

9、以显示所有有DRAWIING和DSSNO相同且且重复的记录录 SELECT * FROOM EM55_PIPEE_PREFFAB WHERE RROWID!=(SELLECT MMAX(ROOWID) FROM EM5_PPIPE_PPREFABB D -D相当于FFirst,Seconnd WHERE EEM5_PIIPE_PRREFAB.DRAWIING=D.DRAWIING ANND EM5_PIPPE_PREEFAB.DDSNO=DD.DSNOO); - 执行行下面SQLL语句后就可可以刪除所有有DRAWIING和DSSNO相同且且重复的记录录 DELETE FROM EM5_PPIP

10、E_PPREFABB WHERE RROWID!=(SELLECT MMAX(ROOWID) FROM EM5_PPIPE_PPREFABB D WHERE EEM5_PIIPE_PRREFAB.DRAWIING=D.DRAWIING ANND EM5_PIPPE_PREEFAB.DDSNO=DD.DSNOO); 10返回表中中N，M条记录： 取得某列中第NN大的行 select colummn_namme froom (selectt tablle_namme.*,ddense_rank() oveer (orrder bby collumn ddesc) rank from tablee_

11、namee) where rrank = &N； 假如要返回前前5条记录： selecct * ffrom ttablenname wwhere rownuum6;(或是rowwnum = 5 或或是rownnum != 6) 假如要返回第55-9条记录录： select * froom tabblenamme where and rowwnum110 minus select * froom tabblenamme where and rowwnum55 order bby namme 选出结果后用nname排序序显示结果。(先选再排序序) 注意：只能用以以上符号(、,=,=,BBetwee

12、en.aand。由于于rownuum是一个总总是从1开始始的伪列，OOraclee 认为这种种条件 不成成立，查不到到记录. 另外，这个方法法更快： select * froom ( select rownuum r,aa fromm yourrtablee where rrownumm 100 这样取出第111-20条记记录!(先选选再排序再选选) 要先排序再选则则须用sellect嵌套套：内层排序序外层选。 rownum是是随着结果集集生成的，一一旦生成，就就不会变化了了；同时,生生成的结果是是依次递加的的，没有1就就永远不会有有2! rownum 是在 查询询集合产生的的过程中产生生的伪

13、列，并并且如果whhere条件件中存在 rrownumm 条件的话话，则: 1： 假如 判判定条件是常常量，则： 只能 rownnum = 1, SPPOOL OON.SQLL SQL SEELECT ALTEER VIEEW |TNAMEE| COMPILEE; FRROM TAAB; SQL SPPOOL OOFF 然后执行ON.SQL即可可。 SQL OON.SQLL 当然，授权和创创建同义词也也可以快速进进行，如： SQL SEELECT GRANNT SELLECT OON |TNAMEE| TTO USEERNAMEE; FRROM TAAB; SQL SEELECT CREAAT

14、E SYYNONYMM |TNAMME| FOR UUSERNAAME.|TNAMME|; FROOM TABB; 12读写文本本型操作系统统文件- 在PPL/SQLL 3.3以以上的版本中中，UTL_FILE包包允许用户通通过PL/SSQL读写操操作系统文件件。如下： DECALREE FILE_HAANDLE UTL_FFILE.FFILE_TTYPE; BEGIN FILE_HAANDLE:=UTL_FILE.FOPENN( C:,TEST.TXT,A); UTL_FILLE.PUTT_LINEE(FILEE_HANDDLE,HELLO,IITS AA TESTT TXT FILE);

15、UTL_FILLE.FCLLOSE(FFILE_HHANDLEE); END; 13在数据库库触发器中使使用列的新值值与旧值 - 在数数据库触发器器中几乎总是是要使用触发发器基表的列列值，如果某某条语句需要要某列修改前前的值，使用用:OLD就就可以了，使使用某列修改改后的新值，用用:NEW就就可以了。如如:OLD.DEPT_NO,:NNEW.DEEPT_NOO。 14数据库文文件的移动方方法 当想将数据库文文件移动到另另外一个目录录下时，可以以用ALTEER DATTABASEE命令来移动动(比ALTTER TAABLESPPACE适用用性强)： 1. 使用SEERVER MANAGGER关闭

16、实实例. SVRMGR connnect interrnal; SVRMGR shuutdownn; SVRMGR exitt; 2. 使用操作作系统命令来来移动数据库库文件位置(假设这里操操作系统为SSOLARIIS 2.66). 在UUNIX中用用 mv命令令可以把文件件移动到新的的位置， #mv /orra13/oorarunn/docuument.dbf /ora122/orarrun 3. 装载数据据库并用allter ddatabaase命令来来改变数据库库中的文件名名. SVRMGR connnect interrnal; SVRMGR staartup mountt RUN77

17、3; SVRMGR altter daatabasse renname ffile / orra13/oorarunn/docuument.dbf / orra12/oorarunn/docuument.dbf; 4. 启动实例例. SVRMGR altter daatabasse opeen; 15连接查询询结果： 表a 列 a11 a2 记录 1 a 1 b 2 x 2 y 2 z 用selectt能选成以下下结果: 1 ab 2 xyz 下面有两个例子子： 1.使用pl/sql代码码实现，但要要求你组合后后的长度不能能超出oraacle vvarchaar2长度的的限制 create o

18、r reeplacee typee striings_ttable is taable oof varrchar22(20); / create or reeplacee funcction mergee (pv in sttringss_tablle) reeturn varchhar2 is ls varcchar2(4000); begin for i iin 1.pv.coount lloop ls := lls | pv(i); end looop; return ls; end; / create tablee t (iid nummber,nname vvarchaar2(100

19、); insert into t vallues(11,Joaan); insert into t vallues(11,Jacck); insert into t vallues(11,Tomm); insert into t vallues(22,Rosse); insert into t vallues(22,Jennny); column namess formmat a880; select t0.idd,mergge(casst(mulltisett(seleect naame frrom t wheree t.idd = t00.id) as sttringss_tablle)

20、nnames from (sselectt disttinct id frrom t) t0; drop tyype sttringss_tablle; drop fuunctioon merrge; drop taable tt; 2.纯粹用sqql： 表dept, emp 要得到如下结果果 deptno, dnamme, emmployeees - 10, acccountiing, cclark;king;milleer 20, ressearchh, smiith;addams;fford;sscott;joness 30, salles, aallen;blakee;marttin;

21、jaames;tturnerrs 每个dept的的emplooyee串起起来作为一条条记录返回 This exxamplee usess a maax of 6, annd wouuld neeed moore cuut n ppastinng to do moore thhan thhat： SQL seelect deptnno, dnname, emps 2 from ( 3 selecct d.ddeptnoo, d.ddname, rtriim(e.eename |, | 4 lead(e.enaame,1) overr (parrtitioon by d.depptno 5 ord

22、err by ee.enamme) |, | 6 lead(e.enaame,2) overr (parrtitioon by d.depptno 7 orderr by ee.enamme) |, | 8 lead(e.enaame,3) overr (parrtitioon by d.depptno 9 orderr by ee.enamme) |, | 10 leadd(e.enname,44) oveer (paartitiion byy d.deeptno 11 ordeer by e.enaame) |, | 12 leadd(e.enname,55) oveer (paartit

23、iion byy d.deeptno 13 ordeer by e.enaame), ) emps, 14 row_numbeer () over (parttitionn by dd.depttno 15 ordeer by e.enaame) xx 16 fromm emp e, deept d 17 wherre d.ddeptnoo = e.deptnno 18 ) 19 wherre x = 1 20 / DEPTNO DNAMEE EMPSS - - - 10 ACCOOUNTINNG CLAARK, KKING, MILLEER 20 RESEEARCH ADAMSS, FORR

24、D, JOONES, ROONEEY, SCCOTT, SMITHH 30 SALEES ALLLEN, BBLAKE, JAMEES, MAARTIN, TURNNER, WWARD 16在Oraacle中建建一个编号会会自动增加的的字段,以利利于查询 1、建立序列： CREATE SEQUEENCE ccheckuup_no_seq NOCYCLEE MAXVALUUE 999999999999 START WWITH 22; 2、建立触发器器： CREATE OR REEPLACEE TRIGGGER sset_chheckupp_no BEFORE INSERRT ON checkkup_hiistoryy FOR EACCH ROWW DECLAREE next_chheckupp_no NNUMBERR; BEGIN -Get tthe neext chheckupp numbber frrom thhe se