如果有，会返回类似与如下的信息：

altersystem kill session '132,731';

altersystem kill session '275,15205';

altersystem kill session '308,206';

altersystem kill session '407,3510';

kill session：

执行alter system kill session '391,48398'(sid为391);

注意：应当注意对于sid在100以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以kill.

查看导致死锁的sql

/* Formatted on2010/8/18 0:06:11 (QP5 v5.115.810.9015) */

SELECT s.sid,q.sql_text

FROM v$sqltext q,v$session s

WHERE q.address = s.sql_addressAND s.sid = &sid -- 这个&sid 是第一步查询出来的

ORDERBY piece;

返回：

SID sql_TEXT

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

77 UPDATE PROFILE_USER SET ID=1,COMPANY_ID=2,CUSTOMER_ID=3,NAMED_INSURED_ID=4,LOGIN=5,ROLE_ID=6,PASSWORD=7,EMAIL=8,TIME_ZONE=9WHERE PROFILE_USER.ID=:34

查看谁锁了谁

/* Formatted on 2010/8/18 0:07:49 (QP5 v5.115.810.9015) */

SELECTs1.username

||'@'

||s1.machine

|| '( SID='

||s1.sid

|| ') is blocking '

||s2.username

||'@'

||s2.machine

|| '( SID='

||s2.sid

|| ') '

ASblocking_status

FROMv$lock l1,

v$sessions1,

v$lockl2,

v$sessions2

WHEREs1.sid = l1.sid

ANDs2.sid = l2.sid

ANDl1.BLOCK = 1

ANDl2.request > 0

ANDl1.id1 = l2.id1

AND l2.id2 =l2.id2;

或者

/*Formatted on 2010/8/18 0:03:46 (QP5 v5.115.810.9015) */

SELECT/*+ rule */

LPAD(' ',DECODE (l.xidusn,3,0))

||l.oracle_username

User_name,

o.owner,

o.object_name,

o.object_type,

s.sid,

s.serial#

FROMv$locked_object l,dba_objects o,v$session s

WHEREl.object_id = o.object_id AND l.session_id = s.sid

ORDERBY o.object_id,xidusn DESC

阻塞

相关概念

通常来讲，系统如果平时运行正常，突然会停止不动，多半是被阻塞（Blocked）住了。我们可以通过v$lock 这张视图，看查看阻塞的信息。

sql> descv$lock;

名称是否为空? 类型

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

ADDR RAW(4)

KADDR RAW(4)

SID NUMBER

TYPE VARCHAR2(2)

ID1 NUMBER

ID2 NUMBER

LMODE NUMBER

REQUEST NUMBER

CTIME NUMBER

BLOCK NUMBER

我们关注的比较多的是request和block 字段。

如果某个request列是一个非0值，那么它就是在等待一个锁。如果block列是1，这个SID 就持有了一个锁，并且阻塞别人获得这个锁。这个锁的类型由TYPE 字段定义。锁的模式有LMODE 字段定义，ID1和ID2 字段定义了这个锁的相关信息。ID1相同，就代表指向同一个资源。这样就有可能有加锁者和等待者。LMODE 的6中模式参考上面的TM锁类型表。

可以结合v$lock 和v$session 视图来查询相关的信息：

/*Formatted on 2010/8/18 10:03:08 (QP5 v5.115.810.9015) */

SELECTsn.username,

m.SID,

sn.SERIAL#,

m.TYPE,

DECODE(m.lmode,

0,

'None',

1,

'Null',

2,

'Row Share',

3,

'Row Excl.',

4,

'Share',

5,

'S/Row Excl.',

6,

'Exclusive',

lmode,

LTRIM (TO_CHAR (lmode,'990')))

lmode,

DECODE (m.request,

0,

'None',

1,

'Null',

request,

LTRIM (TO_CHAR (m.request,'990')))

request,

m.id1,

m.id2

FROM v$session sn,v$lock m

WHERE (sn.SID = m.SID ANDm.request != 0)

--存在锁请求，即被阻塞

OR(sn.SID = m.SID

--不存在锁请求，但是锁定的对象被其他会话请求锁定

ANDm.request = 0 AND lmode != 4

AND(id1,id2) IN

(SELECTs.id1,s.id2

FROMv$lock s

WHERErequest != 0

ANDs.id1 = m.id1

ANDs.id2 = m.id2))

ORDER BY id1,id2,m.request;

或者

/*Formatted on 2010/8/18 0:03:02 (QP5 v5.115.810.9015) */

SELECT /*+rule */

s .username,

DECODE (l.TYPE,'TM','TABLE LOCK','TX','ROW LOCK',NULL)

lock_level,

o.owner,

o.object_name,

o.object_type,

s.sid,

s.serial#,

s.terminal,

s.machine,

s.program,

s.osuser

FROM v$sessions,v$lock l,dba_objects o

WHERE l.sid =s.sid AND l.id1 = o.object_id(+) AND s.username IS NOT NULL

引起阻塞的几种常见情况

（1）DML语句引起阻塞

当一个会话保持另一个会话正在请求的资源上的锁定时，就会发生阻塞。被阻塞的会话将一直挂起，直到持有锁的会话放弃锁定的资源为止。

4个常见的dml语句会产生阻塞:

（1）INSERT

（2）UPDATE

（3）DELETE

（4）SELECT…FOR UPDATE

当一个用户发出select..for update的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，此时Oracle已经对返回的结果集上加了排它的行级锁，所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放(操作commit或rollback.)，产生的外在现象就是其他的操作将发生阻塞.

同样这个查询的事务将会对该表加表级锁，不允许对该表的任何ddl操作，否则将会报出Ora-00054:resource busy andacquire with nowait specified.

可以通过发出select…for update nowait的语句来避免发生阻塞，如果资源已经被另一个会话锁定，则会返回以下错误：Ora-00054:resourcebusy and acquire with nowait specified.

（2）外键没有创建索引

如果系统中有主，外键引用关系，并且满足一下三个条件中的任意一个，那么就应该考虑给外键字段创建索引，否则系统的性能可能会下降甚至阻塞。

（1） 主表上有频繁的删除操作

（2） 主键上有频繁的修改操作。

（3） 业务上经常会出现主表和从表做关联查询的情况。

第一和第二个条件操作的时候，主表会在从表上创建一个锁定，以保证主表主键的修改不会导致从表的数据在引用上出现问题，这是一个数据引用完整性的要求。如果主表上经常出现这样的删除或者是对主键列进行修改的操作，或者每次操作的记录数很多，都将会造成从表长时间被锁定，而影响其他用户的正常操作。

Latch

Latch属于System Lock,用于保护SGA区中共享数据结构的一种串行化锁定机制。Latch的实现是与操作系统相关的，尤其和一个进程是否需要等待一个latch、需要等待多长时间有关。

latch是Oracle提供的轻量级锁资源，是一种能够极快地被获取和释放的锁，能快速，短时间的锁定资源，防止多个并发进程同时修改访问某个共享资源，它只工作在SGA中，通常用于保护描述buffer cache中block的数据结构。

与每个latch相联系的还有一个清除过程，当持有latch的进程成为死进程时，该清除过程就会被调用。Latch还具有相关级别，用于防止死锁，一旦一个进程在某个级别上得到一个latch，它就不可能再获得等同或低于该级别的latch。

Latch不会造成阻塞，只会导致等待。阻塞是一种系统设计上的问题，等待是系统资源竞争的问题。

Latch和Lock的区别:

