第一篇：oracle復制表的sql語句

oracle復制表的sql語句

如下，表a是數(shù)據(jù)庫中已經存在的表，b是準備根據(jù)表a進行復制創(chuàng)建的表：

1、只復制表結構的sql

create table b as select * from a where 1<>12、即復制表結構又復制表中數(shù)據(jù)的sql

create table b as select * from a3、復制表的制定字段的sql

create table b as select row_id,name,age from a where 1<>1//前提是row_id,name,age都是a表的列

4、復制表的指定字段及這些指定字段的數(shù)據(jù)的sql

create table b as select row_id,name,age from a

以上語句雖然能夠很容易的根據(jù)a表結構復制創(chuàng)建b表，但是a表的索引等卻復制不了，需要在b中手動建立。

5、insert into 會將查詢結果保存到已經存在的表中

insert into t2(column1, column2,....)select column1, column2,....from t1rm_data001.dbf'

第二篇：oracle維護常用sql語句(主要)

1、oracle表空間利用率

SELECT UPPER(F.TABLESPACE_NAME)“表空間名”，D.TOT_GROOTTE_MB “表空間大小(M)”，D.TOT_GROOTTE_MBF.TOTAL_BYTES)D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' “使用比(%)”，F(xiàn).TOTAL_BYTES “空閑空間(M)”，F(xiàn).MAX_BYTES “最大塊(M)”

FROM(SELECT TABLESPACE_NAME，ROUND(SUM(BYTES)/(1024 * 1024), 2)TOTAL_BYTES，ROUND(MAX(BYTES)/(1024 * 1024), 2)MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)F，(SELECT DD.TABLESPACE_NAME，ROUND(SUM(DD.BYTES)/(1024 * 1024), 2)TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME)D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY F.TABLESPACE_NAME;查詢結果顯示：

/

非系統(tǒng)表空間使用和(M)

select sum(“已使用空間(M)”)“已使用空間(M)和” from(SELECT UPPER(F.TABLESPACE_NAME)“表空間名”，D.TOT_GROOTTE_MB “表空間大小(M)”，D.TOT_GROOTTE_MBF.TOTAL_BYTES)/ D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' “使用比(%)”，F(xiàn).TOTAL_BYTES “空閑空間(M)”，F(xiàn).MAX_BYTES “最大塊(M)”

FROM(SELECT TABLESPACE_NAME，ROUND(SUM(BYTES)/(1024 * 1024), 2)TOTAL_BYTES，ROUND(MAX(BYTES)/(1024 * 1024), 2)MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)F，(SELECT DD.TABLESPACE_NAME，ROUND(SUM(DD.BYTES)/(1024 * 1024), 2)TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME)D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME and F.TABLESPACE_NAME NOT IN('SYSAUX','USERS','UNDOTBS1','SYSTEM'))

2、查詢當前用戶默認表空間的使用情況

select tablespacename,sum(totalContent),sum(usecontent),sum(sparecontent),avg(sparepercent)from(SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes-sum(nvl(a.bytes,0)))as usecontent,sum(nvl(a.bytes,0))as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent FROM dba_free_space a,dba_data_files b WHERE a.file_id=b.file_id and b.tablespace_name =(select default_tablespace from dba_users where username = user)

group by b.tablespace_name,b.file_name,b.file_id,b.bytes)

GROUP BY tablespacename;

3、查詢所有用戶表使用大小的前三十名

select * from(select segment_name,bytes from dba_segments where owner = USER order by bytes desc)where rownum <= 30

4、查詢單張表的使用情況 select segment_name,bytes from dba_segments where segment_name = 'RE_STDEVT_FACT_DAY' and owner = USER

RE_STDEVT_FACT_DAY是您要查詢的表名稱

5、計算每個用戶占用的磁盤空間 select owner,sum(bytes)/1024/1024/1024 “Space(G)” from dba_segments group by owner order by 2;

6、計算某個用戶占用的磁盤空間

select owner,sum(bytes)/1024/1024/1024 “Space(G)” from dba_segments where owner='LIAOJL' group by owner;

7、查看表空間的名稱及大?。ǚ峙浯笮。?

select t.tablespace_name, round(sum(bytes/(1024*1024)),0)ts_size

