oracle中常用包介绍以及包的用法

- 日理万妓 2022-08-19 02:33 851阅读 0赞

Oracle 10g提供的系统包多达几百个，此处只介绍一些常用的系统包。  
  
一、DBMS\_OUTPUT  
  
1、概述  
作用：用于输入和输出信息,使用过程PUT和PUT\_LINES可以将信息发送到缓冲区,使用过程GET\_LINE和GET\_LINES可以显示缓冲区信息。  
该包用来输出plsql变量的值，属于系统用户sys。下面讲述包的组成：  
  
2、包的组成  
1）、ENABLE  
说明：该过程用于激活本包，如果没有被激活，将无法调用本包的其它其余过程和函数。当调用该过程，缓冲区最大尺寸为1000000字节，最小为2000字节，默认为20000字节。  
注意：如果在SQL\*PLUS中使用SERVEROUTPUT选项，则没有必要使用该过程。  
语法：DBMS\_OUTPUT.ENABLE（buffer\_size in integer default 20000）;  
  
2）、DISABLE  
说明：该过程用于禁止本包，并清除缓冲区的内容。当本包被禁止，将无法调用本包的其它其余过程和函数。  
注意：如果在SQL\*PLUS中使用SERVEROUTPUT选项，则没有必要使用该过程。  
语法：DBMS\_OUTPUT.DISABLE;  
  
3）、put和put\_line  
说明：过程put\_line用于将一个完整行的信息写入到缓冲区中，会自动在行的尾部追加行结束符；  
过程put则用地分块建立行信息，需要换行需要使用过程new\_line追加行结束符。  
语法：dbms\_output.put(item in number\\varchar2\\date);dbms\_output.put\_line(item in number\\varchar2\\date);  
  
当在sql\*plus中使用包过程put、put\_line时，需要设置serveroutput选项。  
例子：  
set serveroutput on  
begin  
dbms\_output.put\_line('伟大的中华民族');  
dbms\_output.put('中国');  
dbms\_output.put(',伟大的祖国');  
dbms\_output.new\_line;  
end;  
  
4）、new\_line  
说明：该过程用于在行的尾部追加行结束符。  
语法：dbms\_output.new\_line;  
  
5）、get\_line和get\_lines  
说明：过程get\_lin用于取得缓冲区的单行信息，get\_lins用于取得缓冲区的多行信息。  
语法：dbms\_output.get\_line(line out varchar2,status out integer);  
dbms\_output.get\_lines(lines out chararr,numlines in out integer);  
其中line用于取得缓冲区的单行信息(最大255字节)，status用于返回过程执行是否成功，0成功1表示没有行；  
lines用于取得缓冲区的多行信息，numlines指定要检索的行数，并返回实际检索的行数。  
  
3、包的应用实例  
1）、例子一  
var line varchar2(100);  
var status number  
begin  
dbms\_output.enable;  
dbms\_output.put\_line('伟大的中华民族');  
dbms\_output.put('中国');  
dbms\_output.put(',伟大的祖国');  
dbms\_output.put.new\_line;  
dbms\_output.get\_line(:line,:status);  
end;  
  
2）、例子二  
declare  
type line\_table\_type is table of varchar2(255) index by binary integer;  
line\_table line\_table\_type;  
lines number(38):=3;  
begin  
dbms\_output.enable;  
dbms\_output.put\_line('伟大的中华民族');  
dbms\_output.put('中国');  
dbms\_output.put(',伟大的祖国');  
dbms\_output.put.new\_lines;  
dbms\_output.get\_line(line\_table,lines);  
end;  
  
  
二、DBMS\_JOB  
  
1、概述  
作用：用于安排和管理作业队列,通过使用作业,可以使ORACLE数据库定期执行特定的任务  
注意：当使用DBMS\_LOB管理作业时，必须确保设置了初始化参数job\_queue\_processes（不能为0）  
  
2、包的组成  
1）、submit  
说明：用于建立一个新作业.当建立作业时,需要给作业要执行的操作,作业的下次运行日期及运行时间间隔.  
语法：  
dbms\_out.submit (  
job out binary\_integer,what in varchar2,  
next\_date in date default sysdate,  
interval in varchar2 default 'null',  
no\_parse in boolean default false,  
instance in binary\_integer default any\_instance,  
force in boolean default false);  
其中，job用于指定作业编号;what用于指定作业要执行的操作;next\_date用于指定作业的下次运行日期;interval用于指定运行作业的时间间隔;  
no\_parse用于指定是否解析与作业相关的过程;instance用于指定哪个例程可以运行作业;force用于指定是否强制运行与作业相关的例程.  
例子1：  
exec dbms\_job.submit(:jobno,'dbms\_ddl.analyze\_object(''table'',''scott'',''emp'',''compute'');',sysdate,'sysdate+1');  
exec dbms\_job.submit(:v\_num,'a;',sysdate,'sysdate + (10/(24\*60\*60))')加入作业。间隔10秒钟  
exec dbms\_job.submit(:v\_num,'a;',sysdate,'sysdate + (11/(24\*60))')加入作业。间隔11分钟  
  
2）、remove  
说明：删除作业队列中的特定作业  
语法：dbms\_job.remove(job in binary\_integer);  
例子：exec dbms\_job.remove(21)--删除21号作业  
  
3）、change  
说明：用于改变与作业相关的所有信息,包括作业操作,作业运行日期以及运行时间间隔等.  
语法：  
dbms\_job.change(  
job in binary\_integer,what in varchar2,  
next\_date in date,interval in varchar2,  
instance in binary\_integer default null,  
force in boolean default false);  
例子：exec dbms\_job.change(2,null,null,'sysdate+2');  
  
4）、what  
说明：用于改变作业要执行的操作  
语法：dbms\_job.what(job in binary\_integer,what in varchar2);  
例子：exec dbms\_job.what(2,'dbms\_stats.gather\_table\_stats->(''scott'',''emp'');');  
  
5）、next\_date  
说明：用于改变作业的下次运行日期  
语法：dbms\_job.next\_date(job in binary\_integer,next\_date in date);  
例子：exec dbms\_job.next\_date('2','sysdate+1');  
  
6）、instance  
说明：用于改变作业的例程  
语法：dbms\_job.instance(job in binary\_integer,instance in binary\_integer,force in boolean default false);  
例子：exec dbms\_job.instance(2,1);  
  
7）、interval  
说明：用于改变作业的运行时间间隔  
语法：dbms\_job.interval(job in binary\_integer,interval in varchar2);  
例子：exec dbms\_job.interval(2,'sysdate+1/24/60');  
  
8）、broken  
说明：用于设置作业的中断标识。当中断了作业之后，作业将不会被运行。  
语法：dbms\_job.broken(job in binary\_integer,broken in boolean,next\_date in date default sysdate);  
其中broken指定中断标记(true表示中断)  
例子：dbms\_job.broken(2,true,'sysdate+1');  
  
9）、run  
说明：用于运行已存在的作业  
语法：dbms\_job.run(job in binary\_integer,force in boolean default false);  
例子：exec dbms\_job.run(1);  
  
  
三、dbms\_pipe  
  
1、概述  
说明：Oracle管道类似UNIX系统的管道，但不采用OS机制实现，管道信息被缓存到SGA中，当关闭例程时会丢失管道信息，建立公用管道所有数据库用户都可访问，私有管道只能由建立这访问。  
作用：用于在同一例程程的不同会话之间进行管道通信.注意,如果用户要执行包dbms\_pipe中的过程和函数,则必须要为用户授权.  
sql>conn sys/oracle as sysdba;  
sql>grant execute on dbms\_pipe to scott;  
  
2、包的组成  
1）、create\_pipe  
作用：该函数用于建立公用管道或私有管道.如果将参数private设置为TRUE,则建立私有管道;如果设置为FALSE,则建立公用管道.  
语法：dbms\_pipe.create\_pipe(pipename in varchar2,maxpipesize in integer default 8192,private in boolean default true) return integer;  
其中，pepename指定管道名称，maxpipesize指定管道消息的最大尺寸，private指定管道类型，函数返回0则成功，反之失败。  
  
2）、pack\_message  
作用：该过程用于将变量写入到本地消息缓冲区。  
说明：为了个管道发消息，首先使用过程pack\_message将消息写入本地消息缓冲区，然后使用send\_message将消息发送到管道。  
语法：dbns\_pipe.pack\_message(item in varchar2/nchar2/number/date);  
dbns\_pipe.pack\_message\_raw(item in raw);  
dbns\_pipe.pack\_message\_rowid(item in rowid);  
  
3）、send\_message  
作用：该函数用于将本地消息缓冲区中的内容发送到管道。  
语法：dbms\_pipe.send\_message(pipename in varchar2,timeout in integer defalut maxwait,maxpipesize in integer default 8192) return integer;  
其中，timeout指定发送消息的超时时间，0成功1超时3中断。  
  
4）、receive\_message  
说明：该函数用于接收管道消息，并将接收到的消息写入到本地消息缓冲区。当接收完管道信息之后，会删除管道消息，管道消息只能被接收一次。  
语法：  
dbms\_pipe.receive\_message(pepename in varchar2,timeout in integer default maxwait) return integer;  
其中，返回0接受成功，返回1超时，返回2本地缓冲区不能容纳管道消息，返回3发生中断。  
  
5）、next\_item\_type  
说明：该函数用于确定本地消息缓冲区下一项的数据类型。在调用receive\_message之后调用。  
语法：dbms\_pipe.next\_item\_type return integer;  
其中，如果该函数返回0,则表示管道没有任何消息;如果返回6,则表示下一项的数据类型为number;如果返回9,则表示下一项的数据类型为varchar2;  
如果返回11,则表示下一项的数据类型为rowid;如果返回12,则表示下一项的数据类型为date;如果返回23,则表示下一项的数据类型为raw.  
  
6）、unpack\_message  
作用：该过程用于将消息缓冲区的内容取出来写入到变量中，每次只能取一条，需要取出多条需要多次调用。  
说明：在使用函数receive\_message接收到管道消息之后，应该使用过程unpack\_message取得消息缓冲区的消息。  
语法：dbms\_pipe.unpack\_message(item out varchar2\\nchar\\number\\date);  
dbms\_pipe.unpack\_message\_raw(item out raw);  
dbms\_pipe.unpack\_message\_rowid(item out rowid);、  
  
7）、remove\_pipe  
作用：该函数用于删除已经建立的管道  
语法：dbms\_pipe.remove\_pipe(pepename in varchar2) return integer;  
其中，函数返回0表示成功，否则会显示错误信息。  
  
8）、purge  
说明：该过程用于清除管道中的内容。  
语法：dbms\_pipe.purge(pipename in varchar2);  
  
9）、reset\_buffer  
说明：该过程用于复位管道缓冲区，因为所有管道都共享单个管道缓冲区，所以在使用新管道之前应该复位管道缓冲区。  
语法：dbms\_pipe.reset\_buffer;  
  
10）、unique\_session\_name  
说明：该函数用于为特定会话返回惟一的名称,并且名称的最长度为30字节，对同一会话其值不变。  
语法：dbms\_pipe.unique\_session\_name  
  
3、包的应用  
1）、综合例子1  
declare  
falg int;  
v\_ename emp.ename%type;  
v\_sal emp.sal%type;  
v\_rowid rowid;  
item\_no int;  
message varchar2(100);  
v\_session varchar2(200);  
begin  
flag := dbms\_pipe.create\_pipe('public\_pipe', 8192, false);  
if flag = 0 then  
dbms\_output.put\_line('建立公用管道成功');  
end if;  
select ename,sal,rowid into v\_ename,v\_sal,v\_rowid from emp where empno=7788;  
dbns\_pipe.pack\_message(v\_ename||','||v\_sal||','||v\_rowid);  
flag :dbms\_pipe.send\_message('PUBLIC\_PIPE');  
if flag = 0 then  
dbms\_output.put\_line('发送成功');  
end if;  
flag := dbms\_pipe.receive\_message('PUBLIC\_PIPE');  
if flag = 0 then  
dbms\_output.put\_line('成功');  
end if;  
item\_no := dbms\_pipe.next\_item\_type;  
dbms\_output.put\_line(item\_no);  
dbms\_pipe.unpack\_message(message);  
dbms\_output.put\_line(message);  
\--remove\_pipe  
flag:=dbms\_pipe.remove\_pipe('PUBLIC\_PIPE');  
if flag = 0 then  
dbms\_output.put\_line('删除成功');  
end if;  
\--unique\_session\_name  
v\_session:=dbms\_pipe.unique\_session\_name;  
dbms\_output.put\_line(v\_session);  
end;  
  
2）、综合例子2  
使用管道是，一个会话需要将消息发送到管道中，另一个会话则需要接收管道消息。  
发送消息到管道需要先将消息写入本地消息缓冲区，然后再发送到管道；  
接收消息需要先使用本地消息缓冲区接收管道消息，然后从消息缓冲区取得具体消息。  
create or replace procedure send\_message(pepename varchar2,message varchar2) is  
flag int;  
begin  
flag:=dbms\_pipe.create\_pipe(pipename);  
if flag=0 then  
dbms\_pipe.pack\_message(message);  
flag:=dbms\_pipe.send\_message(pipename);  
end if;  
end send\_message;  
  
create or replace procedure receive\_message(pipename varchar2,message out varchar2) is  
flag int;  
begin  
flag:=dbms\_pipe.receive\_message(pipename);  
if flag=0 then  
dbms\_pipe.unpack\_message(message);  
flag:=dbms\_pipe.remove\_pipe(pipename);  
end if;  
end receive\_message;  
  
会话一:exec send\_message('pipe1','你好');  
会话二：  
var message varchar2(100)  
exec scorr.receive\_message('pipe1',:message)  
print message  
  
  
四、dbms\_alert  
  
1、概述  
作用:用于生成并传递数据库预警信息.使用包DBMS\_ALERT,则必须以SYS登陆,为该用户授予执行权限.  
sql>conn sys/oracle as sysdba  
sql>grant execute on dbms\_alert to scott;  
  
2、包的组成  
  
1）、register  
说明：用于注册预警事件  
语法：dbms\_alter.register(name in varchar2);  
其中name指定预警事件名称，其值不能超过30字节。  
例子：exec dbms\_alter.register('alter1');  
  
2）、remove  
说明：用于删除会话不需要的预警事件.  
语法：dbms\_alert.remove(name in varchar2);  
例子：exec dbms\_alert.remove('alert1');  
  
3）、removeall  
说明：用于删除当前会话所有已注册的预警事件  
语法：dbms\_alter.removeall  
  
4）、set\_defaults  
说明：用于设置检测预警事件的时间间隔,默认时间间隔为5秒  
语法：dbms\_alert.set\_defaults(sensitivity in number);  
例子：dbms\_alert.set\_defaults(20)  
  
5）、signal  
说明：用于指定预警事件所对应的预警消息。只有在提交事务时才会发出预警信号，而当回退事务时不会发出预警信号。  
语法：dbms\_alert.signal(name in varchar2,message in varchar2);  
其中message指定预警事件的消息，长度不超过1800字节。  
例子：exec dbms\_alert.signal('alert1','hello');  
  
