Oracle笔记十九：内置包

1、DBMS_ALERT

1.1、说明

用于生成并传递数据库预警信息
作用:用于生成并传递数据库预警信息.使用包DBMS_ALERT,则必须以SYS登陆,为该用户授予执行权限.
sql>conn sys/oracle as sysdba
sql>grant execute on dbms_alert to scott;

1.2、DBMS_ALERT.REGISTER

说明：用于注册预警事件
语法：dbms_alter.register(name in varchar2);
其中name指定预警事件名称，其值不能超过30字节。
例子：exec dbms_alter.register(‘alter1’);

1.3、DBMS_ALERT.REMOVE

说明：用于删除会话不需要的预警事件.
语法：dbms_alert.remove(name in varchar2);
例子：exec dbms_alert.remove(‘alert1’);

1.4、DBMS_ALERT.REMOVEALL

说明：用于删除当前会话所有已注册的预警事件
语法：dbms_alter.removeall

1.5、DBMS_ALERT.SET_DEFAULTS

说明：用于设置检测预警事件的时间间隔,默认时间间隔为5秒
语法：dbms_alert.set_defaults(sensitivity in number);
例子：dbms_alert.set_defaults(20)

1.5、DBMS_ALERT.SIGNAL

说明：用于指定预警事件所对应的预警消息。只有在提交事务时才会发出预警信号，而当回退事务时不会发出预警信号。
语法：dbms_alert.signal(name in varchar2,message in varchar2);
其中message指定预警事件的消息，长度不超过1800字节。
例子：exec dbms_alert.signal(‘alert1’,‘hello’);
实例：
create or replace trigger tr_upd_sal
after update of sal on emp
begin
dbms_alert.signal(‘sal_upd_alert’,‘修改了雇员工资’);
end;

1.6、DBMS_ALERT.WAITANY

说明：用于等待当前会话的任何预警事件,并且在预警事件发生时输出相应信息.在执行该过程之前,会隐含地发出COMMIT.
语法：
dbms_alter.waitany(name out varchar2,message out varchar2,status out integer,timeout in number default maxwait);
其中status用于返回状态值,返回0表示发生了预警事件,返回1表示超时;timeout用于设置预警事件的超时时间

1.7、DBMS_ALERT.WAITONE

说明：用于等待当前会话的特定预警事件,并且在发生预警事件时输出预警消息.在执行该过程之前,会隐含地发出COMMIT.
dbms_alter.waitone(name out varchar2,message out varchar2,status out integer,timeout in number default maxwait);

1.8、实例

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;

2、DBMS_DDL

提供了在PL/SQL块中执行DDL语句的方法
作用：提供了在PL/SQL块中执行DDL语句的方法,并且也提供了一些DDL的特殊管理方法.

2.1、DBMS_DDL.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.2、DBMS_DDL.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’);

2.3、DBMS_DDL.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表示只被触发一次。

2.4、DBMS_DDL.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时总是被触发.

3、DBMS_FLASHBACK

用于激活或禁止会话的flashback特征
作用:用于激活或禁止会话的flashback特征,为了使得普通用户可以使用该包,必须要将执行该包的权限授予这些用户，grant execute on dbms_flashback to scott;

3.1、DBMS_FLASHBACK.DISABLE

作用：用于禁止会话的flashback模式
语法：dbms_flashback.disable;

3.2、DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER

作用：用于以系统改变号（scn）方式激活会话的flashback
语法：dbms_flashback.enable_at_system_change_number(query_scn in number);
其中query_scn指定flashback对应的scn值

3.3、DBMS_FLASHBACK.ENABLE_AT_TIME

作用：用于以时间方式激活会话的flashback
语法：dbms_flashback.enable_at_time(query_time in timestamp);
其中query_time指定flashback对应的时间点；

3.4、DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER

作用：用于取得系统的当前scn值
语法：dbms_flashback.get_system_change_number return number;

3.5、实例

1）使用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

2）更新scott工资，并休眠5分钟
update emp set sal=3000 where ename=‘scott’;
commit;
exec dbms_lock.sleep(300);

3）取得SCOTT雇员工资及系统SCN值
select sal from emp where ename=‘scott’;–3600
seelct dbms_flashback.get_system_change_number from dual;–717402

4、DBMS_JOB

用于安排和管理作业队列
作用：用于安排和管理作业队列,通过使用作业,可以使ORACLE数据库定期执行特定的任务
注意：当使用DBMS_JOB管理作业时，必须确保设置了初始化参数job_queue_processes（不能为0）

