可恢复空间分配
在平时我们做一个大的表的索引,或者插入大量数据的时候,临时表空间,undo表空间或者数据表空间都有可能会产生空间不足的错误,这时候就可以利用可恢复表空间来管理,将这个操作挂起,等错误排除之后再恢复。这个好用的功能是在Oracle 9i之后被引入的。
这个功能在thomas的http://tomszrp.itpub.net/post/11835/264965已经介绍比较详细了。
两种启动方式
1、设置resumable_timeout。
2、alter session enable resumable
可恢复语句被挂起的发生状态:
1、超出空间使用
ORA-1653 unable to extend table ... in tablespace ...
ORA-1654 unable to extend index ... in tablespace ...
2、最大扩展数已经达到
ORA-1631 max # extents ... reached in table ...
ORA-1654 max # extents ... reached in index ...
3、空间分配已经达到
ORA-1536 space quote exceeded for tablespace string
通知DBA
在我们设置后将产生空间错误的事务挂起的之后,需要让DBA知道去处理。可以通过一个叫做AFTER SUSPEND的系统时间做触发器来通知DBA,例如:
CREATE OR REPLACE TRIGGER resumable_default_timeout AFTER SUSPEND ON DATABASE BEGIN DBMS_RESUMABLE.SET_TIMEOUT(10800); END; |
上面的触发器是用于修改超时时间,同意我们可以修改为发送邮件等功能来通知dba错误的产生。
另外,系统视图为:
user_resumable
dba_resumable
同时Oracle也提供了一个包来控制这个功能:dbms_resumable
SQL> desc dbms_resumable; Element Type ------------------- --------- ABORT PROCEDURE GET_SESSION_TIMEOUT FUNCTION SET_SESSION_TIMEOUT PROCEDURE GET_TIMEOUT FUNCTION SET_TIMEOUT PROCEDURE SPACE_ERROR_INFO FUNCTION |
还有一种方式就是后台警告文件。
贴出一个触发器的例子,可以根据自己的需要修改,引自oracle的在线文档。
CREATE OR REPLACE TRIGGER resumable_default AFTER SUSPEND ON DATABASE DECLARE /* declare transaction in this trigger is autonomous */ /* this is not required because transactions within a trigger are always autonomous */ PRAGMA AUTONOMOUS_TRANSACTION; cur_sid NUMBER; cur_inst NUMBER; errno NUMBER; err_type VARCHAR2; object_owner VARCHAR2; object_type VARCHAR2; table_space_name VARCHAR2; object_name VARCHAR2; sub_object_name VARCHAR2; error_txt VARCHAR2; msg_body VARCHAR2; ret_value BOOLEAN; mail_conn UTL_SMTP.CONNECTION; BEGIN -- Get session ID SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT; -- Get instance number cur_inst := userenv('instance'); -- Get space error information ret_value := DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner, table_space_name,object_name, sub_object_name); /* -- If the error is related to undo segments, log error, send email -- to DBA, and abort the statement. Otherwise, set timeout to 8 hours. -- -- sys.rbs_error is a table which is to be -- created by a DBA manually and defined as -- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000), -- suspend_time DATE) */ IF OBJECT_TYPE = 'UNDO SEGMENT' THEN /* LOG ERROR */ INSERT INTO sys.rbs_error ( SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME FROM DBMS_RESUMABLE WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst ); SELECT ERROR_MSG INTO error_txt FROM DBMS_RESUMABLE WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst; -- Send email to receipient via UTL_SMTP package msg_body:='Subject: Space Error Occurred Space limit reached for undo segment ' || object_name || on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') || '. Error message was ' || error_txt; mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25); UTL_SMTP.HELO(mail_conn, 'localhost'); UTL_SMTP.MAIL(mail_conn, 'sender@localhost'); UTL_SMTP.RCPT(mail_conn, 'recipient@localhost'); UTL_SMTP.DATA(mail_conn, msg_body); UTL_SMTP.QUIT(mail_conn); -- Abort the statement DBMS_RESUMABLE.ABORT(cur_sid); ELSE -- Set timeout to 8 hours DBMS_RESUMABLE.SET_TIMEOUT(28800); END IF; /* commit autonomous transaction */ COMMIT; END; / |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/222350/viewspace-967981/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/222350/viewspace-967981/