from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

8、查看表空間物理文件的名稱及大小（分配大?。?

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0)total_space from dba_data_files order by tablespace_name;

9、查看回滾段名稱及大小: select segment_name, tablespace_name, r.status,(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+)order by segment_name;

10、如何查看某個回滾段里面，跑的什么事物或者正在執(zhí)行什么sql語句: select d.sql_text,a.name from v$rollname a,v$transaction b,v$session c,v$sqltext d where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=d.address and c.sql_hash_value=d.hash_value and a.usn==2;(備注：你要看哪個，就把usn=?寫成幾就行了)

查看控制文件:

SQL>select * from v$controlfile;

查看日志文件:

SQL> col member format a50 SQL>select * from v$logfile;

11、如何查看當前SQL*PLUS用戶的sid和serial#:

SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');

12、怎樣識別IO競爭和負載平衡:

SQL>col 文件名 format a35 SQL>select df.name 文件名, fs.phyrds 讀次數(shù), fs.phywrts 寫次數(shù),(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds))讀時間,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts))寫時間 from v$datafile df, v$filestat fs where df.file#=fs.file# order by df.name

13、查看有哪些用戶連接

select s.osuser os_user_name, decode(sign(48command), 1, to_char(command), 'Action Code #' || to_char(command))action, status session_status，s.osuser os_user_name,s.sid,p.spid ,s.serial# serial_num,nvl(s.username, '[Oracle process]')user_name,s.terminal terminal,s.program program,st.value criteria_value from v$sesstat st，v$session s , v$process p

where st.sid = s.sid and

st.statistic# = to_number('38')and

('ALL' = 'ALL' or s.status = 'ALL')and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

21、監(jiān)控當前數(shù)據(jù)庫誰在運行什么SQL語句: SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;

Column type format a8

22、查詢每個用戶執(zhí)行過的sql語句 select B.USER_NAME,C.OSUSER,C.MACHINE,C.PROGRAM,C.MODULE, A.USER_IO_WAIT_TIME,B.HASH_VALUE,C.SERVICE_NAME, A.SQL_TEXT from v$sqlarea a, v$open_cursor b,v$session c where A.ADDRESS=B.ADDRESS and C.USERNAME=B.USER_NAME order by C.OSUSER,B.USER_NAME

第三篇：Oracle SQL精妙SQL語句講解

SQL*PLUS界面：

登錄：輸入SQLPLUS回車；輸入正確的ORACLE用戶名并回車；輸入用戶口令并回車，顯示提示符：SQL>

退出：輸入EXIT即可。

2）命令的編輯與運行：

在命令提示符后輸入SQL命令并運行，以分號結束輸入；以斜杠結束輸入；以空行結束輸入；

利用SQL緩沖區(qū)進行PL/SQL塊的編輯和運行；

利用命令文件進行PL/SQL塊的編輯和運行。

數(shù)據(jù)庫查詢

用SELECT語句從表中提取查詢數(shù)據(jù)。語法為

SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

說明：SELECT子句用于指定檢索數(shù)據(jù)庫的中哪些列，F(xiàn)ROM子句用于指定從哪一個表或視圖中檢索數(shù)據(jù)。

SELECT中的操作符及多表查詢WHERE子句。(LIKE,IS,…)

WHERE子句中的條件可以是一個包含等號或不等號的條件表達式，也可以是一個含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比較運算符的條件式，還可以是由單一的條件表達通過邏輯運算符組合成復合條件。

ORDER BY 子句

ORDER BY 子句使得SQL在顯示查詢結果時將各返回行按順序排列，返回行的排列順序由ORDER BY 子句指定的表達式的值確定。

連接查詢

利用SELECT語句進行數(shù)據(jù)庫查詢時，可以把多個表、視圖的數(shù)據(jù)結合起來，使得查詢結果的每一行中包含來自多個表達式或視圖的數(shù)據(jù)，這種操作被稱為連接查詢。

連接查詢的方法是在SELECT命令的FROM子句中指定兩個或多個將被連接查詢的表或視圖，并且在WHERE子句告訴ORACLE如何把多個表的數(shù)據(jù)進行合并。根據(jù)WHERE子句中的條件表達式是等還是不等式，可以把連接查詢分為等式連接和不等式連接。

