心得共享：Oracle經(jīng)驗技巧集錦

1、1刪除表空間DROP TABLESPACE TableSpaceName INCLUDING CONTENTS AND DATAFILES2刪除用戶DROP USER User_Name CASCADE3刪除表的注意事項在刪除一個表中的全部數(shù)據(jù)時，須使用TRUNCATE TABLE 表名;因為用DROP TABLE，DELETE * FROM 表名時，TABLESPACE表空間該表的占用空間并未釋放，反復(fù)幾次DROP，DELETE操作后，該TABLESPACE上百兆的空間就被耗光了。4having子句的用法having子句對group by子句所確定的行組進行控制，having子句條件中只允許

2、涉及常量,聚組函數(shù)或group by 子句中的列。5外部聯(lián)接+的用法外部聯(lián)接+按其在=的左邊或右邊分左聯(lián)接和右聯(lián)接.若不帶+運算符的表中的一個行不直接匹配于帶+預(yù)算符的表中的任何行,則前者的行與后者中的一個空行相匹配并被返回.若二者均不帶+,則二者中無法匹配的均被返回.利用外部聯(lián)接+,可以替代效率十分低下的 not in 運算,大大提高運行速度.例如,下面這條命令執(zhí)行起來很慢 用外聯(lián)接提高表連接的查詢速度 在作表連接(常用于視圖)時，常使用以下方法來查詢數(shù)據(jù): SELECT PAY_NO, PROJECT_NAME FROM A WHERE A.PAY_NO NOT IN (SELECT PA

3、Y_ NO FROM B WHERE VALUE =120000); - 但是若表A有10000條記錄，表B有10000條記錄，則要用掉30分鐘才能查完，主要因為NOT IN要進 行一條一條的比較，共需要10000*10000次比較后，才能得到結(jié)果。該用外聯(lián)接后，可以縮短到1分左右的時間: SELECT PAY_NO,PROJECT_NAMEFROM A,B WHERE A.PAY_NO=B.PAY_NO(+) AND B.PAY_NO IS NULL AND B.VALUE =12000; 6set transaction命令的用法 在執(zhí)行大事務(wù)時,有時oracle會報出如下的錯誤: ORA

4、-01555:snapshot too old (rollback segment too small) 這說明oracle給此事務(wù)隨機分配的回滾段太小了,這時可以為它指定一個足夠大的回滾段,以確保這個事務(wù)的成功執(zhí)行.例如 set transaction use rollback segment roll_abc; delete from table_name where . commit; 回滾段roll_abc被指定給這個delete事務(wù),commit命令則在事務(wù)結(jié)束之后取消了回滾段的指定. 7數(shù)據(jù)庫重建應(yīng)注意的問題 在利用import進行數(shù)據(jù)庫重建過程中,有些視圖可能會帶來問題,因為結(jié)構(gòu)

5、輸入的順序可能造成視圖的輸入先于它低層次表的輸入,這樣建立視圖就會失敗.要解決這一問題,可采取分兩步走的方法:首先輸入結(jié)構(gòu),然后輸入數(shù)據(jù).命令舉例如下 (uesrname:jfcl,password:hfjf,host sting:ora1,數(shù)據(jù)文件:expdata.dmp): imp jfcl/hfjfora1 file=empdata.dmp rows=N imp jfcl/hfjfora1 file=empdata.dmp full=Y buffer=64000 commit=Y ignore=Y 第一條命令輸入所有數(shù)據(jù)庫結(jié)構(gòu),但無記錄.第二次輸入結(jié)構(gòu)和數(shù)據(jù),64000字節(jié)提交一次.ig

6、nore=Y選項保證第二次輸入既使對象存在的情況下也能成功. select a.empno from emp a where a.empno not in (select empno from emp1 where job=SALE); 倘若利用外部聯(lián)接,改寫命令如下: select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job=SALE; 可以發(fā)現(xiàn),運行速度明顯提高. 8從已知表新建另一個表：CREATE TABLE b AS SELECT * (可以是表a中的幾列) FRO

