oracle lob что это

Русские Блоги

Oracle-DBMS_LOB функция и использование

Oracle-DBMS_LOB функция и использование

GETLENGTH

Функция, возвращающая длину указанных LOB-данных.

OPEN

READ

Процесс чтения данных указанной длины из данных LOB в буфер (в переменной)
пример:

WRITE

Процесс записи указанного количества данных в LOB.
пример:

APPEND

Процесс добавления указанных LOB-данных к указанным LOB-данным.
пример:

WRITEAPPEND

Записать данные буфера в конец LOB

TRIM

Процесс усечения части данных указанной длины с первой позиции в данных LOB.
пример:

CLOSE

Закройте открытый LOB

SUBSTR

Функция для извлечения подстрок из данных LOB.
пример:

INSTR

Функция для поиска позиции подстроки из данных LOB.
пример:

COMPARE

ERASE
Процесс удаления части данных в указанном месте в данных LOB.
пример:

COPY

Скопируйте исходный LOB в целевой LOB из указанной позиции

пример:

CREATETEMPORARY
Создание временного LOB во временном табличном пространстве пользователя

ISTEMPORARY
Определите, является ли локатор временным LOB

FILEEXISTS
Определите, существует ли файл ОС, соответствующий FILE_LOC. 1: существует; 0: не существует

FILEGETNAME
Получить псевдоним каталога и имя файла, соответствующие локатору BFILE

FILEISOPEN
Определите, открыт ли файл ОС, соответствующий BFILE.

FREETEMPORARY
Освободите временный LOB во временном табличном пространстве по умолчанию

FILEOPEN
Откройте файл

LOADBLOBFROMFILE
Загрузите данные BFILE в BLOB и получите последнюю позицию смещения после загрузки
пример:

FILECLOSE
Закройте файл ОС, на который указывает открытый локатор BFILE.

FILECLOSEALL
Закройте все файлы BFILE, открытые в текущем сеансе.

Источник

Oracle lob что это

Working with LOBs for application development requires that you understand LOB semantics and various techniques used with LOBs.

Most of the discussions regarding persistent LOBs assume that you are dealing with existing LOBs in tables. The task of creating tables with LOB columns is typically performed by your database administrator.

Using Oracle LOB Storage for creating LOBs using the SecureFiles paradigm

LOB Storage with Applications for storage parameters used in creating LOBs

2.1 LOB Column States

The techniques you use when accessing a cell in a LOB column differ depending on the state of the given cell.

A cell in a LOB Column can be in one of the following states:

The table cell is created, but the cell holds no locator or value.

A LOB instance with a locator exists in the cell, but it has no value. The length of the LOB is zero.

A LOB instance with a locator and a value exists in the cell.

2.2 Locking a Row Containing a LOB

You can lock a row containing a LOB to prevent other database users from writing to the LOB during a transaction.

To lock the row, specify the FOR UPDATE clause when you select the row. While the row is locked, other users cannot lock or update the LOB until you end your transaction.

2.3 LOB Open and Close Operations

The LOB APIs include operations that enable you to explicitly open and close a LOB instance.

Open the LOB in read-only mode

This ensures that the LOB (both the LOB locator and LOB value) cannot be changed in your session until you explicitly close the LOB. For example, you can open the LOB to ensure that the LOB is not changed by some other part of your program while you are using the LOB in a critical operation. After you perform the operation, you can then close the LOB.

Opening a LOB in read/write mode defers any index maintenance on the LOB column until you close the LOB. Opening a LOB in read/write mode is only useful if there is an extensible index on the LOB column, and you do not want the database to perform index maintenance every time you write to the LOB. This technique can increase the performance of your application if you are doing several write operations on the LOB while it is open.

If you open a LOB, then you must close the LOB at some point later in your session. This is the only requirement for an open LOB. While a LOB instance is open, you can perform as many operations as you want on the LOB—provided the operations are allowed in the given mode.

2.4 LOB Locator and LOB Value

You can use two different techniques to access and modify LOB values.

Источник

81 DBMS_LOB

This chapter contains the following topics:

Using DBMS_LOB

Overview

Security Model

Any DBMS_LOB subprogram called from an anonymous PL/SQL block is executed using the privileges of the current user. Any DBMS_LOB subprogram called from a stored procedure is executed using the privileges of the owner of the stored procedure.

When creating the procedure, users can set the AUTHID to indicate whether they want definer’s rights or invoker’s rights. For example:

For more information on AUTHID and privileges, see Oracle Database PL/SQL Language Reference

For information about the security model pertaining to temporary LOB s, see Operational Notes.

Constants

The DBMS_LOB package uses the constants shown in following tables:

Create the TEMP LOB with call duration

Open the specified BFILE read-only

Open the specified LOB read-only

Open the specified LOB read-write

Maximum size of a LOB in bytes

Create the TEMP LOB with session duration

Set/Get the SECUREFILE compress option value

Set/Get the SECUREFILE Deduplicate option value

Get the SECUREFILE encrypt option value

For SETOPTIONS Procedures, set compress off; for GETOPTIONS Functions, compress is off