子查詢

如果某一個SELECT命令（查詢1）出現(xiàn)在另一個SQL命令（查詢2）的一個子句中，則稱查詢1是查詢2的子查詢。

基本數(shù)據(jù)類型（NUMBER,VARCHAR2,DATE）O

RACEL支持下列內部數(shù)據(jù)類型：

VARCHAR2 變長字符串，最長為2000字符。

NUMBER 數(shù)值型。

LONG 變長字符數(shù)據(jù)，最長為2G字節(jié)。

DATE 日期型。

RAW 二進制數(shù)據(jù)，最長為255字節(jié)。

LONG RAW 變長二進制數(shù)據(jù)，最長為2G字節(jié)。

ROWID 二六進制串，表示表的行的唯一地址。

CHAR 定長字符數(shù)據(jù)，最長為255。

常用函數(shù)用法：

一個函數(shù)類似于一個算符，它操作數(shù)據(jù)項，返回一個結果。函數(shù)在格式上不同于算符，它個具有變元，可操作0個、一個、二個或多個變元，形式為：

函數(shù)名（變元，變元，…）

函數(shù)具有下列一般類形：

單行函數(shù)

分組函數(shù)

單行函數(shù)對查詢的表或視圖的每一行返回一個結果行。它有數(shù)值函數(shù)，字符函數(shù)，日期函數(shù)，轉換函數(shù)等。

分組函數(shù)返回的結果是基于行組而不是單行，所以分組函數(shù)不同于單行函數(shù)。在許多分組函數(shù)中可有下列選項：

DISTRNCT 該選項使分組函數(shù)只考慮變元表達式中的不同值。

ALL該選項使分組函數(shù)考慮全部值，包含全部重復。

全部分組函數(shù)（除COUNT（*）外）忽略空值。如果具有分組函數(shù)的查詢，沒有返回行或只有空值（分組函數(shù)的變元取值的行），則分組函數(shù)返回空值。

5、數(shù)據(jù)操縱語言命令：

數(shù)據(jù)庫操縱語言（DML）命令用于查詢和操縱模式對象中的數(shù)據(jù)，它不隱式地提交當前事務。它包含UPDATE、INSERT、DELETE、EXPLAIN PLAN、SELECT和LOCK TABLE 等命令。下面簡單介紹一下：

1)UPDATE tablename SET {column1=expression1,column2=expression2,…} WHERE {conditions};

例如：S QL>UPDATE EMP

SET JOB =’MANAGER’

WHERE ENAME=’MAPTIN’;

SQL >SELECT * FROM EMP;

UPDATE子句指明了要修改的數(shù)據(jù)庫是EMP,并用WHERE子句限制了只對名字(ENAME)為’MARTIN’的職工的數(shù)據(jù)進行修改,SET子句則說明修改的方式,即把’MARTION’的工作名稱(JOB)改為’MARAGER’.2)INSERT INTO tablename {column1,column2,…} VALUES {expression1,expression2,…};

例如：SQL>SELECT INTO DEPT（DNAME，DEPTNO）

VALUES（‘ACCOUNTING’，10）

3)DELETE FROM tablename WHERE {conditions};

例如：SQL>DELETE FROM EMP

WHERE EMPNO = 7654;

DELETE命令刪除一條記錄,而且DELETE命令只能刪除整行,而不能刪除某行中的部分數(shù)據(jù).4）事務控制命令

提交命令(COMMIT):可以使數(shù)據(jù)庫的修改永久化.設置AUTOCOMMIT為允許狀態(tài):SQL >SET AUTOCOMMIT ON;

回滾命令(ROLLBACK):消除上一個COMMIT命令后的所做的全部修改,使得數(shù)據(jù)庫的內容恢復到上一個COMMIT執(zhí)行后的狀態(tài).使用方法是:

SQL>ROLLBACK;

創(chuàng)建表、視圖、索引、同義詞、用戶。、表是存儲用戶數(shù)據(jù)的基本結構。

建立表主要指定義下列信息：

列定義

完整性約束

表所在表空間

存儲特性

可選擇的聚集

從一查詢獲得數(shù)據(jù)

語法如下：CREATE TABLE tablename

