oracle 常用command

Lunatic 整理

1. 刪除表的注意事項

在刪除一個表中的全部資料時，須使用TRUNCATE TABLE 表名;因為用DROP TABLE，DELETE * FROM 表名時，TABLESPACE資料表空間該表的佔用空間並未釋放，反覆幾次DROP，DELETE操作後，該TABLESPACE上百兆的空間就被耗光了。

2.having 子句的用法

　　having 子句對 group by 子句所確定的行組進行控制,having 子句條件中只允許涉及常量,聚組函數或group by 子句中的列.

3.外部聯結"+"的用法

　　外部聯結"+"按其在"="的左邊或右邊分左聯結和右聯結.若不帶"+"運算子的表中的一個行不直接匹配於帶"+"預算符的表中的任何行,則前者的行與後者中的一個空行相匹配並被返回.若二者均不帶’+’,則二者中無法匹配的均被返回.利用外部聯結"+",可以替代效率十分低下的 not in 運算,大大提高運行速度.例如,下面這條命令執行起來很慢

用外聯結提高表串連的查詢速度

在作表串連(常用於視圖)時，常使用以下方法來查詢資料:

SELECT PAY_NO, PROJECT_NAME

FROM A

WHERE A.PAY_NO NOT IN (SELECT PAY_

NO FROM B WHERE VALUE >=120000);

---- 但是若表A有10000條記錄，表B有10000條記錄，則要用掉30分鐘才能查完，主要因為NOT IN要進行一條一條的比較，共需要10000*10000次比較後，才能得到結果。該用外聯結後，可以縮短到1分左右的時間:

SELECT PAY_NO,PROJECT_NAME

FROM A,B

WHERE A.PAY_NO=B.PAY_NO(+)

AND B.PAY_NO IS NULL

AND B.VALUE >=12000;

4.set transaction 命令的用法

在執行大事務時,有時oracle會報出如下的錯誤:

ORA-01555:snapshot too old (rollback segment too small)

　　這說明oracle給此事務隨機分配的復原段太小了,這時可以為它指定一個足夠大的復原段,以確保這個事務的成功執行.例如

set transaction use rollback segment roll_abc;

delete from table_name where ...

commit;

　　復原段roll_abc被指定給這個delete事務,commit命令則在事務結束之後取消了復原段的指定.

5.資料庫重建應注意的問題

　　在利用import進行資料庫重建過程中,有些視圖可能會帶來問題,因為結構輸入的順序可能造成視圖的輸入先於它低層次表的輸入,這樣建立視圖就會失敗.要解決這一問題,可採取分兩步走的方法:首先輸入結構,然後輸入資料.命令舉例如下 (uesrname:jfcl,password:hfjf,host sting:ora1,資料檔案:expdata.dmp):

imp jfcl/hfjf@ora1 file=empdata.dmp rows=N

imp jfcl/hfjf@ora1 file=empdata.dmp full=Y buffer=64000

commit=Y ignore=Y

　　第一條命令輸入所有資料庫結構,但無記錄.第二次輸入結構和資料,64000位元組提交一次.ignore=Y選項保證第二次輸入既使對象存在的情況下也能成功.

select a.empno from emp a where a.empno not in

(select empno from emp1 where job=’SALE’);

　　倘若利用外部聯結,改寫命令如下:

select a.empno from emp a ,emp1 b

where a.empno=b.empno(+)

and b.empno is null

and b.job=’SALE’;

　　可以發現,運行速度明顯提高.

6.從已知表建立另一個表：

CREATE TABLE b

AS SELECT * (可以是表a中的幾列)

FROM a

WHERE a.column = ...;

7.尋找、重複資料刪除記錄:

法一: 用Group by語句 此尋找很快的

select count(num), max(name) from student --尋找表中num列重複的，列出重複的記錄數，並列出他的name屬性

group by num

having count(num) >1 --按num分組後找出表中num列重複，即出現次數大於一次

delete from student(上面Select的)

這樣的話就把所有重複的都刪除了。-----謹慎

法二:當表比較大(例如10萬條以上)時,這個方法的效率之差令人無法忍受,需要另想辦法:

---- 執行下面SQL語句後就可以顯示所有DRAWING和DSNO相同且重複的記錄

SELECT * FROM EM5_PIPE_PREFAB

WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D --D相當於First,Second

WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND

EM5_PIPE_PREFAB.DSNO=D.DSNO);

---- 執行下面SQL語句後就可以刪除所有DRAWING和DSNO相同且重複的記錄

DELETE FROM EM5_PIPE_PREFAB

WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D

WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND

EM5_PIPE_PREFAB.DSNO=D.DSNO);