For SETOPTIONS Procedures, set compress on; for GETOPTIONS Functions, compress is on

For SETOPTIONS Procedures, set deduplicate is off; for GETOPTIONS Functions, deduplicate is off

For SETOPTIONS Procedures, set deduplicate is on; for GETOPTIONS Functions, deduplicate is on

LOB has never been archived

LOB was archived, but as been read back in to the RDBMS

LOB is currently archived

Put the LOB data to the archive, but keep the data in the RDBMS as a cached version

Put the LOB data to the archive, and remove the data from the RDBMS.

Maximum number of bytes allowed in the content type string

Datatypes

The DBMS_LOB package uses the datatypes shown in Table 81-7.

Table 81-7 Datatypes Used by DBMS_LOB

Source or destination RAW buffer (used with BLOB ).

Source or destination character LOB (including NCLOB ).

Source or destination character buffer (used with CLOB and NCLOB ).

Large, binary object stored outside the database.

The DBMS_LOB package defines no special types.

An NCLOB is a CLOB for holding fixed-width and varying-width, multibyte national character sets.

The clause ANY_CS in the specification of DBMS_LOB subprograms for CLOB s enables the CLOB type to accept a CLOB or NCLOB locator variable as input.

Rules and Limits

General Rules and Limits

The following rules apply in the specification of subprograms in this package:

A subprogram raises an INVALID_ARGVAL exception if the following restrictions are not followed in specifying values for parameters (unless otherwise specified):

Only positive, absolute offsets from the beginning of LOB data are permitted: Negative offsets from the tail of the LOB are not permitted.

For example, if the CLOB consists of 2-byte characters, such as:

Then, the maximum amount value should not exceed:

PL/SQL language specifications stipulate an upper limit of 32767 bytes (not characters) for RAW and VARCHAR2 parameters used in DBMS_LOB subprograms. For example, if you declare a variable to be:

The %CHARSET clause indicates that the form of the parameter with %CHARSET must match the form of the ANY_CS parameter to which it refers.

If the value of amount plus the offset exceeds the maximum LOB size allowed by the database, then access exceptions are raised.

For consistent LOB updates, you must lock the row containing the destination LOB before making a call to any of the procedures (mutators) that modify LOB data.

Unless otherwise stated, the default value for an offset parameter is 1, which indicates the first byte in the BLOB or BFILE data, and the first character in the CLOB or NCLOB value. No default values are specified for the amount parameter — you must input the values explicitly.

Rules and Limits Specific to External Files (BFILEs)

DBMS_LOB does not support any concurrency control mechanism for BFILE operations.

In the event of several open files in the session whose closure has not been handled properly, you can use the FILECLOSEALL subprogram to close all files opened in the session and resume file operations from the beginning.

All files opened during a user session are implicitly closed at the end of the session. However, Oracle strongly recommends that you close the files after both normal and abnormal termination of operations on the BFILE.

In the event of abnormal program termination from a PL/SQL program, it is imperative that you provide an exception handler that ensures closure of all files opened in that PL/SQL program. This is necessary because after an exception occurs, only the exception handler has access to the BFILE variable in its most current state.

After the exception transfers program control outside the PL/SQL program block, all references to the open BFILEs are lost. The result is a larger open file count which may or may not exceed the SESSION_MAX_OPEN_FILES value.

For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND exception:

After the exception has occurred, the BFILE locator variable file goes out of scope, and no further operations on the file can be done using that variable. Therefore, the solution is to use an exception handler:

In general, you should ensure that files opened in a PL/SQL block using DBMS_LOB are closed before normal or abnormal termination of the block.

Maximum LOB Size

Maximum Buffer Size

Operational Notes

All DBMS_LOB subprograms work based on LOB locators. For the successful completion of DBMS_LOB subprograms, you must provide an input locator that represents a LOB that already exists in the database tablespaces or external file system. See also Chapter 1 of Oracle Database SecureFiles and Large Objects Developer’s Guide.

To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain LOB columns.

Internal LOBs

To populate your table with internal LOBs after LOB columns are defined in a table, you use the SQL data manipulation language (DML) to initialize or populate the locators in the LOB columns.

External LOBs

For an external LOB (BFILE) to be represented by a LOB locator, you must:

Ensure that a DIRECTORY object representing a valid, existing physical directory has been defined, and that physical files (the LOBs you plan to add) exist with read permission for the database. If your operating system uses case-sensitive path names, then be sure you specify the directory in the correct format.

Once you have completed these tasks, you can insert or update a row containing a LOB column using the specified LOB locator.

After the LOBs are defined and created, you can then SELECT from a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value.

For details on the different ways to do this, you must refer to the section of the Oracle Database SecureFiles and Large Objects Developer’s Guide that describes «Accessing External LOBs (BFILEs).»

Temporary LOBs

The database supports the definition, creation, deletion, access, and update of temporary LOBs. Your temporary tablespace stores the temporary LOB data. Temporary LOBs are not permanently stored in the database. Their purpose is mainly to perform transformations on LOB data.