（1）. Latch是对内存数据结构提供互斥访问的一种机制，而Lock是以不同的模式来套取共享资源对象，各个模式间存在着兼容或排斥

（2）. Latch只作用于内存中，他只能被当前实例访问，而Lock作用于数据库对象，在RAC体系中实例间允许Lock检测与访问

（3）. Latch是瞬间的占用，释放，Lock的释放需要等到事务正确的结束，他占用的时间长短由事务大小决定

（4）. Latch是非入队的，而Lock是入队的

（5）. Latch不存在死锁，而Lock中存在

SELECT *

FROM ( SELECT addr,

ts#,

file#,

dbarfil,

dbablk,

tch

FROM x$bh

ORDER BY tch DESC)

WHERE ROWNUM < 11;

等待事件

查看v$event_name视图的字段结构

sql> descv$event_name;

查看等待事件总数

sql>select count(*) from v$event_name;

查看等待事件分类情况

/* Formatted on 2010/8/11 16:08:55 (QP5 v5.115.810.9015)*/

SELECT wait_class#,

wait_class_id,

wait_class,

COUNT ( * ) AS "count"

FROM v$event_name

GROUPBY wait_class#,wait_class_id,wait_class

ORDER BY wait_class#;

相关的几个视图

V$SESSION：代表数据库活动的开始，视为源起。

V$SESSION_WAIT：视图用以实时记录活动SESSION的等待情况，是当前信息。

V$SESSION_WAIT_HISTORY：是对V$SESSION_WAIT的简单增强，记录活动SESSION的最近10次等待。

V$sqlTEXT：当数据库出现瓶颈时，通常可以从V$SESSION_WAIT找到那些正在等待资源的SESSION，通过SESSION的SID，联合V$SESSION和V$sqlTEXT视图就可以捕获这些SESSION正在执行的sql语句。

V$ACTIVE_SESSION_HISTORY:是ASH的核心，用以记录活动SESSION的历史等待信息，每秒采样一次，这部分内容记录在内存中，期望值是记录一个小时的内容。

WRH#_ACTIVE_SESSION_HISTORY:是V$ACTIVE_SESSION_HISTORY在AWR的存储地。

V$ACTIVE_SESSION_HISTORY:中的信息会被定期(每小时一次)的刷新到负载库中，并缺省保留一个星期用于分析。

DBA_HIST_ACTIVE_SESS_HISTORY:视图是WRH#_ACTIVE_SESSION_HISTORY视图和其他几个视图的联合展现，通常通过这个视图进行历史数据的访问。

V$SYSTEM_EVENT由于V$SESSION记录的是动态信息，和SESSION的生命周期相关，而并不记录历史信息，所以ORACLE提供视图V$SYSTEM_EVENT来记录数据库自启动以来所有等待事件的汇总信息。通过这个视图，用户可以迅速获得数据库运行的总体概况。

函数

函数就是一个有返回值的过程。

定义一个函数：此函数可以根据雇员的编号查询出雇员的年薪

CREATEORREPLACEFUNCTIONmyfun(eno emp.empno%TYPE) RETURNNUMBERAS

rsal NUMBER;

BEGIN

SELECT (sal+ nvl(comm, 0)) * 12INTO rsal FROM emp WHERE empno= eno;

RETURN rsal;

END;

/

直接写sql语句，调用此函数：

SELECT myfun(7369) FROM dual;

存储过程

Procedure 定义

存储过程（Stored Procedure ）是一组为了完成特定功能的sql 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。存储过程是由流控制和sql 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。

优点

1. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般sql语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量。

4.安全性高,可设定只有某用户才具有对指定存储过程的使用权。

存储过程与函数的对比

存储过程使用示例

格式

/* Formatted on2011/1/17 13:20:44 (QP5 v5.115.810.9015) */

CREATEORREPLACEprocedureproc_trade(

v_tradeid innumber,--交易id

v_third_ip invarchar2,--第三方ip

v_third_time indate,--第三方完成时间

v_thire_state innumber,--第三方状态

o_result outnumber,--返回值

o_detail outvarchar2--详细描述

) as

-- 定义变量

v_error varchar2(500);

begin

--对变量赋值

o_result := 0;

o_detail := '验证失败';

--业务逻辑处理

if v_tradeid > 100then

insertintotable_name (.. .) values (...);

commit;

elsif v_tradeid < 100andv_tradeid > 50then

insertintotable_name (.. .) values (...);

commit;

else

gotolog;

endif;

--跳转标志符，名称自己指定

<<log>>

o_result := 1;

--捕获异常

exception

when no_data_found then

result:= 2;

when dup_val_on_index then

result:= 3;

whenothersthen

result:= -1;

endproc_trade;

在上面这个存储过程中使用了输入参数，并返回输出参数，这里的参数类型是我们自己指定的。这种写法可行，但是最好使用%type 来获取参数的类型(table_name.column_name%TYPE)。这样就不会出现参数类型的错误。

如：

CREATEORREPLACEPROCEDUREspdispsms(

aempid INotherinfo.empid%TYPE,

amsg INotherinfo.msg%TYPE,

abillno INotherinfo.billno%TYPE,

ainfotype INotherinfo.infotype%TYPE,

aopid INotherinfo.OPERATOR%TYPE,

ainfoid OUTotherinfo.infoid%TYPE,

RESULT OUTINTEGER)

存储过程中的循环

存储过程写的是业务逻辑，循环是常用的处理方法之一。

1.6.2.2.1 for ... in ... loop 循环

1：循环遍历游标

示例1：

CREATEORREPLACEPROCEDUREproc_test AS

CURSOR c1 IS

SELECT* FROM dat_trade;

BEGIN

FOR x INc1 LOOP

DBMS_OUTPUT.put_line(x.id);

ENDLOOP;

ENDproc_test;

示例2：

CREATEORREPLACEPROCEDUREproc_test AS

BEGIN

FOR x IN(SELECT power_id FROMsys_power) LOOP

DBMS_OUTPUT.put_line(x.power_id);

ENDLOOP;

ENDproc_test;

2：根据数值进行循环

示例1：

CREATEORREPLACEPROCEDUREproc_test(v_num INNUMBER)AS

BEGIN

for x in1.. 100loop

dbms_output.put_line(x);

endloop;

ENDproc_test;

示例2：在过程里指定输入参数v_num. 在调用过程时指定循环次数。

CREATEORREPLACEPROCEDUREproc_test(v_num INNUMBER)AS

BEGIN

FOR x IN1.. v_num LOOP

DBMS_OUTPUT.put_line(x);

ENDLOOP;

ENDproc_test;

loop 循环

DELETEFROMorders

WHEREsenddate < TO_CHAR(ADD_MONTHS(SYSDATE,-3),'yyyy-mm-dd')

ANDROWNUM< 1000;

EXITWHENsql%ROWCOUNT< 1;

COMMIT;

ENDLOOP;

这里的sql%ROWCOUNT是隐士游标。除了这个，还有其他几个：%found，%notfound，%isopen。

while 循环

CREATEORREPLACEPROCEDUREproc_test(v_num INNUMBER)AS

i NUMBER := 1;

BEGIN

WHILE i < v_num LOOP

BEGIN

i := i + 1;

DBMS_OUTPUT.put_line(i);

END;

ENDLOOP;

ENDproc_test;

存储过程中的判断

if ... elsif ... else ... 判断

CREATEORREPLACEPROCEDUREproc_test(v_num INNUMBER)AS