4.1、DBMS_JOB.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’);

4.2、DBMS_JOB.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.3、DBMS_JOB.INSTANCE

说明：用于改变作业的例程
语法：dbms_job.instance(job in binary_integer,instance in binary_integer,force in boolean default false);
例子：exec dbms_job.instance(2,1);

4.4、DBMS_JOB.INTERVAL

说明：用于改变作业的运行时间间隔
语法：dbms_job.interval(job in binary_integer,interval in varchar2);
例子：exec dbms_job.interval(2,‘sysdate+1/24/60’);

4.5、DBMS_JOB.NEXT_DATE

说明：用于改变作业的下次运行日期
语法：dbms_job.next_date(job in binary_integer,next_date in date);
例子：exec dbms_job.next_date(‘2’,‘sysdate+1’);

4.6、DBMS_JOB.REMOVE

说明：删除作业队列中的特定作业
语法：dbms_job.remove(job in binary_integer);
例子：exec dbms_job.remove(21)–删除21号作业

4.7、DBMS_JOB.RUN

说明：用于运行已存在的作业
语法：dbms_job.run(job in binary_integer,force in boolean default false);
例子：exec dbms_job.run(1);

4.8、DBMS_JOB.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/(246060))’)加入作业。间隔10秒钟
exec dbms_job.submit(:v_num,‘a;’,sysdate,‘sysdate + (11/(24*60))’)加入作业。间隔11分钟

4.9、DBMS_JOB.WHAT

说明：用于改变作业要执行的操作
语法：dbms_job.what(job in binary_integer,what in varchar2);
例子：exec dbms_job.what(2,‘dbms_stats.gather_table_stats->(’‘scott’’,’‘emp’’);’);

5、DBMS_LOB

dbms_lob包的使用以及使用dbms_lob包来维护lob数据库类型的基本方法。随着社会的发展，在现代信息系统的开发中，需要存储的已不仅仅是简单的文字信息，同时还包括一些图片和音像资料或者是超长的文本。比如开发一套旅游信息系统，每一个景点都有丰富的图片、音像资料和大量的文字介绍。这就要求后台数据库要有存储这些数据的能力。ORACLE公司在其Oracle8i中通过提供LOB字段实现了该功能。
　　在ORACLE数据库中，LOB大对象类型是用来存储大量的二进制和文本数据的一种数据类型（一个LOB字段可存储可多达4GB的数据）。目前，它又分为两种类型：内部LOB和外部LOB。内部LOB将数据以字节流的形式存储在数据库的内部。因而，内部LOB的许多操作都可以参与事务，也可以像处理普通数据一样对其进行备份和恢复操作。Oracle8i支持三种类型的内部LOB：BLOB（二进制数据）、CLOB（单字节字符数据）、NCLOB（多字节国家字符数据）。其中CLOB和NCLOB类型适用于存储超长的文本数据，BLOB字段适用于存储大量的二进制数据，如图像、视频、音频等。目前，Oracle8i只支持一种外部LOB类型，即BFILE类型。在数据库内，该类型仅存储数据在操作系统中的位置信息，而数据的实体以外部文件的形式存在于操作系统的文件系统中。因而，该类型所表示的数据是只读的，不参与事务。该类型可帮助用户管理大量的由外部程序访问的文件。
　　ORACL提供了多种使用和维护LOB的方式，如使用PL/SQL DBMS_LOB包、调用OCI（Oracle Call Interface）、使用Proc*C/C++、使用JDBC等。其中最为方便有效的是使用PL/SQL调用DBMS_LOB包
　　在Oracle中，存储在LOB中数据称为LOB的值，如使用Select对某一LOB字段进行选择，则返回的不是LOB的值，而是该LOB字段的定位器（可以理解为指向LOB值的指针）

5.1、DBMS_LOB.APPEND

将源LOB中的内容加到目的LOB中
PROCEDURE append(dest_lob IN OUT NOCOPY BLOB,
src_lob IN BLOB);
PROCEDURE append(dest_lob IN OUT NOCOPY CLOB CHARACTER SET any_cs,
src_lob IN CLOB CHARACTER SET dest_lob%charset);
其中，各个参数的含义如下：
dest_lob是被源lob添加到的目标lob的定位器。
src_lob是源lob的定位器。
any_cs用来指定字符集。

5.2、DBMS_LOB.CLOSE

关闭已经打开的LOB

