Shulou(Shulou.com)06/01 Report--

Oracle recoverable space allocation technology allows us to deal with problems by creating an after suspend on database trigger that runs automatically when a session is suspended, such as reporting a suspend event by email, checking and automatically fixing a suspend failure, etc., such as the ability to determine and automatically allocate capacity to a table space when inserting data leads to insufficient table space capacity. The following example shows the application in this area.

I. deployment of recoverable automatic space allocation function

Because DDL statements are not allowed to be called in after suspend on database triggers, space allocation operations cannot be performed by trigger calls. The solution here is to start the job through the scheduler, and the scheduling job must be event-based.

Enable recoverable space for all sessions by setting the instance parameter resumable_timeout. This is a dynamic parameter, such as setting the session to suspend for 1 minute when it encounters a space problem

Alter system set resumable_timeout = 60

Create a user for recoverable space management and authorize

Conn / as sysdba

Create user alloc identified by alloc

Grant connect, resource to alloc

Grant create job to alloc

Grant create trigger to alloc

Grant aq_administrator_role to alloc

Grant execute on dbms_aq to alloc

Grant select on dba_resumable to alloc

Grant select on dba_data_files to alloc

Grant dba to alloc

Connect to alloc user

Conn alloc/alloc

Create a table that holds SQL statements for recoverable space allocation

Create table resumable_sql (sql_text varchar2)

Define a type that records message information

Create or replace type event_queue_type as object (event_name varchar2 (30))

/

Create a queue list to log messages, specifying the table name and the type name of the message

Begin

Dbms_aqadm.create_queue_table (queue_table = > 'event_queue_table'

Queue_payload_type = > 'event_queue_type'

Multiple_consumers = > true)

End

/

Create a message queue, specify the queue name and queue table

Begin

Dbms_aqadm.create_queue (queue_name = > 'event_queue'

Queue_table = > 'event_queue_table')

End

/

Start queue

Begin

Dbms_aqadm.start_queue (queue_name = > 'event_queue')

End

/

Create an error log table to locate errors in the program

Create table err_logs (proc_name varchar2, log_time date, error_stack varchar2, error_backtrace varchar2)

Create a stored procedure that performs space allocation

Create or replace procedure alloc_space authid current_user is

My_count number

My_sql varchar2 (200)

Begin

-- get the execution statement for space allocation

Select count (*) into my_count from resumable_sql

If my_count! = 0 then

Select sql_text into my_sql from resumable_sql where rownum = 1

-- perform space allocation

Execute immediate my_sql

Delete from resumable_sql

Commit

End if

Exception

When others then

-- enter the error log

Insert into err_logs

(proc_name, log_time, error_stack, error_backtrace)

Values

('alloc_space'

Sysdate

Dbms_utility.format_error_stack

Dbms_utility.format_error_backtrace)

Commit

End

/

Create a program that performs space allocation

Begin

Dbms_scheduler.create_program (program_name = > 'alloc_space_pro'

Program_type = > 'stored_procedure'

Program_action = > 'alloc_space'

Enabled = > true)

End

/

Create a scheduling job that performs space allocation, which is triggered by an event

Begin