BEGIN

IF v_num < 10THEN

DBMS_OUTPUT.put_line(v_num);

ELSIF v_num > 10ANDv_num < 50THEN

DBMS_OUTPUT.put_line(v_num - 10);

ELSE

DBMS_OUTPUT.put_line(v_num - 50);

ENDIF;

ENDproc_test;

case ... when ... end case 判断

CREATEORREPLACEPROCEDUREproc_test(v_num INNUMBER)AS

BEGIN

case v_num

when1then

DBMS_OUTPUT.put_line(v_num);

when2then

DBMS_OUTPUT.put_line(v_num);

when3then

DBMS_OUTPUT.put_line(v_num);

else

null;

endcase;

END proc_test;

游标

存储过程中使用游标也是很常见的。这里的游标分两种：

Cursor型游标(不能用于参数传递)

参考循环遍历游标中的示例。

SYS_REFCURSOR型游标

该游标是Oracle以预先定义的游标，可作出参数进行传递。

注意一点：SYS_REFCURSOR只能通过OPEN方法来打开和赋值

我们可以使用这种类似的游标来返回一个结果集：

CREATEORREPLACEprocedureproc_test(

checknum innumber,--每次返回的数据量

ref_cursoroutsys_refcursor--返回的结果集，游标

) as

begin

openref_cursor for

select*

from(select * fromdat_trade where state = 41orderbyid)

whererownum< checknum;

endproc_test;

SYS_REFCURSOR中可使用三个状态属性：

（1）. %NOTFOUND(未找到记录信息)

（2）. %FOUND(找到记录信息)

（3）. %ROWCOUNT(然后当前游标所指向的行位置)

CREATEORREPLACEPROCEDUREproc_test(

checknum INNUMBER,--每次返回的数据量

ref_cursor OUTsys_refcursor--返回的结果集，游标

) AS

t_tmp table_name%ROWTYPE;

BEGIN

OPEN ref_cursor FOR

SELECT*

FROM(SELECT * FROMtable_name WHERE state = 41ORDERBYid)

WHEREROWNUM< checknum;

--循环游标

LOOP

FETCHref_cursor

INTOt_tmp;

EXITWHENref_cursor%NOTFOUND;

--DBMS_OUTPUT.put_line (t_tmp.id);

UPDATEtable_name SET state = 53WHEREid= t_tmp.id;

COMMIT;

ENDLOOP;

CLOSE ref_cursor;

ENDproc_test;

游标是一种PL/sql控制结构；可以对sql语句的处理进行显示控制，便于对表的行数据逐条进行处理。

游标并不是一个数据库对象，只是存留在内存中

操作步骤：

• 声明游标

• 打开游标

• 取出结果，此时的结果取出的是一行数据

• 关闭游标

到底那种类型可以把一行的数据都装进来 • 此时使用ROWTYPE类型，此类型表示可以把一行的数据都装进来。 例如：查询雇员编号为7369的信息（肯定是一行信息）。

DECLARE

eno emp.empno%TYPE;

empInfoemp%ROWTYPE;

BEGIN

eno :=&en;

SELECT * INTOempInfo FROM emp WHERE empno = eno;

DBMS_OUTPUT.put_line('雇员编号：' ||empInfo.empno);

DBMS_OUTPUT.put_line('雇员姓名：' ||empInfo.ename);

END; /

使用for循环操作游标（比较常用）

DECLARE

-- 声明游标

CURSOR mycurIS

SELECT * FROM emp where empno= -1;

empInfoemp%ROWTYPE;

cou NUMBER;

BEGIN

-- 游标操作使用循环，但是在操作之前必须先将游标打开

FOR empInfoIN mycur LOOP

cou :=mycur%ROWCOUNT;

DBMS_OUTPUT.put_line(cou || '雇员编号：' ||empInfo.empno);

DBMS_OUTPUT.put_line(cou || '雇员姓名：' ||empInfo.ename);

ENDLOOP;

END;/

编写第一个游标，输出全部的信息。

DECLARE

-- 声明游标

CURSOR mycurIS

SELECT * FROM emp; --

List(EmpPo)empInfo emp%ROWTYPE;

BEGIN

-- 游标操作使用循环，但是在操作之前必须先将游标打开

OPENmycur;

-- 使游标向下一行

FETCH mycur

INTOempInfo;

-- 判断此行是否有数据被发现

WHILE(mycur%FOUND) LOOP

DBMS_OUTPUT.put_line('雇员编号：' ||empInfo.empno);

DBMS_OUTPUT.put_line('雇员姓名：' ||empInfo.ename);

-- 修改游标，继续向下

FETCH mycur

INTOempInfo;

ENDLOOP;

END; /

也可以使用另外一种方式循环游标：

DECLARE

-- 声明游标

CURSOR mycurIS

SELECT * FROM emp;

empInfoemp%ROWTYPE;

BEGIN

-- 游标操作使用循环，但是在操作之前必须先将游标打开

OPENmycur;

LOOP

-- 使游标向下一行

FETCH mycur

INTOempInfo;

EXITWHENmycur%NOTFOUND;

DBMS_OUTPUT.put_line('雇员编号：' ||empInfo.empno);

DBMS_OUTPUT.put_line('雇员姓名：' ||empInfo.ename);

ENDLOOP;

END;

/

注意1： 在打开游标之前最好先判断游标是否已经是打开的。通过ISOPEN判断，格式：

游标%ISOPENIFmycur%ISOPENTHENnull ;

ELSEOPEN mycur;

ENDIF ;

注意2： 可以使用ROWCOUNT对游标所操作的行数进行记录。

DECLARE

-- 声明游标

CURSOR mycurIS

SELECT * FROM emp;

empInfoemp%ROWTYPE;

cou NUMBER;

BEGIN

-- 游标操作使用循环，但是在操作之前必须先将游标打开

IFmycur%ISOPENTHEN

null;

ELSE

OPENmycur;

ENDIF;

LOOP

-- 使游标向下一行

FETCH mycur

INTOempInfo;

EXITWHENmycur%NOTFOUND;

cou :=mycur%ROWCOUNT;

DBMS_OUTPUT.put_line(cou || '雇员编号：' ||empInfo.empno);

DBMS_OUTPUT.put_line(cou || '雇员姓名：' ||empInfo.ename);

ENDLOOP;

END;

/

一次性上涨全部雇员的工资。根据它所在的部门涨工资，规则： • 10部门上涨10% • 20部门上涨20% • 30部门上涨30% 所有部门的上涨工资，最不能超过5000，如果超过5000，则工资就为5000。

declare

cursor mycuris

select * from emp;

empInfoemp%rowtype;

s emp.sal%type;

begin

forempInfo in mycur loop

ifempInfo.deptno = 10then

s :=empInfo.sal * 1.1;

elsifempInfo.deptno = 20then

s :=empInfo.sal * 1.2;

elsifempInfo.deptno = 30then

s :=empInfo.sal * 1.3;

endif;

if s> 5000then

s := 5000;

endif;

update emp set sal =s where empno = empInfo.empno;

endloop;

end;

存储过程的调试

如果使用PL/sql Developer 或者TOAD 工具的话，调试还是很方便的。如果是在sqlplus里，我们可以使用：

sql>show errors

来查看错误。不过在开发中估计也很少有人直接使用sqlplus来写存储过程。效率低，调试又麻烦。还是使用工具方便点。如果想在某处退出存储过程，直接使用Return;就可以了。与存储过程编写相关的数组和游标，这两块说起来还是有很多东西。在上面的示例中，也简单的举了几个有关游标与存储过程编写的例子。