8.返回表中[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 rownum<6;(或是rownum <= 5 或是rownum != 6)

假如要返回第5-9條記錄：

select * from tablename

where …

and rownum<10

minus

select * from tablename

where …

and rownum<５

order by name

　選出結果後用name排序顯示結果。(先選再排序)

注意：只能用以上符號(<、<=、!=)。

select * from tablename where rownum != 10;返回的是前９條記錄。

不能用：>,>=,=,Between...and。由於rownum是一個總是從1開始的偽列，Oracle 認為這種條件 不成立，查不到記錄.

另外，這個方法更快：

select * from (

select rownum r,a from yourtable

where rownum <= 20

order by name )

where r > 10

這樣取出第11-20條記錄!(先選再排序再選)

要先排序再選則須用select嵌套：內層排序外層選。

rownum是隨著結果集產生的，一旦產生，就不會變化了；同時,產生的結果是依次遞加的，沒有1就永遠不會有2!

rownum 是在 查詢集合產生的過程中產生的偽列，並且如果where條件中存在 rownum 條件的話，則:

1： 假如 判定條件是常量，則：

只能 rownum = 1, <= 大於1 的自然數， = 大於1 的數是沒有結果的， 大於一個數也是沒有結果的

即 當出現一個 rownum 不滿足條件的時候則 查詢結束 　　this is stop key!

2: 當判定值不是常量的時候

若條件是 = var , 則只有當 var 為1 的時候才滿足條件，這個時候不存在 stop key ,必須進行 full scan ,對每個滿足其他where條件的資料進行判定

選出一行後才能去選rownum=2的行……

9.快速編譯所有視圖

---- 當在把資料庫倒入到新的伺服器上後(資料庫重建)，需要將視圖重新編譯一遍，因為該資料表空間視圖到其它資料表空間的表的串連會出現問題，可以利用PL/SQL的語言特性，快速編譯。

SQL >SPOOL ON.SQL

SQL >SELECT ‘ALTER VIEW ‘||TNAME||’

COMPILE;’ FROM TAB;

SQL >SPOOL OFF

然後執行ON.SQL即可。

SQL >@ON.SQL

當然，授權和建立同義字也可以快速進行，如：

SQL >SELECT ‘GRANT SELECT ON ’

||TNAME||’ TO USERNAME;’ FROM TAB;

SQL >SELECT ‘CREATE SYNONYM

‘||TNAME||’ FOR USERNAME.’||TNAME||’;’ FROM TAB;

讓你的天空只有甜和美

遺忘———該怎麼流淚

10.讀寫文本型作業系統檔案

---- 在PL/SQL 3.3以上的版本中，UTL_FILE包允許使用者通過PL/SQL讀寫作業系統檔案。如下：

DECALRE

FILE_HANDLE UTL_FILE.FILE_TYPE;

BEGIN

FILE_HANDLE:=UTL_FILE.FOPEN(

‘C:\’,’TEST.TXT’,’A’);

UTL_FILE.PUT_LINE(FILE_HANDLE,’

HELLO,IT’S A TEST TXT FILE’);

UTL_FILE.FCLOSE(FILE_HANDLE);

END;

11.在資料庫觸發器中使用列的新值與舊值

---- 在資料庫觸發器中幾乎總是要使用觸發器基表的列值，如果某條語句需要某列修改前的值，使用:OLD就可以了，使用某列修改後的新值，用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。

12.資料庫檔案的移動方法

當想將資料庫檔案移動到另外一個目錄下時，可以用ALTER DATABASE命令來移動(比ALTER TABLESPACE適用性強)：

1. 使用SERVER MANAGER關閉執行個體.

SVRMGR > connect internal;

SVRMGR > shutdown;

SVRMGR >exit;

2. 使用作業系統命令來移動資料庫檔案位置(假設這裡作業系統為SOLARIS 2.6). 在UNIX中用 mv命令可以把檔案移動到新的位置，

#mv /ora13/orarun/document.dbf /ora12/orarun

3. 裝載資料庫並用alter database命令來改變資料庫中的檔案名稱.

SVRMGR > connect internal;

SVRMGR > startup mount RUN73;

SVRMGR > alter database rename file

> ‘/ ora13/orarun/document.dbf’

> ‘/ ora12/orarun/document.dbf’;

4. 啟動執行個體.

SVRMGR > alter database open;

13.串連查詢結果：

表a 列 a1 a2

記錄 1 a

1 b

2 x

2 y

2 z

用select能選成以下結果:

1 ab

2 xyz

下面有兩個例子：

1.使用pl/sql代碼實現，但要求你組合後的長度不能超出oracle varchar2長度的限制

create or replace type strings_table 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,'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;

drop table t;

2.純粹用sql：

表dept, emp

要得到如下結果

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 that：

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) ||', '||

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 /

DEPTNO DNAME EMPS

------- ----------- ------------------------------------------

10 ACCOUNTING CLARK, KING, MILLER

20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH

30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

14.在Oracle中建一個編號會自動增加的欄位,以利於查詢

1、建立序列：

CREATE SEQUENCE checkup_no_seq

NOCYCLE

MAXVALUE 9999999999