7、M a WHERE a.column = .; 9查找、刪除重復(fù)記錄: 法一: 用Group by語句 此查找很快的select count(num), max(name) from student -查找表中num列重復(fù)的，列出重復(fù)的記錄數(shù)，并列出他的name屬性group by num having count(num) 1 -按num分組后找出表中num列重復(fù)，即出現(xiàn)次數(shù)大于一次delete from student(上面Select的) 這樣的話就把所有重復(fù)的都刪除了。-慎重 法二:當(dāng)表比較大(例如10萬條以上)時,這個方法的效率之差令人無法忍受,需要另想辦法: - 執(zhí)行下面SQL語句

8、后就可以顯示所有DRAWING和DSNO相同且重復(fù)的記錄 SELECT * FROM EM5_PIPE_PREFAB WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D -D相當(dāng)于First,Second WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND EM5_PIPE_PREFAB.DSNO=D.DSNO); - 執(zhí)行下面SQL語句后就可以刪除所有DRAWING和DSNO相同且重復(fù)的記錄 DELETE FROM EM5_PIPE_PREFAB WHERE ROWID!=(SELECT MAX(R

9、OWID) FROM EM5_PIPE_PREFAB D WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND EM5_PIPE_PREFAB.DSNO=D.DSNO); 10返回表中N，M條記錄： 取得某列中第N大的行 select column_name from (select table_name.*,dense_rank() over (order by column desc) rank from table_name) where rank = &N； 假如要返回前5條記錄： select * from tablename where rownu