PL/sql块

PL/sql块是在sql语言之上发展起来的一种应用，可以集中的处理各种复杂的sql操作。 组成： DECLARE： 声明部分 BEGIN 编写主题

EXCEPTION 捕获异常 END ; /

看一个简单的PL/sql块

DECLARE

i NUMBER;

BEGIN

i := 30;

DBMS_OUTPUT.put_line('I的内容为：'|| i);

END;

/

此时，直接执行程序即可。 执行之后发现没有任何的输出。因为Oracle在系统设置中默认设置了输出不显示，如果要显示的话，输入以下命令：

setserveroutput on

DECLARE

i NUMBER;

BEGIN

i:= 1/0;

EXCEPTION

when ZERO_DIVIDE then

dbms_output.put_line('error');

END ;

/

PL/sql块还可以接收用户的输入信息，例如：现在要求用户输入一个雇员编号，之后根据输入的内容进行查询，查询雇员的姓名。• 用户的输入信息使用“&”完成。

DECLARE

eno NUMBER;

en VARCHAR2(30);

BEGIN

-- 输入的信息保存在eno里

eno := &no ; -- 之后根据eno的值，对数据库进行查询操作

SELECT ename INTOen FROM emp WHEREempno = eno;

DBMS_OUTPUT.put_line('编号为：'|| eno || '雇员的姓名为：'|| en);

EXCEPTION

WHEN no_data_found THEN

DBMS_OUTPUT.put_line('没有此雇员');

END;

/

在以上的查询中再进一步：可以根据雇员的编号查出姓名及其领导的姓名和所在的部门，进行显示。

DECLARE

eno emp.empno%TYPE;

en emp.ename%TYPE;

mn emp.ename%TYPE;

dn dept.dname%TYPE;

dept dept %rowtype;

BEGIN

-- 输入的信息保存在eno里eno := &no ; -- 之后根据eno的值，对数据库进行查询操作

SELECT e.ename,m.ename,d.dname

INTOen,mn,dn

FROMemp e,dept d,emp m

WHEREe.empno = 7369

ANDe.mgr = m.empno

ANDe.deptno = d.deptno;

DBMS_OUTPUT.put_line('编号为：'|| eno || '雇员的姓名为：'|| en);

DBMS_OUTPUT.put_line('编号为：'|| eno || '雇员的上级姓名为：'|| mn);

DBMS_OUTPUT.put_line('编号为：'|| eno || '雇员所在的部门：'|| dn);

DBMS_OUTPUT.put_line(dept.deptno);

EXCEPTION

WHEN no_data_found THEN

DBMS_OUTPUT.put_line('没有此雇员');

END;

/

说明： • emp.empno%TYPE ;：表示以emp表中的empno字段的类型定义变量 •e.ename,d.dname INTO en,dn：一次可以同时放进去多个值

dept dept %rowtype ; 表示dept是一行数据，类似于Hibernate的PO

Loop循环（do…while）

PL/sql之中也包含了：循环、分支等条件控制语句 Loop循环(do…while) 格式： LOOP 循环的语句 ; EXIT WHEN 终止条件 ; 循环条件必须更改 ; END LOOP ;

循环输出1~10。

DECLARE

cou NUMBER;

BEGIN

-- 必须给一个初始值

cou := 1;

LOOP

DBMS_OUTPUT.put_line('cou= ' || cou);

EXITWHENcou > 10;

cou := cou + 1;

ENDLOOP;

END;

/ 此循环是先执行一次之后再进行判断

while循环

格式： while(判断循环的条件) loop 循环的语句 ; 循环条件的改变 ; End loop ;

使用此语句修改上面的程序：

DECLARE

cou NUMBER;

BEGIN

-- 必须给一个初始值

cou := 1;

WHILE (cou < 10)LOOP

DBMS_OUTPUT.put_line('cou= ' || cou);

cou := cou + 1;

ENDLOOP;

END;

/ 此语句，是先判断，之后如果条件满足则执行，与while循环类似。

for循环

格式： FOR 变量名称 in 变量的初始值..结束值 LOOP 循环语句 ;

DECLARE

cou NUMBER;

BEGIN

FOR cou IN1.. 10LOOP

DBMS_OUTPUT.put_line('cou= ' || cou);

ENDLOOP;

END;

/

IF语句

条件判断 格式： IF 条件 THEN 满足条件时，执行此语句

DECLARE

cou NUMBER;

BEGINcou := 11 ;

IF cou>10THEN

DBMS_OUTPUT.put_line('cou= '||cou) ;

ENDIF;

END ;

/

IF…ELSE语句

如果IF满足了，则执行，否则执行ELSE

DECLARE

cou NUMBER;

BEGIN

cou := 1;

IF cou > 10THEN

DBMS_OUTPUT.put_line('cou= ' || cou);

ELSE

DBMS_OUTPUT.put_line('条件不满足');

ENDIF;

END;

/

IF…ELSIF…ELSE语句

DECLARE

cou NUMBER;

BEGIN

cou := 1;

IF cou > 10THEN

DBMS_OUTPUT.put_line('cou= ' || cou);

ELSIF cou < 5THEN

DBMS_OUTPUT.put_line('值小于5');

ELSE

DBMS_OUTPUT.put_line('条件不满足');

ENDIF;

END;

/

GOTO语句

无条件跳转语句

DECLARE

eno emp.empno%TYPE;

sal emp.sal%TYPE;

BEGIN

eno := &en;

SELECT sal INTOsal FROM emp WHEREempno = eno;

IF sal > 3500THEN

gotopo1;

ELSIF sal > 2000THEN

gotopo2;

ELSE

gotopo3;

ENDIF;

<<po1>>

DBMS_OUTPUT.put_line('高工资。。。');

<<po2>>

DBMS_OUTPUT.put_line('中等工资。。');

<<po3>>

DBMS_OUTPUT.put_line('底工资。。。');

END;

视图

视图：包装了复杂的sql语句的查询结果

create or replace view 视图名称 as

复杂sql语句;

conn sys/change on_install assysdba;

grantcreateviewto luffy;

createorreplaceview v_result as

select * from (

selecte.deptno,

e.sal,

rank() over(partitionby e.deptno orderby e.sal desc) as nm

from scott.emp e ) e2

wheree2.nm<=2;

需要由设计人员根据业务需要提供给开发者大量的视图。

视图中的数据都属于映射数据，它不是真实的数据

单表的视图，对视图的增加、修改和删除，会影响到原表中的数据；

多表查询sql的视图，默认不允许对视图的增加。（plsql编程中，通过替代触发器的概念，可以增加），

工作中，视图只能够用于查询，不能用于任何形式的更新。

创建视图的时候有两个创建的选项，避免用户错误的更新操作

1.避免视图创建条件更新：withcheck option

2.设置只读视图：withread only

使with checkoption保护的数据不更新，其他数据可以更新

同义词：

create synonym my_student for luffy.student;

select * from my_student;

默认只能当前创建同义词的用户可以访问同义词，要想所有用户访问，需改为public权限

约束

1.数据类型约束

2.非空约束

编写字段后增加 not null

3.唯一约束 uk unique

编写字段后增加unique

null不属于重复的统计范畴。

约束简写_字段名称，如唯一约束 简写UK，email上约束名称为UK_EMAIL

