Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about Oracle recoverable space allocation technology, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Many operations fail because there is not enough space, and if recoverable space allocation is enabled, when the operation encounters a space problem rather than fails because of an error, the operation is paused and appears to be suspended as a session. When the error is resolved, it will continue. All pending sessions (currently suspended and previously suspended but now running again) are listed in the view dba_resumable. When the session is closed, the records in the view are eliminated.

To enable recoverable space allocation at the session level, the command is as follows:

Alter session enable resumable [timeout] [name]

The timeout option specifies how long the statement will be suspended. If this time is reached, but the problem is not solved, the statement fails and an error is returned. If no timeout is specified, the session is suspended indefinitely. The name option specifies the name displayed in the view dba_resumable to help determine where the space problem occurs in the multi-statement process.

To execute this command, the session user needs to be granted the appropriate permissions:

Grant resumable to

You can also enable recoverable space for all sessions by setting the instance parameter resumable_timeout. This is a dynamic parameter, such as to set an one-minute timeout:

Alter system set resumable_timeout=60

This will cause all sessions to hang for one minute when they encounter space problems.

The Data Pump utilities expdp and impdp have a command line switch resumable=y (default is n) that allows Data Pump jobs to hang if they encounter space problems.

When a session is suspended, it maintains control over all resources used, including undo space, temporary space, PGA memory, and record locking.

When you encounter a problem, you can fix it interactively through another session. Or you can create an after suspend on database trigger that will run when the session is suspended. This trigger can report problems, such as via email, or include code that checks for and automatically fixes the problem, which means that when operations such as inserting data result in insufficient tablespace capacity, automatically allocate capacity to the tablespace after confirmation by the trigger program.

The following lab tests restore the use of the space allocation function:

1. Create table spaces and tables

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

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

2. Grant the session permission to recover the space allocated

Grant resumable to scott

3. Set recoverable space allocation at the session level

Conn scott/tiger

Alter session enable resumable name 't1insert'

4. Insert data into the table, the session will be suspended due to insufficient space

Begin

For i in 1.. 2000 loop

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

End loop

Commit

End

/

5. View sessions suspended due to space allocation problems

Col name for a10

Col sql_text for a30

Col error_parameter1 for a10

Col error_parameter2 for a10

Col error_parameter3 for a10

Col error_parameter4 for a10

Col error_parameter5 for a10

Col error_msg for a30

Select user_id

Session_id

Instance_id

Status

Timeout

Start_time

Suspend_time

Resume_time

Name

Sql_text

Error_number

Error_parameter1

Error_parameter2

Error_parameter3

Error_parameter4

Error_parameter5

Error_msg

From dba_resumable

USER_ID SESSION_ID INSTANCE_ID STATUS TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME NAME SQL_TEXT ERROR_NUMBER ERROR_PARA ERROR_PARA ERROR_PARA ERROR_PARA ERROR_PARA ERROR_MSG

-

84 74 1 SUSPENDED 7200 01 t1_insert INSERT INTO SCOTT.T1 VALUES 25 t1_insert INSERT INTO SCOTT.T1 VALUES 18 09:42:18 01 t1_insert INSERT INTO SCOTT.T1 VALUES ('1653 SCOTT T1 128 TEST ORA-01653: table SCOTT.T1 cannot pass 128)

A row') (in tablespace TEST) extension

6. Fix the problem and the session will continue

Alter database datafile'd:\ oradata\ mes\ test01.dbf' resize 4m

7. Check the session suspended due to space allocation problem again, which shows that the session has resumed.

Select user_id

Session_id

Instance_id

Status

Timeout

Start_time

Suspend_time

Resume_time

Name

Sql_text

Error_number

Error_parameter1

Error_parameter2

Error_parameter3

Error_parameter4

Error_parameter5

Error_msg

From dba_resumable

USER_ID SESSION_ID INSTANCE_ID STATUS TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME NAME SQL_TEXT ERROR_NUMBER ERROR_PARA ERROR_PARA ERROR_PARA ERROR_PARA ERROR_PARA ERROR_MSG

-

84 74 1 NORMAL 7200 01/25/18 10:00:39 t1_insert 0

8. Delete tables and clean up tablespaces

Drop table scott.t1

Drop tablespace test including contents and datafiles

After reading the above, do you have any further understanding of Oracle recoverable space allocation technology? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.