This chapter describes design considerations for more advanced application development issues.
This chapter contains these topics:
The database provides a LOB buffering subsystem (LBS) for advanced OCI-based applications such as Data Cartridges, Web servers, and other client-based applications that need to buffer the contents of one or more LOBs in the client address space. The client-side memory requirement for the buffering subsystem during its maximum usage is 512 KBytes. It is also the maximum amount that you can specify for a single read or write operation on a LOB that has been enabled for buffered access.
The advantages of buffering, especially for client applications that perform a series of small reads and writes (often repeatedly) to specific regions of the LOB, are:
Buffering enables deferred writes to the server. You can buffer up several writes in the LOB buffer in the client address space and eventually flush the buffer to the server. This reduces the number of network round-trips from your client application to the server, and hence, makes for better overall performance for LOB updates.
Buffering reduces the overall number of LOB updates on the server, thereby reducing the number of LOB versions and amount of logging. This results in better overall LOB performance and disk space usage.
The following caveats apply to buffered LOB operations:
Explicitly flush LOB buffer contents. The LOB buffering subsystem is not a cache. The contents of a LOB buffer are not always the same as the LOB value in the server. Unless you explicitly flush the contents of a LOB buffer, you will not see the results of your buffered writes reflected in the actual LOB on the server.
Error recovery for buffered LOB operations is your responsibility. Owing to the deferred nature of the actual LOB update, error reporting for a particular buffered read or write operation is deferred until the next access to the server based LOB.
LOB Buffering is Single User, Single Threaded. Transactions involving buffered LOB operations cannot migrate across user sessions — the LBS is a single user, single threaded system.
Maintain logical savepoints to rollback to. Oracle does not guarantee transactional support for buffered LOB operations. To ensure transactional semantics for buffered LOB updates, you must maintain logical savepoints in your application to rollback all the changes made to the buffered LOB in the event of an error. You should always wrap your buffered LOB updates within a logical savepoint (see "OCI Example of LOB Buffering").
Ensure LOB is not updated by another bypassing transaction. In any given transaction, once you have begun updating a LOB using buffered writes, it is your responsibility to ensure that the same LOB is not updated through any other operation within the scope of the same transaction that bypasses the buffering subsystem.
You could potentially do this by using an SQL statement to update the server-based LOB. Oracle cannot distinguish, and hence prevent, such an operation. This will seriously affect the correctness and integrity of your application.
Updating buffer-enabled LOB locators. Buffered operations on a LOB are done through its locator, just as in the conventional case. A locator that is enabled for buffering will provide a consistent read version of the LOB, until you perform a write operation on the LOB through that locator. See also, "Read Consistent Locators".
Once the locator becomes an updated locator by virtue of its being used for a buffered write, it will always provide access to the most up-to-date version of the LOB as seen through the buffering subsystem. Buffering also imposes an additional significance to this updated locator — all further buffered writes to the LOB can be done only through this updated locator. Oracle will return an error if you attempt to write to the LOB through other locators enabled for buffering. See also, "Example of Updating LOBs Through Updated Locators".
Passing a buffer-enabled LOB locator an IN OUT or OUT parameter. You can pass an updated locator that was enabled for buffering as an IN
parameter to a PL/SQL procedure. However, passing an IN
OUT
or an OUT
parameter will produce an error, as will an attempt to return an updated locator.
You cannot assign an updated locator that was enabled for buffering to another locator. There are a number of different ways that assignment of locators may occur — through OCILobAssign
(), through assignment of PL/SQL variables, through OCIObjectCopy
() where the object contains the LOB attribute, and so on. Assigning a consistent read locator that was enabled for buffering to a locator that did not have buffering enabled, turns buffering on for the target locator. By the same token, assigning a locator that was not enabled for buffering to a locator that did have buffering enabled, turns buffering off for the target locator.
Similarly, if you SELECT
into a locator for which buffering was originally enabled, then the locator becomes overwritten with the new locator value, thereby turning buffering off.
When two or more locators point to the same LOB do not enable both for buffering. If two or more different locators point to the same LOB, then it is your responsibility to make sure that you do not enable both the locators for buffering. Otherwise Oracle does not guarantee the contents of the LOB.
Buffer-enable LOBs do not support appends that create zero-byte fillers or spaces. Appending to the LOB value using buffered write(s) is allowed, but only if the starting offset of these write(s) is exactly one byte (or character) past the end of the BLOB
(or CLOB
/NCLOB
). In other words, the buffering subsystem does not support appends that involve creation of zero-byte fillers or spaces in the server based LOB.
For CLOBs, Oracle requires the client side character set form for the locator bind variable be the same as that of the LOB in the server. This is usually the case in most OCI LOB programs. The exception is when the locator is selected from a remote database, which may have a different character set form from the database which is currently being accessed by the OCI
program. In such a case, an error is returned. If there is no character set form input by the user, then Oracle assumes it is SQLCS_IMPLICIT
.
Here are some details of the LOB buffering subsystem:
Each user session has the following structure:
Fixed page pool of 16 pages, shared by all LOBs accessed in buffering mode from that session.
Each page has a fixed size of up to 32K bytes (not characters) where page size = n x CHUNK
~= 32K.
A LOB buffer consists of one or more of these pages, up to a maximum of 16 in each session. The maximum amount that you ought to specify for any given buffered read or write operation is 512K bytes, remembering that under different circumstances the maximum amount you may read/write could be smaller.
Consider that a LOB is divided into fixed-size, logical regions. Each page is mapped to one of these fixed size regions, and is in essence, their in-memory copy. Depending on the input offset
and amount
specified for a read or write operation, the database allocates one or more of the free pages in the page pool to the LOB buffer. A free page is one that has not been read or written by a buffered read or write operation.
For example, assuming a page size of 32KBytes:
For an input offset of 1000 and a specified read/write amount of 30000, Oracle reads the first 32K byte region of the LOB into a page in the LOB buffer.
For an input offset of 33000 and a read/write amount of 30000, the second 32K region of the LOB is read into a page.
For an input offset of 1000, and a read/write amount of 35000, the LOB buffer will contain two pages — the first mapped to the region 1 — 32K, and the second to the region 32K+1 — 64K of the LOB.
This mapping between a page and the LOB region is temporary until Oracle maps another region to the page. When you attempt to access a region of the LOB that is not already available in full in the LOB buffer, Oracle allocates any available free page(s) from the page pool to the LOB buffer. If there are no free pages available in the page pool, then Oracle reallocates the pages as follows. It ages out the least recently used page among the unmodified pages in the LOB buffer and reallocates it for the current operation.
If no such page is available in the LOB buffer, then it ages out the least recently used page among the unmodified pages of other buffered LOBs in the same session. Again, if no such page is available, then it implies that all the pages in the page pool are modified, and either the currently accessed LOB, or one of the other LOBs, need to be flushed. Oracle notifies this condition to the user as an error. Oracle never flushes and reallocates a modified page implicitly. You can either flush them explicitly, or discard them by disabling buffering on the LOB.
To illustrate the preceding discussion, consider two LOBs being accessed in buffered mode — L1 and L2, each with buffers of size 8 pages. Assume that 6 of the 8 pages in the L1 buffer are dirty, with the remaining 2 containing unmodified data read in from the server. Assume similar conditions in the L2 buffer. Now, for the next buffered operation on L1, Oracle will reallocate the least recently used page from the two unmodified pages in the L1 buffer. Once all the 8 pages in the L1 buffer are used up for LOB writes, Oracle can service two more operations on L1 by allocating the two unmodified pages from the L2 buffer using the least recently used policy. But for any further buffered operations on L1 or L2, Oracle returns an error.
If all the buffers are dirty and you attempt another read from or write to a buffered LOB, then you will receive the following error:
Error 22280: no more buffers available for operation
There are two possible causes:
All buffers in the buffer pool have been used up by previous operations.
In this case, flush the LOBs through the locator that is being used to update the LOB.
You are trying to flush a LOB without any previous buffered update operations.
In this case, write to the LOB through a locator enabled for buffering before attempting to flush buffers.
The term flush refers to a set of processes. Writing data to the LOB in the buffer through the locator transforms the locator into an updated locator. Once you have updated the LOB data in the buffer through the updated locator, a flush call will
Write the dirty pages in the LOB buffer to the server-based LOB, thereby updating the LOB value,
Reset the updated locator to be a read consistent locator, and
Free the flushed buffers or turn the status of the buffer pages back from dirty to unmodified.
After the flush, the locator becomes a read consistent locator and can be assigned to another locator (L2 := L1).
For instance, suppose you have two locators, L1 and L2. Let us say that they are both read consistent locators and consistent with the state of the LOB data in the server. If you then update the LOB by writing to the buffer, L1 becomes an updated locator. L1 and L2 now refer to different versions of the LOB value. If you want to update the LOB in the server, then you must use L1 to retain the read consistent state captured in L2. The flush operation writes a new snapshot environment into the locator used for the flush. The important point to remember is that you must use the updated locator (L1), when you flush the LOB buffer. Trying to flush a read consistent locator will generate an error.
The technique you use to flush the LOB buffer determines whether data in the buffer is cleared and has performance implications as follows:
In the default mode, data is retained in the pages that were modified when the flush operation occurs. In this case, when you read or write to the same range of bytes, no round-trip to the server is necessary. Note that flushing the buffer, in this context, does not clear the data in the buffer. It also does not return the memory occupied by the flushed buffer to the client address space.
Note: Unmodified pages may now be aged out if necessary. |
In the second case, you set the flag parameter in OCILobFlushBuffer()
to OCI_LOB_BUFFER_FREE
to free the buffer pages, and so return the memory to the client address space. Flushing the buffer using this technique updates the LOB value on the server, returns a read consistent locator, and frees the buffer pages.
It is very important to note that you must flush a LOB that has been updated through the LOB buffering subsystem in the following situations:
Before committing the transaction
Before disabling buffering operations on a LOB
Before returning from an external callout execution into the calling function, procedure, or method in PL/SQL
Note that when the external callout is called from a PL/SQL block and the locator is passed as a parameter, all buffering operations, including the enable call, should be made within the callout itself. In other words, adhere to the following sequence:
Call the external callout
Enable the locator for buffering
Read or write using the locator
Flush the LOB
Disable the locator for buffering
Return to the calling function, procedure, or method in PL/SQL
Remember that the database never implicitly flushes the LOB buffer.
Note that there are several cases in which you can use buffer-enabled locators and others in which you cannot.
When it is OK to Use Buffer-Enabled Locators:
OCI — A locator that is enabled for buffering can only be used with the following OCI APIs:
OCILobRead2(), OCILobWrite2(), OCILobAssign(), OCILobIsEqual(), OCILobLocatorIsInit(), OCILobCharSetId(), OCILobCharSetForm()
When it is Not OK to Use Buffer-Enabled Locators: The following OCI APIs will return errors if used with a locator enabled for buffering:
OCI — OCILobCopy2(), OCILobAppend(), OCILobErase2(), OCILobGetLength2(), OCILobTrim2(), OCILobWriteAppend2()
These APIs will also return errors when used with a locator which has not been enabled for buffering, but the LOB that the locator represents is already being accessed in buffered mode through some other locator.
PL/SQL (DBMS_LOB) — An error is returned from DBMS_LOB
APIs if the input lob locator has buffering enabled.
As in the case of all other locators, buffer-enabled locators cannot span transactions.
Suppose you want to save the current state of the LOB before further writing to the LOB buffer. In performing updates while using LOB buffering, writing to an existing buffer does not make a round-trip to the server, and so does not refresh the snapshot environment in the locator. This would not be the case if you were updating the LOB directly without using LOB buffering. In that case, every update would involve a round-trip to the server, and so would refresh the snapshot in the locator.
Therefore to save the state of a LOB that has been written through the LOB buffer, follow these steps:
Flush the LOB, thereby updating the LOB and the snapshot environment in the locator (L1). At this point, the state of the locator (L1) and the LOB are the same.
Assign the locator (L1) used for flushing and updating to another locator (L2). At this point, the states of the two locators (L1 and L2), as well as the LOB are all identical.
L2 now becomes a read consistent locator with which you are able to access the changes made through L1 up until the time of the flush, but not after. This assignment avoids incurring a round-trip to the server to reselect the locator into L2.
The following OCI pseudocode example is based on the PM schema included with the Oracle Database Sample Schemas.
OCI_BLOB_buffering_program() { int amount; int offset; OCILobLocator lbs_loc1, lbs_loc2, lbs_loc3; void *buffer; int bufl; -- Standard OCI initialization operations - logging on to -- server, creating and initializing bind variables... init_OCI(); -- Establish a savepoint before start of LOB buffering subsystem -- operations exec_statement("savepoint lbs_savepoint"); -- Initialize bind variable to BLOB columns from buffered -- access: exec_statement("select ad_composite into lbs_loc1 from Print_media where ad_id = 12001"); exec_statement("select ad_composite into lbs_loc2 from Print_media where ad_id = 12001 for update"); exec_statement("select ad_composite into lbs_loc2 from Print_media where ad_id = 12001 for update"); -- Enable locators for buffered mode access to LOB: OCILobEnableBuffering(..., lbs_loc1); OCILobEnableBuffering(..., lbs_loc2); OCILobEnableBuffering(..., lbs_loc3); -- Read 4K bytes through lbs_loc1 starting from offset 1: amount = 4096; offset = 1; bufl = 4096; OCILobRead2(.., lbs_loc1, &amount, 0, offset, buffer, bufl, ...); if (exception) goto exception_handler; -- This will read the first 32K bytes of the LOB from -- the server into a page (call it page_A) in the LOB -- client-side buffer. -- lbs_loc1 is a read consistent locator. -- Write 4K of the LOB throgh lbs_loc2 starting from -- offset 1: amount = 4096; offset = 1; bufl = 4096; buffer = populate_buffer(4096); OCILobWrite2(.., lbs_loc2, &amount, 0, offset, buffer, bufl, ..); if (exception) goto exception_handler; -- This will read the first 32K bytes of the LOB from -- the server into a new page (call it page_B) in the -- LOB buffer, and modify the contents of this page -- with input buffer contents. -- lbs_loc2 is an updated locator. -- Read 20K bytes through lbs_loc1 starting from -- offset 10K amount = 20480; offset = 10240; OCILobRead2(.., lbs_loc1, &amount, 0, offset, buffer, bufl, ..); if (exception) goto exception_handler; -- Read directly from page_A into the user buffer. -- There is no round-trip to the server because the -- data is already in the client-side buffer. -- Write 20K bytes through lbs_loc2 starting from offset -- 10K amount = 20480; offset = 10240; bufl = 20480; buffer = populate_buffer(20480); OCILobWrite2(.., lbs_loc2, &amount, 0, offset, buffer, bufl, ..); if (exception) goto exception_handler; -- The contents of the user buffer will now be written -- into page_B without involving a round-trip to the -- server. This avoids making a new LOB version on the -- server and writing redo to the log. -- The following write through lbs_loc3 will also -- result in an error: amount = 20000; offset = 1000; bufl = 20000; buffer = populate_buffer(20000); OCILobWrite2(.., lbs_loc3, amount, 0, offset,buffer, bufl, ..); if (exception) goto exception_handler; -- No two locators can be used to update a buffered LOB -- through the buffering subsystem -- The following update through lbs_loc3 will also -- result in an error OCILobFileCopy(.., lbs_loc3, lbs_loc2, ..); if (exception) goto exception_handler; -- Locators enabled for buffering cannot be used with -- operations like Append, Copy, Trim and so on -- When done, flush the LOB buffer to the server: OCILobFlushBuffer(.., lbs_loc2, OCI_LOB_BUFFER_NOFREE); if (exception) goto exception_handler; -- This flushes all the modified pages in the LOB buffer, -- and resets lbs_loc2 from updated to read consistent -- locator. The modified pages remain in the buffer -- without freeing memory. These pages can be aged -- out if necessary. -- Disable locators for buffered mode access to LOB */ OCILobDisableBuffering(..., lbs_loc1); OCILobDisableBuffering(..., lbs_loc2); OCILobDisableBuffering(..., lbs_loc3); if (exception) goto exception_handler; -- This disables the three locators for buffered access, -- and frees up the LOB buffer resources. exception_handler: handle_exception_reporting(); exec_statement("rollback to savepoint lbs_savepoint"); }
The OPEN and CLOSE interfaces enable you to explicitly open a persistent LOB instance. When you open a LOB instance with the OPEN interface, the instance remains open until you explicitly close the LOB using the CLOSE interface. The ISOPEN interface enables you to determine whether a persistent LOB is already open.
Note that the open state of a LOB is associated with the LOB instance, not the LOB locator. The locator does not save any information indicating whether the LOB instance that it points to is open.
See Also: "Opening and Closing LOBs" for general information on situations that you would open a LOB instance. |
Explicitly opening a LOB instance can benefit performance of a persistent LOB in an indexed column.
If you do not explicitly open the LOB instance, then every modification to the LOB implicitly opens and closes the LOB instance. Any triggers on a domain index are fired each time the LOB is closed. Note that in this case, any domain indexes on the LOB are updated as soon as any modification to the LOB instance is made; the domain index is always valid and can be used at any time.
When you explicitly open a LOB instance, index triggers do not fire until you explicitly close the LOB. Using this technique can increase performance on index columns by eliminating unneeded indexing events until you explicitly close the LOB. Note that any index on the LOB column is not valid until you explicitly close the LOB.
If you explicitly open a LOB instance, then you must close the LOB before you commit the transaction.
Committing a transaction on the open LOB instance will cause an error. When this error occurs, the LOB instance is closed implicitly, any modifications to the LOB instance are saved, and the transaction is committed, but any indexes on the LOB column are not updated. In this situation, you must rebuild your indexes on the LOB column.
If you subsequently rollback the transaction, then the LOB instance is rolled back to its previous state, but the LOB instance is no longer explicitly open.
You must close any LOB instance that you explicitly open:
Between DML statements that start a transaction, including SELECT
... FOR
UPDATE
and COMMIT
Within an autonomous transaction block
Before the end of a session (when there is no transaction involved)
If you do not explicitly close the LOB instance, then it is implicitly closed at the end of the session and no index triggers are fired.
Keep track of the open or closed state of LOBs that you explicitly open. The following will cause an error:
Explicitly opening a LOB instance that is already explicitly open.
Explicitly closing a LOB instance that is already explicitly closed.
This occurs whether you access the LOB instance using the same locator or different locators.
Oracle Database provides the same read consistency mechanisms for LOBs as for all other database reads and updates of scalar quantities. Refer to Oracle Database Concepts for general information about read consistency. Read consistency has some special applications to LOB locators that you must understand. These applications are described in the following sections.
A selected locator, regardless of the existence of the FOR
UPDATE
clause, becomes a read consistent locator, and remains a read consistent locator until the LOB value is updated through that locator. A read consistent locator contains the snapshot environment as of the point in time of the SELECT
operation.
This has some complex implications. Suppose you have created a read consistent locator (L1) by way of a SELECT
operation. In reading the value of the persistent LOB through L1, note the following:
The LOB is read as of the point in time of the SELECT
statement even if the SELECT
statement includes a FOR
UPDATE
.
If the LOB value is updated through a different locator (L2) in the same transaction, then L1 does not see the L2 updates.
L1 will not see committed updates made to the LOB through another transaction.
If the read consistent locator L1 is copied to another locator L2 (for example, by a PL/SQL assignment of two locator variables — L2:= L1), then L2 becomes a read consistent locator along with L1 and any data read is read as of the point in time of the SELECT
for L1.
You can use the existence of multiple locators to access different transformations of the LOB value. However, in doing so, you must keep track of the different values accessed by different locators.
Read consistent locators provide the same LOB value regardless of when the SELECT
occurs.
The following example demonstrates the relationship between read-consistency and updating in a simple example. Using the Print_media
table and PL/SQL, three CLOB
instances are created as potential locators:
clob_selected
clob_update
clob_copied
Observe these progressions in the code, from times t1 through t6:
At the time of the first SELECT
INTO
(at t1), the value in ad_sourcetext
is associated with the locator clob_selected
.
In the second operation (at t2), the value in ad_sourcetext
is associated with the locator clob_updated
. Because there has been no change in the value of ad_sourcetext
between t1 and t2, both clob_selected
and clob_updated
are read consistent locators that effectively have the same value even though they reflect snapshots taken at different moments in time.
The third operation (at t3) copies the value in clob_selected
to clob_copied
. At this juncture, all three locators see the same value. The example demonstrates this with a series of DBMS_LOB
.READ
() calls.
At time t4, the program uses DBMS_LOB
.WRITE
() to alter the value in clob_updated
, and a DBMS_LOB
.READ
() reveals a new value.
However, a DBMS_LOB
.READ
() of the value through clob_selected
(at t5) reveals that it is a read consistent locator, continuing to refer to the same value as of the time of its SELECT
.
Likewise, a DBMS_LOB
.READ
() of the value through clob_copied
(at t6) reveals that it is a read consistent locator, continuing to refer to the same value as clob_selected
.
INSERT INTO PRINT_MEDIA VALUES (2056, 20020, EMPTY_BLOB(), 'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL); COMMIT; DECLARE num_var INTEGER; clob_selected CLOB; clob_updated CLOB; clob_copied CLOB; read_amount INTEGER; read_offset INTEGER; write_amount INTEGER; write_offset INTEGER; buffer VARCHAR2(20); BEGIN -- At time t1: SELECT ad_sourcetext INTO clob_selected FROM Print_media WHERE ad_id = 20020; -- At time t2: SELECT ad_sourcetext INTO clob_updated FROM Print_media WHERE ad_id = 20020 FOR UPDATE; -- At time t3: clob_copied := clob_selected; -- After the assignment, both the clob_copied and the -- clob_selected have the same snapshot as of the point in time -- of the SELECT into clob_selected -- Reading from the clob_selected and the clob_copied will -- return the same LOB value. clob_updated also sees the same -- LOB value as of its select: read_amount := 10; read_offset := 1; dbms_lob.read(clob_selected, read_amount, read_offset, buffer); dbms_output.put_line('clob_selected value: ' || buffer); -- Produces the output 'abcd' read_amount := 10; dbms_lob.read(clob_copied, read_amount, read_offset, buffer); dbms_output.put_line('clob_copied value: ' || buffer); -- Produces the output 'abcd' read_amount := 10; dbms_lob.read(clob_updated, read_amount, read_offset, buffer); dbms_output.put_line('clob_updated value: ' || buffer); -- Produces the output 'abcd' -- At time t4: write_amount := 3; write_offset := 5; buffer := 'efg'; dbms_lob.write(clob_updated, write_amount, write_offset, buffer); read_amount := 10; dbms_lob.read(clob_updated, read_amount, read_offset, buffer); dbms_output.put_line('clob_updated value: ' || buffer); -- Produces the output 'abcdefg' -- At time t5: read_amount := 10; dbms_lob.read(clob_selected, read_amount, read_offset, buffer); dbms_output.put_line('clob_selected value: ' || buffer); -- Produces the output 'abcd' -- At time t6: read_amount := 10; dbms_lob.read(clob_copied, read_amount, read_offset, buffer); dbms_output.put_line('clob_copied value: ' || buffer); -- Produces the output 'abcd' END; /
When you update the value of the persistent LOB through the LOB locator (L1), L1 (that is, the locator itself) is updated to contain the current snapshot environment as of the point in time after the operation was completed on the LOB value through locator L1. L1 is then termed an updated locator. This operation enables you to see your own changes to the LOB value on the next read through the same locator, L1.
Note: The snapshot environment in the locator is not updated if the locator is used to merely read the LOB value. It is only updated when you modify the LOB value through the locator using the PL/SQLDBMS_LOB package or the OCI LOB APIs. |
Any committed updates made by a different transaction are seen by L1 only if your transaction is a read-committed transaction and if you use L1 to update the LOB value after the other transaction committed.
Note: When you update a persistent LOB value, the modification is always made to the most current LOB value. |
Updating the value of the persistent LOB through any of the available methods, such as OCI LOB APIs or PL/SQL DBMS_LOB
package, updates the LOB value and then reselects the locator that refers to the new LOB value.
Note that updating the LOB value through SQL is merely an UPDATE
statement. It is up to you to do the reselect of the LOB locator or use the RETURNING
clause in the UPDATE
statement so that the locator can see the changes made by the UPDATE
statement. Unless you reselect the LOB locator or use the RETURNING
clause, you may think you are reading the latest value when this is not the case. For this reason you should avoid mixing SQL DML with OCI and DBMS_LOB piecewise operations.
Using table Print_media
in the following example, a CLOB
locator is created as clob_selected
. Note the following progressions in the example, from times t1 through t3:
At the time of the first SELECT
INTO
(at t1), the value in ad_sourcetext
is associated with the locator clob_selected
.
In the second operation (at t2), the value in ad_sourcetext
is modified through the SQL
UPDATE
statement, without affecting the clob_selected
locator. The locator still sees the value of the LOB as of the point in time of the original SELECT
. In other words, the locator does not see the update made using the SQL UPDATE
statement. This is illustrated by the subsequent DBMS_LOB
.READ
call.
The third operation (at t3) re-selects the LOB value into the locator clob_selected. The locator is thus updated with the latest snapshot environment which allows the locator to see the change made by the previous SQL UPDATE
statement. Therefore, in the next DBMS_LOB
.READ
, an error is returned because the LOB value is empty, that is, it does not contain any data.
INSERT INTO Print_media VALUES (3247, 20010, EMPTY_BLOB(), 'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL); COMMIT; DECLARE num_var INTEGER; clob_selected CLOB; read_amount INTEGER; read_offset INTEGER; buffer VARCHAR2(20); BEGIN -- At time t1: SELECT ad_sourcetext INTO clob_selected FROM Print_media WHERE ad_id = 20010; read_amount := 10; read_offset := 1; dbms_lob.read(clob_selected, read_amount, read_offset, buffer); dbms_output.put_line('clob_selected value: ' || buffer); -- Produces the output 'abcd' -- At time t2: UPDATE Print_media SET ad_sourcetext = empty_clob() WHERE ad_id = 20010; -- although the most current current LOB value is now empty, -- clob_selected still sees the LOB value as of the point -- in time of the SELECT read_amount := 10; dbms_lob.read(clob_selected, read_amount, read_offset, buffer); dbms_output.put_line('clob_selected value: ' || buffer); -- Produces the output 'abcd' -- At time t3: SELECT ad_sourcetext INTO clob_selected FROM Print_media WHERE ad_id = 20010; -- the SELECT allows clob_selected to see the most current -- LOB value read_amount := 10; dbms_lob.read(clob_selected, read_amount, read_offset, buffer); -- ERROR: ORA-01403: no data found END; /
In the following example, using table Print_media
, two CLOB
s are created as potential locators:
clob_updated
clob_copied
Note these progressions in the example at times t1 through t5:
At the time of the first SELECT
INTO
(at t1), the value in ad_sourcetext
is associated with the locator clob_updated
.
The second operation (at time t2) copies the value in clob_updated
to clob_copied
. At this time, both locators see the same value. The example demonstrates this with a series of DBMS_LOB
.READ
calls.
At time t3, the program uses DBMS_LOB
.WRITE
to alter the value in clob_updated
, and a DBMS_LOB.READ
reveals a new value.
However, a DBMS_LOB
.READ
of the value through clob_copied
(at time t4) reveals that it still sees the value of the LOB as of the point in time of the assignment from clob_updated
(at t2).
It is not until clob_updated
is assigned to clob_copied
(t5) that clob_copied
sees the modification made by clob_updated
.
INSERT INTO PRINT_MEDIA VALUES (2049, 20030, EMPTY_BLOB(), 'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL); COMMIT; DECLARE num_var INTEGER; clob_updated CLOB; clob_copied CLOB; read_amount INTEGER; read_offset INTEGER; write_amount INTEGER; write_offset INTEGER; buffer VARCHAR2(20); BEGIN -- At time t1: SELECT ad_sourcetext INTO clob_updated FROM PRINT_MEDIA WHERE ad_id = 20030 FOR UPDATE; -- At time t2: clob_copied := clob_updated; -- after the assign, clob_copied and clob_updated see the same -- LOB value read_amount := 10; read_offset := 1; dbms_lob.read(clob_updated, read_amount, read_offset, buffer); dbms_output.put_line('clob_updated value: ' || buffer); -- Produces the output 'abcd' read_amount := 10; dbms_lob.read(clob_copied, read_amount, read_offset, buffer); dbms_output.put_line('clob_copied value: ' || buffer); -- Produces the output 'abcd' -- At time t3: write_amount := 3; write_offset := 5; buffer := 'efg'; dbms_lob.write(clob_updated, write_amount, write_offset, buffer); read_amount := 10; dbms_lob.read(clob_updated, read_amount, read_offset, buffer); dbms_output.put_line('clob_updated value: ' || buffer); -- Produces the output 'abcdefg' -- At time t4: read_amount := 10; dbms_lob.read(clob_copied, read_amount, read_offset, buffer); dbms_output.put_line('clob_copied value: ' || buffer); -- Produces the output 'abcd' -- At time t5: clob_copied := clob_updated; read_amount := 10; dbms_lob.read(clob_copied, read_amount, read_offset, buffer); dbms_output.put_line('clob_copied value: ' || buffer); -- Produces the output 'abcdefg' END; /
When a LOB locator is used as the source to update another persistent LOB (as in a SQL INSERT
or UPDATE
statement, the DBMS_LOB
.COPY
routine, and so on), the snapshot environment in the source LOB locator determines the LOB value that is used as the source. If the source locator (for example L1) is a read consistent locator, then the LOB value as of the point in time of the SELECT
of L1 is used. If the source locator (for example L2) is an updated locator, then the LOB value associated with the L2 snapshot environment at the time of the operation is used.
In the following example, using the table Print_media
, three CLOBs are created as potential locators:
clob_selected
clob_updated
clob_copied
Note these progressions in the example at times t1 through t5:
At the time of the first SELECT
INTO
(at t1), the value in ad_sourcetext
is associated with the locator clob_updated
.
The second operation (at t2) copies the value in clob_updated
to clob_copied
. At this juncture, both locators see the same value.
Then (at t3), the program uses DBMS_LOB
.WRITE
to alter the value in clob_updated
, and a DBMS_LOB
.READ
reveals a new value.
However, a DBMS_LOB
.READ
of the value through clob_copied
(at t4) reveals that clob_copied
does not see the change made by clob_updated
.
Therefore (at t5), when clob_copied
is used as the source for the value of the INSERT
statement, the value associated with clob_copied
(for example, without the new changes made by clob_updated
) is inserted. This is demonstrated by the subsequent DBMS_LOB
.READ
of the value just inserted.
INSERT INTO PRINT_MEDIA VALUES (2056, 20020, EMPTY_BLOB(), 'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL); COMMIT; DECLARE num_var INTEGER; clob_selected CLOB; clob_updated CLOB; clob_copied CLOB; read_amount INTEGER; read_offset INTEGER; write_amount INTEGER; write_offset INTEGER; buffer VARCHAR2(20); BEGIN -- At time t1: SELECT ad_sourcetext INTO clob_updated FROM PRINT_MEDIA WHERE ad_id = 20020 FOR UPDATE; read_amount := 10; read_offset := 1; dbms_lob.read(clob_updated, read_amount, read_offset, buffer); dbms_output.put_line('clob_updated value: ' || buffer); -- Produces the output 'abcd' -- At time t2: clob_copied := clob_updated; -- At time t3: write_amount := 3; write_offset := 5; buffer := 'efg'; dbms_lob.write(clob_updated, write_amount, write_offset, buffer); read_amount := 10; dbms_lob.read(clob_updated, read_amount, read_offset, buffer); dbms_output.put_line('clob_updated value: ' || buffer); -- Produces the output 'abcdefg' -- note that clob_copied does not see the write made before -- clob_updated -- At time t4: read_amount := 10; dbms_lob.read(clob_copied, read_amount, read_offset, buffer); dbms_output.put_line('clob_copied value: ' || buffer); -- Produces the output 'abcd' -- At time t5: -- the insert uses clob_copied view of the LOB value which does -- not include clob_updated changes INSERT INTO PRINT_MEDIA VALUES (2056, 20022, EMPTY_BLOB(), clob_copied, EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL) RETURNING ad_sourcetext INTO clob_selected; read_amount := 10; dbms_lob.read(clob_selected, read_amount, read_offset, buffer); dbms_output.put_line('clob_selected value: ' || buffer); -- Produces the output 'abcd' END; /
This section discusses the use of LOB locators in transactions and transaction IDs. A basic description of LOB locators and their operations is given in "LOB Locators and BFILE Locators".
Note the following regarding LOB locators and transactions:
Locators contain transaction IDs when:
You Begin the Transaction, Then Select Locator. If you begin a transaction and subsequently select a locator, then the locator contains the transaction ID. Note that you can implicitly be in a transaction without explicitly beginning one. For example, SELECT
... FOR
UPDATE
implicitly begins a transaction. In such a case, the locator will contain a transaction ID.
Locators Do Not Contain Transaction IDs When...
You are Outside the Transaction, Then Select Locator. By contrast, if you select a locator outside of a transaction, then the locator does not contain a transaction ID.
When Selected Prior to DML Statement Execution. A transaction ID will not be assigned until the first DML statement executes. Therefore, locators that are selected prior to such a DML statement will not contain a transaction ID.
You can always read the LOB data using the locator irrespective of whether the locator contains a transaction ID.
Cannot Write Using Locator: If the locator contains a transaction ID, then you cannot write to the LOB outside of that particular transaction.
Can Write Using Locator: If the locator does not contain a transaction ID, then you can write to the LOB after beginning a transaction either explicitly or implicitly.
Cannot Read or Write Using Locator With Serializable Transactions: If the locator contains a transaction ID of an older transaction, and the current transaction is serializable, then you cannot read or write using that locator.
Can Read, Not Write Using Locator With Non-Serializable Transactions: If the transaction is non-serializable, then you can read, but not write outside of that transaction.
The following examples show the relationship between locators and non-serializable transactions
The following scenarios describe techniques for using locators in non-serializable transactions when the locator is selected outside of a transaction.
Scenario:
Select the locator with no current transaction. At this point, the locator does not contain a transaction id.
Begin the transaction.
Use the locator to read data from the LOB.
Commit or rollback the transaction.
Use the locator to read data from the LOB.
Begin a transaction. The locator does not contain a transaction id.
Use the locator to write data to the LOB. This operation is valid because the locator did not contain a transaction id prior to the write. After this call, the locator contains a transaction id.
Scenario:
Select the locator with no current transaction. At this point, the locator does not contain a transaction id.
Begin the transaction. The locator does not contain a transaction id.
Use the locator to read data from the LOB. The locator does not contain a transaction id.
Use the locator to write data to the LOB. This operation is valid because the locator did not contain a transaction id prior to the write. After this call, the locator contains a transaction id. You can continue to read from or write to the LOB.
Commit or rollback the transaction. The locator continues to contain the transaction id.
Use the locator to read data from the LOB. This is a valid operation.
Begin a transaction. The locator already contains the previous transaction id.
Use the locator to write data to the LOB. This write operation will fail because the locator does not contain the transaction id that matches the current transaction.
The following scenarios describe techniques for using locators in non-serializable transactions when the locator is selected within a transaction.
Scenario:
Select the locator within a transaction. At this point, the locator contains the transaction id.
Begin the transaction. The locator contains the previous transaction id.
Use the locator to read data from the LOB. This operation is valid even though the transaction id in the locator does not match the current transaction.
Use the locator to write data to the LOB. This operation fails because the transaction id in the locator does not match the current transaction.
Scenario:
Begin a transaction.
Select the locator. The locator contains the transaction id because it was selected within a transaction.
Use the locator to read from or write to the LOB. These operations are valid.
Commit or rollback the transaction. The locator continues to contain the transaction id.
Use the locator to read data from the LOB. This operation is valid even though there is a transaction id in the locator and the transaction was previously committed or rolled back.
See Also: "Read Consistent Locators" for more information on the using the locator to read LOB data. |
Use the locator to write data to the LOB. This operation fails because the transaction id in the locator is for a transaction that was previously committed or rolled back.
Modifying a persistent LOB value through the LOB locator using DBMS_LOB
, OCI, or SQL INSERT
or UPDATE
statements changes the locator from a read consistent locator to an updated locator. The INSERT
or UPDATE
statement automatically starts a transaction and locks the row. Once this has occurred, the locator cannot be used outside the current transaction to modify the LOB value. In other words, LOB locators that are used to write data cannot span transactions. However, the locator can be used to read the LOB value unless you are in a serializable transaction.
See Also: "LOB Locators and Transaction Boundaries", for more information about the relationship between LOBs and transaction boundaries. |
In the following example, a CLOB
locator is created: clob_updated
At the time of the first SELECT
INTO
(at t1), the value in ad_sourcetext
is associated with the locator clob_updated
.
The second operation (at t2), uses the DBMS_LOB
.WRITE
function to alter the value in clob_updated
, and a DBMS_LOB
.READ
reveals a new value.
The commit
statement (at t3) ends the current transaction.
Therefore (at t4), the subsequent DBMS_LOB
.WRITE
operation fails because the clob_updated
locator refers to a different (already committed) transaction. This is noted by the error returned. You must re-select the LOB locator before using it in further DBMS_LOB
(and OCI) modify operations.
INSERT INTO PRINT_MEDIA VALUES (2056, 20010, EMPTY_BLOB(), 'abcd', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, NULL, NULL); COMMIT; DECLARE num_var INTEGER; clob_updated CLOB; read_amount INTEGER; read_offset INTEGER; write_amount INTEGER; write_offset INTEGER; buffer VARCHAR2(20); BEGIN -- At time t1: SELECT ad_sourcetext INTO clob_updated FROM PRINT_MEDIA WHERE ad_id = 20010 FOR UPDATE; read_amount := 10; read_offset := 1; dbms_lob.read(clob_updated, read_amount, read_offset, buffer); dbms_output.put_line('clob_updated value: ' || buffer); -- This produces the output 'abcd' -- At time t2: write_amount := 3; write_offset := 5; buffer := 'efg'; dbms_lob.write(clob_updated, write_amount, write_offset, buffer); read_amount := 10; dbms_lob.read(clob_updated, read_amount, read_offset, buffer); dbms_output.put_line('clob_updated value: ' || buffer); -- This produces the output 'abcdefg' -- At time t3: COMMIT; -- At time t4: dbms_lob.write(clob_updated , write_amount, write_offset, buffer); -- ERROR: ORA-22990: LOB locators cannot span transactions END; /
Consider these object cache issues for internal and external LOB attributes:
Persistent LOB attributes: Creating an object in object cache, sets the LOB attribute to empty.
When you create an object in the object cache that contains a persistent LOB attribute, the LOB attribute is implicitly set to empty. You may not use this empty LOB locator to write data to the LOB. You must first flush the object, thereby inserting a row into the table and creating an empty LOB — that is, a LOB with 0 length. Once the object is refreshed in the object cache (use OCI_PIN_LATEST
), the real LOB locator is read into the attribute, and you can then call the OCI LOB API to write data to the LOB.
External LOB (BFILE
) attributes: Creating an object in object cache, sets the BFILE
attribute to NULL
.
When creating an object with an external LOB (BFILE)
attribute, the BFILE
is set to NULL
. It must be updated with a valid directory object name and file name before reading from the BFILE
.
When you copy one object to another in the object cache with a LOB locator attribute, only the LOB locator is copied. This means that the LOB attribute in these two different objects contain exactly the same locator which refers to one and the same LOB value. Only when the target object is flushed is a separate, physical copy of the LOB value made, which is distinct from the source LOB value.
See Also: "Example of Updating LOBs and Read-Consistency" for a description of what version of the LOB value will be seen by each object if a write is performed through one of the locators. |
Therefore, in cases where you want to modify the LOB that was the target of the copy, you must flush the target object, refresh the target object, and then write to the LOB through the locator attribute.
Terabyte-size LOBs—LOBs up to a maximum size of 8 to 128 terabytes depending on your database block size—are supported by the following APIs:
Java using JDBC (Java Database Connectivity)
PL/SQL using the DBMS_LOB Package
C using OCI (Oracle Call Interface)
You cannot create and use LOB instances of size greater than 4 gigabytes —"terabyte-size LOBs"— in the following programmatic environments:
COBOL using the Pro*COBOL Precompiler
C or C++ using the Pro*C/C++ Precompiler
Visual Basic using OO4O (Oracle Objects for OLE)
In supported environments, you can create and manipulate LOBs that are up to the maximum storage size limit for your database configuration.
Oracle Database lets you create tablespaces with block sizes different from the database block size, and the maximum size of a LOB depends on the size of the tablespace blocks. CHUNK
is a parameter of LOB storage whose value is controlled by the block size of the tablespace in which the LOB is stored.
When you create a LOB column, you can specify a value for CHUNK
, which is the number of bytes to be allocated for LOB manipulation. The value must be a multiple of the tablespace block size, or Oracle Database rounds up to the next multiple. (If the tablespace block size is the same as the database block size, then CHUNK
is also a multiple of the database block size.)
The maximum allowable storage limit for your configuration depends on the tablespace block size setting, and is calculated as (4 gigabytes - 1) times the value obtained from DBMS_LOB.GETCHUNKSIZE
or OCILobGetChunkSize()
. This value, in number of bytes for BLOB
s or number of characters for CLOB
s, is actually less than the size of the CHUNK
parameter due to internal storage overhead. With the current allowable range for the tablespace block size from 2K to 32K, the storage limit ranges from 8 terabytes to 128 terabytes.
For example, suppose your database block size is 32K bytes and you create a tablespace with a nonstandard block size of 8K. Further suppose that you create a table with a LOB column and specify a CHUNK size of 16K (which is a multiple of the 8K tablespace block size). Then the maximum size of a LOB in this column is (4 gigabytes - 1) * 16K.
See Also:
|
This storage limit applies to all LOB types in environments that support terabyte-size LOBs. However, note that CLOB
and NCLOB
types are sized in characters, while the BLOB
type is sized in bytes.
You can use the LOB APIs included in the Oracle JDBC classes to access terabyte-size LOBs.
You can access terabyte-size LOBs with all APIs in the DBMS_LOB PL/SQL package. Use DBMS_LOB.GETCHUNKSIZE
to obtain the value to be used in reading and writing LOBs. The number of bytes stored in a chunk is actually less than the size of the CHUNK
parameter due to internal storage overhead. The DBMS_LOB.GET_STORAGE_LIMIT
function returns the storage limit for your database configuration. This is the maximum allowable size for LOBs. BLOB
s are sized in bytes, while CLOB
s and NCLOB
s are sized in characters.
See Also: Oracle Database PL/SQL Packages and Types Reference for details on the initialization parameter setting for your database installation. |
The Oracle Call Interface API provides a set of functions for operations on LOBs of all sizes. OCILobGetChunkSize()
returns the value, in bytes for BLOB
s, or in characters for CLOB
s, to be used in reading and writing LOBs. For varying-width character sets, the value is the number of Unicode characters that fit. The number of bytes stored in a chunk is actually less than the size of the CHUNK
parameter due to internal storage overhead. The function OCILobGetStorageLimit()
returns the maximum allowable size, in bytes, of internal LOBs in the current database installation. If streaming mode is used, where the whole LOB is read, there is no need to get the chunk size.
See Also: Oracle Call Interface Programmer's Guide, the chapter "LOB and BFILE Operations", section "Using LOBs of Size Greater than 4GB" for details on OCI functions that support LOBs. |
To create gigabyte LOBs in supported environments, use the following guidelines to make use of all available space in the tablespace for LOB storage:
Single Data File Size Restrictions: There are restrictions on the size of a single data file for each operating system. For example, Solaris 2.5 only allows operating system files of up to 2 gigabytes. Hence, add more data files to the tablespace when the LOB grows larger than the maximum allowed file size of the operating system on which your Oracle Database runs.
Set PCT INCREASE Parameter to Zero: PCTINCREASE
parameter in the LOB storage clause specifies the percent growth of the new extent size. When a LOB is being filled up piece by piece in a tablespace, numerous new extents get created in the process. If the extent sizes keep increasing by the default value of 50 percent every time, then extents will become unmanageable and eventually will waste unnecessary space in the tablespace. Therefore, the PCTINCREASE
parameter should be set to zero or a small value.
Set MAXEXTENTS to a Suitable Value or UNLIMITED: The MAXEXTENTS
parameter limits the number of extents allowed for the LOB column. A large number of extents are created incrementally as the LOB size grows. Therefore, the parameter should be set to a value that is large enough to hold all the LOBs for the column. Alternatively, you could set it to UNLIMITED
.
Use a Large Extent Size: For every new extent created, Oracle generates undo information for the header and other metadata for the extent. If the number of extents is large, then the rollback segment can be saturated. To get around this, choose a large extent size, say 100 megabytes, to reduce the frequency of extent creation, or commit the transaction more often to reuse the space in the rollback segment.
The following example illustrates how to create a tablespace and table to store gigabyte LOBs.
CREATE TABLESPACE lobtbs1 DATAFILE '/your/own/data/directory/lobtbs_1.dat' SIZE 2000M REUSE ONLINE NOLOGGING DEFAULT STORAGE (MAXEXTENTS UNLIMITED); ALTER TABLESPACE lobtbs1 ADD DATAFILE '/your/own/data/directory/lobtbs_2.dat' SIZE 2000M REUSE; CREATE TABLE print_media_backup (product_id NUMBER(6), ad_id NUMBER(6), ad_composite BLOB, ad_sourcetext CLOB, ad_finaltext CLOB, ad_fltextn NCLOB, ad_textdocs_ntab textdoc_tab, ad_photo BLOB, ad_graphic BLOB, ad_header adheader_typ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab5 LOB(ad_sourcetext) STORE AS (TABLESPACE lobtbs1 CHUNK 32768 PCTVERSION 0 NOCACHE NOLOGGING STORAGE(INITIAL 100M NEXT 100M MAXEXTENTS UNLIMITED PCTINCREASE 0));
Note the following with respect to this example:
The storage clause in this example is specified in the CREATE TABLESPACE
statement.
You can specify the storage clause in the CREATE TABLE
statement as an alternative.
The storage clause is not allowed in the CREATE TEMPORARY TABLESPACE
statement.
Setting the PCTINCREASE
parameter to 0 is recommended for gigabyte LOBs. For small, or medium size lobs, the default PCTINCREASE
value of 50 is recommended as it reduces the number of extent allocations.