增加错误数据：constraintuk_email unique(email)

4.主键约束 primary key,PK

主键约束=非空约束 + 唯一约束。如：公民身份证号

增加错误数据：constraintpk_mid primary key(mid)

一般至少设置一个主键或不设置，不用复合主键。

5.检查约束 check,ck

由用户自己来设置过滤条件，如设置年龄范围：0-255.设置性别：男，女，中

增加错误数据：constraint ck_age check (age between 0 and 255)

constraint ck_sex check(sex in('男','女'))

即使设置多个检查约束，也是一个一个约束过滤；

设置约束也多，更新时效率就越低，因此，一般的约束由程序进行校验。

6.主-外键约束 foreignkey fk

menber表与book表

book表中参考member表的mid,book表中增加的mid必须是member表中存在的

子表的某个字段与父表的主键或唯一约束的字段有关

增加错误数据：constraintfk_mid foreign key(mid) reference menber(mid)

限制1：删除父表前，一定要先删除子表。

如果是不熟悉的数据库，删除时可强制删除(不建议)：

drop table member cascade constraint;

限制2：作为主表，必须有主键约束或唯一约束。

限制3：数据级联操作问题。

删除主表数据时，如果有子表数据，那么主表数据无法删除。

级联删除：建立外键时，使用ondelete cascade

constraint fk_mid foreign key(mid) reference menber(mid) on deletecascade

删除主表不删除字表：ondelete set null

constraint fk_mid foreign key(mid) reference menber(mid) on delete setnull

修改约束：

首先明确：约束是在建立表的同时就已经设置完成。

只为了解的知识点：

增加约束：

alter table 表名称 add constraint 约束名称 约束类型（约束字段）[选项]

alter table member add constraint pk_midprimary ke (mid)

如果此时表中已经有违反约束的数据，那无法增加约束，需先将数据修改或删除，满足增加约束的条件

无法增加非空约,只能是通过修改字段类型方式。

alter table member modify (namevarchar2(20) not null)

删除约束：

alter table member drop constraint pk_mid;

oracle会将约束也作为一个个对象保存，也会保存在数据字典中

select * from user_cons_columns;

事务

4个特性

原子性：将一系列的操作作为一个整体去执行，要么全部成功，要么全部失败

一致性：从一个一致的状态变成另一个一致的状态

隔离性：一个事务的执行，不能对其他正在执行的事务造成影响；

持久性：事务提交，对系统的改变是永久的

--事务隔离性级别

可串行化：保证可串行化调度。

可重复读：只允许读取已提交数据，且一个事务在两个读取一个数据项期间，其它事务不能更新该数据。

已提交读：只允许读取已提交数据，但不要求可重复读。

未提交读：允许读取未提交数据。

脏读：事务B读到了事务A修改的数据，但事务A提交失败；

幻读：同一个事务中，同一个查询提取到的结果不一致；

常用sql:

修改字段名称、类型

--增加字段

altertabletpssbill.Settle_Item_Det1_201411 add (CDR_CONTENT VARCHAR2(2000));

altertabletpssbill.Settle_Item_Det1_201411 add(PROD_OFFER_INST_ID NUMBER(12));

--删除字段

altertabletpssbill.Settle_Item_Det1_201411 dropcolumnCDR_CONTENT;

altertabletpssbill.Settle_Item_Det1_201411 dropcolumnPROD_OFFER_INST_ID;

altertable tpssbill.Settle_Item_Det1_201411MODIFYcolumnPROD_OFFER_INST_ID NUMBER(20) ;

ALTERTABLE tableName modifycolumn columnName 类型;

--例如MysqL的修改字段类型语句：

altertabletestmodifycolumnnamevarchar(255);

--Oracle修改字段类型和长度语句：

ALTERTABLE tableName modify(columnName 类型);

例如

altertabletestmodify(namevarchar(255));

实例名查询：

select instance_name fromv$instance;

数据库名查询：

selectnamefrom v$database;

--查询某用户下所有的表

select * from dba_tables where owner='ESB_TEST';

selectnamefrom v$datafile;

select * from v$session;

更改表名 当前用户下

renamereward_hs_8101014_office toreward_hs_7201012_office;

oracle解锁表

select s.sid,s.SERIAL# fromv$locked_object o,v$session s whereo.SESSION_ID=s.SID;

--altersystem kill session '10,36797';

备份表数据

--备份某种表数据

createtablesend_msg_0130 asselect* from send_msg;

insertintotable_name select * fromtable_name_another;

--导数据

--导出到本机：命令窗口执行：

expsmsc/smsc@hbltsmsc file=D:\zhongxing\send_msg_his.dmp tables=(send_msg_his)

expsmsc/smsc@hbltsmsc file=D:\zhongxing\send_msg.dmp tables=(send_msg)

expsmsc/smsc@hbltsmsc file=D:\zhongxing\recv_msg_his.dmp tables=(recv_msg_his)

expsmsc/smsc@hbltsmsc file=D:\zhongxing\recv_msg.dmp tables=(send_msg)

--导出到服务器：服务器上执行

expsmsc/smsc file=/data/dmp/send_msg_his.dmptables=\(send_msg_his\)

expsmsc/smsc file=/data/dmp/send_msg.dmptables=\(send_msg\)

expsmsc/smsc file=/data/dmp/recv_msg_his.dmptables=\(recv_msg_his\)

expsmsc/smsc file=/data/dmp/recv_msg.dmptables=\(recv_msg\)

--备份某几张表 ！！！！

expsmsc/smsc file=/data/oracle_bak/dmp/bakup0209_2.dmptables=\(send_msg_his,send_msg,recv_msg_his,recv_msg\)

--备份整个数据库 ！！！！

expsmsc/smsc file=/data/oracle_bak/dmp/bakupsmmc0209_2.dmpfull=y

expcoprule/ztesoft@133.96.84.8:1521/coprulefile=/home/oracle/coprule.dmp owner=coprule log=/home/oracle/coprule.log

expzop/zop@orcl file= D:\zop_bak.dmp owner=zop log=D:\zop_ba.log

--数据的导入

--1 将D:\daochu.dmp中的数据导入TEST数据库中。

imp system/manager@TESTfile=d:\daochu.dmp

imp aichannel/aichannel@TESTfull=yfile=d:\datanewsmgnt.dmp ignore=y

--上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。

-- 在后面加上ignore=y 就可以了。

--2 将d:daochu.dmp中的表table1导入

imp system/manager@TESTfile=d:\daochu.dmptables=(table1)

--基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。

注意：

操作者要有足够的权限，权限不够它会提示。

数据库时可以连上的。可以用tnspingTEST来获得数据库TEST能否连上。

--

tools->export tables是导出表结构还有数据

tools->export userobjects是导出表结构 注意选择对应的用户

查询、更改表和用户对应的表空间

--查询用户对应的表空间

select username,default_tablespace from dba_users;

--查询表及所属表空间

SELECT TABLE_NAME,TABLESPACE_NAME FROMUSER_TABLES;

--查找某个表属于哪个用户

select owner from dba_tables where table_name='表名';

更改表所在的表空间

altertable table_name movetablespace tablespace_name;

altertable SMMC_CALL_TRANSFER movetablespaceSMS_SMS_TABLESPACE;

altertable SEND_MSG_0126_3 movetablespaceSYSAUX;

删除一个表中的重复数据

DELETE

　　FROMtable_name a

　　WHERErowid > ( SELECT min(rowid)

　　FROMtable_name b

WHERE b.id = a.id and b.name=a.name);