6）、waitany  
说明：用于等待当前会话的任何预警事件,并且在预警事件发生时输出相应信息.在执行该过程之前,会隐含地发出COMMIT.  
语法：  
dbms\_alter.waitany(name out varchar2,message out varchar2,status out integer,timeout in number default maxwait);  
其中status用于返回状态值,返回0表示发生了预警事件,返回1表示超时;timeout用于设置预警事件的超时时间.  
  
7）、waitone  
说明：用于等待当前会话的特定预警事件,并且在发生预警事件时输出预警消息.在执行该过程之前,会隐含地发出COMMIT.  
dbms\_alter.waitone(name out varchar2,message out varchar2,status out integer,timeout in number default maxwait);  
  
3、应用实例  
修改员工工资时发出预警  
create or replace trigger tr\_upd\_sal  
after update of sal on emp  
begin  
dbms\_alert.signal('sal\_upd\_alert','修改了雇员工资');  
end;  
  
create or replace procedure wait\_event(name varchar2) is  
message varchar2(200);  
status int;  
begin  
dbms\_alert.register(name);  
dbms\_alert.waitone(name,message,status);  
if status=0 then  
dbms\_output.put\_line(message);  
end if;  
dbms\_alert.remove(name);  
end wait\_event;  
  
set serveroutput on   
begin  
for i in 1..5 loop  
wait\_event('sal\_upd\_alert');  
end loop;  
end;  
  
  
五.dbms\_transaction  
  
1、概述  
作用:用于在过程,函数,和包中执行SQL事务处理语句.  
  
2、包的组成  
1）、read\_only  
说明：用于开始只读事务,其作用与SQL语句SET TRANSACTION READ ONLY完全相同，该过程必须是事务开始的第一条语句。  
语法：dbms\_transactino.read\_only  
  
2）、read\_write  
说明：用于开始读写事务,其作用与SQL语句SET TRANSACTION READ WRITE完全相同，该过程必须是事务开始的第一条语句。  
语法：dbms\_transactino.read\_write  
  
3）、advise\_rollback  
说明：用于建议回退远程数据库的分布式事务，其作用与SQL语句alter seesion advise rollback完全相同  
语法：dbms\_transactino.advise\_rollback  
  
4）、advise\_nothing  
说明：用于建议远程数据库的分布式事务不进行任何处理，其作用与SQL语句alter seesion advise nothing完全相同  
语法：dbms\_transactino.advise\_nothing  
  
5）、advise\_commit  
说明：用于建议提交远程数据库的分布式事务，其作用与SQL语句alter seesion advise commit完全相同  
语法：dbms\_transactino.advise\_commit;  
  
6）、use\_rollback\_segment  
说明：用于指定事务所要使用的回滚段，其作用与SQL语句set transaction use rollback segment完全相同  
语法：dbms\_transactino.use\_rollback\_segment(rb\_name varchar2);  
其中，rb\_name指定事务所要使用的回滚段名称。  
  
7）、commit\_comment  
说明：用于在提交事务时指定注释，其作用与SQL语句commit comment<text>完全相同  
语法：dbms\_transactino.commit\_comment(cmnt varchar2);  
其中cmnt用于指定与事务相关的注释信息。  
  
8）、commit\_force  
说明：用于强制提交分布式事务，其作用与SQL语句commit force text,number完全相同  
语法：dbms\_transaction.commit\_force(xid varchar2,scn carchar2 default null);  
  
9）、commit  
说明：用于提交当前事务，其作用与sql语句commit完全相同。  
语法：dbms\_transaction.commit;  
  
10）、savepoint  
说明：用于设置保存点，其作用与sql语句savepoint 完全相同。  
语法：dbms\_transaction.sevepoint(savept varchar2);  
其中，savept指定保存点名称。  
  
11）、rollback  
说明：用于回退当前事务，其作用与sql语句rollback完全相同。  
语法：dbms\_transaction.rollback;  
  
12）、rollback\_savepoint  
说明：用于回退到保存点，并取消部分事务，其作用与sql语句rollback to savepoint<savepoint\_name>完全相同。  
语法：dbms\_transaction.rollback\_savepoint(savept varchar2);  
  
13）、rollback\_force  
说明：用于强制回退分布式事务，其作用与sql语句rollback force<text>完全相同。  
语法：dbms\_transaction.rollback\_force(xid varchar2);  
  
14）、begin\_discrete\_transaction  
说明：用于开始独立事务模式  
语法：dbms\_transaction.begin\_discreate\_transaction;  
  
15）、purge\_mixed  
说明：用于清除分布式事务的混合事务结果  
语法：dbms\_transaction.purge\_mixed(xid varchar2);  
其中xid指定事务ID号。  
  
16）、purge\_lost\_db\_entry  
说明：用于清除本地数据库所记载的远程事务入口,该事务入口操作因为远程数据库问题未能在远程数据库完成.  
语法：dbms\_transaction.purge\_lost\_db\_entry(xid varchar2);  
其中xid指定事务ID号。  
  
17）、local\_transaction\_id  
说明：用于返回当前事务的事务标识号  
语法：dbms\_transaction.local\_transaction\_id(create\_transaction boolean:=false) return varchar2;  
其中create\_transaction指定是否要启动新事务，true为启动。  
  
（18）、step\_id  
说明：用于返回排序DML事务的惟一正整数  
语法：dbms\_transaction.step\_id return number;  
  
  
六.dbms\_session  
  
1、概述  
作用:提供了使用PL/SQL实现ALTER SESSION命令,SET ROLE命令和其他会话信息的方法.  
  
2、包的组成  
1）、set\_identifier  
说明：用于设置会话的客户ID号。  
语法：dbms\_session.set\_identifier(client\_id varchar2);  
其中client\_id指定当前会话的应用标识符。  
  
2）、set\_context  
说明：用于设置应用上下文属性  
语法：  
dbms\_session.set\_context(namespace varchar2,attribute varchar2,value varchar2);  
dbms\_session.set\_context(namespace varchar2,attribute varchar2,value varchar2,username varchar2,client\_id varchar2);  
其中namespace指定应用上下文的命名空间，attribute指定应用上下文的属性，value指定属性值，username指定应用上下文的用户名属性。  
  
3）、clear\_context  
说明：用于清除应用上下文的属性设置  
语法：dbms\_session.clear\_context(namespace varchar2,client\_identifier varchar2,attribute varchar2);  
其中client\_identifier只适用于全局上下文。  
  
4）、clear\_identifier  
说明：用于删除会话的set\_client\_id.  
语法：dbms\_session.clear\_identifier();  
  
5）、set\_role  
说明：用于激活或禁止会话角色，与sql语句set role作用完全相同。  
语法：dbms\_seesion.set\_role(role\_cmd varchar2);  
例子：在sql\*plus使用该过程激活或禁止角色如下  
dbms\_session.set\_role('DBA')  
dbms\_session.set\_role('none')  
  
6）、set\_sql\_trace  
说明：用于激活或禁止当前会话的SQL跟踪，其作用与SQL语句alter session set sql\_trace= 完全相同。  
语法如下:dbms\_session.set\_sql\_trace(sql\_strace boolean);  
其中sql\_strace用于指定布尔值，true表示激活sql跟踪，false表示禁止。  
例子：在sql\*plus使用该过程  
exec dbms\_session.set\_sql\_trace(true);  
exec dbms\_session.set\_sql\_trace(false);  
  
7）、set\_nls  
说明：用于设置NLS特征，其作用与sql语句alter session set <nls\_param>=<value>完全相同。  
语法：dbms\_session.set\_nls(param varchar2,value varchar2);  
其中param指定nls参数，value指定nls参数  
例子：在sql\*plus使用该过程  
exec dbms\_seesion.set\_nls('nls\_date\_format','''YYYY-MM-DD''');  
select sysdate from dual;  
  
8）、close\_database\_link  
说明：用于关闭已经打开的数据库链，其作用与SQL语句alter session close datebase link<name>完全相同。  
语法：dbms\_session.close\_database\_link(dbline varchar2);  
其中用于指定要关闭的数据库连接名。  
  
9）、reset\_package  
说明：用于复位当前会话的所有包,并且会释放包状态。  
语法：dbms\_session.reset\_package;  
  
10）、modify\_package\_state  
说明：用于修改当前会话的PL/SQL程序单元的状态  
语法:dbms\_session.modify\_package\_state(action\_flags in pls\_integer);  
其中，action\_flags指定pl/sql程序单元标记，当设置为1是，会释放plsql程序单元所占用的内存；2则会重新初始化pl/sql包。  
  
11）、unique\_session\_id  
说明：用于返回当前会话的惟一ID标识符  
例子：select dbms\_session.unique\_session\_id from dual;  
  
12）、is\_role\_enabled  
说明：用于确定当前会话是否激活了特定角色.  
语法:dbms\_session.is\_role\_enabled(rolename varchar2) return boolean;  
其中rolename指定角色名。返回true表示已激活，false表示未激活。  
例子：set serveroutput on  
begin  
if dbms\_session.is\_role\_enabled('DBA') then  
dbms\_output.put\_line('激活了');  
else dbms\_output.put\_line('没激活');  
end if;  
end;  
  
13）、is\_session\_alive  
说明：用于确定特定会话是否处于活动状态.  
语法：dbms\_session.is\_session\_alive(uniqueid varchar2) return boolean;  
其中uniqueid指定会话ID号，处于活动返回true，否则false。  
  
14）、set\_close\_cached\_open\_cursors  
说明：用于打开或关闭close\_cached\_open\_cursors，其作用与alter session set close\_cached\_open\_cursors完全相同。  
语法：dbms\_session.set\_close\_cached\_open\_cursors(close\_cursors boolean);  
其中close\_cursors指定布尔值，设置为true打开，false关闭。  
  
15）、free\_unused\_user\_meory  
说明：用于在执行了大内存操作(超过100K)之后回收未用内存  
语法：dbms\_session.pree\_unused\_user\_memory;  
  
16）、set\_context  
说明：设置应用上下文属性的值.  
语法：dbms\_session.set\_context(namespare varchar2,attribute varchar2,value varchar2,username varchar2,client\_id varchar2);  
  
17）、list\_context  
说明：用于返回当前会话原命名空间和上下文列表  
语法：  
type appctxrectyp is record(namespace varchar2(30),attribute varchar2(30),value varchar2(256));  
type appctxtabtyp is table of appctxrectyp index by binary\_integer;  
dbms\_session.list\_context(list out appctxtabtyp,size out number);  
其中list用于取得当前会话的列表集，size用于返回列表个数。  
  
18）、swith\_current\_consumer\_group  
说明：用于改变当前会话的资源使用组  
语法：dbms\_session.switch\_current\_consumer\_group(  
new\_group in varchar2,old\_group out varchar2,initial\_group in boolean);  
其中new\_group指定新资源使用组；old\_group指定原有资源实用组；initial\_group指定布尔值，true表示出错后使用原有资源使用组。  
  
  
七、dbms\_rowid  
  
1、概述  
作用:用于在PL/SQL程序和SQL语句中取得行标识符(rowid)的信息并建立ROWID,通过该包可以取得行所在的文件号,行所在文件的数据块号,行所在数据块的行号,以及数据库对象号等消息.  
  
2、包的组成  
1）、rowid\_create  
说明：建立ROWID  
语法:dbms\_rowid.rowid\_create (  
rowid\_type in number,object\_number in number,  
relative\_fno in n umber,block\_number in number,  
row\_number in number) return rowid;  
其中，rowid\_type用于指定ROWID类型(0:受限ROWID,1:扩展ROWID);object\_number用于指定数据对象号;relative\_fno用于指定相对文件号;block\_number用于指定在文件中的数据块号;row\_number用于指定在数据块中的行号.  
  
2）、rowid\_info  
说明：用于取得特定ROWID的详细信息.  
语法：dbms\_rowid\_info(  
rowid\_in in rowid,rowid\_type out number,  
object\_number out number,relative\_fno out number,  
block\_number out number,row\_number out number);  
其中rowid\_in指定rowid。  
  
3）、rowid\_type  
说明：用于返回特定ROWID的类型  
语法：dbms\_rowid.rowid\_type(row\_id in rowid) return number;  
其中返回0表示受限rowid，返回1表示扩展rowid。  
例子：select dbms\_rowid.rowid\_type(AAAC90AAFAAAAACAAK) from dual;  
  
4）、rowid\_object  
说明：用于取得特定ROWID所对应的数据对象号  
语法：dbms\_rowid.rowid\_object(row\_id in rowid) return number;  
例子：select dbms\_rowid.rowid\_object(row\_id) from dept;  
  
5）、rowid\_relative\_fno  
说明：用于取得特定ROWID所对应的相对文件号  
语法：dbms\_rowid.rowid\_relative\_fno(row\_id in rowid) return numer;  
例子：select dbms\_rowid.rowid\_relative\_fno(rowid) from dept;  
  
6）、rowid\_block\_number  
说明：用于返回特定ROWID在数据文件中所对应的数据块号.  
语法：dbms\_rowid.rowid\_block\_number(row\_id in rowid) return number;  
例子：select dbms\_rowid.rowid\_block\_number(rowid) from dept;  
  
7）、rowid\_row\_number  
说明：用于返回特定ROWID在数据块中所对应的行号.  
语法：dbms\_rowid.rowid\_row\_number(row\_id in rowid) return number;  
例子：select dbms\_rowid.rowid\_row\_number(rowid) from dept;  
  
8）、rowid\_to\_obsolute\_fno  
说明：用于返回特定ROWID所对应的绝对文件号  
语法：dbms\_rowid.rowid\_to\_obsolute\_fno(row\_id in rowid,schema\_name in varchar2,object\_name in varchar2) return number;  
declare  
my\_rowid rowid;  
rowid\_type number;  
object\_number number;  
relative\_fno number;  
block\_number number;  
row\_number number;  
abs\_fno int;  
rowid\_val char(18);  
begin  
\--rowid\_create  
my\_rowid:=dbms\_rowid.rowid\_create(1,12197,3,100,1);  
dbms\_output.put\_line(my\_rowid);  
\--rowid\_info  
dbms\_rowid.rowid\_info('AAAC90AAFAAAAACAAK',rowid\_type,object\_number,relative\_fno,block\_number,row\_number);  
dbms\_output.put\_line('数据对象号'||object\_number);  
\--rowid\_to\_obsolute\_fno  
select rowid into rowid\_val from emp where empno=7788;  
abs\_fno:=dbms\_rowid.rowid\_to\_obsolute\_fno(rowid\_val,'scott','emp');  
dbms\_output.put\_line('7788对应的绝对文件号'||abs\_fno);  
end;  
  
9）、rowid\_to\_extended  
说明：用于将受限rowid转变为扩展rowid  
语法：dbms\_rowid.rowid\_to\_extended(old\_rowid in rowid,schema\_name in varchar2,object\_name in varchar2,conversion\_type in integer) return rowid;  
其中，conversion\_type指定转换类型(rowid\_convert\_internal/external\_convert\_external)  
例子；select empno,ename from emp where rowid=dbms\_rowid.rowid\_to\_extended('0000001C.0007.0005','SCOTT','EMP',0);  
  