A temporary LOB is empty when it is created. By default, all temporary LOBs are deleted at the end of the session in which they were created. If a process dies unexpectedly or if the database crashes, then temporary LOBs are deleted, and the space for temporary LOBs is freed.

There is no support for consistent read (CR), undo, backup, parallel processing, or transaction management for temporary LOBs. Because CR and rollbacks are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.

Because temporary LOBs are not associated with any table schema, there are no meanings to the terms in-row and out-of-row temporary LOBs. Creation of a temporary LOB instance by a user causes the engine to create and return a locator to the LOB data. The PL/SQL DBMS_LOB package, PRO*C/C++, OCI, and other programmatic interfaces operate on temporary LOBs through these locators just as they do for permanent LOBs.

There is no support for client side temporary LOBs. All temporary LOBs reside in the server.

Temporary LOBs do not support the EMPTY_BLOB or EMPTY_CLOB functions that are supported for permanent LOBs. The EMPTY_BLOB function specifies the fact that the LOB is initialized, but not populated with any data.

A temporary LOB instance can only be destroyed by using OCI or the DBMS_LOB package by using the appropriate FREETEMPORARY or OCIDurationEnd statement.

A temporary LOB instance can be accessed and modified using appropriate OCI and DBMS_LOB statements, just as for regular permanent internal LOBs. To make a temporary LOB permanent, you must explicitly use the OCI or DBMS_LOB COPY command, and copy the temporary LOB into a permanent one.

Security is provided through the LOB locator. Only the user who created the temporary LOB is able to see it. Locators are not expected to be able to pass from one user’s session to another. Even if someone did pass a locator from one session to another, they would not access the temporary LOBs from the original session. Temporary LOB lookup is localized to each user’s own session. Someone using a locator from somewhere else is only able to access LOBs within his own session that have the same LOB ID. Users should not try to do this, but if they do, they are not able to affect anyone else’s data.

The following notes are specific to temporary LOBs:

Operations based on CLOBs do not verify if the character set IDs of the parameters ( CLOB parameters, VARCHAR2 buffers and patterns, and so on) match. It is the user’s responsibility to ensure this.

Data storage resources are controlled by the DBA by creating different temporary tablespaces. DBAs can define separate temporary tablespaces for different users, if necessary.

Источник

Oracle Lob Класс

Определение

Некоторые сведения относятся к предварительной версии продукта, в которую до выпуска могут быть внесены существенные изменения. Майкрософт не предоставляет никаких гарантий, явных или подразумеваемых, относительно приведенных здесь сведений.

Представляет двоичный тип данных больших объектов ( LOB ), хранящихся на сервере Oracle. Этот класс не наследуется.

Комментарии

OracleLobМожет быть одним из этих OracleType типов данных.

После получения LOB значения Oracle из базы данных Oracle в OracleLob класс можно изменить LOB данные в открытой транзакции, и изменения будут непосредственно отражены в базе данных. Если значение Oracle извлекается LOB в Array тип Byte или String и обновляются эти массивы, изменения не отражаются в базе данных.

При использовании OracleLob класса для доступа к блоку LOB значения только этот блок передается клиенту из базы данных Oracle. При использовании GetChars метода для доступа к блоку LOB значения все содержимое значения передается клиенту из базы данных Oracle.

Чтобы получить OracleLob объект, вызовите GetOracleLob метод.

Можно создать, имеющий OracleLob значение null, в следующем формате:

Значение NULL LOB ведет себя так же, как нулевой байт LOB в, который Read завершается успешно и всегда возвращает нулевые байты.

В следующем примере показано, как открыть временную LOB :

Унаследованный WriteByte метод завершается ошибкой, если используется с символьными данными, и InvalidOperationException вызывается исключение. Вместо этого рекомендуется использовать метод Write.

Представляет пустой объект OracleLob.

Свойства

Получает значение, показывающее, могут ли быть выполнены операции прямого и обратного поиска.

Возвращает значение, которое показывает, может ли для данного потока истечь время ожидания.

Всегда возвращает логическое значение ИСТИНА, независимо от того, поддерживает ли LOB запись или нет.

Получает значение, определяющее минимальное количество байтов, которое необходимо извлечь с сервера или отправить на сервер при выполнении операции чтения или записи.

Получает OracleConnection, используемый данным экземпляром OracleLob.

Получает значение, определяющее, вызвало ли приложение метод BeginBatch().

Получает значение, определяющее, является ли OracleLob потоком Null.

Получает значение, возвращающее размер OracleLob.

Получает текущую позицию чтения в потоке OracleLob.

Возвращает или задает значение в миллисекундах, определяющее период времени, отведенного потоку на выполнение операции чтения.

Получает эквивалент общеязыковой среды выполнения потока для базового значения.

Возвращает или задает значение в миллисекундах, определяющее период времени, отведенного потоку на выполнение операции записи.

Методы

Предотвращает срабатывание серверных триггеров при выполнении нескольких операций чтения.

Предотвращает срабатывание серверных триггеров при выполнении нескольких операций чтения и записи в указанном режиме доступа.

