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

删除表空间

1.DROP TABLESPACETableSpaceName[INCLUDING CONTENTS[AND DATAFILES|]删除用户

2.DROP USERUser_Name CASCADE删除表的注意事项

3.在删除一个表中的全部数据时，须使用表名;因为用TRUNCATE TABLEDROP TABLE,表名时，表空间该表的占用空间并未释放，反复几次DELETE*FROM TABLESPACEDROP,操作后，该上百兆的空间就被耗光了DELETE TABLESPACE子句的用法

4.having子句对子句所确定的行组进行控制，子句条件中只允许涉及常量，聚组having group by having函数或子句中的列groupby外部联接”+”的用法

5.外部联接”+“按其在”的左边或右边分左联接和右联接.若不带”+”运算符的表中的一个行♦不直接匹配于带预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回.若二者均不带+,则二者中无法匹配的均被返回.利用外部联接”+”,可以替代效率十分低下的运算，大大提高运行速度.例如，下面这条命令执行起来很慢not in用外联接提高表连接的查询速度在作表连接常用于视图时，常使用以下方法来查询数据SELECT PAY_NO,PROJECT_NAMEFROM AWHEREA.PAY_NO NOTIN SELECT PAY_NO FROMB WHEREVALUE=120000;--但是若表有条记录,表有条记录,则要用掉分钟才能查完，主要因为A10000B1000030NOT要进行一条一条的比较，共需要次比较后，才能得到结果该用外联接后，IN10000*1000可以缩短到分左右的时间1SELECTPAY_NO,PROJECT_NAMEFROM A,BWHERE A.PAY_NO=B.PAY_NO+AND B.PAY_NO ISNULLAND B.VALUE=12000;from dba_objects where rownum32t，一找出当前月份的周五的日期where to_chart.d,MM=to_charsysdate,MM andtrimto_chart.d,星期五^ay*=03-05-0203-05-0903-05-1603-05-2303-05-30如果把改成即为查找当前月份的前三where to_chart.dJMM=to_charsysdateJMM sysdate-90,个月中的每周五的日期命令的用法

6.set transaction在执行大事务时，有时会报出如下的错误oracleORA-O1555:snapshot tooold rollbacksegment toosmall这说明给此事务随机分配的回滚段太小了，这时可以为它指定一个足够大的回滚段,oracle以确保这个事务的成功执行,例如set transactionuse rollbacksegment roll_abc;delete from table_name where...commit;回滚段被指定给这个事务,命令则在事务结束之后取消了回滚段的指rolLabc deletecommit定.数据库重建应注意的问题

7.在利用进行数据库重建过程中，有些视图可能会带来问题，因为结构输入的顺序可能import造成视图的输入先于它低层次表的输入，这样建立视图就会失败.要解决这一问题,可采取分两步走的方法首先输入结构，然后输入数据.命令举例如下uesrname:jfcl,password:hfjf,host数据文件:sting:oral,expdata.dmp:imp jfcl/hQf@oral file=empdata.dmp rows=Nimp jfcl/hfjf@ora1file=empdata.dmp full=Y buffer=64000commit=Y ignore=Y第一条命令输入所有数据库结构，但无记录.第二次输入结构和数据字节提交一,64000次二选项保证第二次输入既使对象存在的情况下也能成功..ignore Yselect a.empno from emp awhere a.empno notin selectempno fromempl wherejob=,SALE,;倘若利用外部联接，改写命令如下selecta.empno fromemp a,empl b二where a.empno b.empno+and b.empno isnulland b.job=,SALE,;可以发现,运行速度明显提高.从已知表新建另一个表:

8.CREATETABLEb可以是表中的几列AS SELECT*aFROM a二…；WHERE a.column查找、删除重复记录

9.法一用语句此查找很快的Group by-查找表中列重复的，列出重复的记录数，并列select countnum,maxname fromstudent num出他的属性namegroup bynum-按分组后找出表中列重复，即出现次数大于一次having countnum1num num上面的delete fromstudent Select这样的话就把所有重复的都删除了-一慎重法二:当表比较大例如万条以上时,这个方法的效率之差令人无法忍受，需要另想办法10--执行下面语句后就可以显示所有和相同且重复的记录SQL DRAWINGDSNOSELECT*FROM EM5_PIPE_PREFAB相当于WHERE ROW1D!=SELECT MAXROWIDFROM EM5_PIPE_PREFAB D-DFirst,SecondWHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING ANDEM5_PIPE_PREFAB.DSNO=D.DSNO;---执行下面语句后就可以删除所有和相同且重复的记录SQL DRAWINGDSNODELETE FROM EM5_PIPE_PREFABWHERE ROWID!=SELECT MAXROWIDFROMEM5_PIPE_PREFAB DWHEREEM5_PIPE_PREFAB.DRAWING=D.DRAWING ANDEM5_PIPE_PREFAB.DSNO=D.DSNO;返回表中［］条记录

10.N,M取得某列中第大的行Nselect column_name fromselecttable_name.*,dense_rank overorder bycolumn descrank fromtable_name whererank=；N假如要返回前条记录:5（或是或是）select*from tablenamewhere rownum6;rownum=5rownum!=6假如要返回第条记录5-9select*from tablenamewhere...and rownum10minusselect*from tablenamewhere...and rownum5order byname选出结果后用排序显示结果（先选再排序）name注意只能用以上符号（、=、！=）返回的是前条记录select*from tablenamewhererownum!=10;9不能用由于是一个总是从开始的伪列，认为这种条件,=,=,Between...ando rownum1Oracle不成立，查不到记录.另外，这个方法更快（select*fromselect rownumr,a fromyourtablewhere rownum=20）order bynamewhere r10这样取出第条记录!（先选再排序再选）11-20要先排序再选则须用嵌套内层排序外层选select是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没rownum有就永远不会有12!是在查询集合产生的过程中产生的伪列，并且如果条件中存在条件的话，rownum whererownum则:假如判定条件是常量，贝」1I只能二大于的自然数，=大于的数是没有结果的，大于一个数也是rownum=1,11没有结果的即当出现一个不满足条件的时候则查询结束rownum thisis stop key!当判定值不是常量的时候2:若条件是=则只有当为的时候才满足条件，这个时候不存在必须进行，var,var1stopkey,full scan对每个满足其他条件的数据进行判定where选出一行后才能去选的行rownum=

2.快速编译所有视图11—当在把数据库倒入到新的服务器上后数据库重建，需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用的语言特性，快速编译PL/SQLSQL SPOOLON.SQLSQL SELECTALTER VIEW||TNAME||,COMPILE;9FROM TAB;SQL SPOOLOFF然后执行即可ON.SQLSQL@ON.SQL当然，授权和创建同义词也可以快速进行，如SQL SELECTGRANT SELECTON||TNAME『TO USERNAME;9FROM TAB;SQL SELECTCREATE SYNONYM||TNAME/FOR USERNAME.RTNAMEFROM TAB;读写文本型操作系统文件

12.--在以上的版本中，包允许用户通过读写操作系统文件如下PL/SQL

3.3UTJFILE PL/SQLDECALREFILE HANDLEUTL FILE.FILE TYPE;BEGINFILE_HANDLE:=UTL_FILE.FOPENC:\1TEST.TXTYA；UTL FILE.PUT LINECFILEHANDLEHELLO JT^A TESTTXTFILE,）;（）UTL_FILE.FCLOSE FILE_HANDLE;END;在数据库触发器中使用列的新值与旧值

13.--在数据库触发器中几乎总是要使用触发器基表的列值，如果某条语句需要某列修改前的值，使用就可以了，使用某列修改后的新值，用就可以了OLD:NEW如:OLD.DEPT_NO,:NEW.DEPT_NO数据库文件的移动方法

14.当想将数据库文件移动到另外一个目录下时，可以用命令来移动（比ALTER DATABASE适用性强）ALTER TABLESPACE.使用关闭实例.1SERVER MANAGERSVRMGRconnect internal;SVRMGRshutdown;SVRMGR exit;,使用操作系统命令来移动数据库文件位置（假设这里操作系统为）在中2SOLARIS

2.

6.UNIX用命令可以把文件移动到新的位置，mv#mv/ora13/orarun/document.dbf/ora12/orarun装载数据库并用命令来改变数据库中的文件名.

3.alter databaseSVRMGRconnect internal;SVRMGRstartup mountRUN73;SVRMGRalter databaserename file7ora13/orarun/document.dbf7ora12/orarun/document.dbf9;启动实例.

4.SVRMGRalter databaseopen;连接查询结果

15.表列a ala2记录1a2x2y2z用能选成以下结果select1ab2xyz下面有两个例子.使用代码实现，但要求你组合后的长度不能超出长度的限制1pl/sql oraclevarchar2create orreplace typestrings_table istable ofvarchar220;/create orreplace functionmerge pvin strings_table returnvarchar2isIs varchar24000;beginfor iin

1..pv.count loopIs:=Is||pvi;end loop;return Is;end;/create tablet idnumber,name varchar210;insert intot valuesCl/Joan;insert intot valuesJack;,insert intot valuesTom;；insert intot values2,Roseinsert intot values2,Jenny;column namesformat a80;select tO.id,mergecastmultisetselect name fromtwhere t.id=tO.id asstrings_table namesfromselect distinctid fromt tO;drop typestrings_table;drop functionmerge;drop tablet;.纯粹用2sql表dept,emp要得到如下结果deptno,dname,employees10,accounting,clark;king;miller20,research,smith;adams;ford;scott;jones30,sales,allen;blake;martin;james;turners每个的串起来作为一条记录返回dept employeeThis exampleuses amax of6,and wouldneed morecut npasting todo morethan thatSQL selectdeptno,dname,emps2from3select d.deptno,d.dname,rtrime.ename||\f||4leade.enamej over partition by d.deptno5order bye.ename||,|||6leade.ename,2over partitionby d.deptno7order bye.ename||\|||8leade.ename,3over partitionby d.deptno9order bye.ename\\\|||10leade.ename,4over partitionby d.deptno11order bye.ename||\|||12leade.ename,5over partitionby d.deptno13order bye.ename/,*emps,14row_numberoverpartitionbyd.deptno15order bye.ename x16fromempe,dept d17where d.deptno=e.deptno1819where x=120/DEPTNO DNAMEEMPS10ACCOUNTING CLARK,KING,MILLER20RESEARCH ADAMS,FORD,JONES,ROONEY,SCOTT,SMITH30SALES ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD在中建一个编号会自动增加的字段，以利于查询

16.Oracle、建立序列1CREATE SEQUENCEcheckup_no_seqNOCYCLEMAXVALUE9999999999START WITH2;、建立触发器2CREATE ORREPLACE TRIGGERset_checkup_noBEFORE INSERTON checkup_historyFOR EACHROWDECLAREnext_checkup_no NUMBER;BEGIN—Get thenext checkupnumber fromthe sequenceSELECTcheckup_no_seq.NEXTVALINTO next_checkup_noFROM dual;—use thesequence numberas theprimary key—for therecord beinginserted:new.checkup_no:=next_checkup_no;END;查看对象的依赖关系比如视图与表的引用

17.查看视图记录了相关的依赖关系dba_dependencies查东西不知道要查看哪个视图时，可以在里看，DBAJDbjects假如查看相关select object_namefromdba_objects whereobject_name like%ROLE%ROLE然后一下就大体上知道了DESC要找到某月中所有周五的具体日期；

18.select to_chart.d YY-MM-DD fromselect truncsysdate,MM-Frownum-1as d。