Dbms_scheduler.create_job (job_name = > 'alloc_space_job'

Program_name = > 'alloc_space_pro'

Start_date = > sysdate

Event_condition = > 'tab.user_data.event_name =' alloc_space_event'''

Queue_spec = > 'event_queue'

Enabled = > true)

End

/

Create stored procedures, check for recoverable space-pending sessions, generate DDL statements for space allocation, and send space allocation events to message queues

Create or replace procedure sus_tri_pro (v_tablespace varchar2

V_file_size number) authid current_user is

My_count number

My_err_number number

My_err_tablespace varchar2 (50)

My_filename varchar2 (200)

My_sql varchar2 (200)

My_enqueue_options dbms_aq.enqueue_options_t

My_message_properties dbms_aq.message_properties_t

My_message_handle raw (16)

My_queue_msg event_queue_type

Begin

-- check to see if there is a session suspended in recoverable space

Select count (*)

Into my_count

From dba_resumable

Where status = 'SUSPENDED'

If my_count! = 0 then

-- get the error number

Select error_number

Into my_err_number

From dba_resumable

Where rownum = 1

-is it due to a hang caused by insufficient tablespace capacity

If my_err_number = 1653 then

-- get the table space name

Select error_parameter4

Into my_err_tablespace

From dba_resumable

Where error_number = 1653

And rownum = 1

-- disposable tablespaces should be user-defined tablespaces

If my_err_tablespace = v_tablespace then

-- the new data file name after the last data file for the tablespace is generated

-- Files should be named according to two-digit rules, such as TEST01.DBF and TEST02.DBF...

Select replace (file_name

Substr (file_name,-6,2)

Trim (to_char (to_number (substr (file_name,-6,2)) + 1

'00')

Into my_filename

From dba_data_files

Where file_id = (select max (file_id))

From dba_data_files

Where tablespace_name = v_tablespace)

-- generate a SQL statement that recovers space allocation

My_sql: = 'alter tablespace' | | v_tablespace | | 'add datafile''| |

My_filename | |''size' | | v_file_size | |'m'

-- the SQL statement is inserted into the table waiting for processing

Delete from resumable_sql

Insert into resumable_sql (sql_text) values (my_sql)

Commit

-- send space allocation events to the message queue to notify the scheduler job for space allocation

My_queue_msg: = event_queue_type ('alloc_space_event')

Dbms_aq.enqueue (queue_name = > 'alloc.event_queue'

Enqueue_options = > my_enqueue_options

Message_properties = > my_message_properties

Payload = > my_queue_msg

Msgid = > my_message_handle)

End if

End if

End if

Exception

When others then

-- enter the error log

Insert into err_logs

(proc_name, log_time, error_stack, error_backtrace)

Values

('sus_tri_pro'

Sysdate

Dbms_utility.format_error_stack

Dbms_utility.format_error_backtrace)

Commit

End

/

Create an after suspend on database trigger to check and handle space allocation problems when the database hangs

Create or replace trigger sus_tri

After suspend on database

Begin

Sus_tri_pro ('TEST', 4)

End

/

Second, the running effect test

Create tablespaces and tables

Create tablespace test datafile'd:\ oradata\ mes\ test01.dbf' size 2m

Create table scott.t1 (C1 char (1000)) tablespace test

View tablespace data files

Col file_name for a30

Select file_name, bytes from dba_data_files where tablespace_name = 'TEST'

FILE_NAME BYTES

D:\ ORADATA\ MES\ TEST01.DBF 2097152

Grant session permission to restore space allocation

Grant resumable to scott

Connect to scott user

Conn scott/tiger

Insert data into the table

Begin

For i in 1.. 2000 loop

Insert into scott.t1 values ('a row')

End loop

Commit

End

/

As you can see, the session waits a little because there is not enough table space, and then the scheduler that performs the space allocation is started, and after the space allocation is completed, the operation is completed.

View the tablespace data file, and the system automatically allocates the new data file

Conn / as sysdba

Col file_name for a30

Select file_name, bytes from dba_data_files where tablespace_name = 'TEST'

FILE_NAME BYTES

D:\ ORADATA\ MES\ TEST01.DBF 2097152

D:\ ORADATA\ MES\ TEST02.DBF 4194304

View the successful execution of the scheduling job allocated by the space

Col owner for a10

Col job_name for a20

Col status for a10

Col run_duration for a20

Select *

From (select owner

Job_name

Status

To_char (actual_start_date, 'yyyy-mm-dd hh34:mi:ss') actual_start_date

Run_duration

From dba_scheduler_job_run_details

Where job_name = 'ALLOC_SPACE_JOB'

Order by actual_start_date desc)

Where rownum < 10

OWNER JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION

--

ALLOC ALLOC_SPACE_JOB SUCCEEDED 2018-02-07 12:05:32 + 000 00:00:00

Finish the test and do the cleaning.

Drop table scott.t1 purge

Drop tablespace test including contents and datafiles

If you want to remove the deployment of recoverable space features, you can simply delete the administrative user and all its objects

Conn / as sysdba

Drop user alloc cascade

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.