Начинает операцию асинхронного чтения. (Рекомендуется использовать ReadAsync(Byte[], Int32, Int32).)

Начинает операцию асинхронной записи. (Рекомендуется использовать WriteAsync(Byte[], Int32, Int32).)

Создает новый объект OracleLob, который ссылается на тот же LOB Oracle, что и исходный объект OracleLob.

Закрывает текущий поток и освобождает все ресурсы, связанные с данным потоком.

Закрывает текущий поток и отключает все ресурсы (например, сокеты и файловые дескрипторы), связанные с текущим потоком. Вместо вызова данного метода, убедитесь в том, что поток надлежащим образом ликвидирован.

Копирует из данного объекта OracleLob в OracleLob, являющийся объектом назначения, с указанным объемом данных и смещением источника.

Копирует из данного объекта OracleLob в объект назначения — OracleLob.

Копирование из данного объекта OracleLob в объект назначения — OracleLob с указанным объемом данных.

Считывает байты из текущего потока и записывает их в другой поток.

Считывает байты из текущего потока и записывает их в другой поток, используя указанный размер буфера.

Асинхронно считывает байты из текущего потока и записывает их в другой поток.

Асинхронно считывает байты из текущего потока и записывает их в другой поток, используя указанный токен отмены.

Асинхронно считывает байты из текущего потока и записывает их в другой поток, используя указанный размер буфера.

Асинхронно считывает байты из текущего потока и записывает их в другой поток, используя указанный размер буфера и токен отмены.

Создает объект, который содержит всю необходимую информацию для создания прокси-сервера, используемого для взаимодействия с удаленным объектом.

Освобождает ресурсы, используемые данным объектом.

Освобождает все ресурсы, занятые модулем Stream.

Освобождает неуправляемые ресурсы, используемые объектом Stream, а при необходимости освобождает также управляемые ресурсы.

Асинхронно освобождает неуправляемые ресурсы, используемые классом Stream.

Позволяет серверным триггерам возобновить работу после выполнения нескольких операций записи.

Ожидает завершения отложенного асинхронного чтения. (Рекомендуется использовать ReadAsync(Byte[], Int32, Int32).)

Заканчивает операцию асинхронной записи. (Рекомендуется использовать WriteAsync(Byte[], Int32, Int32).)

Определяет, равен ли указанный объект текущему объекту.

Удаляет все данные из этого OracleLob.

Удаляет заданный объем данных из этого OracleLob.

В настоящее время не поддерживается.

Асинхронно очищает все буферы для этого потока и вызывает запись всех буферизованных данных в базовое устройство.

Асинхронно очищает все буферы данного потока, вызывает запись буферизованных данных в базовое устройство и отслеживает запросы отмены.

Служит хэш-функцией по умолчанию.

Извлекает объект обслуживания во время существования, который управляет политикой времени существования данного экземпляра.

Возвращает объект Type для текущего экземпляра.

Получает объект службы времени существования для управления политикой времени существования для этого экземпляра.

Создает неполную копию текущего объекта Object.

Создает неполную копию текущего объекта MarshalByRefObject.

Обеспечивает поддержку для Contract.

Считывает последовательность байтов из текущего потока OracleLob и перемещает позицию внутри потока на число считанных байтов.

При переопределении в производном классе считывает последовательность байтов из текущего потока и перемещает позицию в потоке на число считанных байтов.

Асинхронно считывает последовательность байтов из текущего потока и перемещает позицию внутри потока на число считанных байтов.

Асинхронно считывает последовательность байтов из текущего потока, перемещает позицию в потоке на число считанных байтов и отслеживает запросы отмены.

Асинхронно считывает последовательность байтов из текущего потока, перемещает позицию в потоке на число считанных байтов и отслеживает запросы отмены.

Задает позицию в текущем потоке OracleLob.

Задает длину потока OracleLob меньше текущей длины.

Возвращает строку, представляющую текущий объект.

Записывает последовательность байтов в текущий поток OracleLob и перемещает текущую позицию внутри потока на число записанных байтов.

При переопределении в производном классе записывает последовательность байтов в текущий поток и перемещает текущую позицию в нем вперед на число записанных байтов.

Асинхронно записывает последовательность байтов в текущий поток и перемещает текущую позицию внутри потока на число записанных байтов.

Асинхронно записывает последовательность байтов в текущий поток, перемещает текущую позицию внутри потока на число записанных байтов и отслеживает запросы отмены.

Асинхронно записывает последовательность байтов в текущий поток, перемещает текущую позицию внутри потока на число записанных байтов и отслеживает запросы отмены.

Записывает байт в текущее положение в потоке OracleLob, при этом позиция в потоке перемещается вперед на один байт.

Записывает байт в текущее положение в потоке и перемещает позицию в потоке вперед на один байт.

Явные реализации интерфейса

Освобождает все ресурсы, занятые модулем Stream.

Источник

11 LOB Storage

This chapter describes issues specific to tables that contain LOB columns, with both the SECUREFILE and BASICFILE parameters. If a feature applies to only one of the two kinds of LOB, it is so stated.