5.3、DBMS_LOB.COMPARE

比较两个同种数据类型的LOB的部分或全部值是否相同
dbms_lob.compare(
lob_1 IN BLOB/CLOB/BFILE,
lob_2 IN BLOB/CLOB/BFILE,
amount IN INTEGER:=4294967295,–要比较的字符数(CLOB),字节数(BLOB)
offset_1 IN INTEGER:=1,–lob_1的起始位置
offset_2 IN INTEGER:=1–lob_2 的起始位置)

5.4、DBMS_LOB.COPY

从源LOB中复制数据到目的LOB

5.5、DBMS_LOB.CREATETEMPORARY

在用户的临时表空间中，建立临时LOB

5.6、DBMS_LOB.ERASE

删除LOB中全部或部分内容

5.7、DBMS_LOB.FILECLOSE

关闭打开的BFILE定位符所指向的OS文件

5.8、DBMS_LOB.FILECLOSEALL

关闭当前会话已经打开的所有BFILE文件

5.9、DBMS_LOB.FILEEXISTS

确定file_loc对应的OS文件是否存在，1：存在。0：不存在

5.10、DBMS_LOB.FILEGETNAME

获取BFILE定位符所对应的目录别名和文件名

5.11、DBMS_LOB.FILEISOPEN

确定BFILE对应的OS文件是否打开

5.12、DBMS_LOB.FILEOPEN

打开文件

5.13、DBMS_LOB.FREETEMPORARY

释放在默认临时表空间中的临时LOB

5.14、DBMS_LOB.GETCHUNKSIZE

当建立包含CLOB/BLOB列的表时，通过指定CHUNK参数可以指定操纵LOB需要分配的字节数（数据库尺寸的整数倍）默认为数据块的尺寸

5.15、DBMS_LOB.GETLENGTH

获取LOB的长度
返回指定 LOB 数据的长度的函数
DBMS_LOB.GETLENGTH(lob_loc IN BLOB/CLOB/BFILE/NCLOB) RETURN INTEGER;

5.16、DBMS_LOB.INSTR

返回特定样式数据从LOB某偏移位置开始出现N次的具体位置。

5.17、DBMS_LOB.ISOPEN

确定LOB是否打开，打开：1，未打开：0

5.18、DBMS_LOB.SUBSTR

DBMS_LOB.SUBSTR(
lob_loc IN BLOB/CLOB/BFILE, – 提取的来源
amount IN INTEGER:=32762, – 提取长度
offset IN INTEGER:=1 – 开始位置
)RETURN RAW/VARCHAR2; – 提取到的内容
实例：
SELECT SUBSTR(DDLSQL,1,INSTR(DDLSQL,’) SEGMENT CREATION’,1)+1)||’;’ ,TABLE_NAME FROM (
select REPLACE(REPLACE(DBMS_LOB.substr(dbms_metadata.get_ddl(‘TABLE’,U.TABLE_NAME)),’"’,’’),‘C##CITIBANK.’,’’)AS DDLSQL ,
U.TABLE_NAME FROM USER_TABLES U ) ;

5.19、DBMS_LOB.TRIM

将LOB值减少到指定的长度

5.20、DBMS_LOB.WRITE

向LOB中写入数据
PROCEDURE WRITE(lob_loc IN OUT BLOB,
amount IN BINARY_INTEGER,
offset IN INTEGER,
buffer IN RAW);
PROCEDURE WRITE(lob_loc IN OUT CLOB CHARACTER SET any_cs,
amount IN BINARY_INTEGER,
offset IN INTEGER,
buffer IN VARCHAR2 CHARACTER SET lob_loc%charset);
各参数的含义为：
lob_loc：要写入的LOB定位器。
amount：写入LOB中的字节数。
offset：指定开始操作的偏移量。
buffer：指定写操作的缓冲区。

6、DBMS_LOGMNR

分析重做日志和归档日志
作用:通过使用包DBMS_LOGMNR和DBMS_LOGMNR_D,可以分析重做日志和归档日志所记载的事务变化,最终确定误操作(例如DROP TABLE)的时间,跟踪用户事务操作,跟踪并还原表的DML操作

6.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删除文件)

6.2、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.3、DBMS_LOGMNR.END_LOGMNR

作用：结束logminer会话
语法：dbms_logmnr.end_logmnr

6.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);

6.5、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分析选项。

6.6、实例

首先建表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 l o g m n r c o n t e n t s w h e r e s e g n a m e = ′ T E M