建序列

自动增长列

MysqL,db2,sql server

oracle12C之后提供了手工的自动增长列

create sequence 序列名称

increment by 步长

start with 开始值

maxvalue 最大值 （nomaxvalue）

minvalue 最小值 （nominvalue）

cycle(nocycle)

cache 缓存大小 （nocache）

如果直接使用create sequence 序列名称

则是创建默认序列，数值从1开始，默认增长1，无最大值。

序列的数据字典

select * from user_sequences

取序列：

序列下一个增长数据 序列.nextval

取得序列的当前数据 序列.currval

select LOG_SEQUENCE.nextval from dual;

select LOG_SEQUENCE.currval from dual;

在使用currval之前，一定要使用nextval，否则会出现ora-08002

关于user_sequences数据字典之中的last_number和cache的作用：

last_number并不是当前序列真正增长的数据，实际上当用户每次进行数据增长控制的时候，

last_number都表示已经处理完的一批序列内容

当前序列等于last_number数据时，那会自动再增加指定“cache”大小的序列

但是由于数据存在有缓存的问题，所以数据库重启后，可能会出现跳号的问题。

如果想避免：nocache,但是性能会降低

建立数据表，同时保存数据：

特殊的控制：

1.改变序列增长步长

drop sequence myseq;

create sequence myseq

increment by 2;

2.改变序列开始值

默认从1开始，如果是订单编号等，需要从一个较大的值开始

create sequence myseq

increment by 2

start with 1000000000;

3.循环序列

如：希望序列达到某个值后，循环显示

create sequence myseq

increment by 2

start with 1

cycle nocache;

创建用户及表空间分配权限

创建表空间

windows下

--创建表空间

createtablespace test_data

logging

datafile'D:\APP\luxuefeng\ORADATA\orcl\test_data.dbf'

size50m

autoextendon

next50m maxsize600m

extentmanagementlocal;

linux下

--创建表空间

createtablespace TEST_DB datafile '/oracle/product/fstest/testdb.dbf' size 500M reuseautoextend on next 40M maxsize unlimited default storage(initial 128k next 128kminextents 2 maxextents unlimited);

建用户

--2.建用户

createusertestidentifiedbytest

defaulttablespacetest_data;

赋权

--3.赋权

grantconnect,resourcetotest;

grantcreateanysequencetotest;

grantcreateanytabletotest;

grantdeleteanytabletotest;

grantinsertanytabletotest;

grantselectanytabletotest;

grantunlimitedtablespacetotest;

grantexecuteanyproceduretotest;

grantupdateanytabletotest;

grantcreateanyviewtotest;

--赋权DBA

grantdbatotest;

--表空间改名

altertablespace scott_data renameto luffy_data;

表空间查询

--查询表及所属表空间

SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES;

--查看表空间物理文件的名称及大小

SELECT tablespace_name,

file_id,

file_name,

round(bytes / (1024 * 1024),0) total_space

FROM dba_data_files

ORDERBY tablespace_name;

--查看表空间详细情况

SELECT a.tablespace_name "表空间名",

total"表空间大小",

free"表空间剩余大小",

(total- free) "表空间使用大小",

total /(1024 * 1024 * 1024) "表空间大小(G)",

free /(1024 * 1024 * 1024) "表空间剩余大小(G)",

(total- free) / (1024 * 1024 * 1024) "表空间使用大小(G)",

round((total - free) / total,4) * 100 "使用率 %"

FROM (SELECT tablespace_name,SUM(bytes) free

FROM dba_free_space

GROUPBY tablespace_name) a,

(SELECT tablespace_name,SUM(bytes) total

FROM dba_data_files

GROUPBY tablespace_name) b

WHERE a.tablespace_name =b.tablespace_name;

扩充表空间方法

--Oracle增加表空间大小的四种方法

--Meathod1：给表空间增加数据文件

ALTERTABLESPACE app_data ADDDATAFILE

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'SIZE50M;

-- Meathod2：新增数据文件，并且允许数据文件自动增长

ALTERTABLESPACE app_data ADDDATAFILE

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF'SIZE50M

AUTOEXTENDONNEXT5MMAXSIZE100M;

-- Meathod3：允许已存在的数据文件自动增长

ALTERDATABASEDATAFILE'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'

AUTOEXTENDONNEXT5MMAXSIZE100M;

--Meathod4：手工改变已存在数据文件的大小

ALTERDATABASEDATAFILE'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'

RESIZE100M;

Oracle 字符

Oracle字符集是一个字节数据的解释的符号集合,有大小之分,有相互的包容关系。ORACLE 支持国家语言的体系结构允许你使用本地化语言来存储，处理，检索数据。它使数据库工具，错误消息，排序次序，日期，时间，货币，数字，和日历自动适应本地化语言和平台。

影响Oracle数据库字符集最重要的参数是NLS_LANG参数。

它的格式如下: NLS_LANG = language_territory.charset

它有三个组成部分(语言、地域和字符集)，每个成分控制了NLS子集的特性。

其中:

Language： 指定服务器消息的语言，影响提示信息是中文还是英文

Territory： 指定服务器的日期和数字格式

Charset： 指定字符集。

如:AMERICAN _ AMERICA. ZHS16GBK

从NLS_LANG的组成我们可以看出，真正影响数据库字符集的其实是第三部分。

所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。

unicode编码

Unicode是一个涵盖了目前全世界使用的所有已知字符的单一编码方案，也就是说Unicode为每一个字符提供唯一的编码。UTF-16是unicode的16位编码方式，是一种定长多字节编码，用2个字节表示一个unicode字符，AF16UTF16是UTF-16编码字符集。 UTF-8是unicode的8位编码方式，是一种变长多字节编码，这种编码可以用1、2、3个字节表示一个unicode字符，AL32UTF8，UTF8、UTFE是UTF-8

编码字符集

数据库字符集（oracle服务器端字符集）

数据库字符集在创建数据库时指定，在创建后通常不能更改。在创建数据库时，可以指定字符集(CHARACTER SET)和国家字符集(NATIONALCHARACTER SET)。

符集

(1)用来存储CHAR,VARCHAR2,CLOB,LONG等类型数据(2)用来标示诸如表名、列名以及PL/sql变量等 (3)用来存储sql和PL/sql程序单元等

国家字符集

(1)用以存储NCHAR,NVARCHAR2,NCLOB等类型数据(2)国家字符集实质上是为oracle选择的附加字符集，主要作用是为了增强oracle的字符处理能力，因为NCHAR数据类型可以提供对亚洲使用定长多字节编码的支持，而数据库字符集则不能。国家字符集在oracle9i中进行了重新定义，只能在unicode编码中的AF16UTF16和UTF8中选择，默认值是AF16UTF16

查询字符集参数

可以查询以下数据字典或视图查看字符集设置情况 nls_database_parameters、props$、v$nls_parameters 查询结果中NLS_CHARACTERSET表示字符集，NLS_NCHAR_CHARACTERSET表示国家字符集

修改数据库字符集

按照上文所说，数据库字符集在创建后原则上不能更改。不过有2种方法可行。

（1） 如果需要修改字符集，通常需要导出数据库数据，重建数据库，再导入数据库数据的方式来转换。

（2） 通过ALTER DATABASECHARACTER SET语句修改字符集，但创建数据库后修改字符集是有限制的，只有新的字符集是当前字符集的超集时才能修改数据库字符集，例如UTF8是US7ASCII的超集，修改数据库字符集可使用ALTERDATABASE CHARACTER SET UTF8。