This chapter contains these topics:

Creating Tables That Contain LOBs

When creating tables that contain LOBs, use the guidelines described in the following sections:

Initializing Persistent LOBs to NULL or Empty

You can set a persistent LOB — ­that is, a LOB column in a table, or a LOB attribute in an object type that you defined— to be NULL or empty:

Setting a Persistent LOB to NULL : A LOB set to NULL has no locator. A NULL value is stored in the row in the table, not a locator. This is the same process as for all other data types.

Setting a Persistent LOB to Empty : By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column or attribute, then you get back a locator which you can use to populate the LOB with data using supported programmatic environments, such as OCI or PL/SQL(DBMS_LOB ). See Chapter 13, «Overview of Supplied LOB APIs» for more information on supported environments.

Details for these options are given in the following discussions.

Setting a Persistent LOB to NULL

You may want to set a persistent LOB value to NULL upon inserting the row in cases where you do not have the LOB data at the time of the INSERT or if you want to use a SELECT statement, such as the following, to determine whether the LOB holds a NULL value:

Note that you cannot call OCI or DBMS_LOB functions on a NULL LOB, so you must then use an SQL UPDATE statement to reset the LOB column to a non- NULL (or empty) value.

Setting a Persistent LOB to Empty

As an alternative, you can use the RETURNING clause to obtain the LOB locator in one operation rather than calling a subsequent SELECT statement:

Initializing LOBs

You can initialize the LOBs in print_media by using the following INSERT statement:

Initializing Persistent LOB Columns and Attributes to a Value

You can initialize the LOB column or LOB attributes to a value that contains more than 4G bytes of data, the limit before release 10.2.

Initializing BFILEs to NULL or a File Name

A BFILE can be initialized to NULL or to a filename. To do so, you can use the BFILENAME() function.

Restriction on First Extent of a LOB Segment

The first extent of any segment requires at least 2 blocks (if FREELIST GROUPS was 0). That is, the initial extent size of the segment should be at least 2 blocks. LOBs segments are different because they need at least 3 blocks in the first extent. If you try to create a LOB segment in a permanent dictionary managed tablespace with initial = 2 blocks, then it still works because it is possible for segments in permanent dictionary-managed tablespaces to override the default storage setting of the tablespaces.

But if uniform locally managed tablespaces or dictionary managed tablespaces of the temporary type, or locally managed temporary tablespaces have an extent size of 2 blocks, then LOB segments cannot be created in these tablespaces. This is because in these tablespace types, extent sizes are fixed and the default storage setting of the tablespaces is not ignored.

Choosing a LOB Column Data Type

When selecting a data type, consider the following three topics:

LOBs Compared to LONG and LONG RAW Types

Table 11-1 lists the similarities and differences between LOBs, LONGs, and LONG RAW types.

Table 11-1 LOBs Vs. LONG RAW

You can store multiple LOBs in a single row

You can store only one LONG or LONG RAW in each row.

LOB s can be attributes of a user-defined data type

This is not possible with either a LONG or LONG RAW

Only the LOB locator is stored in the table column; BLOB and CLOB data can be stored in separate tablespaces and BFILE data is stored as an external file.

For inline LOBs, the database stores LOBs that are less than approximately 4000 bytes of data in the table column.

In the case of a LONG or LONG RAW the entire value is stored in the table column.

When you access a LOB column, you can choose to fetch the locator or the data.

When you access a LONG or LONG RAW, the entire value is returned.

A LOB can be up to 128 terabytes or more in size depending on your block size.

A LONG or LONG RAW instance is limited to 2 gigabytes in size.

There is greater flexibility in manipulating data in a random, piece-wise manner with LOBs. LOBs can be accessed at random offsets.

You can replicate LOBs in both local and distributed environments.

Replication in both local and distributed environments is not possible with a LONG or LONG RAW (see Oracle Database Advanced Replication)

Storing Varying-Width Character Data in LOBs

Varying-width character data in CLOB and NCLOB data types is stored in an internal format that is compatible with UCS2 Unicode character set format. This ensures that there is no storage loss of character data in a varying-width format. Also note the following if you are using LOBs to store varying-width character data:

You can create tables containing CLOB and NCLOB columns even if you use a varying-width CHAR or NCHAR database character set.

You can create a table containing a data type that has a CLOB attribute regardless of whether you use a varying-width CHAR database character set.

Implicit Character Set Conversions with LOBs

For CLOB and NCLOB instances used in OCI (Oracle Call Interface), or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.

Oracle Database Globalization Support Guide for more detail on character set conversions.

The database character set cannot be changed from a single-byte to a multibyte character set if there are populated user-defined CLOB columns in the database tables. The national character set cannot be changed between AL16UTF16 and UTF8 if there are populated user-defined NCLOB columns in the database tables.

LOB Storage Parameters

This section summarizes LOB storage characteristics to consider when designing tables with LOB storage. For a discussion of SECUREFILE parameters:

Inline and Out-of-Line LOB Storage

