The Sky Is The Limit With Bulk Loads
Bulk loading rows into a database can be unreliable if the present storage capacity is suddenly exceeded.
Ideally, the database would already be in noarchivelog mode before you started the bulk load, but you may have downstream processes that rely on the archivelogs to be generated.
Even if using nologging mode, there are still some redo records to be created, and those records will be present in the archivelogs. The rapid creation of redo records will generate archivelogs that can fill the disk volume and cause a database outage.
Filling a disk volume with archivelogs will result in an error such as the following:
col dest_name format a30
SELECT dest_name, status, error
FROM v$archive_dest
WHERE schedule='ACTIVE';DEST_NAME STATUS ERROR
---------------------- --------- -----------------------------------
LOG_ARCHIVE_DEST_1 ERROR ORA-19504: failed to create file ""
On top of that there is also the risk of space crunch in the Undo tablespace. Space crunch can occur if the bulk operation is not using the direct path.
Space crunch in the Undo tablespace will cause an error such as “ORA-30036: unable to extend segment by 8 in undo tablespace ‘SYS_UNDOTS’”. If this error occurs, the bulk load will automatically rollback.
But the above issues are only the start of the issues that can affect a bulk load.
The question is, what is the landscape of features that Oracle provide to promote greater reliability of a bulk load?
1) Redirect errors
Oracle provides a facility to redirect errors encountered in a bulk load to an error table.
The error table can be setup as follows, before the bulk load begins.
-- create an error table named ERR$_&tbl
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('&schm..&tbl');SQL> describe &schm..&tbl
Enter value for schm: TEST
Enter value for tbl: TESTINGName Null? Type
----------------------------------------- -------- -----------------X NUMBERSQL>
Errors while bulk loading can now be redirected to the error table using the LOG ERRORS INTO clause, and the execution will continue when an error is encountered, instead of rolling back.
alter session set current_schema=test;-- An example to bulk load 10M rows into a table
SET TRANSACTION NAME 'bulkload';
INSERT /*+ APPEND */ INTO &tbl
SELECT rownum id, a.created a, a.object_id b, 22/7 c, 22/7 d, 0 e,
a.owner||a.object_name z
FROM dba_objects a, dba_objects b
WHERE rownum<10000000
LOG ERRORS INTO
err$_&tbl('bulkload_'||TO_CHAR(sysdate,'Dy_YYYYMMDD'))
REJECT LIMIT UNLIMITED;COMMIT;
So this is just one feature that Oracle provides to promote greater reliability of a bulk load.
2) Preallocate storage for CTAS
Another feature that Oracle provides is to preallocate the storage of the object in advance. That way if the tablespace is too small originally, then the bulk load fails immediately, and you can address the tablespace issue early.
This is much more preferable than performing a bulk load, which then fails after a several hours due to a space issue, and then all that time is wasted and you need to start again.
This feature is used for a Create Table As Select (CTAS).
Default bulk load (without using a storage clause)
If you don’t use a STORAGE clause for the CTAS, then extents will be auto allocated dynamically while the bulk load is running.
-- An example to CTAS direct load 10M rows
-- AUTOALLOCATE extent management
-- Extents of 64K, 1M, 8M, and 64M can be auto allocated.
SET TRANSACTION NAME 'bulkload';
CREATE TABLE &tbl
NOLOGGING
TABLESPACE &tblspc AS
SELECT rownum id, a.created a, a.object_id b, 22/7 c, 22/7 d, 0 e,
a.owner||a.object_name z
FROM dba_objects a, dba_objects b
WHERE rownum<10000000;Initially there is no preallocation of extents, as the following query shows just after a CTAS bulk load begins.
SELECT segment_name, segment_type, header_file, header_block,
bytes/1024/1024/1024 GB
FROM dba_segments
WHERE SEGMENT_TYPE='TEMPORARY'
OR (owner=UPPER('&schm') and segment_name=UPPER('&tbl'));SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK GB
------------ ------------ ----------- ------------ --
19.145 TEMPORARY 19 145 0SQL>
However, query for extents as the CTAS bulk load continues, and it’s clear that the temporary extents are being created dynamically.
-- dynamic query
col segment_name format a30
SELECT segment_name, segment_type, bytes/1024 kbytes,
count(*),
sum(bytes)/1024/1024 total_mbytes
FROM dba_extents
WHERE tablespace_name=UPPER('&tblspc')
GROUP BY segment_name, segment_type, bytes
ORDER BY segment_name, segment_type, bytes;SEGMENT_NAME SEGMENT_TYPE KBYTES COUNT(*) TOTAL_MBYTES
------------ ------------------ ---------- ---------- ------------
19.130 TEMPORARY 64 16 1
19.130 TEMPORARY 1024 63 63
19.130 TEMPORARY 8192 27 216SQL>
Query again, to see the allocation of more extents as the CTAS bulk load progresses.
SEGMENT_NAME SEGMENT_TYPE KBYTES COUNT(*) TOTAL_MBYTES
------------ ------------------ ---------- ---------- ------------
19.130 TEMPORARY 64 16 1
19.130 TEMPORARY 1024 63 63
19.130 TEMPORARY 8192 72 576SQL>
When the CTAS bulk load is complete the segment becomes permanent, and the table is created.
SELECT segment_name, segment_type, header_file, header_block,
bytes/1024/1024/1024 GB
FROM dba_segments
WHERE owner=UPPER('&schm') and segment_name=UPPER('&tbl');SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK GB
------------ ------------------ ----------- ------------ ----------
BULKLOAD TABLE 19 145 .9765625SQL>
So in this example there was ample space, and the CTAS bulk load completed.
However, if the tablespace was too small, or couldn’t extend to meet the capacity demand, then an error similar to the following will result:
ERROR at line 4:
ORA-01659: unable to allocate MINEXTENTS beyond 18 in tablespace TEST_BULKLOADSQL>
So it would be better to pre-allocate the extents of the segment in advance, so if the capacity is not available up front, then the CTAS bulk load will fail early.
This is done using a STORAGE clause.
Bulk load using a storage clause
The STORAGE clause lets you specify storage characteristics for the table, object table OIDINDEX, partition, LOB data segment, or index-organized table overflow data segment.
The STORAGE clause is interpreted differently for locally managed tablespaces. For locally managed tablespaces, Oracle Database uses INITIAL, NEXT, PCTINCREASE, and MINEXTENTS to compute how many extents are allocated when the object is first created. After object creation, those parameters are ignored.
So we can perform the CTAS bulk load again, but this time using a STORAGE clause to preallocate 1000MB to the segment up front.
-- Example to CTAS bulk load 10M rows but now using a storage clause
SET TRANSACTION NAME 'bulkload';
CREATE TABLE &tbl
NOLOGGING
STORAGE (initial 1m minextents 1000 /*initial 1000m*/)
TABLESPACE &tblspc AS
SELECT rownum id, a.created a, a.object_id b, 22/7 c, 22/7 d, 0 e,
a.owner||a.object_name z
FROM dba_objects a, dba_objects b
WHERE rownum<10000000;The pre-allocation of the extents can be shown with same query as before.
SEGMENT_NAME SEGMENT_TYPE KBYTES COUNT(*) TOTAL_MBYTES
------------ ------------------ ---------- ---------- ------------
19.145 TEMPORARY 8192 5 40
19.145 TEMPORARY 65536 15 960SQL>SEGMENT_NAME SEGMENT_TYPE KBYTES COUNT(*) TOTAL_MBYTES
------------ ------------------ ---------- ---------- ------------
19.145 TEMPORARY 8192 5 40
19.145 TEMPORARY 65536 15 960SQL>
As can be seen all the extents are pre-allocated in advance, according to the storage clause. If the pre-allocation is enough for the complete CTAS bulk load, then no more extents need to be allocated.
When the CTAS bulk load finishes, the temporary segments become permanent, and the table is created.
SEGMENT_NAME SEGMENT_TYPE KBYTES COUNT(*) TOTAL_MBYTES
------------ ------------------ ---------- ---------- ------------
BULKLOAD TABLE 8192 5 40
BULKLOAD TABLE 65536 15 960SQL>
Space that is allocated in the STORAGE clause for the creation of the table can’t be deallocated later, but that may be just a minor inconvenience.
3) Use the resumable space configuration
In previous versions of Oracle, the Import utility offered a resumable feature.
imp system RESUMABLE=y RESUMABLE_TIMEOUT=999 ..So you could utilise this feature as a parameter, to give a DBA the opportunity to rectify any error that occurs during the import.
In later versions of Oracle, the Import utility was replaced by the datapump. Unfortunately, the resumable feature is not available in the datapump import utility.
Instead, Oracle have made the resumable feature available for a session.
ALTER SESSION ENABLE RESUMABLE;To delay a segment allocation error when it occurs, use the RESUMABLE_TIMEOUT system parameter to define the delay.
The delay can be up to 68 years!!!
The session is suspended on the following events:
- Out of space condition. eg any tablespace, including temporary and undo.
- Maximum extents reached condition. eg any table, index, etc.
- Space quota exceeded condition. eg user quota on tablespace.
When the error is resolved, then the session resumes.
Paul Guerin has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, Quest, and Oracle Developers (Medium). In 2019, he was awarded as a most valued contributor for the My Oracle Support Community. He is a DBA OCP, and continues to be a participant of the Oracle ACE program.