10）、rowid\_to\_restricted  
说明：用于将扩展rowid转换为受限rowid  
语法：dbms\_rowid.rowid\_to\_restricted(old\_rowid in rowid,conversion\_type in integer) return rowid;  
例子：select dbms\_rowid.rowid\_to\_restricted(rowid,0) from emp;  
  
11）、rowid\_verify  
说明：检查是否可以将受限rowid转变为扩展rowid  
语法：dbms\_rowid.rowid\_verify(rowid\_in in rowid,schema\_name in varchar2,object\_name in varchar2,vonversion\_type in integer) return number;  
其中返回0表示可以，1表示不能。  
例子：select dbms\_rowid.rowid\_verify('0000001C.0007.0005','SCOTT','EMP',0) from dual;  
  
  
八、dbms\_rls  
1、概述  
本报只适用于Oracle Enterprise Edition,它用于实现精细访问控制,并且精细访问控制是通过在SQL语句中动态增加谓词(WHERE子句)来实现的.  
通过使用ORACLE的精细访问控制特征,可以使不同数据库用户在执行相同SQL语句时操作同一张表上的不同数据.  
例如多个用户执行select \* from emp时，各自看到的行数不同。A只能看到财务部的记录，B只能看到市场部的数据。  
  
2、包的组成  
1）、add\_policy  
作用：用于为表、视图或同义词增加一个安全策略，当执行该操作结束是会自动提交事务。  
语法dbms\_rls.add\_policy(  
object\_aschema in varchar2 null,  
object\_name in varchar2,  
policy\_name in varchr2,  
function\_schema in varchar2 null,  
policy\_function in varchar2,  
statement\_type in varchar2 null,  
update\_check in boolean false,  
enable in boolean true,  
static\_policy in boolean false);  
其中object\_aschema指定包含表、视图或同义词的方案(默认null表示当前方案)，  
object\_name指定要增加安全策略的表、视图或同义词，  
policy\_name指定要增加的安全策略名称，  
function\_schema指定策略函数的所在方案(默认null表示当前方案)，  
policy\_function指定生成安全策略谓词的函数名，  
statement\_type指定使用安全策略的sql语句(默认null表示适用于select、insert、update、delete)，  
update\_check指定执行insert、update时是否检查安全策略，  
enable指定是否要激活安全策略，  
static\_policy指定是否要生成静态的安全策略。  
  
2）、drop\_policy  
作用：用于删除定义在特定表、视图或同义词的安全策略，当试行该操作结束时会自动提交事务。  
语法：dbms\_rls.drop\_policy(  
object\_achema in varchr2 null,  
object\_name in varchar2,  
policy\_name in varchar2);  
  
3）、refresh\_policy  
作用：用于刷新与安全策略修改相关的所有sql语句，并使得Oracle重新解析相关sql语句，自动提交。  
语法：dbms\_rls.refresh\_policy(  
object\_achema in varchr2 null,  
object\_name in varchar2 null,  
policy\_name in varchar2 null);  
  
4）、enable\_policy  
说明：用于激活或禁止特定的安全策略，默认情况下当增加安全策略时会自动激活，自动提交。  
语法：dbms\_rls.enable\_policy(  
object\_achema in varchr2 null,  
object\_name in varchar2,  
policy\_name in varchar2,  
enable in boolean);  
  
5）、create\_policy\_group  
作用：用于建立安全策略组  
语法：dbms\_rls.create\_policy\_group(object\_schema varchar2, object\_name varchar2,policy\_group varchar2);  
其中policy\_group指定策略组的名称。  
  
6）、add\_grouped\_policy  
作用：用于增加与特定策略组相关的安全策略。  
语法：dbms\_rls.add\_grouped\_policy(object\_schema varchar2, object\_name varchar2,policy\_group varchar2,policy\_name varchar2,  
function\_schema varchar2,policy\_function varchar2,statement\_types varchar2,update\_check boolean,enabled boolean,static\_policy boolean false);  
  
7）、add\_policy\_context  
作用：用于为应用增加上下文  
语法：dbms\_rls.add\_policy\_context(object\_schema varchar2,object\_name varchar2,namespace varchar2,attribute varchar2);  
其中namespace指定命名空间，attribute指定上下文属性。  
  
8）、delete\_policy\_group  
作用：用于删除安全策略组  
语法：dbms\_rls.delete\_policy\_group(object\_schema varchar2,object\_name varchar2,policy\_group varchar2);  
  
9）、drop\_grouped\_policy  
作用：用于删除特定策略组的安全策略。  
语法：dbms\_rls.drop\_grouped\_policy(object\_schema varchar2,object\_name varchar2,policy\_group varchar2,policy\_name varchar2);  
  
10）、drop\_policy\_context  
作用：用于删除对象的上下文。  
语法：dbms\_rls.drop\_policy\_context(object\_schema varchar2,object\_name varchar2,namespace varchar2,attribute varchar2);  
  
11）、enable\_grouped\_policy  
作用：用于激活或禁止特定策略组的安全策略  
语法：dbms\_rls.enable\_grouped\_policy(object\_schema varchar2,object\_name varchar2,group\_name varchar2,policy\_name varchar2,enable boolean);  
  
12）、refresh\_grouped\_policy  
作用：用于刷新与特定安全策略组的安全策略相关的sql语句(重新解析sql语句)  
语法：dbms\_rls.refresh\_grouped\_policy(object\_schema varchar2,object\_name varchar2,group\_name varchar2,policy\_name varchar2);  
  
3、使用dbms\_rls实现精细访问控制  
假设希望sys、system、scott用户可以访问emp表的所有员工，blake用于智能访问部门30的员工，jones用户只能访问部门20的员工，其他用户只能访问部门10的员工。  
  
步骤如下  
1）、建立应用上下文  
说明：要求用户必须具有create any context系统权限。  
create or replace context empenv using scott.ctx;--建立名称为empenv的应用上下文，其属性有scott方案的包ctx包设置。  
  
2）、建立包过程设置应用上下文属性  
create or replace package scott.ctx as  
procedure set\_deptno;  
end;  
create or replace package body scott.ctx as   
procedure set\_deptno is   
id number;  
begin  
if sys\_context('userenv','session\_user')='JONES' then  
dbms\_session.set\_context('empenv','deptno',20);  
elsif sys\_context('userenv','session\_user')='BLAKE' then  
dbms\_session.set\_context('empenv','deptno',30);  
else  
dbms\_session.set\_context('empenv','deptno',10);  
end if;  
end;  
end;  
  
3）、建立登录触发器  
说明：用户登录数据库之后会自动触发登陆触发器，建立登录触发器的目的是要隐含调用过程ctx.set\_deptno，从而设置上下文属性。必须要以sys用户身份建立登陆触发器。  
conn sys/oracle as sysdba  
create or replace trigger login\_trig  
after logon on database call scott.ctx.set\_deptno  
  
4）、建立策略函数  
策略函数必须带有2个参数，第一个参数对应于方案名，第二个参数对应于表名、视图名、同义词名。  
create or replace package scott.emp\_security as  
function emp\_sec(p1 varchar2,p2 varchar2) return varchar2;  
end;  
create or replace package body scott.emp\_security ad  
function emp\_sec(p1 varchar2,p2 varchar2) return varchar2  
is  
d\_predicate varchar2(2000);  
begin  
if user not in ('SYS','SYSTEM','SCOTT') then  
d\_predicate:='deptno=SYS\_CONTEXT(''empenv'',''deptno'')'  
return d\_predicate;  
end if;  
return '1=1';  
end;  
end;  
  
5）、增加策略  
说明：增加策略，并定义对象、策略、策略函数以及SQL语句之间的对应关系。  
execute dbms\_rls.add\_policy('scott','emp','emp\_policy','scott','emp\_security.emp\_sec','select');  
执行add\_policy后会在系统默认策略组sys\_default中增加策略emp\_policy，并在scott.emp上的select语句使用该策略。  
其中第一个参数是对象所在方案名，第二个参数是对象名，第三个参数是策略名，第四个参数策略函数所在的方案名，  
第五个参数时策略函数，第六个参数是使用该策略的sql语句(如果不指定则select,insert,ypdate,delete都会使用该策略)  
  
当SYS,SYSTEM,SCOTT登录查询，谓词为1=1，当JONES登陆谓词为deptno=20，当blake登陆谓词为deptno=30。  
  
  
九、dbms\_ddl  
1、概述  
作用：提供了在PL/SQL块中执行DDL语句的方法,并且也提供了一些DDL的特殊管理方法.  
  
2、包的组成  
1）、alter\_compile  
说明：用于重新编译过程、函数和包  
语法：dbms\_ddl.alter\_compile(type varchar2,schema varchar2,name varchar2);  
其中type指定对象类型(procedure,function,package,trigger)，schema指定对象所在方案，name指定对象名  
例子：dbms\_ddl.alter\_compile('PROCUDURE',NULL,'ADD\_EMP');  
  
2）、analyze\_object  
作用：用于分析表、索引、簇并生成统计数据  
语法：dbms\_ddl.analyze\_object(type varchar2,schema varchar2,name varchar2,method varchar2,  
estimate\_rows number default null,estmate\_percent number default null,  
method\_opt varchar2 default null,partname varchar2 default null);  
其中type指定对象类型(table\\index\\delete)，method指定分析方法(compute,estimate,delete)，estimate\_rows指定要顾忌的行数，estimate\_percent指定要顾忌的百分比，  
method\_opt指定分析选项(for table,for all columns等)，partname指定要分析的分区。  
例子：exec dbms\_ddl.analyze\_object('TABLE',null,'EMP','COMPUTE');  
  
3）、is\_trigger\_fire\_once  
作用：用于检测特定的DML或DDL触发器是否只执行一次  
语法：dbms\_ddl.is\_trigger\_fire\_once(trig\_owver in varchar2,trig\_name in varchar2) return boolean;  
其中，trig\_owver指定触发器所有者，trig\_name指定触发器名，返回true表示只被触发一次。  
  
4）、set\_trigger\_firing\_property  
作用：用于设置DML或DDL触发器的触发属性  
语法：dbms\_ddl.set\_trigger\_firing\_property(trig\_owner in varchar2,trig\_name in varchar2,fire\_once in boolean);  
其中，fire\_once指定触发器属性，当设置为true时只触发一次，false时总是被触发。  
  
  
十、dbms\_shared\_pool  
1、概述  
作用：提供了对共享池的一些过程和函数访问,它使用户可以显示共享池中的对象尺寸,绑定对象到共享池,清除绑定到共享池的对象.为了使用该包,必须运行dbmspool.sql脚本来建立该包.  
  
2、包的组成  
1）、sizes  
作用：用于显示在共享池中大于指定尺寸的对象  
语法：dbms\_shared\_pool.sizes(minsize number);  
其中minsize用于指定要显示对象的最小尺寸(单位KB)  
例子：exec dbms\_shared\_pool.sizes(100);  
  
2）、keep  
作用：用于将特定对象绑定到共享池中  
语法：dbms\_shared\_pool.keep(name varchar2,flag char default 'P');  
其中name指定要绑定的对象名，flag指定对象类型(P是过程、函数和包；T是对象类型；R是触发器；Q是序列)。  
例子：exec dbms\_shared\_pool.keep('standard');  
  
3）、unkeep  
作用：用于清除被绑定到共享池中的对象  
语法：dbms\_shared\_pool.unkeep(name varchar2,flag char default 'P');  
例子：exec dbms\_shared\_pool.unkeep('standard');  
  
4）、aborted\_request\_threshold  
作用：用于设置共享池终止请求的阈值。  
语法：dbms\_shared\_pool.aborted\_request\_threshold(threshold\_size number);  
例子：threshold\_size用于指定共享池阈值尺寸  
例子：exec dbms\_shared\_pool.aborted\_request\_threshold(100000);  
  
  
十一、dbms\_random  
1、概述  
提供了内置的随机数生成器,可以用于快速生成随机数.  
  
2、包的组成  
1）、initialize  
作用：用于初始化dbms\_random包，必须提供随机数种子。  
语法：dbms\_random.initialize(seed in binary\_integer);  
其中seed指定随机数种子。  
  
2）、seed  
作用：用于复位随机数种子  
语法：dbms\_random.seed(seed in binary\_integer);  
  
3）、random  
作用：用于生产随机数  
语法：dbms\_random.randon return binary\_integer;  
  
4）、terminate  
作用：关闭DBMS\_RANDOM包  
语法：dbms\_random。terminate；  
  
3、综合例子  
declare  
num int;  
seed number:=1000000;  
begin  
dbms\_random.initialize(seed);  
for i in 1..10 loop  
num:=abs(dbms\_random.random()/seed);  
dbms\_output.put\_line(num);  
end loop;  
dbms\_random.terminate.  
end;  
  
  
十二、dbms\_logmnr  
1、概述  
作用:通过使用包DBMS\_LOGMNR和DBMS\_LOGMNR\_D,可以分析重做日志和归档日志所记载的事务变化,最终确定误操作(例如DROP TABLE)的时间,跟踪用户事务操作,跟踪并还原表的DML操作.  
  
2、包的组成  
1）、dbms\_logmnr.add\_logfile  
作用：用于为日志分析列表增加或删除日志文件，或者建立日志分析列表。  
语法：dbms\_logmnr.add\_logfile(LogFileName in varchar2,Option in binary\_integer default addfile);  
其中LogFileName指定要增加或删除的日志文件名称，Option指定选项(dbms\_logmnr.new建立日志分析列表，dbms\_logmnr.addfile增加日志文件，dbms\_logmnr.removefile删除文件)  
  
2）、dbms\_logmnr.start\_logmnr  
作用：用于启动logmnr会话  
语法：dbms\_logmnr.start\_logmnr(startscn in number default o,endscn in number default 0,  
starttime in date default '01-jan-1988',endtime in date default '01-jan-2988',  
dictfilename in varchar2 default '',option in binary\_integer default 0);  
其中startscn指定日志分析的起始scn值，endscn指定日志分析的结束scn值，starttime指定日志分析的起始时间，endtime指定日志分析的结束时间，  
dictfilename指定日志分析要使用的字典文件名，option指定logminer分析选项。  
  
3）、dbms\_logmnr.end\_logmnr  
作用：结束logminer会话  
语法：dbms\_logmnr.end\_logmnr  
  
4）、dbms\_logmnr.mine\_value  
作用：用于返回要摘取的列信息，该函数在启动logminer之后调用。  
语法：dbms\_logmner.mine\_value(sql\_redo\_undo in raw,column\_name in varchar2 default '') return varchar2;  
其中sql\_redo\_undo用于指定要摘取的数据(redo\_value或undo\_value)，column\_name用于指定要摘取的列(格式：schema.table.column);  
  
5）、dbms\_logmnr.column\_present  
作用：用于确定列是否出现在数据的redo部分或undo部分  
语法：dbms\_logmnr.column\_present(sql\_redo\_undo in raw,column\_name in varchar2 default '') return number;  
其中如果列在redo或undo部分存在，则返回1，否则返回0。  
  