客户端字符集（NLS_LANG参数）

客户端字符集含义

客户端字符集定义了客户端字符数据的编码方式，任何发自或发往客户端的字符数据均使用客户端定义的字符集编码,客户端可以看作是能与数据库直接连接的各种应用，例如sqlplus,exp/imp等。客户端字符集是通过设置NLS_LANG参数来设定的。

NLS_LANG参数格式

NLS_LANG=<language>_<territory>.<client character set>Language: 显示oracle消息,校验，日期命名 Territory：指定默认日期、数字、货币等格式 Client characterset：指定客户端将使用的字符集例如：NLS_LANG=AMERICAN_AMERICA.US7ASCIIAMERICAN是语言，AMERICA是地区，US7ASCII是客户端字符集

客户端字符集设置方法

1)UNIX环境 $NLS_LANG=―simplifiedchinese‖_china.zhs16gbk $export NLS_LANG 编辑oracle用户的profile文件 2)Windows环境 编辑注册表 Regedit.exe ---》 HKEY_LOCAL_MACHINE---》SOFTWARE ---》 ORACLE-HOME

NLS参数查询

Oracle提供若干NLS参数定制数据库和用户机以适应本地格式，例如有NLS_LANGUAGE,NLS_DATE_FORMAT,NLS_CALENDER等，可以通过查询以下数据字典或v$视图查看。 NLS_DATABASE_PARAMETERS:显示数据库当前NLS参数取值，包括数据库字符集取值 NLS_SESSION_PARAMETERS： 显示由NLS_LANG 设置的参数，或经过alter session 改变后的参数值（不包括由NLS_LANG 设置的客户端字符集） NLS_INSTANCE_PARAMETE： 显示由参数文件init<SID>.ora定义的参数

V$NLS_PARAMETERS：显示数据库当前NLS参数取值

Jobs

JOBS，其实就是Scheduler管理的一个(或多个)任务的执行调度。

创建Jobs

通过DBMS_SCHEDULER包来创建Jobs，是使用其CREATE_JOB过程。在创建Job时，用户可以指定要执行的任务，调度信息(什么时候执行，执行周期，终止日期等)以及其它一些任务相关的属性。例如：

createtableTEST(idnumber);

CREATEORREPLACEPROCEDUREIT

AS

BEGIN

insertintoTESTVALUES(1);

END;

sql>BEGIN

DBMS_SCHEDULER.CREATE_JOB(

job_name=>'JobTest',

job_type=>'STORED_PROCEDURE',

job_action=>'SYSTEM.IT',

start_date=>sysdate,

repeat_interval=>'FREQ=MINUTELY;INTERVAL=10');

END;

/

事实上，有权限的话，用户也可以创建其它SCHEMA下的JOB，只需要在指定JOB_NAME时，按照schema.job_name的格式即可。这种情况下创建的JOB，其CREATED与OWNER有可能并不相同。当使用CREATE_JOB过程创建JOB时，可指定的参数值很多，只不过多数情况下用户仅指定部分参数即可满足要求。

其中，上例中指定的参数，分别代表的含义如下：

JOB_NAME：指定任务的名称，必选值，注意要确保指定的名称唯一。

JOB_TYPE：任务执行的操作类型，必选值，有下列几个可选值：

（1）PLsql_BLOCK：表示任务执行的是一个PL/sql匿名块。

（2）STORED_PROCEDURE：表示任务执行的是ORACLE过程(含L/sqlPROCEDURE和JAVAPROCEDURE)，本例中正是指定这一参数值。

（3）EXECUTABLE：表示任务执行的是一个外部程序，比如说操作系统命令。

（4）CHAIN：表示任务执行的是一个CHAIN。

JOB_ACTION：任务执行的操作，必选值，应与JOB_TYPE类型中指定的参数相匹配。比如说对于PL/sql匿名块，此处就可以放置PL/sql块的具体代表，类似DECLARE..BEGIN..END这类；如果是ORACLE过程，那么此处应该指定具体的过程名，注意由于任务执行，即使过程中有OUT之类参数，实际执行时也不会有输出的。

START_DATE：指定任务初次执行的时间，本参数可为空，当为空时，表示任务立刻执行，效果等同于指定该参数值为SYSDATE。

REPEAT_INTERVAL：指定任务执行的频率，比如多长时间会被触发再次执行。本参数也可以为空，如果为空的话，就表示当前设定的任务只执行一次。REPEAT_INTERVAL参数与标准JOB中的INTERVAL参数有很大区别，相比之下，REPEAT_INTERVAL参数的语法结构要复杂的多。其中最重要的是FREQ和INTERVAL两个关键字。

（1）FREQ关键字用来指定间隔的时间周期，可选参数有：YEARLY,MONTHLY,WEEKLY,DAILY,HOURLY,MINUTELY,andSECONDLY，分别表示年、月、周、日、时、分、秒等单位。

（2）INTERVAL关键字用来指定间隔的频繁，可指定的值的范围从1-99。

例如：REPEAT_INTERVAL=>'FREQ=DAILY;INTERVAL=1';表示每天执行一次，如果将INTERVAL改为7就表示每7天执行一次，效果等同于FREQ=WEEKLY;INTERVAL=1。

一般来说，使用DBMS_SCHEDULER.CREATE_JOB创建一个JOB，至少需要指定上述参数中的前3项。除此之外，还可以在CREATE_JOB时，指定下列参数：

（1）NUMBER_OF_ARGUMENTS：指定该JOB执行时需要附带的参数的数量，默认值为0，注意当JOB_TYPE列值为PLsql_BLOCK或CHAIN时，本参数必须设置为0，因为上述两种情况下不支持附带参数。

（2）END_DATE：指定任务的过期时间，默认值为NULL。任务过期后，任务的STATE将自动被修改为COMPLETED，ENABLED被置为FALSE。如果该参数设置为空的话，表示该任务永不过期，将一直按照REPEAT_INTERVAL参数设置的周期重复执行，直到达到设置的MAX_RUNS或MAX_FAILURES值。

（3）JOB_CLASS：指定任务关联的CLASS，默认值为DEFAULT_JOB_CLASS。

（4）ENABLED：指定任务是否启用，默认值为FALSE。FALSE状态表示该任务并不会被执行，除非被用户手动调用，或者用户将该任务的状态修改为TRUE。

管理Jobs

启用Jobs

前面创建JOB时，由于未显式的指定ENABLED参数，因此即使指定了START_DATE，不过默认情况下JOB不会自动执行。对于这种情况，DBMS_SCHEDULER包中提供了一个过程ENABLE，可以用来修改JOB的启

用状态，调用方式非常简单，例如：

sql>exec dbms_scheduler.enable('JOBTEST');

PL/sqlproceduresuccessfullycompleted.

禁用Jobs

DBMS_SCHEDULER.ENABLE仅用来将JOB(其实不仅仅对JOB有效，对于CHAIN、PROGRAM等也有效)的启用状态置为TRUE。如果想将其启用状态置为FALSE，还有一个与该功能对应的过程：DBMS_SCHEDULER.DISABLE，如：

sql>exec dbms_scheduler.disable('JOBTEST');

修改Jobs

