- Managing Resumable Space Allocation
Managing Resumable Space Allocation
A resumable statement:
1- Enables you to suspend large operations instead of receiving an error
2- Gives you a chance to fix the problem while the operation is suspended,
rather than starting over
3-Is suspended for the following conditions:
>>Out of space
>>Maximum extents reached
>>Space quota exceeded
4-Can be suspended and resumed multiple times
show user;
show con_name;
/*
USER is "PDB10_ADMIN"
CON_NAME
------------------------------
PDB10
*/
select tablespace_name , username, bytes /1024 /1024 MB, max_bytes from dba_ts_quotas;
/*
TABLESPACE_NAME USERNAME MB MAX_BYTES
--------------- -------------------- ---------- ----------
SYSTEM OUTLN 0 -1
SYSTEM MDSYS 0 -1
SYSTEM LBACSYS .3125 -1
SYSAUX AUDSYS 1.3125 -1
SYSAUX DBSFWUSER 0 -1
SYSAUX GSMADMIN_INTERNAL .875 -1
SYSAUX APPQOSSYS 0 -1
SYSAUX GGSYS 0 -1
SYSAUX MDSYS 122.875 -1
SYSAUX OLAPSYS 0 -1
10 rows selected.
*/
CREATE TABLESPACE tbsalert
DATAFILE '/u01/app/oracle/oradata/ORCL/PDB10/tbsalert.dbf'
SIZE 10M LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
/*
TABLESPACE TBSALERT created.
*/
create table test100 ( emp_id number, name varchar2(100) )
tablespace TBSALERT;
/*
Table TEST100 created.
*/
begin
for i in 1..400000
loop
insert into test100 values ( i, 'any dummy name' );
commit;
end loop;
end;
/*
Error report -
ORA-01653: unable to extend table PDB10_ADMIN.TEST100 by 128 in tablespace TBSALERT
ORA-06512: at line 4
01653. 00000 - "unable to extend table %s.%s by %s in tablespace %s"
*Cause: Failed to allocate an extent of the required number of blocks for
a table segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.
*/
select count(1) from test100
/*
COUNT(1)
----------
326941
*/
/*
A statement executes in resumable mode only if its session has been enabled by
one of the following actions:
1- The RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.
2- An 'ALTER SESSION ENABLE RESUMABLE' statement is issued
*/
ALTER SESSION ENABLE RESUMABLE
/*
Session altered.
*/
truncate table test100;
/*
Table TEST100 truncated.
*/
begin
for i in 1..400000
loop
insert into test100 values ( i, 'any dummy name' );
commit;
end loop;
end;
/*
*/
--the script will be suspend 7,200 seconds=2 hours. ( this the default )
--you can do ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600
--now oracle will not give us any message that this script has been suspend
--but we can see it as DBA. open new session and do this
# 위의 프로시져가 'SUSPENDED' 상태인데, 새로운 세션을 연결해서 아래 쿼리로 확인 가능하다
select user_id, session_id, status, timeout, start_time, suspend_time, resume_time, error_msg from dba_resumable;
/*
USER_ID SESSION_ID STATUS TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME
---------- ---------- --------- ---------- ------------------ ------------------ -----------
105 395 SUSPENDED 7200 12/15/23 21:55:52 12/15/23 21:55:52
ERROR_MSG
-----------------------------------------------------------------------------------
ORA-01653: unable to extend table PDB10_ADMIN.TEST100 by 128 in tablespace TBSALERT
*/
SELECT file_name, autoextensible FROM dba_data_files WHERE
tablespace_name='TBSALERT';
/*
FILE_NAME AUT
-------------------------------------------------------------- ---
/u01/app/oracle/oradata/ORCL/PDB10/tbsalert.dbf NO
*/
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/ORCL/PDB10/tbsalert.dbf'
AUTOEXTEND ON MAXSIZE unlimited;
/*
Database altered.
*/
# 데이터파일을 자동연장으로 옵션을 바꾸면 다른 세션에서 SUSPENDED 상태로 대기중인 프로시져가 완료된다.
# 아래 쿼리로 해당 프로시져가 정상적으로 완료된것을 알 수 있다.
select user_id, session_id, status, timeout, start_time, suspend_time, resume_time, error_msg from dba_resumable;
/*
USER_ID SESSION_ID STATUS TIMEOUT START_TIME SUSPEND_TIME RESUME_TIME
---------- ---------- --------- ---------- --------------- --------------- ------------------
105 395 NORMAL 7200 12/15/23 21:56:46
ERROR_MSG
----------------
*/
# 데이터파일이 자동확장 모드인 것을 확인.
SELECT file_name, autoextensible FROM dba_data_files WHERE
tablespace_name='TBSALERT';
/*
FILE_NAME AUT
--------------------------------------------------------- ---
/u01/app/oracle/oradata/ORCL/PDB10/tbsalert.dbf YES
*/
# 루프문이 400000 번 반복된 것을 확인
select count(1) from test100;
/*
COUNT(1)
----------
400000
*/