6）、dbms\_logmnr\_d.build  
作用：用于建立字典文件  
语法：dbms\_logmnr\_d.build(dictionary\_filename in varcahr2,dictionary\_location in varchar2,options in number);  
其中dictionary\_filename指定字段文件名，dictionary\_location指定文件所在位置，options指定字典要写入位置(store\_in\_flat\_file:文本文件,store\_in\_redo\_log2:重新日志)  
  
7）、dbms\_logmnr\_d.set\_tablespace  
作用：用于改变logminer表所在的表空间  
语法：dbms\_logmnr\_d.set\_tablespace(new\_tablespace in default varchar2,dict\_tablespace in default varchar2,spill\_tablespace in default varchar2);  
其中new\_tablespace指定logminer表所在的表空间，dict\_tablespace指定字典表所在表空间，spill\_tablespace指定溢出表所在表空间。  
  
3、综合例子  
首先建表temp，然后执行dml操作和日志切换操作，生产归档日志。  
sqlplus /nolog  
connect system/manager@test  
create table temp(cola number,colb varchar2(10));  
alter system swith logfile;  
insert into temp values(9,'A');  
update temp set cola=10;  
commit;  
alter system switch logfile;  
delete from temp;  
alter system switch logfile;  
  
1）、建立字典文件  
说明：字典文件用于存放表及对象ID号之间的对应关系。从9i开始，字典信息既可被摘取到字段文件中，也可被摘取到重做日志中。摘取字典信息到字典文件方法如下：  
（1）、设置字典文件所在目录  
alter system set utl\_file\_dir="g:\\test"  
scope=spfile;  
（2）、重启Oracle Server  
sqlplus /nolog  
conn sys/test@test as sysdba  
shutdown immediate  
startup  
（3）、摘取字典信息  
begin  
dbms\_logmnr\_d.build(dictionary\_filename=>'dict.ora',dictionary\_location=>'g:\\test\\logminer');  
end;  
  
2）、建立日志分析列表  
（1）、停止Oracle Server并装载数据库  
sqlplus /nolog  
conn sys/test@test as sysdba  
shutdown immediate  
startup mount  
（2）、建立日志分析列表  
begin  
dbms\_logmnr.add\_logfile(options=>dbms\_logmnr.new,logfilename=>'g:\\test\\arc1\\test1.arc');  
end;  
（3）、增加其他日志文件(可选)  
begin  
dbms\_logmnr.add\_logfile(option=>dbms\_logmnr.addfile,logfilename=>'g:\\test\\arc1\\test12.arc');  
end;  
  
3）、启动LogMiner分析  
begin  
dbms\_logmnr.start\_logmnr(dictfilename=>'g:\\test\\logminer\\dict.ora',  
starttime=>to\_date('2004-04-03:10:10:00','YYYY-MM-DD:HH24:MI:SS'),  
endtime=>to\_date('2004-04-03:15:30:00','YYYY-MM-DD:HH24:MI:SS'));  
end;  
  
4）、查看日志分析结果  
说明：日志分析结果只能在当前会话查看。  
（1）、显示DML分析结果  
select operation,sql\_redo,sql\_undo from v$logmnr\_contents where seg\_name='TEMP';  
（2）、显示DDL分析结果  
select to\_cahr(timestamp,'yyyy-mm-dd hh23:mi:ss') time,sql\_redo from v$logmnr\_contents where sql\_redo like '%create%' or sql\_redo like '%create%';  
（3）、显示在用字典文件  
select db\_name,filename from v$logmnr\_dictionary;  
  
5）、结束LogMiner  
execute dbms\_logmnr.end\_logmnr;  
  
  
十三、dbms\_flashback  
1、概述  
作用:用于激活或禁止会话的flashback特征,为了使得普通用户可以使用该包,必须要将执行该包的权限授予这些用户，grant execute on dbms\_flashback to scott;  
  
2、包的组成  
1）、enable\_at\_time  
作用：用于以时间方式激活会话的flashback  
语法：dbms\_flashback.enable\_at\_time(query\_time in timestamp);  
其中query\_time指定flashback对应的时间点；  
  
2）、enable\_at\_system\_change\_number  
作用：用于以系统改变号（scn）方式激活会话的flashback  
语法：dbms\_flashback.enable\_at\_system\_change\_number(query\_scn in number);  
其中query\_scn指定flashback对应的scn值。  
  
3）、get\_system\_change\_number  
作用：用于取得系统的当前scn值  
语法：dbms\_flashback.get\_system\_change\_number return number;  
  
4）、disable  
作用：用于禁止会话的flashback模式  
语法：dbms\_flashback.disable;  
  
3、综合例子  
1）、取得SCOTT雇员工资及系统SCN值  
select sal from emp where ename='scott';--3600  
seelct dbms\_flashback.get\_system\_change\_number from dual;--717402  
  
2）、更新scott工资，并休眠5分钟  
update emp set sal=3000 where ename='scott';  
commit;  
exec dbms\_lock.sleep(300);  
  
3）、使用dbms\_flashback取得特定scn时间点对应的数据  
exec dbms\_flashback.enable\_at\_system\_change\_number(717402);  
select sal from emp where ename='scott';--3600  
exec dbms\_flashback.disable;  
select sal from emp where ename='scott';--3000  
  
  
十四、dbms\_obfuscation\_toolkit  
1、概述  
作用:用于加密和解密应用数据,另外还可以生成密码检验和.通过加密输入数据,可以防止黑客或其他用户窃取私有数据;而通过结合使用加密和密码检验和,可以防止黑客破坏初加密的数据.  
当使用该包加密数据时,要求被加密数据的长度必须为8字节的整数倍.当使用DES算法加密数据时,密钥长度不能低于8字节;当使用DES3算法加密数据时,密钥长度不能低于16字节.  
  
2、包的组成  
1）、DESencrypt  
作用：用于使用DES算法对输入数据进行加密，并生成加密格式的数据。密钥长度不能低于8字节且输入数据必须是8字节的整数倍。  
语法1：dbms\_obfuscation\_toolkit.DESencrypt(input raw,key raw,encrypted\_date out raw);  
语法2：dbms\_obfuscation\_toolkit.DESencrypt(ipput\_string varchar2,key\_string varchar2,encrypted\_string out varchar2);  
其中input\\input\_string指定输入的二进制数据或输入字符串，key\\key\_string指定加密密钥，encrypted\_date\\encrypted\_string指定存放加密结果的字符串。  
  
2）、DESdecrypt  
作用：用于对使用DES算法所生成的加密数据进行解密。当对数据进行  
解密时，解密密钥必须要与加密密钥完全一致。  
语法1：dbms\_obfuscation\_toolkit.desdecrypt(input raw,key raw,decrypted\_data out raw);  
语法2：dbms\_obfuscation\_toolkit.desdecrypt(input\_string varchar2,key\_string varchar2,decrypted\_string out varchar2);  
其中decrypted\_data\\decrypted\_string指定存放解密结果的字符串。  
  
3）、DES3encrypt  
作用：用于使用DES3算法对输入数据进行加密，并生成加密格式的数据。密钥不能少于16个字符并且数据数据必须是8字节的整数倍。  
语法1：dbms\_obfuscation\_toolkit.DES3encrypt(input raw,key raw,encrypted\_data out raw);  
语法2：dbms\_obfuscation\_toolkit.DES3encrypt(input\_string varchar2,key\_string varchar2,encrypted\_string out varchar2);  
  
4）、DES3decrypt  
作用：用于对使用DES3算法所生成的加密数据进行解密。解密密钥必须要与加密密钥完全一致。  
语法1：dbms\_obfuscation\_toolkit.DES3decrypt(input raw,key raw,decrypted\_data out raw);  
语法2：dbms\_obfuscation\_toolkit.DES3decrypt(input\_string varchar2,key\_string varchar2,encrypted\_string out varchar2);  
  
5）、md5  
作用：用于使用md5算法生成密码校验码。可以防止其他用户破坏被传输的加密数据。  
语法1：dbms\_obfuscation\_toolkit.md5(input raw,checksum out raw);  
语法2：dbms\_obfuscation\_toolkit.md5(input\_string varchar2,checksum\_string out varchar2);  
其中checksum\_string(checksum)指定存放密码校验码的字符串。  
  
3、综合例子  
1）、简单例子  
declare   
encrypted\_string varchar2(100);  
decrypted\_string varchar2(100);  
str1 varchar2(8):='中国你好';  
key varchar2(16):='ABCDFDSDSASS$1234';  
str2 varchar2(100);  
str3 varchar2(100);  
begin  
\--DESencrypt  
dbms\_obfuscation\_toolkit.DESencrypt(input\_string=>'SCOTTsco',key\_string=>'abcd1234',encrypted\_string=>encrypted\_string);  
dbms\_output.put\_line(encrypted\_string);  
\--DESdecrypt  
dbms\_obfuscation\_toolkit.DESdecrypt(input\_string=>encrypted\_string,key\_string=>'abcd1234',decrypted\_string=>decrypted\_string);  
dbms\_output.put\_line(decrypted\_string);  
\--DES3encrypt  
dbms\_obfuscation\_toolkit.DES3encrypt(input\_string=>str1,  
key\_string=>key,encrypted\_string=>str2);  
dbms\_output.put\_line(str2);  
\--DES3decrypt  
dbms\_obfuscation\_toolkit.DES3encrypt(input\_string=>str2,  
key\_string=>key,decrypted\_string=>str3);  
dbms\_output.put\_line(str3);  
\--md5  
dbms\_obfuscation\_toolkit.md5(input\_string=>str1,checksum\_string=>str2);  
dbms\_output.put\_line(str2);  
end;  
  
2）、例子2  
说明：为了防止黑客窃取数据，应该对数据进行加密，为防止黑客窜改数据，应该使用密码校验来确保数据的正确性。  
下面使用管道发送加密信息并确保消息正确性  
（1）、建立过程send\_message  
说明：用于生产消息的密码校验码、加密信息，并分别发到不同的管道。  
create or replace procedure send\_message(message varchar2) is  
flag int;  
checksum varchar2(100);  
key varchar2(100);  
encry\_str varchar2(100);  
begin  
dbms\_obfuscation\_toolkit.md5(input\_string=>message,checksum\_string=>checksum);--用MD5为消息生产密码校验码  
flag:=dbmspipe.create\_pipe('checksum');--建立checksum管道  
if flag=0 then  
dbms\_pipe.pack\_message(checksum);  
flag:=dbms\_pipe.send\_message('checksum');--发送校验码  
end if;  
dbms\_obfuscation\_toolkit.desencrypt(input\_string=>message,key\_string=>key,encrypted\_string=>encry\_str);--加密要发送的消息。  
flag:=dbms\_pipe.create\_pipe('encrypt');--建立encrypt管道  
if flag=0 then  
dbms\_pipe.pack\_message(encrypt);  
flag:=dbms\_pipe.send\_message('encrypt');--发送信息  
end if;  
end;  
（2）、建立过程receive\_message  
说明：用于接收校验码和消息  
create or replace procedure receive\_message is  
flag int;  
source\_checksum varchar2(100);  
dest\_checksum varchar2(100);  
key varchar2(100):='123456778SAD';  
encry\_str varchar2(100);  
decry\_str varchar2(100);  
begin  
flag:=dbms\_pipe.receive\_message('encrypt');--接收加密消息  
if flag=0 then  
dbms\_pipe.unpack\_message(encry\_str);  
flag:=dbms\_pipe.remove\_pipe('encrypt');  
end if;  
flag:=dbms\_pipe.receive\_message('checksum');--接收加密消息  
if flag=0 then  
dbms\_pipe.unpack\_message(source\_checksum);  
flag:=dbms\_pipe.remove\_pipe('checksum');  
end if;  
dbms\_obfuscation\_toolkit.desdecrypt(input\_string=>encry\_str,  
key\_string=>key,decrypted\_string=>decry\_str);--使用密钥解密信息  
dbms\_obfuscation\_toolkit.md5(input\_string=>decry\_str,checksum\_string=>dest\_checksum);--生产密码校验码  
if trim(source\_checksum)=trim(dest\_checksum) then  
dbms\_output.put\_line(decry\_str);  
else  
dbms\_output.put\_line('消息被窜改');  
end if;  
end;  
（3）、使用  
会话1：exec send\_message('中国你好');  
会话2：exec scott.receive\_message;  
  
  
十五、dbms\_space  
1、概述  
作用:用于分析段增长和空间的需求。  
  
2、包的组成  
1）、unused\_space  
作用：用于返回对象(表、索引、簇)的未用空间  
语法：dbms\_space.unused\_space(segment\_owner in varchar2,  
segment\_name in varchar2,segment\_type in varchar2,  
total\_blocks out number,total\_bytes out number,  
unused\_blocks out number,unused\_bytes out number,  
last\_used\_extent\_file\_id out number,  
last\_used\_extent\_block\_id out number,  
last\_used\_block out number,  
partition\_name in varchar2 default null);  
其中segment\_owner指定段所有者，segment\_name指定段名，segment\_type指定段类型，  
tatal\_blocks返回段的总计块个数，total\_bytes返回段的总计字节数，  
unused\_blocks返回段的末用块个数，unused\_byte返回段未用字节，  
last\_used\_extent\_file\_id返回包含数据的最后一个区所在文件的编号，  
last\_used\_extent\_block\_id返回包含数据的最有一个区的块编号，  
last\_used\_block返回包含数据的最有一个区的最后一个块，  
partition\_name指定要分析的段分区号。  
  
2）、free\_blocks  
作用：用于返回对象（表、索引、簇）的空闲块信息。  
语法：dbms\_space.free\_blocks(segment\_owner in varchar2,segment\_name in varchar2,segment\_type in varchar2,freelist\_group\_id in number,  
free\_blks out number,scan\_limit in number default null,partition\_name in varchar2 default null);  
其中freelist\_group\_id指定返回空闲列表组所对应的空闲列表个数，scan\_limit指定要读取的空闲列表块的最大个数。  
  
3）、space\_usage  
作用：显示段HWM(High Water Mark)以下数据块的空闲使用情况，并且该过程只适用于自动段空间管理的表空间。  
语法：dbms\_space.space\_usage(segment\_owner in varchar2,segment\_name in varchar2,segment\_type in varchar2,unformatted\_blocks out number,unformatted\_bytes out number,  
fs1\_blocks out number,fs1\_bytes out number,  
fs2\_blocks out number,fs2\_bytes out number,  
fs3\_blocks out number,fs3\_bytes out number,  
fs4\_blocks out number,fs4\_bytes out number,  
full\_blocks out number,full\_bytes out number,  
partition\_name in varchr2 default null);  
其中unformatted\_blocks用于返回未格式化块的个数，unformatted\_bytes用于返回未格式化的字节数，  
fs1\_blocks用于返回空闲空间在0~25%之间的块个数，fs1\_bytes返回空闲空间在0~25%之间的字节数，2范围为25%~50%之间，3范围为50%~75%之间，4范围为75%~100%之间。  
full\_blocks返回段的总计块个数，full\_bytes返回段的总计字节数。  
  