(column1 datatype [DEFAULT expression] [constraint], column1 datatype [DEFAULT expression] [constraint], ……）

[STORAGE子句] [其他子句…]；

例如：

SQL>CREATE TABLE NEW_DEPT(DPTNO NUMBER(2), DNAME CHAR(6), LOC CHAR(13);

更改表作用：

增加列

增加完整性約束

重新定義列（數(shù)據(jù)類型、長度、缺省值）

修改存儲參數(shù)或其它參數(shù)

使能、使不能或刪除一完整性約束或觸發(fā)器

顯式地分配一個范圍

2）、視圖

視圖是一個邏輯表，它允許操作者從其它表或視圖存取數(shù)據(jù)，視圖本身不包含數(shù)據(jù)。視圖所基于的表稱為基表。

引入視圖有下列作用：

提供附加的表安全級，限制存取基表的行或/和列集合。

隱藏數(shù)據(jù)復雜性。

為數(shù)據(jù)提供另一種觀點。

促使ORACLE的某些操作在包含視圖的數(shù)據(jù)庫上執(zhí)行，而不在另一個數(shù)據(jù)庫上執(zhí)行。

3）、索引

索引是種數(shù)據(jù)庫對象。對于在表或聚集的索引列上的每一值將包含一項，為行提供直接的快速存取。在下列情況ORACLE可利用索引改進性能：

按指定的索引列的值查找行。

按索引列的順序存取表。

建立索引： CREATE [UNIQUE] INDEX indexname ON tablename(column ,。。)；

例如：SQL>CREAT INDEX IC_EMP

ON CLUSTER EMPLOYEE

4）、同義詞

同義詞：為表、視圖、序列、存儲函數(shù)、包、快照或其它同義詞的另一個名字。使用同義詞為了安全和方便。對一對象建立同義詞可有下列好處：

引用對象不需指出對象的持有者。

引用對象不需指出它所位于的數(shù)據(jù)庫。

為對象提供另一個名字。

建立同義詞：

CREATE SYNONYM symnon_name FOR [username.]tablename;

例如：CREAT PUBLIC SYNONYM EMP

FOR SCOTT.EMP @SALES

5）、用戶

CREATE USER username IDENTIFIED BY password;

例如：SQL>CREATE USER SIDNEY

IDENTIFIED BY CARTON;

Oracle擴展PL/SQL簡介

PL/SQL概述。

PL/SQL是Oracle對SQL規(guī)范的擴展，是一種塊結構語言，即構成一個PL/SQL程序的基本單位（過程、函數(shù)和無名塊）是邏輯塊，可包含任何數(shù)目的嵌套了快。這種程序結構支持逐步求精方法解決問題。一個塊（或子塊）將邏輯上相關的說明和語句組合在一起，其形式為：

DECLARE

---說明

BEGIN

---語句序列

EXCEPTION

---例外處理程序

END；

它有以下優(yōu)點:

支持SQL;

生產率高;

性能好;

可稱植性;

與ORACLE集成.PL/SQL體系結構

PL/SQL運行系統(tǒng)是種技術，不是一種獨立產品，可認為這種技術是PL/SQL塊和子程序的一種機，它可接收任何有效的PL/SQL塊或子程序。如圖所示：

PL/SQL機可執(zhí)行過程性語句，而將SQL語句發(fā)送到ORACLE服務器上的SQL語句執(zhí)行器。在ORACLE預編譯程序或OCI程序中可嵌入無名的PL/SQL塊。如果ORACLE具有PROCEDURAL選件，有名的PL/SQL塊（子程序）可單獨編譯，永久地存儲在數(shù)據(jù)庫中，準備執(zhí)行。

PL/SQL基礎：

PL/SQL有一字符集、保留字、標點、數(shù)據(jù)類型、嚴密語法等，它與SQL有相同表示，現(xiàn)重點介紹。

1）、數(shù)據(jù)類型：如下表所示

數(shù)據(jù)類型 子類型

純量類型 數(shù)值 BINARY_INTEGER NATURAL,POSITIVE

NUMBER DEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT

字符 CHAR CHARACTER,STRING

VARCHAR2 VARCHAR

LONG

LONG RAW

RAW

RAWID

邏輯 BOOLEAN

日期 DATE

組合 類型 記錄 RECORD

表 TABLE

2）、變量和常量

在PL/SQL程序中可將值存儲在變量和常量中，當程序執(zhí)行時，變量的值可以改變，而常量的值不能改變。

3）、程序塊式結構：

DECLARE

變量說明部分；

BEGIN

執(zhí)行語句部分；

[EXCEPTION

例外處理部分；] END;控制語句：

分支語句：

IF condition THEN

Sequence_of_statements;

END IF;

IF condition THEN

Sequence_of_statement1;

ELSE

Sequence_of_statement2;

END IF;

IF condition1 THEN

Sequence_of_statement1;

ELSIF condition2 THEN

Sequence_of_statement2;

ELSIF condition3 THEN

Sequence_of_statement3;

END IF;

循環(huán)語句：

LOOP

Sequence_of_statements;

IF condition THEN

EXIT;

END IF;

END LOOP;

WHILE condition LOOP

Sequence_of_statements;

END LOOP;

FOR counter IN lower_bound..higher_bound LOOP

Sequence_of_statements;

END LOOP;

子程序：

存儲過程：

CREATE PROCEDURE 過程名(參數(shù)說明1，參數(shù)說明2，[局部說明]

BEGIN

執(zhí)行語句；

END 過程名；

。)IS。

存儲函數(shù)：

CREATE FUNCTION 函數(shù)名(參數(shù)說明1，參數(shù)說明2。。)RETURN 類型 IS [局部說明] BEGIN

執(zhí)行語句；

END 函數(shù)名

Oracle SQL精妙SQL語句講解

好東西，大家趕緊收藏吧~~~

--行列轉換 行轉列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc

SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

--行列轉換 列轉行

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code，t.rn q，decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal

FROM(SELECT a.*, b.rn

FROM t_change_cl a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

--行列轉換 行轉列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q

FROM(SELECT a.card_code，a.q，row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn

FROM t_change_lc_comma a)t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code

AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;SELECT t.card_code，substr(t.q，instr(';' || t.q, ';', 1, rn)，instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q

FROM(SELECT a.card_code, a.q, b.rn

FROM t_change_cl_comma a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b

WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

--實現(xiàn)一條記錄根據(jù)條件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));

SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;

INSERT ALL WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一個語句實現(xiàn) DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

SELECT * FROM t_mg;

MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN

UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN

INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/刪除重復記錄

DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;

INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);

SELECT b.code, b.NAME

FROM(SELECT a.code，a.NAME，row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn

FROM t_dup a)b WHERE b.rn > 1;

--IN/EXISTS的不同適用環(huán)境--t_orders.customer_id有索引 SELECT a.*

FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a.*

FROM t_employees a WHERE EXISTS(SELECT 1

FROM t_orders b

WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);

--t_employees.department_id有索引 SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a.*

FROM t_employees a WHERE a.department_id = 10

AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);

--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual

CONNECT BY ROWNUM <=10;

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2006-09-21','yyyy-mm-dd');

--不建議使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';

--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

SELECT * FROM t_loop;

--逐行提交 DECLARE BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

COMMIT;

END LOOP;END;

--模擬批量提交 DECLARE

v_count NUMBER;BEGIN

FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

v_count := v_count + 1;

IF v_count >= 100 THEN

COMMIT;

END IF;

END LOOP;

COMMIT;END;

--真正的批量提交 DECLARE

CURSOR cur IS

SELECT * FROM user_objects;

TYPE rec IS TABLE OF user_objects%ROWTYPE;

recs rec;BEGIN

OPEN cur;

WHILE(TRUE)LOOP

FETCH cur BULK COLLECT

INTO recs LIMIT 100;

--forall 實現(xiàn)批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);

