Managing Resumable Space in Oracle

Back to: Oracle DBA Tutorials

Managing Resumable Space in Oracle with Examples

In this article, I will explain Managing Resumable Space in Oracle with Examples. Please read our previous article discussing Segment Advisor in Oracle with Examples. A resumable statement performs the following operations:

• Enables us to suspend large operations instead of receiving an error
• It gives us a chance to fix the problem while the operation is suspended rather than starting over.
• The statement will be suspended for conditions like Out of space, Maximum extents reached, and space quota exceeded.
• The statement can be suspended and resumed multiple times.

The concept is very simple and easy, and the meaning of the above operations.

Step 1: Connect to the pluggable database

Command: show con_name

Step 2: Drop the tablespace tbsalert and recreate the tablespace.

We have created the tbsalert, including its contents and datafiles, because we have performed previous operations in this tablespace. To make the tablespace fresh, let’s drop it and recreate it again.

Statement: drop tablespace tbsalert including contents and datafiles;

So, the tablespace tbsalert is now dropped. Let’s create the tablespace again.

CREATE TABLESPACE tbsalert
DATAFILE '/u01/app/oracle/oradata/JAYADB/JAYAPDB/tbsalert.dbf'
SIZE 50M LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Step 3: Create a table and add records.

In order to check the segment advisor details, let us create a table in the tablespace tbsalert.

create table test100 ( emp_id number, name varchar2(100) ) tablespace TBSALERT;

So, now let’s try to insert the 400000 records into the table.

begin
 for i in 1..400000
 loop
 insert into test100 values ( i, 'any dummy name' );
  commit;
 end loop;
end;

If we run the above script, it will take some time to execute because it has 400000 records. In the PL/SQL block, the commit is done after every insert operation. Let’s now try to execute the statement.

We can see an error in the Output, which gives us the error description “ORA-30032: the suspended statement has timed out,” which explains the lack of tablespace in the database. It clearly mentions that it is unable to extend tablespace TBSALERT. The script has failed. Now, let’s check the data in the table test100.

Statement: select * from test100;

We can see there is data in the table. However, the complete data is not inserted into the table. In this case, we have to truncate the table and then insert the data again using the resumable option.

There is a solution for this error. A statement executes in resumable mode only if its sessions have been enabled by one of the following actions:

• The RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.
• An ALTER SESSION ENABLE RESUMABLE statement is issued.

Step 4: Session Resumable

Now, let’s try to run the command for resumable.

ALTER SESSION ENABLE RESUMABLE;

So, the session is now altered. This indicates that we are giving a statement to the Oracle database that if we run any statement and that statement goes out of space, we will be asking Oracle not to cancel the transaction and not give me an error. This will suspend the statement until we fix the error. In order to do this operation, let’s try to truncate the table and insert the data again.

Statement: truncate table test100;

The table test100 is now truncated and empty. Let’s try to insert the data again.

begin
 for i in 1..400000
 loop
 insert into test100 values ( i, 'any dummy name' );
  commit;
 end loop;
end;

So, the script will be executed at the database level.

So, the script will be executed at the database level. It took some time to execute. The script will be suspended for 7200 seconds, which is approximately 2 hours, by default. Oracle will not give us a message that this script has been suspended, and the output will not be shown in any of the errors.

Step 5: DBA Work

As a DBA, we have to open a new session and check the data dictionary dba_resumables

select * from dba_resumable;

We can see the status of the session is suspended. We can check the error message as well.

We can see the error indicating the inability to extend the tablespace TBSALERT. Let’s check the data files and check if the data file can be auto-extended or not.

SELECT file_name, file_id, autoextensible FROM dba_data_files WHERE tablespace_name=’TBSALERT’;

From the output, we can see the data file is present, with a file_id of 16, and it is auto-extensible, which is no. Now, let’s try to extend the data file.

alter database datafile 16 autoextend on maxsize unlimited;

We have taken the file_id, and we can use the file_id to extend the data file at the database level.

So, the data file is now auto-extended. Now, let’s look at the data file status of auto-extensible.

SELECT file_name, autoextensible FROM dba_data_files WHERE tablespace_name=’TBSALERT’;

The datafile is now auto-extended. Now, let’s have a look at the dba_resumables data dictionary. We have to check it after 5 to 10 minutes until we get the no rows selected output, or the status should be normal.

The insert statement is running fine. Now, let’s examine the number of rows in the table.

select count(1) from test100;

We can now see that all the rows were inserted successfully, even though there is not much space present inside the tablespace. The session is not canceled, and after increasing the space, the session is completed, and all the records are inserted. In this article, I explain Resumable Space in Oracle with Examples. I hope you enjoy this article on Resumable Space in Oracle.