START WITH 2;

2、建立觸發器：

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 sequence

SELECT checkup_no_seq.NEXTVAL

INTO next_checkup_no

FROM dual;

--use the sequence number as the primary key

--for the record being inserted

:new.checkup_no := next_checkup_no;

END;

15.查看對象的依賴關係(比如視圖與表的引用)

查看視圖：dba_dependencies 記錄了相關的依賴關係

查東西不知道要查看哪個視圖時，可以在DBA_Objects裡看，

select object_name from dba_objects where object_name like '%ROLE%'(假如查看ROLE相關)

然後DESC一下就大體上知道了。

16.要找到某月中所有周五的具體日期

select to_char(t.d,'YY-MM-DD') from (

select trunc(sysdate, 'MM')+rownum-1 as d

from dba_objects

where rownum < 32) t

where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出當前月份的周五的日期

and trim(to_char(t.d, 'Day')) = '星期五'

--------

03-05-02

03-05-09

03-05-16

03-05-23

03-05-30

如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即為尋找當前

月份的前三個月中的每周五的日期。

讓你的天空只有甜和美

遺忘———該怎麼流淚

17 How to find the tablespace of a table?

SELECT tablespace_name

FROM all_tables

WHERE table_name = 'YOURTABLENAME';

18 How to remove duplicate rows from a table

If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:

DELETE FROM tablename

WHERE rowid not in (SELECT MIN(rowid)

FROM tablename

GROUP BY column1, column2, column3...);

--Here column1, column2, column3 constitute the identifying key for each record.

If the keys cannot be identified for the table, you may create a temporary table using the query

CREATE TABLE temptablename

AS SELECT DISTINCT *

FROM tablename;

Then drop the original table and rename the temp table to original tablename.

19 How to identify and remove bad 'child' records to enable / create a foreign key ("Parent Keys Not Found" error when you try to enable/create relation from child table to parent table!)

The records can be identified and removed using the query

DELETE FROM childtablename ct

WHERE NOT EXISTS (SELECT 'x' FROM parenttablename pt

WHERE ct.keycolumn1 = pt.keycolumn1 AND ct.keycolumn2 = pt.keycolumn2...)

Or if you need to provide the user with bad records you may change the DELETE to SELECT with column list.

20 What is my current session id?

The username, program, machine, terminal, session id, serial # and more can be found from the v$session view. This view has a column audsid. When you join this coulum to your userenv('sessionid') value, you get the session information for your current session. The query could be

SELECT USERNAME, SID, SERIAL#, PROGRAM FROM V$SESSION

WHERE AUDSID = USERENV('SESSOINID');

21 How to terminate a session?

Using the above method you find the SID and SERIAL# for the session you wish to terminate. Then issue the command

ALTER SYSTEM KILL SESSION 'sid, serial#';

Please note that the sid and serial# should be in quotes separated by a comma.

22 Which database am I connected to? As which user?

The database name can be found out from different views. The view which everyone has access is GLOBAL_NAME. The query is

SELECT GLOBAL_NAME FROM GLOBAL_NAME;

To find the user, from sqlplus you can do "SHOW USER".

23 What is the SGA size?

There are two simple ways to find this. The first is to invoke server manager (svrmgrl) and connect ineternal (or as any user). Issue the command "SHOW SGA". Or you can run the query "SELECT * FROM V$SGA;" from svrmgrl or sqlplus.

24 Where are my alert log and dump files written to?

The alert file is written to your BACKGROUND_DUMP_DEST. This variable is set in the config.ora (init.ora) file. You can find the current values of the dump directories from the database. Invoke svrmgrl and connect. Issue command "SHOW PARAMETER DUMP". The SHOW PARAMETER command can be used to find the value of any database parameter value. For example if you want to find the block size and block buffers, issue command "SHOW PARAMETER BLOCK".

25 How to create structure (no data) of a table from another table?

If you need to duplicate a table you can do a "create table newtablename as select * from tablename;" This will create the new table will all the data. If you need to create only the structure, add where condition "1=2" but in the new table every relation is false.

CREATE TABLE NEWTABLENAME AS

SELECT * FROM OLDTABLENAME WHERE 1=2;

26 How to increase the size of a tablespace?

The size of the tablespace is increased by changing the size of the underlying physical files. You can either add more space to the existing file by

ALTER DATABASE DATAFILE 'filename' RESIZE nn M;

OR you can add more physical datafiles to the tablespace by

ALTER TABLESPACE tablespacename

ADD DATAFILE 'filename' SIZE nn M;

Make sure you specify the full path name for the filename. Use the script tsinfo.sql to find the size and related physical files of a tablespace.

27 Is my database running in Archivelog mode? Where are the archived files written?

This can be found by invoking server manager (svrmgrl) and issuing the command "ARCHIVE LOG LIST".

To be continued ......

讓你的天空只有甜和美

遺忘———該怎麼流淚