LOB columns store locators that reference the location of the actual LOB value. Depending on the column properties you specify when you create the table, and depending the size of the LOB, actual LOB values are stored either in the table row (inline) or outside of the table row (out-of-line).

LOB values are stored out-of-line when any of the following situations apply:

If you explicitly specify DISABLE STORAGE IN ROW for the LOB storage clause when you create the table.

If the size of the LOB is greater than approximately 4000 bytes (4000 minus system control information), regardless of the LOB storage properties for the column.

If you update a LOB that is stored out-of-line and the resulting LOB is less than approximately 4000 bytes, it is still stored out-of-line.

LOB values are stored inline when any of the following conditions apply:

When the size of the LOB stored in the given row is small, approximately 4000 bytes or less, and you either explicitly specify ENABLE STORAGE IN ROW or the LOB storage clause when you create the table, or when you do not specify this parameter (which is the default).

When the LOB value is NULL (regardless of the LOB storage properties for the column).

Using the default LOB storage properties (inline storage) can allow for better database performance; it avoids the overhead of creating and managing out-of-line storage for smaller LOB values. If LOB values stored in your database are frequently small in size, then using inline storage is recommended.

LOB locators are always stored in the row.

If the LOB is created with DISABLE STORAGE IN ROW properties and the BasicFiles LOB holds any data, then a minimum of one CHUNK of out-of-line storage space is used; even when the size of the LOB is less than the CHUNK size.

LOB storage properties do not affect BFILE columns. BFILE data is always stored in operating system files outside the database.

Defining Tablespace and Storage Characteristics for Persistent LOBs

When defining LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each persistent LOB column.

To create a BasicFiles LOB, the BASICFILE keyword is optional but is recommended for clarity, as shown in the following example:

For SecureFiles, the SECUREFILE keyword is necessary, as shown in the following example (assuming TABLESPACE lobtbs1 is ASSM ):

There are no tablespace or storage characteristics that you can specify for external LOBs ( BFILE s) as they are not stored in the database.

Assigning a LOB Data Segment Name

LOB Storage Characteristics for LOB Column or Attribute

LOB storage characteristics that can be specified for a LOB column or a LOB attribute include the following:

PCTVERSION or RETENTION

Note that you can specify either PCTVERSION or RETENTION for BasicFiles LOBs, but not both. For SecureFiles, only the RETENTION parameter can be specified.

CACHE / NOCACHE/CACHE READS

ENABLE / DISABLE STORAGE IN ROW

For most users, defaults for these storage characteristics are sufficient. If you want to fine-tune LOB storage, then consider the following guidelines.

«STORAGE clause» and «RETENTION parameter» in Oracle Database SQL Language Reference

TABLESPACE and LOB Index

Best performance for LOBs can be achieved by specifying storage for LOBs in a tablespace different from the one used for the table that contains the LOB. If many different LOBs are accessed frequently, then it may also be useful to specify a separate tablespace for each LOB column or attribute in order to reduce device contention.

The LOB index is an internal structure that is strongly associated with LOB storage. This implies that a user may not drop the LOB index and rebuild it.

The LOB index cannot be altered.

The system determines which tablespace to use for LOB data and LOB index depending on your specification in the LOB storage clause:

If you do not specify a tablespace for the LOB data, then the tablespace of the table is used for the LOB data and index.

If you specify a tablespace for the LOB data, then both the LOB data and index use the tablespace that was specified.

Tablespace for LOB Index in Non-Partitioned Table

When creating a table, if you specify a tablespace for the LOB index for a non-partitioned table, then your specification of the tablespace is ignored and the LOB index is co-located with the LOB data. Partitioned LOBs do not include the LOB index syntax.

Specifying a separate tablespace for the LOB storage segments enables a decrease in contention on the tablespace of the table.

PCTVERSION

When a BasicFiles LOB is modified, a new version of the BasicFiles LOB page is produced in order to support consistent read of prior versions of the BasicFiles LOB value.

PCTVERSION is the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. As soon as old versions of BasicFiles LOB data pages start to occupy more than the PCTVERSION amount of used BasicFiles LOB space, Oracle Database tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used BasicFiles LOB data blocks that is available for versioning old BasicFiles LOB data.

PCTVERSION has a default of 10 (%), a minimum of 0, and a maximum of 100.

To decide what value PCTVERSION should be set to, consider the following:

How often BasicFiles LOBs are updated?

How often the updated BasicFiles LOBs are read?

Table 11-2, «Recommended PCTVERSION Settings» provides some guidelines for determining a suitable PCTVERSION value given an update percentage of ‘X’.

Table 11-2 Recommended PCTVERSION Settings

Updates X% of LOB data

Reads updated LOBs

Updates X% of LOB data

Reads LOBs but not the updated LOBs

Updates X% of LOB data

Reads both updated and non-updated LOBs

Setting PCTVERSION to twice the default value allows more free pages to be used for old versions of data pages. Because large queries may require consistent reads of BasicFiles LOB columns, it may be useful to retain old versions of BasicFiles LOB pages. In this case, BasicFiles LOB storage may grow because the database does not reuse free pages aggressively.