3、综合例子  
1）、简单例子  
例子：  
declare  
total\_blocks number;  
total\_bytes number;  
unused\_blocks number;  
unused\_bytes number;  
last\_used\_extent\_file\_id number;  
last\_used\_extent\_block\_id number;  
last\_used\_block number;  
free\_blocks number;  
variable unf number,  
variable unfb number,  
variable fs1 number,  
variable fs1b number,  
variable fs2 number,  
variable fs2b number,  
variable fs3 number,  
variable fs3b number,  
variable fs4 number,  
variable fs4b number,  
variable full number,  
variable fullb number,  
begin  
\--unused\_space  
dbms\_space.unused\_space('SYSTEM','T1','TABLE',total\_blocks,total\_bytes,unused\_blocks,unused\_bytes,last\_used\_extent\_file\_id,last\_used\_extent\_block\_id,last\_used\_block);  
dnms\_output.put\_line(to\_char('HWM='||total\_blocks-unused\_blocks-1));  
\--free\_blocks  
dbms\_space.free\_blocks('SYSTEM','T1','TABLE',0,free\_blocks);  
dbms\_output.put\_line('组0的空闲列表个数'||free\_blocks);  
\--space\_usage  
dbms\_space.space\_usage('U1','T','TABLE',:nuf,:unfb,:fs1,:fs1b,:fs2,:fs2b,:fs3,:fs3b,:fs4,:fs4b,:fnull,:fnullb);  
end;  
  
  
十六、dbms\_space\_admin  
1、概述  
作用:提供了局部管理表空间的功能  
  
2、包的组成  
1）、segment\_verify  
作用：用于检查段的区映像是否与位图一致  
语法：dbms\_space\_admin.segment\_verify(tablespace\_name in varchar2,header\_relative\_file in positive,heaeder\_block in positive,verify\_option in positive default segment\_verify\_extents);  
其中tablespace\_name指定段所在的表空间，header\_relative\_file指定段头所在的相对文件好，heaeder\_block指定段头所在的块号，verify\_option指定检查方式。  
例子：exec dbms\_space\_admin.segment\_verify('',9,68);  
  
2）、segment\_corrupt  
作用：用于将段标记为损坏或有效。  
语法：dbms\_space\_admin.segment\_corrupt(tablespace\_name in varchar2,header\_relative\_file in positive,header\_block in positive,corrupt\_option in positive default segment\_mark\_corrupt);  
其中corrupt\_option指定损坏(segment\_mark\_corrupt)或有效(segment\_mark\_valid)选项。  
例子：dbms\_space\_admin.segment\_corrupt('USER3',9,68);  
  
3）、segment\_drop\_corrupt  
作用：用于删除被标记为损坏的段。  
语法：dbms\_space\_admin.segment\_drop\_corrupt(tablespace\_name in varchar2,header\_relative\_file in positive,header\_block in positive);  
例子：exec dbms\_space\_admin.segment\_drop\_corrupt('USER3',9,68);  
  
4）、segment\_dump  
作用：用于转储特定段的头块和区映像块  
语法：dbms\_space\_admin.segment\_dump(tablespace\_name in varchar2,header\_relative\_file in positive,header\_block in positive,dump\_option in positive default defult segment\_dump\_extent\_map);  
其中dump\_option指定转储特项。  
  
5）、tablespace\_verify  
作用：检查表空间所有段的位图和区映像。  
语法：dbms\_space\_admin.tablespace\_verify(tablespace\_name in varchar2,verify\_option in positive default tablespace\_verify\_bitmap);  
例子：exec dbms\_space\_admin.tablespace\_verify('USERS3');  
  
6）、tablespace\_fix\_bitmaps  
作用：用于将特定范围的空间标记为空闲或已用。  
语法：dbms\_space\_admin.tablespace\_fix\_bitmaps(tablespace\_name in varchar2,dbarange\_relative\_file in positive,dbarange\_begin\_block in positive,  
dbarange\_end\_block in positive,fix\_option in positive);  
其中dbarange\_relative\_file指定DBA范围内的相对文件号，dbarange\_begin\_block指定数据文件区的起始块编号，dbarange\_end\_block指定数据文件区的结束块编号，  
fix\_option指定选项(TABLESPACE\_EXTENT\_MAKE\_FREE或TABLESPACE\_EXTENT\_MAKE\_USED)  
例子：exec dbms\_space\_admin.tablespace\_fix\_bitmaps('USES',4,33,83,7);  
  
7）、tablespace\_rebuild\_bitmaps  
作用：用于重新建立合适的位图。如果没有指定位图块，则将重建特定表空间的所有位图块。  
语法：dbms\_space\_admin.tablespace\_rebuild\_bitmaps(tablespace\_name in varchar2,bitmap\_relative\_file in positive default null,bitmap\_block in positive default null);  
其中bitmap\_relative\_file指定位图块的相对文件号，bitmap\_block指定位图块的块号。  
例子：exec dbms\_space\_admin.tablespace\_rebuild\_bitmaps('users3');  
  
8）、tablespace\_rebuild\_quotas  
作用：用于重建表空间配额。  
语法：dbms\_space\_admin.tablespace\_rebuild\_quotas(tablespace\_name in varchar2);  
例子：exec dbms\_space\_admin.tablespace\_rebuild\_quotas('USERS3');  
  
9）、tablesapce\_migrate\_from\_local  
作用：用于将局部管理表空间转变为字典管理表空间。  
语法：dbms\_space\_admin.tablesapce\_migrate\_from\_local(tablespace\_name in varchar2);  
例子：exec dbms\_space\_admin.tablesapce\_migrate\_from\_local('USERS1');  
  
10）、tablespace\_miorate\_to\_local  
作用：用于将字典管理表空间转变为局部管理表空间  
语法：dbms\_space\_admin.tablespace\_miorate\_to\_local(tablespace\_name in varchar2);  
其中tablespace\_name指定字典管理表空间名。  
例子：dbms\_space\_admin.tablespace\_miorate\_to\_local('USERS1');  
  
11）、tablespace\_reloacte\_bitmaps  
作用：用于移动位图到指定位置。  
语法：dbms\_space\_admin.tablespace\_reloacte\_bitmaps(tablespace\_name in varchar2,relative\_fno in binary\_integer,block\_number in binary\_integer);  
其中relative\_fno指定相对文件号，block\_number指定数据块编号。  
例子：exec dbms\_space\_admin.tablespace\_reloacte\_bitmaps('USERS3',9,8);  
  
12）、tablespace\_fix\_segment\_states  
作用：用于修正表空间的段状态。当升级表空间时，如果出现例程终止，那么为了重新升级表空间，必须要修正该表空间中相应段的状态。  
语法：dbms\_space\_admin.tablespace\_fix\_segment\_states(tablespace\_name);  
例子：exec dbms\_space\_admin.tablespace\_fix\_segment\_states('USERS3');  
  
  
十七、dbms\_tts  
1、概述  
作用:用于检查表空间集合是否是自包含的,并在执行了检查之后,将违反自包含规则的信息写入到临时表TRANSPORT\_SET\_VIOLATIONS中.  
  
2、包的组成  
1）、transport\_set\_check  
作用：用于检查表空间集合是否是自包含的。  
语法：dbms\_tts.transport\_set\_check(ts\_list in varchar2,incl\_constraints in boolean default,full\_closure in boolean default false);  
其中ts\_list指定表空间列表，如果要指定多个表空间则使用逗号隔开，incl\_constraints指定是否要检查完整性约束，full\_closure指定是否要进行完全或部分相关性检查。  
例子：  
exec sys.dbms\_tts.transport\_set\_check('users1,users2');  
select \* from sys.transport\_set\_violations;  
在查询临时表transport\_set\_violations时，如果返回信息，则会显示违反自包含表空间规则的原因，如果没有返回则表示表空间时自包含的。  
  
2）、downgrade  
作用：用于降级搬移表空间的相关数据  
语法：dbms\_tts.downgrade;  
  
  
十八、dbms\_repair  
1、概述  
作用:用于检测,修复在表和索引上的损坏数据块.  
  
2、包的组成  
1）、admin\_tables  
语法：dbms\_repair.admin\_tables(table\_name in varchar2,table\_type in binary\_integer,action in binary\_integer,tablespace in varchar2 default null);  
其中table\_name用于指定要处理的表名，必须要指定前缀orphan或repair,table\_type指定表类型(orphan或repair\_table)，  
action指定要执行的管理操作(建立表create\_action，删除所有行purge\_action，删除表drop\_action)，tablespace用于指定表所在的表空间。  
例子：  
exec dbms\_repair.admin\_tables('repair\_table',dbms\_repair.repair\_table,dbms\_repair.create\_action,'system');  
exec dbms\_repair.admin\_tables('orphan\_table',dbms\_repair.orphan\_table,dbms\_repair.create\_action,'system');  
上面例子执行第一条语句后，会建立修复表repair\_table，并且该修复表用于存放损坏数据块的信息；执行第二条语句后，会建立孤表orphan\_table，该表用于存放指向损坏数据块的索引入口信息。  
  
2）、check\_object  
作用：用于检查特定对象，并将损坏信息填写到修复表中。  
语法：dbms\_repair.check\_object(schema\_name in varchar2,object\_name in varchar2,partition\_name in varchar2 default null,object\_type in binary\_integer default tables\_object,  
repair\_table\_name in varchar2 default 'repair\_table',flags in binary\_integer default null,relative\_fno in binary\_integer default null,  
block\_start in binary\_integer default null,block\_end in binary\_integer default null,corrupt\_count out binary\_integer);  
其中schema\_name指定要检查对象的方案名，object\_name指定要检查的对象名，partition\_name用于指定要检查的分区名，object\_type指定要检查对象的类型(table\_object或index\_object)，  
repair\_table\_name指定要被填写的修复表，flags为将来使用而保留，relative\_fno指定相对文件号，block\_start指定要检查的起始块号，block\_end指定要检查的结束块号，  
corrupt\_count用于返回损坏的块个数。  
例子：  
var corr\_count number;  
exec dbms\_repair.check\_object('scott','emp',corrupt\_count=>:corr\_count);  
print corr\_count  
  
3）、dump\_orphan\_keys  
作用：用于报告指向损坏数据块行的索引入口，并且会将相应索引入口的信息插入到孤表中。  
语法：dbms\_repair.dump\_orphan\_keys(schema\_name in varchar2,object\_name in varchar2,  
partition\_name in varchar2 default null,object\_type in binary\_integer default index\_object,  
repair\_table\_name in varchar2 default 'repair\_table',orphan\_table\_name in varchar2 default 'orphan\_keys\_table',  
flags in binary\_integer default null,key\_count out binary\_integer);  
其中object\_type指定对象类型(index\_object),repair\_table\_name指定修复表名，orphan\_table\_name指定孤表名，key\_count用于返回索引入口个数。  
例子：  
var key\_count number  
exec dbms\_repair.dump\_orphan\_keys('scott','pk\_emp',orphan\_table\_name=>'orphan\_table',key\_count=>:key\_count);  
print key\_count  
  
4）、fix\_corrupt\_blocks  
作用：修复被损坏的数据块，这些被损坏的数据块是在执行了check\_object之后生成的。  
语法：dbms\_repair.fix\_corrupt\_blocks(schema\_name in varchar2,object\_name in varchar2,partition\_name in varchar2 default null,  
object\_type in binary\_integer default table\_object,repair\_table\_name in varchr2 default 'repair\_table',  
flags in binary\_integer default null,fix\_count out binary\_integer);  
其中object\_type指定对象类型(table\_object)，fix\_count返回修复的数据块个数。  
例子：  
var fix\_count number  
exec dbms\_repair.fix\_corrup\_block('scott','emp',fix\_count=>:fix\_count);  
print fix\_count  
  
5）、rebuild\_freelists  
作用：用于重建指定对象的空闲列表  
语法：dbms\_repair.rebuild\_freelists(schema\_name in varchar2,object\_name in varchar2,partition\_name in varchar2 default null,  
object\_type in binary\_integer default table\_object);  
其中object\_type指定对象类型(table\_object)。  
例子：exec dbms\_repair.rebuild\_freelists('scott','emp');  
  