COMMIT;

EXIT WHEN cur%NOTFOUND;

END LOOP;

CLOSE cur;END;

--悲觀鎖定/樂觀鎖定

DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

--常見的實現(xiàn)邏輯，隱含bug DECLARE

v_cnt NUMBER;BEGIN

--這里有并發(fā)性的bug

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation

v_cnt := v_cnt + 1;

INSERT INTO t_lock(ID)VALUES(v_cnt);

COMMIT;END;

--高并發(fā)環(huán)境下，安全的實現(xiàn)邏輯 DECLARE

v_cnt NUMBER;BEGIN

--對指定的行取得lock

SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;

--在有l(wèi)ock的情況下繼續(xù)下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation

v_cnt := v_cnt + 1;

INSERT INTO t_lock(ID)VALUES(v_cnt);

COMMIT;--提交并且釋放lock END;

--硬解析/軟解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);

SELECT * FROM t_hard;

DECLARE

sql_1

VARCHAR2(200);BEGIN

--hard parse

--java中的同等語句是 Statement.execute()

FOR i IN 1..1000 LOOP

sql_1 := 'insert into t_hard(id)values(' || i || ')';

EXECUTE IMMEDIATE sql_1;

END LOOP;

COMMIT;

--soft parse

--java中的同等語句是 PreparedStatement.execute()