由于JOB的属性众多，难免可能会遇到需要修改的情况，比如说前面创建JOB时不小心，指定要执行的过程名输入错误(CREATE_JOB在创建时不会自动检查指定的过程是否有效)，这种情况下就必然涉及到对JOB的修改(或者说重定义)，DBMS_SCHEDULER包中专门提供了一个过程SET_ATTRIBUTE，可以用来修改任务的属性值。

例如，修改刚刚创建的JOB：INSERT_TEST_TBL执行的过程，执行语句如下：

sql>execdbms_scheduler.set_attribute('JobTest','JOB_ACTION','SYSTEM.IT');

PL/sqlproceduresuccessfullycompleted

SET_ATTRIBUTE过程虽然仅有三个参数，不过能够修改的属性值可是不少，以下列举几个较常用到的：

（1）LOGGING_LEVEL：指定对jobs执行情况记录的日志信息级别。SCHEDULER管理的JOB对任务的执行情况专门进行了记录，同时用户还可以选择日志中记录信息的级别，有下列三种选择：

1）DBMS_SCHEDULER.LOGGING_OFF：关闭日志记录功能；

2）DBMS_SCHEDULER.LOGGING_RUNS：对任务的运行信息进行记录；

3）DBMS_SCHEDULER.LOGGING_FULL：记录任务所有相关信息，不仅有任务的运行情况，甚至连任务的创建、修改等也均将记入日志。

提示：查看scheduler管理的job，可以通过user_scheduler_job_log和user_scheduler_job_run_details两个视图中查询。

（2）RESTARTABLE：指定jobs运行出错后，是否能够适时重启创建任务时如未明确指定，本参数默认情况下设置为FALSE，如果设置为TRUE，就表示当任务运行时出错，下次运行时间点到达时仍会启动，并且如果运行仍然出错，会继续重新运行，不过如果连接出错达到6次，该job就会停止。

（3）MAX_FAILURES：指定jobs最大连续出错次数该参数值可指定的范围从1-1000000，默认情况下该参数设置为NULL，表示无限制。达到指定出错次数后，该job会被自动disable。

（4）MAX_RUNS：指定jobs最大运行次数，该参数值可指定的范围从1-1000000，默认情况下该参数设置为NULL，表示无限制(只是运行次数无限制，实际job是否继续运行，仍受制于end_date以及max_failures等参数的设置)。达到指定运行次数后，该job也将被自动disable，并且状态会被置为COMPLETED。

（5）JOB_TYPE：指定job执行的任务的类型，有四个可选值：'PLsql_BLOCK','STORED_PROCEDURE','EXECUTABLE',and'CHAIN'。

（6）JOB_ACTION：指定job执行的任务.这一参数所指定的值依赖于JOB_TYPE参数中的值，比如说JOB_TYPE设置为'STORED_PROCEDURE'，那么本参数值中指定的一定是ORACLE中的过程名。

（7）START_DATE：指定job初次启动的时间

（8）END_DATE：指定job停止运行的时间

本参数又与AUTO_DROP相关联，如果AUTO_DROP设置为TRUE的话，那么一旦job到达停止运行的时间，该job就会被自动删除，否则的话job任何存在，不过状态被修改为COMPLETED。

除此之外，其它还包括max_run_duration，job_weight，instance_stickiness，stop_on_window_close，job_priority，schedule_limit，program_name，number_of_arguments，schedule_name，repeat_interval，job_class，comments，auto_drop，event_spec，raise_events等等。

另外需要注意一点，除了用户手动创建的jobs之外，数据库在运行过程中也有可能自动创建jobs。对于这类jobs除非必要，否则不建议进行修改。至于如何区分jobs是用户创建，还是数据库自动创建，可以通过*_SCHEDULER_JOBS视图的SYSTEM列来确定，如果该列显示为TRUE，则表示由系统创建。

执行Jobs

虽然说jobs大多都应该是自动执行，不过经过前面的示例看出，并不是说创建了jobs它就会自动执行，是否能够真正自动执行由jobs自身的多个相关属性

决定。

可以使用DBMS_SCHEDULER包手动调用jobs并执行。如：

sql>execdbms_scheduler.run_job('JOBTEST');

PL/sqlproceduresuccessfullycompleted

停止Jobs

停止job可以使用DMBS_SCHEDULER.STOP_JOB过程，例如：

sql>execdbms_scheduler.stop_job('JOBTEST');

删除Jobs

删除创建的job就比较简单了，直接执行dbms_scheduler.drop_job过程即可，例如：

sql>execdbms_scheduler.drop_job('JOBTEST');

PL/sqlproceduresuccessfullycompleted

删除jobs并不是修改该job中某个字段的标记值，而是直接删除其在数据字典中的字义，因此被删除的job如果未来发现仍然需要，只能重建，而无法通过其它方式快速恢复。不过，删除jobs的操作，并不会级联删除这些job曾经执行过的日志信息。

临时表

ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。

Oracle的临时表创建之后基本不占用表空间，临时表并非存放在用户的表空间中，而是存放在Schema 所指定的临时表空间中。如果你没有指定临时表（包括临时表的索引）存放的表空的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中（TEMP）。

可以对临时表创建索引，视图，触发器，可以用export和import工具导入导出表的定义，但是不能导出数据。表的定义对所有的会话可见。建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效.

尽管对临时表的DML操作速度比较快，但同样也是要产生Redo Log ，只是同样的DML语句，比对PERMANENT 的DML 产生的Redo Log

临时表的不足之处：

1.不支持lob对象，这也许是设计者基于运行效率的考虑，但实际应用中确

实需要此功能时就无法使用临时表了。

2.不支持主外键关系

特性和性能(与普通表和视图的比较)

1.临时表只在当前连接内有效

2.临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用

3.数据处理比较复杂的时候时表快,反之视图快点

4.在仅仅查询数据的时候建议用游标: open cursor for 'sqlclause';

CREATE TEMPORARY TABLESPACEtbs_t1

TEMPFILE 'tbs_t1.f' SIZE 50mREUSE AUTOEXTEND ON

MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL UNIFORMSIZE 64K;

CREATEGLOBAL TEMPORARY TABLE admin_work_area

(startdate DATE,

enddate DATE,

class CHAR(20))

ON COMMIT DELETE ROWS

TABLESPACE tbs_t1;

分析函数

Blog：http://blog.csdn.net/tianlesoftware/archive/2009/11/10/4795632.aspx

Oracle 外键 说明

Blog:http://blog.csdn.net/tianlesoftware/archive/2011/06/04/6526492.aspx

Rowid 介绍

Blog：http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx

rowid说明

rowid是伪列（pseudocolumn），伪劣的意思是实际上这一列本身在数据字典中并不存在，在查询结果输出时它被构造出来的。

rowid并不会真正存在于表的data block中,但是他会存在于index当中，用来通过rowid来寻找表中的行数据。

Rowid的结构

ROWID格式:

扩展的ROWID在磁盘上需要10个字节的存储空间，并使用18个字符来显示。

它包含下列组成元素：

1.数据对象编号：每个数据对象（如表或索引）在创建时都分配有此编号，并且此编号在数据库中是唯一的

2.相关文件编号：此编号对于表空间中的每个数据文件是唯一的

3.块编号：表示包含此行的块在数据文件中的位置

4.行编号：标识块头中行目录位置的位置

在内部，存储的10个字节(bytes)，即80位(bit)又按如下规则进行划分：

（1）数据对象编号需要32 bit

（2）相关文件编号需要10 bit

（3）块编号需要22 bit

（4）行编号需要16 bit

原文链接：https://www.f2er.com/oracle/207820.html