6）、skip\_corrupt\_blocks  
作用：用于指定在扫描对象(表或索引)时跳过损坏块。  
语法：dbms\_repair.skip\_corrupt\_blocks(schema\_name in varchar2,object\_name in varchar2,object\_type in binary\_integer default table\_object,  
flag in binary\_integer default skip\_flag);  
其中object\_type指定对象类型(table\_object)，flags指定是否要跳过损坏块（skip\_flao跳过，no\_skip\_flag不跳过）  
例子：exec dbms\_repair.skip\_corrupt\_blocks('scott','emp'）;  
  
7）、segment\_fix\_status  
作用：用于修复位图入口的损坏  
语法：dbms\_repair.segment\_fix\_status(segment\_owner in varchar2,segment\_name in varchar2,segment\_type in binary\_integer default table\_object,  
file\_number in binary\_integer default null,block\_number in binary\_integer default null,  
status\_value in binary\_integer default null,partition\_name in varchar2 default null);  
其中segment\_owner指定段所有者，segment\_name指定段名，segment\_type指定段类型，file\_number指定数据块所在的相对文件号，  
block\_number指定数据块号，status\_value指定块状态值（1：全块，2：0~25%，3：25%~50%，4：50%~75%，5：75%~100%）  
partition\_name指定分区名。  
例子：exe dbms\_repair.segment\_fix\_status('sys','mytab');  
  
  
十九、dbms\_resource\_manager  
1、概述  
作用:用于维护资源计划,资源使用组和资源计划指令;包dbms\_resource\_manager\_privs用于维护与资源管理相关的权限.  
  
2、包的组成  
1）、dbms\_resource\_manager.create\_plan  
作用：建立资源计划  
语法：dbms\_resource\_manager.create\_plan(plan in varchar2,comment in varchar2,cpu\_mth in varchar2 default 'EMPHASIS',  
active\_sess\_pool\_mth in varchar2 default 'ACTIVE\_SESS\_POOL\_ABSOLUTE',  
parallele\_degree\_limit\_mth in varchar2 default 'PARALLEL\_DEGREE\_LIMIT\_ABSOLUTE',  
queueing\_mth in varchar2 default 'FIFO\_TIMEOUT');  
其中plan指定资源计划名，comment指定用户注释信息，cpu\_mth指定cpu资源的分配方法，active\_sess\_pool\_mth指定最大活动会话的分配方法，  
parallele\_degree\_limit\_mth指定并行度的分配方法，queueing\_mth指定活动会话池的队列策略类型。  
  
2）、dbms\_resource\_manager.create\_simple\_plan  
作用：用于建立简单资源计划，该资源计划最多包含8个资源使用组。  
语法：dbms\_resource\_manager.create\_simple\_plan(simple\_plan in varchar2 default,  
consumer\_group1 in varchar2 default,group1\_cpu in number default,  
consumer\_group2 in varchar2 default,group2\_cpu in number default,  
consumer\_group3 in varchar2 default,group3\_cpu in number default,  
consumer\_group4 in varchar2 default,group4\_cpu in number default,  
consumer\_group5 in varchar2 default,group5\_cpu in number default,  
consumer\_group6 in varchar2 default,group6\_cpu in number default,  
consumer\_group7 in varchar2 default,group7\_cpu in number default,  
consumer\_group8 in varchar2 default,group8\_cpu in number default);  
  
3）、dbms\_resource\_manager.update\_plan  
作用：用于更新资源计划的定义  
语法：dbms\_resource\_manager.update\_plan(plan in varchar2,  
new\_comment in varchar2 default null,new\_cpu\_mth in varchar2 default null,  
new\_active\_sess\_pool\_mth in varchar2 default null,new\_parallel\_degree\_limit\_mth in varchar2 default null,  
new\_queueing\_mth in varchar2 default null);  
其中new\_comment指定用户的新的注释信息，new\_cpu\_mth指定cpu资源的新的分配方法，  
new\_active\_sess\_pool\_mth指定最大活动会话的新的分配方法，  
new\_parallel\_degree\_limit\_mth指定并行度的新的分配方法，  
new\_queueing\_mth指定活动会话池的新的队列策略类型。  
  
4）、dbms\_resource\_manager.delete\_plan  
作用：用于删除资源计划  
语法：dbms\_resource\_manager.delete\_plan(plan in varchar2);  
  
5）、dbms\_resource\_manager.delete\_plan\_cascade  
作用：用于删除资源计划及其所有后代（资源计划指令、子计划和资源使用组）  
语法：dbms\_resource\_manager.delete\_plan\_cascade(plan in varchar2);  
  
6）、dbms\_resource\_manager.greate\_consumer\_group  
作用：用于建立资源使用组  
语法：dbms\_resource\_manager.greate\_consumer\_group(consumer\_group in varchar2,  
comment in varchar2,cpu\_mth in varchar2 default 'ROUND-ROBIN');  
其中consumer\_group指定资源使用组名。  
  
7）、dbms\_resource\_manager.update\_consumer\_group  
作用：用于更新资源使用组信息  
语法：dbms\_resource\_manager.update\_consumer\_group(  
consumer\_group in varcahr2,new\_comment in varchar2,new\_cpu\_mth in varchar2 default null);  
  
8）、dbms\_resource\_manager.delete\_consumer\_group  
作用：删除资源使用组  
语法：dbms\_resource\_manager.delete\_consumer\_group(consumer\_group in varchar2);  
  
9）、dbms\_resource\_manager.create\_plan\_directive  
作用：建立资源计划指令  
语法：dbms\_resource\_manager.create\_plan\_directive(  
plan in varchar2,group\_or\_subplan in varchar2,  
comment in varchar2,cpu\_p1 in number default null,  
cpu\_p2 in number default null,cpu\_p3 in number default null,  
cpu\_p4 in number default null,cpu\_p5 in number default null,  
cpu\_p6 in number default null,cpu\_p7 in number default null,  
cpu\_p8 in number default null,  
active\_sess\_pool\_p1 in number default unlimited,  
queueing\_p1 in number default unlimited,  
switch\_group in varchar2 default null,  
switch\_time in number default unlimited,  
switch\_estimate in boolean default false,  
max\_est\_exec\_time in number default nulimited,  
undo\_pool in number default unlimited,  
paralle1\_degree\_limit\_p1 in number default unlimited);  
其中group\_or\_subplan指定资源使用组或者子计划的名称，cpu\_p1指定CPU资源分配方法的第一个参数(p2是第二个参数。。。)，  
active\_sess\_pool\_p1指定最大活动会话分配方法的第一个参数，queueing\_p1指定队列超时时间，  
switch\_group指定到达切换时间时要切换到的资源使用组，switch\_time指定切换时间，  
switch\_estimate默认false，设置为true时表示通知Oracle使用执行时间估计自动切换资源使用组。  
undo\_pool指定资源使用组的undo池尺寸，paralle1\_degree\_limit\_p1指定并行度分配方法的第一个参数。  
  
10）、dbms\_resource\_manager.update\_plan\_directive  
作用：用于更新资源计划指令  
语法：dbms\_resource\_manager.update\_plan\_directive(  
plan in varchar2,group\_or\_subplan in varchar2,  
new\_comment in varchar2,cpu\_p1 in number default null,  
new\_cpu\_p2 in number default null,new\_cpu\_p3 in number default null,  
new\_cpu\_p4 in number default null,new\_cpu\_p5 in number default null,  
new\_cpu\_p6 in number default null,new\_cpu\_p7 in number default null,  
new\_cpu\_p8 in number default null,  
new\_active\_sess\_pool\_p1 in number default unlimited,  
new\_queueing\_p1 in number default unlimited,  
new\_switch\_group in varchar2 default null,  
new\_switch\_time in number default unlimited,  
new\_switch\_estimate in boolean default false,  
new\_max\_est\_exec\_time in number default nulimited,  
new\_undo\_pool in number default unlimited,  
new\_paralle1\_degree\_limit\_p1 in number default unlimited);  
其中new\_cpu\_p1指定CPU资源分配方法的第一个参数(p2是第二个参数。。。)，  
new\_active\_sess\_pool\_p1指定最大活动会话分配方法的第一个参数，new\_queueing\_p1指定队列超时时间，  
new\_switch\_group指定到达切换时间时要切换到的资源使用组，new\_switch\_time指定切换时间，  
new\_switch\_estimate默认false，设置为true时表示通知Oracle使用执行时间估计自动切换资源使用组。  
new\_undo\_pool指定资源使用组的undo池尺寸，new\_paralle1\_degree\_limit\_p1指定并行度分配方法的第一个参数。  
  
11）、dbms\_resource\_manager.delete\_plan\_directive  
作用：用于删除资源计划指令  
语法：dbms\_resource\_manager.delete\_plan\_directive(plan in varchr2,group\_or\_subplan in varchar2);  
  
12）、dbms\_resource\_manager.create\_pending\_area  
作用：用于建立pending内存区，并且该内存区将用于改变资源管理对象  
语法：dbms\_resource\_manager.create\_pending\_area；  
  
13）、dbms\_resource\_manager.validate\_pending\_area  
作用：用于校验资源管理器的改变  
语法：dbms\_resource\_manager.validate\_pending\_area;  
  
14）、dbms\_resource\_manager.clear\_pending\_area  
作用：用于清除资源管理器的改变  
语法：dbms\_resource\_manager.clear\_pending\_area;  
  
15）、dbms\_resource\_manager.submit\_pending\_area  
作用：用于提交资源管理器的改变  
语法：dbms\_resource\_manager.submit\_pending\_area;  
  
16）、dbms\_resource\_manager..set\_initial\_consumer\_group  
作用：用于指定用户的初始资源使用组  
语法：dbms\_resource\_manager.set\_initial\_consumer\_group(user in varchar2,consumer\_group in varchar2);  
其中user指定用户名，consumer\_group指定用户的初始资源使用组名。  
  
17）、dbms\_resource\_manager.switch\_consumer\_group\_for\_sess  
作用：dbms\_resource\_manager.switch\_consumer\_group\_for\_sess(session\_id in number,session\_serial in number,consumer\_group in varchr2);  
其中session\_id指定会话ID号，session\_serial指定会话序列号。  
  
18）、dbms\_resource\_manager.switch\_consumer\_group\_for\_user  
作用：用于改变特定用户所有会话的资源使用组  
语法：dbms\_resource\_manager.switch\_consumer\_group\_for\_user(user in varchar2,consumer\_group in varchar2);  
  
19）、dbms\_resource\_manager\_privs.grant\_system\_privilege  
作用：用于将资源管理权限授予用户或角色  
语法：dbms\_resource\_manager\_privs.grant\_system\_privilege(grantee\_name in varchar2,  
privilege\_name in varchar2 default 'administer\_resource\_manager',admin\_option in boolean);  
其中grantee\_name指定被授权的用户或角色，privilege\_name指定要授予的资源管理权限，  
admin\_option指定是否可以转授资源管理权限(true为能，false为不能)  
例子：exec dbms\_resource\_manager\_prive.grant\_system\_privilege('scott','administer\_resource\_manager',true)  
  
20）、dbms\_resource\_manager\_privs.revoke\_system\_privilege  
作用：用于回收资源管理权限  
语法：dbms\_resource\_manager\_privs。revoke\_system\_privilege(revokee\_name in varchar2,privilege\_name in varchar2 default 'administer\_resource\_manager');  
其中revoke\_name指定被收回权限的用户或角色，privilege\_name指定要收回的资源管理权限。  
例子：exec dbms\_resource\_manager\_privs.revoke\_system\_privilege('scott','administer\_resource\_maneger');  
  
21）、dbms\_resource\_manager\_privs.grant\_switch\_consumer\_group  
作用：用于将用户或角色分配给特定的资源使用组  
语法：dbms\_resource\_manager\_privs.grant\_switch\_consumer\_group(grantee\_name in varchar2,consumer\_group in varchar2,grant\_option in boolean);  
其中grant\_option指定资源使用组转授选项  
例子：exec dbms\_resource\_manager\_privs.grant\_switch\_consumer\_group('scott','sys\_group',true)  
  
22）、dbms\_resource\_manager\_privs.revoke\_switch\_consumer\_group  
作用：用于收回分配给用户或角色的资源使用组  
语法：dbms\_resource\_manager\_privs.revoke\_switch\_consumer\_group(revokee in varchar2,consumer\_group in varchar2);  
例子：dbms\_resource\_manager\_privs.revoke\_switch\_consumer\_group('scott','sys\_group')；  
  
3、综合实例  
1）、为用户授权资源管理权限  
说明：默认情况下只有特权用户sys，dba用户system可以进行资源管理，其余用户要使用要为其授权，下面授权给scott。  
conn system/menager@test  
exec dbms\_resource\_manager\_prive.grant\_system\_privilege('scott','administer\_resource\_manager',true)  
2）、建立各种资源对象  
说明：建立资源对象前，必须分配pending内存区，建立资源对象后，必须检查并提交pending内存区。  
（1）、建立pending内存区  
conn scott/tiger@test  
exec dbms\_resource\_manager.create\_pending\_area  
（2）、建立资源使用组  
exec dbms\_resource\_manager.create\_consumer\_group('oltp','联机事务处理组');  
exec dbms\_resource\_manager.create\_consumer\_group('dss','决策支持组');  
（3）、建立资源计划  
exec dbms\_resource\_manager.create\_plan('day','该资源计划用于联机事务处理');  
exec dbms\_resource\_manager.create\_plan('night','该资源计划用于决策支持');  
（4）、建立资源计划指令  
说明：建立资源计划指令必须要在资源计划和other\_groups组之间的定义管理关系。  
例子：  
begin  
dbms\_resource\_manager.create\_plan\_directive(plan=>'DAY',group\_or\_subplan=>'sys\_group',comment=>'最高级别组',cpu\_p1=>100,parallel\_degree\_limit\_p1=>3);  
dbms\_resource\_manager.create\_plan\_directive(plan=>'DAY',group\_or\_subplan=>'olpt',comment=>'中间级别组',cpu\_p2=>80,parallel\_degree\_limit\_p1=>1);  
dbms\_resource\_manager.create\_plan\_directive(plan=>'DAY',group\_or\_subplan=>'other\_groups',comment=>'最低级别组',cpu\_p3=>80,parallel\_degree\_limit\_p1=>1);  
dbms\_resource\_manager.create\_plan\_directive(plan=>'NIGHT',group\_or\_subplan=>'sys\_group',comment=>'最高级别组',cpu\_p1=>100,parallel\_degree\_limit\_p1=>20);  
dbms\_resource\_manager.create\_plan\_directive(plan=>'NIGHT',group\_or\_subplan=>'dss',comment=>'中间级别组',cpu\_p2=>80,parallel\_degree\_limit\_p1=>20);  
dbms\_resource\_manager.create\_plan\_directive(plan=>'NIGHT',group\_or\_subplan=>'other\_groups',comment=>'最低级别组',cpu\_p3=>80,parallel\_degree\_limit\_p1=>20);  
end;  
（5）、验证pending内存区  
说明：验证通过则可以提交pending内存区，不通过需要清除pending内存区并重新建立资源对象。  
exec dbms\_resource\_manager.validate\_pending\_area  
（6）、提交pending内存区  
说明：提交pending内存区最终建立永久的资源管理对象。  
exec dbms\_resource\_manager.submit\_pending\_area  
3）、分配用户到资源使用组  
exec dbms\_resource\_manager\_privs.grant\_switch\_consumer\_group('scott','oltp',false);  
exec dbms\_resource\_manager\_privs.grant\_switch\_consumer\_group('scott','dss',false);  
4）、设置用户的默认资源使用组  
说明：数据库用户可以属于多个资源使用组，但在特定会话特定时刻只能使用某个资源使用组的相应资源。  
通过设置用户的默认资源使用组，可以使用户登录时自动使用相应资源使用组的资源。  
exec dbms\_resource\_manager.set\_initial\_consumer\_group('scott','oltp');  
5）、激活资源计划  
说明：为了通过数据库资源管理器限制数据库用户的资源使用，必须要激活资源计划。  
alter system set resource\_manager\_plan=DAY where scope=momory.  
6）、改变会话或用户的资源使用组  
说明：如果用户属于多个资源使用组，初始登录时会使用默认资源使用组，为了改变特定会话的资源使用组，  
执行exec dbms\_resource\_manager.switch\_consumer\_group\_for\_sess(7,8,'dss');  
为了改变特定用户所有会话的资源使用组，  
执行exec dbms\_resource\_manager.switch\_consumer\_group\_for\_user('scott','dss');  
  
  
二十、dbms\_stats  
1、概述  
作用:用于搜集,查看,修改数据库对象的优化统计信息.  
  
2、包的组成  
1）、get\_column\_stats  
作用：用于取得列的统计信息  
语法：dbms\_stats.get\_column\_stats(ownname varchar2,tabname varchar2,colname varchar2,partname varchar2 default null,stattab varchar2 default null,  
statid varchar2 default null,distcnt out number,density out number,nullcnt out number,srec out statrec,avgclen out number,statown varchar2 default null);  
其中ownname指定方案名，tabname指定表名，colname指定列名，partname指定分区名，stattab指定用户统计表名，statid指定与统计相关的标识符，distcnt返回不同值的个数，  
density返回列的密度，nullcnt返回列的null个数，srec返回列的最大、最小和直方图值，avgclen返回列的平均长度，statown指定包含stattab的方案名。  
  
2）、get\_index\_stats  
作用：用于取得索引的统计信息  
语法：dbms\_stats.get\_index\_stats(ownnane varchar2,indname varchar2,  
partname varchar2 default null,stattab varchar2 default null,  
statid varchar2 default null,numrows out number,  
numlblks out number,numdist out number,  
avglblk out number,avgdblk out number,clstfct out number,  
indlevel out number,statown varchar2 default null);  
其中ownnane指定索引所有者名，indname指定索引名，partname指定索引分区名，  
stattab指定统计表名，statid指定统计表相关的标识符，numrows返回索引行数，  
numlblks返回索引块个数，numdist返回索引不同键值个数，  
avglblk返回每个键值占用的平均叶块个数，avgdblk返回每个键值对应表行所占用的平均数据块个数，  
clstfct返回索引的聚簇因子，indlevel返回索引层数，statown指定统计表所有者。  
  
3）、get\_system\_stats  
作用：用于从统计表或数据字典中取得系统统计信息  
语法：dbms\_stats.get\_system\_stats(status out varchar2,dstart out date,dstop out date,  
pname varchar2,pvalue out number,stattab in varchar2 default null,statid in varchar2 default null,statown in varchar2 default null);  
其中status返回状态信息(completed,autogathering,manualgathering)，dstat返回起始搜集日期，dstop返回结束搜集日期，  
pname指定要取得的参数名(sreadtim,mreadtim,cpuspeed,mbrc,maxthr,slavethr)，pvalue返回参数值。  
  
4）、get\_table\_stats  
作用：用于取得表的统计信息  
语法：dbms\_stats.get\_table\_stats(owname varchar2,tabname varchar2,  
partname varchar2 default null,stattab varchar2 default null,  
statid varchar2 default null,numrows out number,numblks out number,  
avgrlen out number,statown varchar2 default null);  
其中avgrlen返回表行的平均长度。  
  
5）、delete\_column\_stats  
作用：用于删除列的统计信息。  
语法：dbms\_stats.delete\_column\_stats(ownname varchar2,tabname varchar2,colname varchar2,  
partname varchar2 default null,stattab varchar2 default null,statid varchar2 default null,  
cascade\_parts boolean default true,statown varchar2 default null,no\_invalidate boolean default false);  
其中cascade\_parts指定是否要级联删除分区统计，no\_invalidate指定是否要使相关游标无效。  
例子：dbms\_stats.delete\_column\_stats('scott','emp','ename');  
  
6）、delete\_index\_stat  
作用：用于删除索引统计信息  
语法：dbms\_stats.delete\_index\_stats(ownname varchar2,indname varchar2,  
partname varchar2 default null,stattab varchar2 default null,  
statid varchar2 default null,cascade\_parts boolean default true,  
statown varchar2 default null,no\_invalidate boolean default false);  
例子：exec dbms\_stats.delete\_index\_stats('scott','pk\_emp');  
  
7）、delete\_system\_stats  
作用：用于删除系统统计信息  
语法：dbms\_state.delete\_system\_stats(stattab varchar2 default null,  
statid varchar2 default null,statown varchar2 default null);  
  
8）、delete\_table\_stats  
作用：用于删除表的统计信息  
语法：dbms\_stats.delete\_table\_stats(ownname varchar2,tabname varchar2,  
partname varchar2 default null,stattab varchar2 default null,  
statid varchar2 default null,cascade\_parts boolean default true,  
cascade\_columns boolean default true,cascade\_indexes boolean default true,  
statown varchar2 default null,no\_invalidate boolean default false);  
其中cascade\_columns指定是否级联删除列统计，cascade\_indexes指定是否级联删除索引统计。  
例子：exec dbms\_stats.delete\_table\_stats('scott','emp');  
  
9）、delete\_schema\_stats  
作用：用于删除特定方案的统计信息  
语法：dbms\_stats.delete\_schema\_stats(ownname varchar2,stattab varchar2 default null,  
statid varchar2 default null,statown varchar2 default null,no\_invalidate boolean default false);  
例子：exec dbms\_stats.delete\_schema\_stats('scott');  
  
10）、delete\_database\_stats  
作用：用于删除整个数据库的统计信息  
语法：dbms\_stats.delete\_database\_stats(stattab varchar2 default null,statid varchar2 default null,  
statown varchar2 default null,no\_invalidate boolean default false);  
例子：exec dbms\_stats.delete\_database\_stats  
  
11）、create\_stat\_table  
作用：用于在特定方案建立统计表  
语法：dbms\_stats.create\_stat\_table(ownname varchar2,stattab varchar2,tblspace varchar2 default null);  
其中tblspace指定统计表所在表空间。  
例子：exec dbms\_stats.create\_stat\_table('scott','stattab');  
  
12）、drop\_stat\_table  
作用：用于删除特定方案的统计表  
语法：dbms\_stats.drop\_stat\_table(ownnanme varchar2,stattab varchar2);  
例子：exec dbms\_stats.drop\_stat\_table('scott','stattab');  
  
13）、export\_column\_stats  
作用：用于导出列统计并存储到统计表中。  
语法：dbms\_stats.export\_column\_stats(ownname varchar2,tabname varchar2,colname varchar2,  
partname varchar2 default null,stattab varchar2,  
statid varchar2 default null,statown varchar2 default null);  
例子：exec dbms\_stats.export\_column\_stats('scott','emp','ename',stattab=>'stattab');  
  
14）、export\_index\_stats  
作用：导出索引统计信息，并存储到统计表中  
语法：dbms\_stats.export\_index\_stats(owname varchar2,indname varchar2,  
partname varchar2 default null,stattab varchar2,  
statid varchar2 default null,statown varchar2 default null,  
statown varchar2 default null);  
例子：exec dbms\_stats.export\_index\_stats('scott','pk\_emp',stattab=>'stattab');  
  
15）、export\_system\_stats  
作用：用于导出系统统计信息，并存储到统计表中。  
语法：dbms\_stats.export\_system\_stats(stattab varchar2,statid varchar2 default null,statown varchar2 default null);  
  
16）、export\_table\_stats  
作用：用于导出表的统计信息，并将其存储到统计表中  
语法：dbms\_stats.export\_table\_stats(ownname varchar2,tabname varchar2,partname varchar2 default null,stattab varchar2,  
statid varchar2 default null,cascade boolean default true,statown varchar2 default null);  
例子：exec dbms\_stats.export\_table\_stats('scott','emp',stattab=>'stattab');  
  
17）、export\_schema\_stats  
作用：用于导出方案的统计信息，并将其存储到统计表中。  
语法：dbms\_stats.export\_schema\_stats(ownname varchar2,stattab varchar2,  
statid varchar2 default null,statown varchar2 default null);  
例子：exec dbms\_stats.export\_shcema\_stats('scott',stattab=>'stattab');  
  
18）、export\_database\_stats  
作用：用于导出数据库的所有统计信息，并存储到统计表中。  
语法：dbms\_stats.export\_database\_stats(stattab varchar2,statid varchar2 default null,statown varchar2 default null);  
例子：exec dbms\_stats.export\_database\_stats(stattab=>'stattab',statown=>'scott');  
  
19）、import\_column\_stats  
作用：用于从统计表取得列统计，并将其存储到数据字典中。  
语法：dbms\_stats.import\_column\_stats(ownname varchar2,tabname varchar2,colname varchar2,  
partname varchar2 default null,stattab varchar2,statid varchar2 default null,statown varchar2 default null,no\_invalidate boolean default false);  
例子：exec dbms\_stats.import\_column\_stats('scott','emp','ename',stattab=>'stattab',statown=>'scott');  
  
20）、import\_index\_stats  
作用：用于从统计表中取得索引统计，并将其存储到数据字典中。  
语法：dbms\_stats.import\_index\_stats(ownname varchar2,indname varchar2,  
partname varchar2 default null,stattab varchar2,statid varchar2 default null,  
statown varchar2 default null,no\_invalidate boolean default false);  
例子：exec dbms\_stats.import\_index\_stats('scott','pk\_emp',stattab=>'stattab',statown=>'scott');  
  
21）、import\_system\_stats  
作用：用于从统计表中取得系统统计，并将其存储到数据字典中。  
语法：dbms\_stats.import\_system\_stats(stattab varchar2,statid varchar2 default null,statown varchar2 default null);  
  
22）、import\_table\_stats  
作用：用于从统计表中取得表统计，并将去存储到数据字段中。  
语法：dbms\_stats.import\_table\_stats(ownname varchar2,tabname varchar2,partname varchar2 default null,  
stattab varchar2,statid varchar2 default null,cascade boolean default true,  
statown varchar2 default null,no\_invalidate boolean default false);  
例子：exec dbms\_stats.import\_table\_stats('scott','emp',stattab=>'stattab',statown=>'scott');  
  
23）、import\_schema\_stats  
作用：用于从统计表中取得方案统计，并将去存储到数据字典中。  
语法：dbms\_stats.import\_schema\_stats(owname varchar2,stattab varchar2,  
statid varchar2 default null,statown varchar2 default null,no\_invalidate boolean default flase);  
例子：exec dbms\_stats.import\_schema\_stats('scott',stattab=>'stattab',statown=>'scott');  
  
24）、import\_database\_stats  
作用：用于从统计表中取得数据库所有对象的统计，并将其存储到数据字典中。  
语法：dbms\_stats.import\_database\_stats(stattab varchar2,statid varchar2 default null,  
statown varchar2 default null,no\_invalidate boolean default false);  
例子：exec dbms\_stats.import\_database\_stats(stattab=>'stattab',statown=>'scott');  
  
25）、gather\_index\_stats  
作用：用于搜集索引统计  
语法：dbms\_stats.gather\_index\_stats(ownname varchar2,indname varchar2,  
partname varchar2 default null,estimate\_percent number default null,  
stattab varchar2 default null,statid varchar2 default null,  
statown varchar2 default null,degree number default null,  
granularity varchar2 default 'default',no\_invalidate boolean default false);  
其中ownname指定方案名，indname指定索引名，partname指定分区名，estimate\_percent指定要预估的行百分比，  
stattab指定用户统计表名，statid指定与统计相关的标识符，statown指定包含STATTAB的方案名，degree指定并行度，  
granularity指定要搜集索引的粒度(default,subpartition,partition,global,all)，no\_invalidate指定是否要使用相关游标无效。  
例子：exec dbms\_stats.gather\_index\_stats('scott','pk\_emp');  
  
26）、gather\_table\_stats  
作用：用于搜集表统计  
语法：dbms\_stats.gather\_table\_stats(ownname varchar2,tabname varchar2,  
partname varchar2 default null,estimate\_percent number default null,  
block\_sample boolean default false,method\_opt varchar2 default 'FOR ALL COLUMNS SIZE 1',  
degree number default null,granularity varchar2 default 'default',cascade boolean default false,  
stattab varchar2 default null,statid varchar2 default null,statown varchar2 default null,  
no\_invalidate boolean default false);  
其中block\_sample指定是否使用随机采样的块个数取代行百分比，method\_opt指定列统计的搜集方法，cascade指定是否要级联搜集索引统计。  
例子：exec dbms\_stats.gather table\_stats('scott','emp');  
  
27）、gather\_schema\_stats  
作用：用于搜集特定方案所有对象的统计  
语法：dbms\_stats.gather\_schema\_stats(ownname varchar2,  
estimate\_percent number default null,block\_sample boolean default false,  
method\_opt varchar2 default 'FOR ALL COLUMNS SIZE 1',  
degree number default null,granularity varchar2 default 'default',  
cascade boolean default false,stattab varchar2 default null,  
statid varchar2 default null,option varchar2 default 'GATHER',  
objlist out ObjectTab,statown varchar2 default null,  
no\_invalidate boolean default false,gather\_temp boolean default false);  
其中option指定统计搜集选项(GATHER,GATHER AUTO,GATHER STALE,GATHER EMPTY,LIST AUTO,LIST STALE,LIST EMPTY);  
objlist返回旧对象或空对象，gather\_temp指定是否要搜集临时表统计。  
例子：exec dbms\_stats.gather\_schema\_stats('scott');  
  
28）、gather\_database\_stats  
作用：用于搜集数据库所有对象的统计。  
语法：dbms\_stats.gather\_database\_stats(  
estimate\_percnet number default null,block\_sample boolean default false,  
method\_opt varchar2 default 'FOR ALL COLUMNS SIZE 1',  
degree number default null,granularity varchar2 default 'default',  
cascade boolean default false,stattab varchar2 default null,  
statid varchar2 default null,options varchar2 default 'gather',  
objlist out ObjectTab,statown varchar2 default null,  
gather\_sys boolean default false,no\_invalidate boolean default false,  
gather\_temp boolean default false);  
其中estimate\_percent指定要预估的行百分比，block\_sample指定是否使用随机采样的块个数取代行百分比，  
method\_opt指定列统计的搜集方法，degree指定并行度，  
granularity指定要搜集索引的粒度(default,subpartition,partition,global,all)，  
cascade指定是否要级联搜集索引统计，stattab指定用户统计表名，statid指定与统计相关的标识符，  
option指定统计搜集选项(GATHER,GATHER AUTO,GATHER STALE,GATHER EMPTY,LIST AUTO,LIST STALE,LIST EMPTY)，  
objlist返回旧对象或空对象，statown指定包含STATTAB的方案名,garher\_sys指定是否要搜集sys用户对象的统计，  
no\_invalidate指定是否要使用相关游标无效，gather\_temp指定是否要搜集临时表统计。  
例子：exec dbms\_stats.gather\_database\_stats.  
  
29）、oather\_system\_stats  
作用：用于搜集系统统计  
语法：dbms\_stats.gather\_system\_stats(  
gathering\_mode varchar2 default 'NOWORKLOAD',  
interval integer default null,stattab varchar2 default null,  
statid varchar2 default null,statown varchar2 default null);  
其中gathering\_mode用于指定搜集模式值(noworkload,interval,start|stop),  
interval用于指定搜集统计的时间间隔(只适用interval模式)  
例子：exec dbms\_stats.gather\_system\_stats.  
  
30）、UNLOCK\_TABLE\_STATS  
作用：专治ORA-38029，解锁对象统计信息。  
例子：DBMS\_STATS.UNLOCK\_TABLE\_STATS('MID\_SC','DM\_OLD\_FEATURE\_VALUE')  
  
3、综合示例  
declare  
dist\_count number;  
density number;  
null\_count number;  
srec dbms\_stats.statrec;  
avg\_col\_len number;  
numrows number;  
numlblks number;  
numdist number;  
avglblk number;  
avgdblk number;  
clstfct number;  
indlevel number;  
avgrlen numer;  
begin  
\--get\_column\_stats  
dbms\_stats.get\_column\_stats('scott','emp','job',distcnt=>dist\_count,density=>density,nullcnt=>null\_count,srec=>srec,avgclen=>avg\_col\_len);  
dbms\_output.put\_line('不同列值个数'||dist\_count);  
dbms\_output.put\_line('列平均长度'||avg\_col\_len);  
\--get\_index\_stats  
dbmns\_stats.get\_index\_stats('scott','PK\_EMP',numrows=>numrows,numlblks=>numlblks,  
numdist=>numdist,avglblk=>avglblk,avgdblk=>avgdblk,clstfct=>clstfct,indlevel=>indlevel);  
dbms\_output.put\_line('叶块个数'||numlblks);  
dbms\_output.put\_line('索引层次'||indlevel);  
\--get\_table\_stats  
dbms\_stats.get\_table\_stats('scott','emp',numrows=>numrows,numlblks=>numlblks,avgrlen=>avgrlen);  
dbms\_output.put\_line('表的总计行数'||numrows);  
dbms\_output.put\_line('表所占有的块个数'||numlblks);  
dbms\_output.put\_line('表行的平均水平'||avgrlen);  
end;  
  
  
二十一、utl\_file  
1、概述  
作用:用于读写OS文件.使用该包访问OS文件时,必须要为OS目录建立相应的DIRECTORY对象..当用户要访问特定目录下的文件时,必须要具有读写DIRECTORY对象的权限.在使用UTL\_FILE包之前,应首先建立DIRECTORY对象.  
  
2、包的组成  
1）、file\_type  
作用：该类型是utl\_file包中所定义的记录类型，其成员是私有的，不能被直接引用。  
该类型的定义如下：  
type file\_type is record(is binary\_integer,datatype binary\_integer);  
  
2）、fopen  
作用：用于打开OS文件。注意，使用该函数最多可以同时打开50个文件  
语法：file\_type.fopen(  
location in varchar2,filename in varchar2,  
open\_mode in varchar2,max\_linesize in binary\_integer) return file\_type;  
说明：当成功执行后会返回文件句柄，访问该文件可以直接使用文件句柄。  
如果执行失败会触发异常或显示错误。注意当指定文件位置时必须要使用directory对象，且其名称必须大写。  
  
3）、fopen\_nchar  
作用：用于以unicode方式打开文件。当使用该函数打开文件后，读写文件会使用unicode取代数据库字符集。  
语法：utl\_file.fopen\_nchar(  
location in varchar2,  
filename in varchar2,  
open\_mode in varchar2  
max\_linesize in binary\_integer) return file\_type;  
  
4）、is\_open  
作用：用于确定文件是否已经打开  
语法：utl\_file.is\_open(file in file\_type) return boolean;  
其中file指定文件句柄，如果文件已经被打开返回true，否则返回false。  
  
5）、fclose  
作用：用于关闭已经打开的文件  
语法：utl\_file.fclose(file in out file\_type);  
  
6）、fclose\_all  
作用：用于关闭当前会话打开的所有文件  
语法：utl\_file.fclose\_all;  
  
7）、get\_line  
作用：用于从已打开文件中读取行内容，行内容会被读取到输出缓冲区  
语法：utl\_file.get\_line(file in file\_type,buffer out varchar2,  
linesize in number,len in pls\_integer default null);  
其中buffer用于存储读取信息，linesize指定要读取的最大字节输，len指定实际读取长度。  
  
8）、get\_line\_nchar  
作用：用于以unicode方式读取已打开文件的行内容，并且将行内容读取到输出缓冲区。  
语法：utl\_file.get\_line\_nchar(file in file\_type,buffer out varchar2,  
len in pls\_integer default null);  
  
9）、get\_raw  
作用：用于从文件中读取raw字符串，并调节文件指针到读取位置。  
语法：utl\_file.get\_raw(fid in utl\_file.file\_type,r out nocopy raw,  
len in pls\_integer default null);  
  
10）、put  
作用：用于将缓冲区内容写入到文件中，当使用put过程时文件必须要以写方式打开。  
在写入缓冲区内容后如果要结束行，可以用new\_line过程。  
语法：utl\_file.put(file in file\_type,buffer in varchar2);  
  
11）、put\_nchar  
作用：用于将缓冲区内容以unicode方式写入到文件。  
语法：utl\_file.put\_nchar(file in file\_type,buffer in varchar2);  
  
12）、put\_raw  
作用：用于将raw缓冲区中的数据写入到OS文件。  
语法：utl\_file.put\_raw(fid in utl\_file.file\_type,r in raw,  
autoflush in boolean default false);  
其中fid指定文件句柄，r指定存放raw数据的缓冲区，autoflush指定是否要自动刷新缓冲区数据。  
  
13）、new\_line  
作用：用于为文件增加行终止符。  
语法：utl\_file.new\_line(file in file\_type,lines in natural:=1);  
其中lines指定要增加的行终止符个数。  
  
14）、put\_line  
作用：用于将文本缓冲区内容写入到文件中。当使用该过程为文本追加内容时会自动在内容的尾部追加终止符。  
语法：utl\_file.put\_line(file in file\_type,buffer in varchar2,  
autoflush in boolean default false);  
  
15）、put\_line\_nchar  
作用：用于将文本缓冲区内容以unicode方式写入文件。自动在尾部追加终止符。  
语法：utl\_file.put\_line\_nchar(file in file\_type,buffer in varchar2);  
  
16）、putf  
作用：用于以特定格式将文本内容写入到OS文件，其中格式符%s表示字符串，格式符\\n表示行终止符。  
语法：utl\_file.putf(file in file\_type,format in varchar2  
\[,arg1 in varchar2 default null,...arg5 in varchar2 default null\]);  
其中format指定格式符（最多5个%s），arg1...arg5指定对应格式符的字符串。  
  
17）、putf\_nchar  
作用：用于以特定格式将文本内容以unicode的方式写入到OS文件，其中格式符%s表示字符串，格式符\\n表示行终止符。  
语法：utl\_file.putf\_nchar(file in file\_type,format in varchar2  
\[,arg1 in varchar2 default null,...arg5 in varchar2 default null\]);  
其中format指定格式符（最多5个%s），arg1...arg5指定对应格式符的字符串。  
  
18）、fflush  
作用：用于将数据强制性写入到OS文件。正常情况下当给文件写入数据时，  
数据会被暂时存放在缓冲中，过程fflush用于强制将数据写入到OS文件中。  
语法：utl\_file.fflush(file in file\_type);  
  
19）、fseek  
作用：用于移动文件指针到特定位置。既可以指定文件指针的绝对位置，也可以指定文件指针的相对位置。  
语法：utl\_file.fseek(  
fid in utl\_file.file\_type,absolute\_offset in pl\_integer default null,  
relative\_offset in pls\_integer default null);  
其中absolute\_offset指定文件指针的绝对位置(单位：字节)，  
relative\_offset指定文件指针的相对位置（单位：字节）。  
  
20）、fremove  
作用：用于删除磁盘文件  
语法：utl\_file.fremove(location in varchar2,filename in varchar2);  
其中location指定directory对象（必须大写），filename指定要删除的OS文件名。  
  
21）、fcopy  
作用：用于将源文件的全部或部分内容复制到目标文件中。如果不设置起始行和结束行则复制文件所有内容。  
语法：utl\_file.fcopy(location ni varcahr2,  
filename in varchar2,  
dest\_dir in varchar2,  
dest\_file in varchar2,  
start\_line in pls\_integer default 1,  
end\_line in pls\_integer default null);  
其中location指定源文件所在目录对应的directory对象，filename指定源文件名，  
dest\_dir指定目标文件所在目录对应的directory对象，dest\_file指定目标文件的名字，  
start\_line指定起始行号，end\_line指定结束行号。  
例子：exec utl\_file.fcopy('USER\_DIR','a.txr','USER\_DIR','c.txt');  
  
22）、fgetpos  
作用：返回文件指针所在的偏移位置。  
语法：utl\_file.fgetpos(fileid in file\_type) return pls\_integer;  
  
23）、fgetattr  
作用：用于读取磁盘文件，并返回文件属性  
语法：utl\_file.fgetattr(location in varchar2,filename in varchar2,  
exists out boolean,file\_length out number,blocksize out number)  
其中location指定OS目录所对应的directory对象，filename指定OS文件名，  
exists确定文件是否存在，file\_length取得文件长度，blocksize取得OS块的尺寸。  
  
24）、frename  
作用：用于修改已存在的OS文件名，其作用与unix的mv命令完全相同。  
在修改文件名时，通过指定overwrite参数，可以覆盖已存在的文件。  
语法：utl\_file.frename(location in varchar2,filename in varchar2,dest\_dir in varchar2,  
dest\_file in varchar2,overwrite in boolean default false);  
其中overwrite指定是否要覆盖已存在文件(false:不能覆盖，true:覆盖)。  
例子：exec utl\_file.frename('USER\_DIR','d.txt','USER\_DIR','c.txt');  
  
3、综合例子  
declare  
handle utl\_file.file\_type;  
buffer varchar2(100);  
buffer1 raw(100);  
fileexist boolean;  
filelen int;  
os\_block int;  
begin  
\--is\_open  
if not utl\_file.is\_open(handle) then  
\--fopen  
bandle:=utl\_file.fopen('user\_dir','readme.txt','r',1000);  
end if;  
dbms\_output.put\_line('打开文件成功');  
\--get\_line  
utl\_file.get\_line(handle,buffer,100);  
dbms\_output.put\_line(buffer);  
utl\_file.fclose(handle);  
\--get\_line\_nchar  
handle:=utl.file.fopen\_nchar('USER\_DIR','a.txt'.'r',1000);  
utl\_file.get\_line\_nchar(bandle.buffer);  
dbms\_output.put\_line(buffer);  
utl\_file.fclose(handle);  
\--get\_raw  
handle:=utl\_file.fopen('USER\_DIR','a.txt'.'r',1000);  
utl\_file.get\_raw(handle,buffer1,100);  
dbms\_output.put\_line(buffer1);  
utl\_file.fclose(handle);  
\--put  
handle:=utl\_file.fopen('USER\_DIR','b.txt'.'w',1000);  
buffer:='中华';  
utl\_file.put(handle,buffer);  
utl\_file.new\_line(handle);  
buffer:='中国';  
utl\_file.put\_line(handle,buffer);  
utl\_file.fclose(handle);  
\--put\_nchar  
handle:=utl\_file.fopen('USER\_DIR','b.txt'.'w',1000);  
buffer:='中华';  
utl\_file.put(handle,buffer);  
utl\_file.new\_line(handle);  
buffer:='中国';  
utl\_file.put\_line\_nchar(handle,buffer);  
utl\_file.fclose(handle);  
\--put\_raw  
handle:=utl\_file.fopen('USER\_DIR','b.txt'.'w',1000);  
buffer:='01f3d5a4c7d8';  
utl\_file.put\_raw(handle,buffer);  
utl\_file.new\_line(handle);  
utl\_file.fclose(handle);  
\--putf  
handle:=utl\_file.fopen('USER\_DIR','b.txt'.'w',1000);  
utl\_file.putf(handle,'%s\\n%s\\n%s\\n','历史','文明','万岁');  
utl\_file.fclose(handle);  
\--fseek  
bandle:=utl\_file.fopen('USER\_DIR','a.txt','r');  
dbms\_output.put\_line('文件指针起始位置:'||utl\_file.fgetpos(handle));  
utl\_file.fseek(handle,20);  
dbms\_output.put\_line('文件指针起当前位置:'||utl\_file.fgetpos(handle));  
utl\_file.fclose(handle);  
\--fgetattr  
utl\_file.fgetatttr('USER\_DIR','readme.txt',fileexist,filelen,os\_block);  
if fileexist then  
dbms\_output.put\_line('文件尺寸：'||filelen);  
dbms\_output.put\_line('OS块尺寸：'||os\_block);  
end if;  
end;  
  
  
二十二、utl\_inaddr  
1、概述  
作用:用于取得局域网或Internet环境中的主机名和IP地址.  
  
2、包的组成  
1）、get\_host\_name  
作用：用于取得指定IP地址所对应的主机名  
语法：utl\_inaddr.get\_host\_name(ip in varchr2 default null) return varchar2;  
其中ip指定tcp/ip地址  
例子：select utl\_inaddr.get\_host\_name('127.0.0.1') hostname from dual;  
  
2）、get\_host\_address  
作用：用于取得指定主机所对应的ip地址  
语法：utl\_inaddr.get\_host\_address(host in varchar2 default null) return varchar2;  
其中host指定主机名  
例子：select utl\_inaddr.get\_host\_address('liutao') ip from dual;  
  
  
二十三、DBMS\_METADATA  
1、概述  
作用：提供提取数据库对象的完整定义的接口。这些定义可以用XML或SQL DDL格式描述。  
提供两种类型接口：可编程控制的接口；用于Ad Hoc查询的简单接口。  
  
  
二十三、DBMS\_SQL  
1、概述  
1),在整个程序的设计过程中,对游标的操作切不可有省略的部分,一旦省略其中某一步骤,则会程序编译过程既告失败,如在程序结尾处未对改游标进行关闭操作,则在再次调用过程时会出现错误.  
2),dbms\_sql除了可以做一般的select,insert,update,delete等静态的sql做能在过程中所做工作外,还能执行create等DDL操作,不过在执行该类操作时应首先显式赋予执行用户相应的系统权限,比如create table等.该类操作只需open cursor--->prase--->close cursor即能完成.  
  