sql_1

:= 'insert into t_hard(id)values(:id)';

FOR i IN 1..1000 LOOP

EXECUTE IMMEDIATE sql_1

USING i;

END LOOP;

COMMIT;END;

--正確的分頁算法

SELECT *

FROM(SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a

WHERE ROWNUM <= 500)WHERE rn > 480;

--分頁算法(why not this one)SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

--分頁算法(why not this one)SELECT b.*

FROM(SELECT a.*, ROWNUM rn

FROM t_employees a

WHERE ROWNUM < = 500

ORDER BY first_name)b WHERE b.rn > 480;

--OLAP--小計合計 SELECT CASE

WHEN a.deptno IS NULL THEN

'合計'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN

'小計'

ELSE

'' || a.deptno

END deptno，a.empno，a.ename，SUM(a.sal)total_sal

FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分組排序 SELECT a.deptno，a.empno，a.ename，a.sal，--可跳躍的rank

rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1，--密集型rank

dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2，--不分組排序

rank()over(ORDER BY sal DESC)r3

FROM scott.emp a

ORDER BY a.deptno,a.sal DESC;

--當前行數(shù)據(jù)和前/后n行的數(shù)據(jù)比較 SELECT a.empno，a.ename，a.sal，--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1，--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3

FROM scott.emp a ORDER BY a.sal DESC;

一、數(shù)據(jù)表設計圖

二、創(chuàng)建語句

/*================*/ /* DBMS name: ORACLE Version 9i */ /* Created on: 2008-11-10 23:39:24 */ /*================*/

alter table “emp”

drop constraint FK_EMP_REFERENCE_DEPT;

drop table “dept” cascade constraints;

drop table “emp” cascade constraints;

drop table “salgrade” cascade constraints;

/*================*/ /* Table: “dept” */ /*================*/

create table dept(deptno NUMBER(11)not null, dname VARCHAR2(15)not null, loc VARCHAR2(15)not null, constraint PK_DEPT primary key(deptno));

/*================*/ /* Table: “emp” */ /*================*/

create table emp(empno NUMBER(11)not null, deptno NUMBER(11), ename VARCHAR2(15)not null, sal NUMBER(11)not null, job VARCHAR2(15)not null, mgr NUMBER(11)not null, hirdate DATE not null, comm NUMBER(11)not null, constraint PK_EMP primary key(empno));

/*================*/ /* Table: salgrade */ /*================*/

create table salgrade(grade NUMBER(11)not null, losal NUMBER(11)not null, hisal NUMBER(11)not null, constraint PK_SALGRADE primary key(grade));

alter table emp add constraint FK_EMP_REFERENCE_DEPT foreign key(deptno)references dept(deptno);

三、測試要求及語句

/** *公司工資最高的員工列表 子查詢 */

select t.ename,t.sal from emp t where t.sal =(select max(sal)from emp)

/** *查詢每一個員工的經理人及自己的名字 */

select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno)

/** *查詢公司平均薪水的等級 */

select s.grade from salgrade s where(select avg(t.sal)from emp t)between s.losal and s.hisal

/** *求部門中那些人的工資最高 */

select d.dname,ename,sal from(select t.deptno,ename,sal from(select deptno,max(sal)as max_sal from emp group by deptno)e join emp t on(e.deptno = t.deptno and t.sal = max_sal))et join dept d on(d.deptno = et.deptno)