The more infrequent and smaller the BasicFiles LOB updates are, the less space must be reserved for old copies of BasicFiles LOB data. If existing BasicFiles LOBs are known to be read-only, then you could safely set PCTVERSION to 0% because there would never be any pages needed for old versions of data.

RETENTION Parameter for BasicFiles LOBs

The RETENTION parameter is designed for use with UNDO features of the database, such as Flashback Versions Query. When a LOB column has the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.

Note the following with respect to the RETENTION parameter:

UNDO SQL is not enabled for LOB columns as it is with other data types. You must set the RETENTION property on a LOB column to use Undo SQL on LOB data.

You cannot set the value of the RETENTION parameter explicitly. The amount of time for retention of LOB versions in determined by the UNDO_RETENTION parameter.

Usage of the RETENTION parameter is only supported in Automatic Undo Management mode. You must configure your table for use with Automatic Undo Management before you can set RETENTION on a LOB column. ASSM is required for LOB RETENTION to be in effect for BasicFiles LOBs. The RETENTION parameter of the SQL (in the STORE AS clause) is silently ignored if the BasicFiles LOB resides in an MSSM tablespace.

Oracle Database Advanced Application Developer’s Guide for more information on using flashback features of the database.

Oracle Database SQL Language Reference for details on LOB storage clause syntax.

RETENTION Parameter for SecureFiles LOBs

Specifying the RETENTION parameter for SecureFiles indicates that the database manages consistent read data for the SecureFiles storage dynamically, taking into account factors such as the UNDO mode of the database.

Specify AUTO if you want to retain UNDO sufficient for consistent read purposes only. This is the default.

Specify NONE if no UNDO is required for either consistent read or flashback purposes.

CACHE / NOCACHE / CACHE READS

When creating tables that contain LOBs, use the cache options according to the guidelines in Table 11-3, «When to Use CACHE, NOCACHE, and CACHE READS»:

Table 11-3 When to Use CACHE, NOCACHE, and CACHE READS

Once or occasionally

Once or occasionally

CACHE / NOCACHE / CACHE READS: LOB Values and Buffer Cache

CACHE: Oracle places LOB pages in the buffer cache for faster access.

NOCACHE: As a parameter in the STORE AS clause, NOCACHE specifies that LOB values are not brought into the buffer cache.

CACHE READS: LOB values are brought into the buffer cache only during read and not during write operations.

NOCACHE is the default for both SecureFiles and BasicFiles LOBs.

Using the CACHE option results in improved performance when reading and writing data from the LOB column. However, it can potentially age other non-LOB pages out of the buffer cache prematurely.

LOGGING / NOLOGGING Parameter for BasicFiles LOBs

[ NO ] LOGGING has a similar application with regard to using LOBs as it does for other table operations. In the usual case, if the [ NO ] LOGGING clause is omitted, then this means that neither NO LOGGING nor LOGGING is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.

For LOBs, there is a further alternative depending on how CACHE is stipulated.

CACHE is specified and [ NO ] LOGGING clause is omitted. LOGGING is automatically implemented (because you cannot have CACHE NOLOGGING ).

CACHE is not specified and [ NO ] LOGGING clause is omitted. The process defaults in the same way as it does for tables and partitioned tables. That is, the [ NO ] LOGGING value is obtained from the tablespace in which the LOB segment resides.

The following issues should also be kept in mind.

LOBs Always Generate Undo for LOB Index Pages

Regardless of whether LOGGING or NOLOGGING is set, LOBs never generate rollback information (undo) for LOB data pages because old LOB data is stored in versions. Rollback information that is created for LOBs tends to be small because it is only for the LOB index page changes.

When LOGGING is Set Oracle Generates Full Redo for LOB Data Pages

NOLOGGING is intended to be used when a customer does not care about media recovery. Thus, if the disk/tape/storage media fails, then you cannot recover your changes from the log because the changes were never logged.

NOLOGGING is Useful for Bulk Loads or Inserts.

LOGGING/FILESYSTEM_LIKE_LOGGING for SecureFiles LOBs

Using the CACHE option results in improved performance when reading and writing data from the LOB column. However, it can potentially age other non-LOB pages out of the buffer cache prematurely.

CACHE Implies LOGGING

For SecureFiles, there is a further alternative depending on how CACHE is specified:

CACHE is specified and the LOGGING clause is omitted, then LOGGING is used.

The following issues should also be kept in mind.

SecureFiles and an Efficient Method of Generating REDO and UNDO

This means that Oracle Database determines if it is more efficient to generate REDO and UNDO for the change to a block, similar to heap blocks, or if it generates a version and full REDO of the new block similar to BasicFiles LOBs.

FILESYSTEM_LIKE_LOGGING is Useful for Bulk Loads or Inserts

CHUNK

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 default CHUNK size is equal to the size of one tablespace block, and the maximum value is 32K.

«Terabyte-Size LOB Support» for information about maximum LOB sizes

Choosing the Value of CHUNK