10、m6;(或是rownum = 5 或是rownum != 6) 假如要返回第5-9條記錄： select * from tablename where and rownum10 minus select * from tablename where and rownum5 order by name 選出結(jié)果后用name排序顯示結(jié)果。(先選再排序) 注意：只能用以上符號(、,=,=,Between.and。由于rownum是一個總是從1開始的偽列，Oracle 認(rèn)為這種條件 不成立，查不到記錄. 另外，這個方法更快： select * from ( select rownum r,a from

11、 yourtable where rownum 10 這樣取出第11-20條記錄!(先選再排序再選) 要先排序再選則須用select嵌套：內(nèi)層排序外層選。 rownum是隨著結(jié)果集生成的，一旦生成，就不會變化了；同時,生成的結(jié)果是依次遞加的，沒有1就永遠(yuǎn)不會有2! rownum 是在 查詢集合產(chǎn)生的過程中產(chǎn)生的偽列，并且如果where條件中存在 rownum 條件的話，則: 1： 假如 判定條件是常量，則： 只能 rownum = 1, SPOOL ON.SQL SQL SELECT ALTER VIEW |TNAME| COMPILE; FROM TAB; SQL SPOOL OFF 然后執(zhí)

12、行ON.SQL即可。 SQL ON.SQL 當(dāng)然，授權(quán)和創(chuàng)建同義詞也可以快速進行，如： SQL SELECT GRANT SELECT ON |TNAME| TO USERNAME; FROM TAB; SQL SELECT CREATE SYNONYM |TNAME| FOR USERNAME.|TNAME|; FROM TAB; 12讀寫文本型操作系統(tǒng)文件- 在PL/SQL 3.3以上的版本中，UTL_FILE包允許用戶通過PL/SQL讀寫操作系統(tǒng)文件。如下： DECALRE FILE_HANDLE UTL_FILE.FILE_TYPE; BEGIN FILE_HANDLE:=UTL_FI

13、LE.FOPEN( C:,TEST.TXT,A); UTL_FILE.PUT_LINE(FILE_HANDLE,HELLO,ITS A TEST TXT FILE); UTL_FILE.FCLOSE(FILE_HANDLE); END; 13在數(shù)據(jù)庫觸發(fā)器中使用列的新值與舊值 - 在數(shù)據(jù)庫觸發(fā)器中幾乎總是要使用觸發(fā)器基表的列值，如果某條語句需要某列修改前的值，使用:OLD就可以了，使用某列修改后的新值，用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。 14數(shù)據(jù)庫文件的移動方法 當(dāng)想將數(shù)據(jù)庫文件移動到另外一個目錄下時，可以用ALTER DATABASE命令來移動(比AL

14、TER TABLESPACE適用性強)： 1. 使用SERVER MANAGER關(guān)閉實例. SVRMGR connect internal; SVRMGR shutdown; SVRMGR exit; 2. 使用操作系統(tǒng)命令來移動數(shù)據(jù)庫文件位置(假設(shè)這里操作系統(tǒng)為SOLARIS 2.6). 在UNIX中用 mv命令可以把文件移動到新的位置， #mv /ora13/orarun/document.dbf /ora12/orarun 3. 裝載數(shù)據(jù)庫并用alter database命令來改變數(shù)據(jù)庫中的文件名. SVRMGR connect internal; SVRMGR startup moun

15、t RUN73; SVRMGR alter database rename file / ora13/orarun/document.dbf / ora12/orarun/document.dbf; 4. 啟動實例. SVRMGR alter database open; 15連接查詢結(jié)果： 表a 列 a1 a2 記錄 1 a 1 b 2 x 2 y 2 z 用select能選成以下結(jié)果: 1 ab 2 xyz 下面有兩個例子： 1.使用pl/sql代碼實現(xiàn)，但要求你組合后的長度不能超出oracle varchar2長度的限制 create or replace type strings_ta

16、ble is table of varchar2(20); / create or replace function merge (pv in strings_table) return varchar2 is ls varchar2(4000); begin for i in 1.pv.count loop ls := ls | pv(i); end loop; return ls; end; / create table t (id number,name varchar2(10); insert into t values(1,Joan); insert into t values(1,

17、Jack); insert into t values(1,Tom); insert into t values(2,Rose); insert into t values(2,Jenny); column names format a80; select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table) names from (select distinct id from t) t0; drop type strings_table; drop function merge;

18、 drop table t; 2.純粹用sql： 表dept, emp 要得到如下結(jié)果 deptno, dname, employees - 10, accounting, clark;king;miller 20, research, smith;adams;ford;scott;jones 30, sales, allen;blake;martin;james;turners 每個dept的employee串起來作為一條記錄返回 This example uses a max of 6, and would need more cut n pasting to do more than t

19、hat： SQL select deptno, dname, emps 2 from ( 3 select d.deptno, d.dname, rtrim(e.ename |, | 4 lead(e.ename,1) over (partition by d.deptno 5 order by e.ename) |, | 6 lead(e.ename,2) over (partition by d.deptno 7 order by e.ename) |, | 8 lead(e.ename,3) over (partition by d.deptno 9 order by e.ename)

20、|, | 10 lead(e.ename,4) over (partition by d.deptno 11 order by e.ename) |, | 12 lead(e.ename,5) over (partition by d.deptno 13 order by e.ename), ) emps, 14 row_number () over (partition by d.deptno 15 order by e.ename) x 16 from emp e, dept d 17 where d.deptno = e.deptno 18 ) 19 where x = 1 20 / D

21、EPTNO DNAME EMPS - - - 10 ACCOUNTING CLARK, KING, MILLER 20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 16在Oracle中建一個編號會自動增加的字段,以利于查詢 1、建立序列： CREATE SEQUENCE checkup_no_seq NOCYCLE MAXVALUE 9999999999 START WITH 2; 2、建立觸發(fā)器： CREATE OR REPLACE TRIGGER set_checkup_no BEFORE INSERT ON checkup_history FOR EACH ROW DECLARE next_checkup_no NUMBER; BEGIN -Get the next checkup number from the sequen