(二)一般过程  
对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:  
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;  
而对于dml操作(insert,update)则需要进行以下几个步骤:  
open cursor--->parse--->bind variable--->execute--->close cursor;  
对于delete操作只需要进行以下几个步骤:  
open cursor--->parse--->execute--->close cursor;  
  
  
2、包的组成  
1）、OPEN\_CURSOR  
作用：打开一个动态游标，并返回一个整型，返回新游标的整型ID值。  
  
2）、PARSE  
作用：对动态游标所提供的sql语句进行解析编译。  
语法：DBMS\_SQL.parse(c in integer,statement in varchar2,language\_flag in integer)  
其中，参数C表示游标(可以设为C=DBMS\_SQL.open\_cursor),statement为sql语句,language\_flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用 BMS\_SQL.native)。  
  
3）、BIND\_VARIABLE  
作用：将给定的数量与特定的变量相连接，定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;  
语法：bind\_variable(c in integer, name in varchar2, value):  
  
4）、DEFINE\_COLOUMN  
作用：定义动态游标所能得到的对应值,定义字段变量，其值对应于指定游标中某个位置元素的值 （仅用于SELECT语句）  
语法：DBMS\_SQL.define\_column(c in integer,position in integer,column any datatype,\[column\_size in integer\])  
其中，c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column\_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);  
  
5）、EXECUTE  
作用：执行游标,并返回处理一个整型,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);  
语法：function execute(c in integer):  
  
6）、EXECUTE\_AND\_FETCH  
作用：执行指定的游标并取记录   
  
7）、FETCH\_ROWS  
作用：从指定的游标中取出记录，循环取数据,并返回一个整数,为0时表示已经取到游标末端;  
语法：function fetch\_rows(c in integer):   
  
8）、COLUMN\_VALUE  
作用：返回游标中指定位置的元素，将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;  
语法：column\_value(c in integer, position in integer, value):  
  
9）、IS\_OPEN  
作用：当指定的游标状态为OPEN时返回真值   
  
10）、CLOSE\_CURSOR  
作用：关闭一个动态游标，参数为open\_cursor所打开的游标，并释放内存。  
语法：DBMS\_SQL.close\_cursor(c in out integer)  
  
11）、LAST\_ERROR\_POSITION  
作用：返回出错SQL语句的字节偏移量   
  
12）、LAST\_ROW\_ID  
作用：返回最后一条记录的ROWID   
  
13）、LAST\_SQL\_FUNCTION\_CODE  
作用：返回语句的SQL FUNCTION CODE