Once the value of CHUNK is chosen (when the LOB column is created), it cannot be changed. Hence, it is important that you choose a value which optimizes your storage and performance requirements. For SecureFiles, CHUNK is an advisory size and is provided for backward compatibility purposes.

Space Considerations

The value of CHUNK does not matter for LOBs that are stored inline. This happens when ENABLE STORAGE IN ROW is set, and the size of the LOB locator and the LOB data is less than approximately 4000 bytes. However, when the LOB data is stored out-of-line, it always takes up space in multiples of the CHUNK parameter. This can lead to a large waste of space if your data is small, but the CHUNK is set to a large number. Table 11-4, «Data Size and CHUNK Size» illustrates this point:

Table 11-4 Data Size and CHUNK Size

3500 enable storage in row

3500 disable storage in row

3500 disable storage in row

Performance Considerations

Set INITIAL and NEXT to Larger than CHUNK

If you explicitly specify storage characteristics for the LOB, then make sure that INITIAL and NEXT for the LOB data segment storage are set to a size that is larger than the CHUNK size. For example, if the database block size is 2KB and you specify a CHUNK of 8KB, then make sure that INITIAL and NEXT are bigger than 8KB and preferably considerably bigger (for example, at least 16KB).

ENABLE or DISABLE STORAGE IN ROW Clause

You use the ENABLE | DISABLE STORAGE IN ROW clause to indicate whether the LOB should be stored inline (in the row) or out-of-line.

Guidelines for ENABLE or DISABLE STORAGE IN ROW

The maximum amount of LOB data stored in the row is the maximum VARCHAR2 size (4000). This includes the control information and the LOB value. If you indicate that the LOB should be stored in the row, once the LOB value and control information is larger than approximately 4000, then the LOB value is automatically moved out of the row.

This suggests the following guidelines:

The default, ENABLE STORAGE IN ROW, is usually the best choice for the following reasons:

Small LOBs : If the LOB is small (less than approximately 4000 bytes), then the whole LOB can be read while reading the row without extra disk I/O.

Large LOBs : If the LOB is big (greater than approximately 4000 bytes), then the control information is still stored in the row if ENABLE STORAGE IN ROW is set, even after moving the LOB data out of the row. This control information could enable us to read the out-of-line LOB data faster.

However, in some cases DISABLE STORAGE IN ROW is a better choice. This is because storing the LOB in the row increases the size of the row. This impacts performance if you are doing a lot of base table processing, such as full table scans, multi-row accesses (range scans), or many UPDATE/SELECT to columns other than the LOB columns.

Indexing LOB Columns

This section discusses different techniques you can use to index LOB columns.

After you move a LOB column any existing table indexes must be rebuilt.

Using Domain Indexing on LOB Columns

You might be able to improve the performance of queries by building indexes specifically attuned to your domain. Extensibility interfaces provided with the database allow for domain indexing, a framework for implementing such domain specific indexes.

You cannot build a B-tree or bitmap index on a LOB column.

Oracle Database Data Cartridge Developer’s Guide for information on building domain specific indexes.

Indexing LOB Columns Using a Text Index

Depending on the nature of the contents of the LOB column, one of the Oracle Text options could also be used for building indexes. For example, if a text document is stored in a CLOB column, then you can build a text index to speed up the performance of text-based queries over the CLOB column.

Oracle Text Reference for more information regarding Oracle Text options.

Function-Based Indexes on LOBs

A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.

Function-based indexes cannot be built on nested tables or LOB columns. However, you can build function-based indexes on VARRAYs.

Like extensible indexes and domain indexes on LOB columns, function-based indexes are also automatically updated when a DML operation is performed on the LOB column. Function-based indexes are also updated when any extensible index is updated.

Extensible Indexing on LOB Columns

The cartridge is responsible for defining the index structure, maintaining the index content during load and update operations, and searching the index during query processing. The index structure can be stored in Oracle as heap-organized, or an index-organized table, or externally as an operating system file.

When the database system handles the physical storage of domain indexes, data cartridges

Define the format and content of an index. This enables cartridges to define an index structure that can accommodate a complex data object.

Build, delete, and update a domain index. The cartridge handles building and maintaining the index structures. Note that this is a significant departure from the medicine indexing features provided for simple SQL data types. Also, because an index is modeled as a collection of tuples, in-place updating is directly supported.

Access and interpret the content of an index. This capability enables the data cartridge to become an integral component of query processing. That is, the content-related clauses for database queries are handled by the data cartridge.

By supporting extensible indexes, the database significantly reduces the effort needed to develop high-performance solutions that access complex data types such as LOBs.

Extensible Optimizer

The extensible optimizer functionality allows authors of user-defined functions and indexes to create statistics collections, selectivity, and cost functions. This information is used by the optimizer in choosing a query plan. The cost-based optimizer is thus extended to use the user-supplied information.

Oracle Text Indexing Support for XML

You can create Oracle Text indexes on CLOB columns and perform queries on XML data.

Источник

• ← серьги ваше золото с бриллиантами в подарок
• rnb что за валюта →