/** *查詢部門平均薪水的等級 */

select d.dname,avg_sal,grade from(select deptno,avg_sal,grade from(select deptno,avg(sal)as avg_sal from emp group by deptno)e join salgrade s on(e.avg_sal between s.losal and s.hisal))es join dept d on(es.deptno = d.deptno)

/** *求部門的平均薪水等級 */

select deptno,avg(grade)from(select deptno,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal))t group by t.deptno

/** * 求那些人是經理人 */

select ename from emp e where empno in(select distinct mgr from emp)

/** *不準用組函數(shù) 求薪水的最高值 */

select ename from emp where empno not in(select distinct e1.empno from emp e1 join emp e2 on(e1.sal

/** *平均薪水最高的部門編號與名稱 */

select d.deptno,dname from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join dept d on(d.deptno = t1.deptno)where avg_sal =(select max(avg_sal)from(select deptno,avg(sal)avg_sal from emp group by deptno)t2)

/** *求平均薪水的等級最低的部門名稱 */

select dname from dept d where d.deptno in(select deptno from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t2 where t2.grade =(select min(grade)from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t3))

/** *求部門經理人中平均薪水最低的部門名稱 */

select d.dname,t1.avg_sal from dept d join(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno)t1 on(d.deptno = t1.deptno)where avg_sal =(select min(avg_sal)from(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno))/** *求必普通員工的最高薪水還要高的經理人名稱 */

select ename from(select e2.ename,e2.empno,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t where t.sal >(select max(e.sal)from emp e where e.empno not in(select e1.mgr from emp e1 join emp e2 on(e1.mgr = e2.empno)))

/** *求薪水最高的第6名到10名雇員 */

SELECT * FROM(SELECT A.*, ROWNUM RN FROM(SELECT * FROM(select e1.ename,e1.sal from emp e1 order by e1.sal desc))A WHERE ROWNUM <= 10)WHERE RN >= 6

第四篇：Oracle SQL精妙SQL語句講解

好東西，大家趕緊收藏吧~~~ 轉自junsansi

--行列轉換 行轉列

DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc

SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

--行列轉換 列轉行

DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code，t.rn q，decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal FROM(SELECT a.*, b.rn

FROM t_change_cl a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

--行列轉換 行轉列 合并

DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q FROM(SELECT a.card_code，a.q，row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn

FROM t_change_lc_comma a)t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code

AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;

SELECT t.card_code，substr(t.q，instr(';' || t.q, ';', 1, rn)，instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q FROM(SELECT a.card_code, a.q, b.rn

FROM t_change_cl_comma a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b

WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

--實現(xiàn)一條記錄根據(jù)條件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));

SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;

INSERT ALL WHEN(c1 IN('a1','a3'))THEN

INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

--如果存在就更新，不存在就插入用一個語句實現(xiàn) DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

SELECT * FROM t_mg;

MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN INSERT(code, NAME)VALUES(b.code, b.NAME);

--抽取/刪除重復記錄

DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);

SELECT b.code, b.NAME FROM(SELECT a.code，a.NAME，row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn

FROM t_dup a)b WHERE b.rn > 1;

--IN/EXISTS的不同適用環(huán)境--t_orders.customer_id有索引 SELECT a.* FROM t_employees a WHERE a.employee_id IN

(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a.* FROM t_employees a WHERE EXISTS(SELECT 1

FROM t_orders b

WHERE b.customer_id = 12

AND a.employee_id = b.sales_rep_id);

--t_employees.department_id有索引 SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM <=10;

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2006-09-21','yyyy-mm-dd');

--不建議使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-09-21';

--LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

SELECT * FROM t_loop;

--逐行提交 DECLARE BEGIN FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

COMMIT;END LOOP;END;

--模擬批量提交 DECLARE v_count NUMBER;BEGIN FOR cur IN(SELECT * FROM user_objects)LOOP

INSERT INTO t_loop VALUES cur;

v_count := v_count + 1;

IF v_count >= 100 THEN COMMIT;

END IF;END LOOP;COMMIT;END;

--真正的批量提交 DECLARE CURSOR cur IS

SELECT * FROM user_objects;TYPE rec IS TABLE OF user_objects%ROWTYPE;recs rec;BEGIN OPEN cur;WHILE(TRUE)LOOP

FETCH cur BULK COLLECT

INTO recs LIMIT 100;

--forall 實現(xiàn)批量

FORALL i IN 1..recs.COUNT

INSERT INTO t_loop VALUES recs(i);

COMMIT;

EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;

--悲觀鎖定/樂觀鎖定

DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

--常見的實現(xiàn)邏輯，隱含bug DECLARE v_cnt NUMBER;BEGIN--這里有并發(fā)性的bug SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;END;

--高并發(fā)環(huán)境下，安全的實現(xiàn)邏輯 DECLARE v_cnt NUMBER;BEGIN--對指定的行取得lock SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;--在有l(wèi)ock的情況下繼續(xù)下面的操作

SELECT MAX(ID)INTO v_cnt FROM t_lock;

--here for other operation v_cnt := v_cnt + 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;--提交并且釋放lock END;

--硬解析/軟解析

DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);

SELECT * FROM t_hard;

DECLARE sql_1 VARCHAR2(200);BEGIN--hard parse--java中的同等語句是 Statement.execute()FOR i IN 1..1000 LOOP

sql_1 := 'insert into t_hard(id)values(' || i || ')';

EXECUTE IMMEDIATE sql_1;END LOOP;COMMIT;

--soft parse--java中的同等語句是 PreparedStatement.execute()sql_1 := 'insert into t_hard(id)values(:id)';FOR i IN 1..1000 LOOP

EXECUTE IMMEDIATE sql_1

USING i;END LOOP;COMMIT;END;

--正確的分頁算法

SELECT * FROM(SELECT a.*, ROWNUM rn

FROM(SELECT * FROM t_employees ORDER BY first_name)a

WHERE ROWNUM <= 500)WHERE rn > 480;

--分頁算法(why not this one)SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

--分頁算法(why not this one)SELECT b.* FROM(SELECT a.*, ROWNUM rn

FROM t_employees a

WHERE ROWNUM < = 500

ORDER BY first_name)b WHERE b.rn > 480;--OLAP--小計合計 SELECT CASE

WHEN a.deptno IS NULL THEN

'合計'

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN

'小計'

ELSE

'' || a.deptno

END deptno，a.empno，a.ename，SUM(a.sal)total_sal FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

--分組排序 SELECT a.deptno，a.empno，a.ename，a.sal，--可跳躍的rank

rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1，--密集型rank

dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2，--不分組排序

rank()over(ORDER BY sal DESC)r3 FROM scott.emp a ORDER BY a.deptno,a.sal DESC;--當前行數(shù)據(jù)和前/后n行的數(shù)據(jù)比較 SELECT a.empno，a.ename，a.sal，--上面一行

lag(a.sal)over(ORDER BY a.sal DESC)lag_1，--下面三行

lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3 FROM scott.emp a ORDER BY a.sal DESC;

第五篇：查詢Oracle正在執(zhí)行和執(zhí)行過的SQL語句

查詢Oracle正在執(zhí)行和執(zhí)行過的SQL語句

--正在執(zhí)行的select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT

from v$session a, v$sqlarea bwhere a.sql_address = b.address

---執(zhí)行過的select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT

from v$sqlarea b

where b.FIRST_LOAD_TIME between '2009-10-15/09:24:47' and'2009-10-15/09:24:47' order by b.FIRST_LOAD_TIME

（此方法好處可以查看某一時間段執(zhí)行過的sql，并且 SQL_FULLTEXT 包含了完整的 sql 語句）其他

select OSUSER,PROGRAM,USERNAME,SCfrom V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS=B.ADDRESS AND A.SQL_HASH_VALUE=B.HASH_VALUE order by b.cpu_time desc

HEMANAME,B.Cpu_Time,STATUS,B.SQL_TEXTselect address, sql_text, piece

from v$session, v$sqltextwhere address = sql_address

--and machine = < you machine name >order by address, piece

查找前十條性能差的sql.SELECT * FROM(select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea

order BY disk_reads DESC)where ROWNUM<10;

查看占io較大的正在運行的sessionfacebook:http:// SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,、se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes FROM v$session se,v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC