Что такое lob oracle
DBMS_LOB can read and modify BLOBs , CLOBs , and NCLOBs ; it provides read-only operations for BFILEs . The bulk of the LOB operations are provided by this package.
93.2 DBMS_LOB Deprecated Subprograms
The DBMS_LOB.LOADFROMFILE program is deprecated from the DBMS_LOB package in Oracle Database 12 c release 12.2
Oracle recommends that do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.
Use DBMS_LOB.LoadClobFromFile or DBMS_LOB.LoadBlobFromFile instead.
93.3 DBMS_LOB Security Model
This package must be created under SYS . Operations provided by this package are performed under the current calling user, not under the package owner SYS .
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
You can provide secure access to BFILEs using the DIRECTORY feature discussed in BFILENAME function in the Oracle Database SecureFiles and Large Objects Developer's Guide and the Oracle Database SQL Language Reference .
For information about the security model pertaining to temporary LOBs, see Operational Notes.
93.4 DBMS_LOB Constants
This topic describes the constants used by the DBMS_LOB package
These are shown in following tables:
Table 93-1 DBMS_LOB Constants - Basic
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
Table 93-2 DBMS_LOB Constants - Option Types
Set/Get the SECUREFILE compress option value
Set/Get the SECUREFILE Deduplicate option value
Get the SECUREFILE encrypt option value
Table 93-3 DBMS_LOB Constants - Option Values
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
Table 93-4 DBMS_LOB Constants - DBFS State Value Types
LOB has never been archived
LOB was archived, but as been read back in to the RDBMS
LOB is currently archived
Table 93-5 DBMS_LOB Constants - DBFS Cache Flags
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.
Table 93-6 DBMS_LOB Constants - Miscellaneous
Maximum number of bytes allowed in the content type string
The maximum length of DBFS pathnames
93.5 DBMS_LOB Datatypes
The table in this topic describes the datatypes used by DBMS_LOB.
Table 93-7 Datatypes Used by DBMS_LOB
Source or destination binary 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 ).
Specifies the size of a buffer or LOB, the offset into a LOB, or the amount to access.
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.
93.6 DBMS_LOB 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.
Starting from 12.2 release, you can select a persistent LOB locator from a remote table into a local variable. The remote column can be of type BLOB, CLOB, or NCLOB. You cannot select BFILE from a remote table. The LOB variable that refers to the LOB value in a remote table is called a remote locator.
All the DBMS_LOB APIs other than the ones that are meant for BFILEs will now accept and support operations on remote LOB locators. All the APIs that take in two locators must have both LOBs collocated at one database.
To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain LOB columns.
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.
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.
Pass the DIRECTORY object and the filename of the external LOB you are adding to the BFILENAME function to create a LOB locator for your external LOB.
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.
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.
For temporary LOBs, you must use the OCI, PL/SQL, or another programmatic interface to create or manipulate them. Temporary LOBs can be either BLOBs , CLOBs , or NCLOBs .
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 also an interface to let you group temporary LOBs together into a logical bucket. The duration represents this logical store for temporary LOBs. Each temporary LOB can have separate storage characteristics, such as CACHE / NOCACHE . There is a default store for every session into which temporary LOBs are placed if you don't specify a specific duration. Additionally, you are able to perform a free operation on durations, which causes all contents in a duration to be freed.
There is no support for consistent read (CR), undo, backup, parallel processing, or transaction management for temporary LOBs. Because CR and roll backs are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.
Because CR, undo, and versions are not generated for temporary LOBs, there is potentially a performance impact if you assign multiple locators to the same temporary LOB. Semantically, each locator should have its own copy of the temporary LOB.
A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB. Other locators no longer see the same data as the locator through which the modification was made. A deep copy was not incurred by permanent LOBs in these types of situations, because CR snapshots and version pages enable users to see their own versions of the LOB cheaply.
You can gain pseudo- REF semantics by using pointers to locators in OCI and by having multiple pointers to locators point to the same temporary LOB locator, if necessary. In PL/SQL, you must avoid using more than one locator for each temporary LOB. The temporary LOB locator can be passed by reference to other procedures.
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 database keeps track of temporary LOBs for each session in a v$ view called V$TEMPORARY_LOBS , which contains information about how many temporary LOBs exist for each session. V$ views are for DBA use. From the session, the database can determine which user owns the temporary LOBs. By using V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS , a DBA can see how much space is being used by a session for temporary LOBs. These tables can be used by DBAs to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.
The following notes are specific to temporary LOBs:
All functions in DBMS_LOB return NULL if any of the input parameters are NULL . All procedures in DBMS_LOB raise an exception if the LOB locator is input as NULL .
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.
DBMS_LOB can read and modify BLOBs , CLOBs , and NCLOBs ; it provides read-only operations for BFILEs . The bulk of the LOB operations are provided by this package.
105.2 DBMS_LOB Security Model
This package must be created under SYS . Operations provided by this package are performed under the current calling user, not under the package owner SYS .
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
You can provide secure access to BFILEs using the DIRECTORY feature discussed in BFILENAME function in the Oracle Database SecureFiles and Large Objects Developer's Guide and the Oracle Database SQL Language Reference .
For information about the security model pertaining to temporary LOBs, see Operational Notes.
105.3 DBMS_LOB Constants
This topic describes the constants used by the DBMS_LOB package
These are shown in following tables:
Table 105-1 DBMS_LOB Constants - Basic
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
Table 105-2 DBMS_LOB Constants - Option Types
Set/Get the SECUREFILE compress option value
Set/Get the SECUREFILE Deduplicate option value
Get the SECUREFILE encrypt option value
Table 105-3 DBMS_LOB Constants - Option Values
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
Table 105-4 DBMS_LOB Constants - DBFS State Value Types
LOB has never been archived
LOB was archived, but as been read back in to the RDBMS
LOB is currently archived
Table 105-5 DBMS_LOB Constants - DBFS Cache Flags
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.
Table 105-6 DBMS_LOB Constants - Miscellaneous
Maximum number of bytes allowed in the content type string
The maximum length of DBFS pathnames
105.4 DBMS_LOB Datatypes
The table in this topic describes the datatypes used by DBMS_LOB.
Table 105-7 Datatypes Used by DBMS_LOB
Source or destination binary 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 ).
Specifies the size of a buffer or LOB, the offset into a LOB, or the amount to access.
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.
105.5 DBMS_LOB 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.
Starting from 12.2 release, you can select a persistent LOB locator from a remote table into a local variable. The remote column can be of type BLOB, CLOB, or NCLOB. You cannot select BFILE from a remote table. The LOB variable that refers to the LOB value in a remote table is called a remote locator.
All the DBMS_LOB APIs other than the ones that are meant for BFILEs will now accept and support operations on remote LOB locators. All the APIs that take in two locators must have both LOBs collocated at one database.
To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain LOB columns.
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.
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.
Pass the DIRECTORY object and the filename of the external LOB you are adding to the BFILENAME function to create a LOB locator for your external LOB.
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.
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.
For temporary LOBs, you must use the OCI, PL/SQL, or another programmatic interface to create or manipulate them. Temporary LOBs can be either BLOBs , CLOBs , or NCLOBs .
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 also an interface to let you group temporary LOBs together into a logical bucket. The duration represents this logical store for temporary LOBs. Each temporary LOB can have separate storage characteristics, such as CACHE / NOCACHE . There is a default store for every session into which temporary LOBs are placed if you don't specify a specific duration. Additionally, you are able to perform a free operation on durations, which causes all contents in a duration to be freed.
There is no support for consistent read (CR), undo, backup, parallel processing, or transaction management for temporary LOBs. Because CR and roll backs are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.
Because CR, undo, and versions are not generated for temporary LOBs, there is potentially a performance impact if you assign multiple locators to the same temporary LOB. Semantically, each locator should have its own copy of the temporary LOB.
A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB. Other locators no longer see the same data as the locator through which the modification was made. A deep copy was not incurred by permanent LOBs in these types of situations, because CR snapshots and version pages enable users to see their own versions of the LOB cheaply.
You can gain pseudo- REF semantics by using pointers to locators in OCI and by having multiple pointers to locators point to the same temporary LOB locator, if necessary. In PL/SQL, you must avoid using more than one locator for each temporary LOB. The temporary LOB locator can be passed by reference to other procedures.
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 database keeps track of temporary LOBs for each session in a v$ view called V$TEMPORARY_LOBS , which contains information about how many temporary LOBs exist for each session. V$ views are for DBA use. From the session, the database can determine which user owns the temporary LOBs. By using V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS , a DBA can see how much space is being used by a session for temporary LOBs. These tables can be used by DBAs to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.
The following notes are specific to temporary LOBs:
All functions in DBMS_LOB return NULL if any of the input parameters are NULL . All procedures in DBMS_LOB raise an exception if the LOB locator is input as NULL .
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.
DBMS_LOB can read and modify BLOBs , CLOBs , and NCLOBs ; it provides read-only operations for BFILEs . The bulk of the LOB operations are provided by this package.
99.2 DBMS_LOB Deprecated Subprograms
The DBMS_LOB.LOADFROMFILE program is deprecated from the DBMS_LOB package in Oracle Database 12 c release 12.2
Oracle recommends that do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.
Use DBMS_LOB.LoadClobFromFile or DBMS_LOB.LoadBlobFromFile instead.
99.3 DBMS_LOB Security Model
This package must be created under SYS . Operations provided by this package are performed under the current calling user, not under the package owner SYS .
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
You can provide secure access to BFILEs using the DIRECTORY feature discussed in BFILENAME function in the Oracle Database SecureFiles and Large Objects Developer's Guide and the Oracle Database SQL Language Reference .
For information about the security model pertaining to temporary LOBs, see Operational Notes.
99.4 DBMS_LOB Constants
This topic describes the constants used by the DBMS_LOB package
These are shown in following tables:
Table 99-1 DBMS_LOB Constants - Basic
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
Table 99-2 DBMS_LOB Constants - Option Types
Set/Get the SECUREFILE compress option value
Set/Get the SECUREFILE Deduplicate option value
Get the SECUREFILE encrypt option value
Table 99-3 DBMS_LOB Constants - Option Values
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
Table 99-4 DBMS_LOB Constants - DBFS State Value Types
LOB has never been archived
LOB was archived, but as been read back in to the RDBMS
LOB is currently archived
Table 99-5 DBMS_LOB Constants - DBFS Cache Flags
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.
Table 99-6 DBMS_LOB Constants - Miscellaneous
Maximum number of bytes allowed in the content type string
The maximum length of DBFS pathnames
99.5 DBMS_LOB Datatypes
The table in this topic describes the datatypes used by DBMS_LOB.
Table 99-7 Datatypes Used by DBMS_LOB
Source or destination binary 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 ).
Specifies the size of a buffer or LOB, the offset into a LOB, or the amount to access.
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.
99.6 DBMS_LOB 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.
Starting from 12.2 release, you can select a persistent LOB locator from a remote table into a local variable. The remote column can be of type BLOB, CLOB, or NCLOB. You cannot select BFILE from a remote table. The LOB variable that refers to the LOB value in a remote table is called a remote locator.
All the DBMS_LOB APIs other than the ones that are meant for BFILEs will now accept and support operations on remote LOB locators. All the APIs that take in two locators must have both LOBs collocated at one database.
To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain LOB columns.
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.
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.
Pass the DIRECTORY object and the filename of the external LOB you are adding to the BFILENAME function to create a LOB locator for your external LOB.
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.
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.
For temporary LOBs, you must use the OCI, PL/SQL, or another programmatic interface to create or manipulate them. Temporary LOBs can be either BLOBs , CLOBs , or NCLOBs .
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 also an interface to let you group temporary LOBs together into a logical bucket. The duration represents this logical store for temporary LOBs. Each temporary LOB can have separate storage characteristics, such as CACHE / NOCACHE . There is a default store for every session into which temporary LOBs are placed if you don't specify a specific duration. Additionally, you are able to perform a free operation on durations, which causes all contents in a duration to be freed.
There is no support for consistent read (CR), undo, backup, parallel processing, or transaction management for temporary LOBs. Because CR and roll backs are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.
Because CR, undo, and versions are not generated for temporary LOBs, there is potentially a performance impact if you assign multiple locators to the same temporary LOB. Semantically, each locator should have its own copy of the temporary LOB.
A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB. Other locators no longer see the same data as the locator through which the modification was made. A deep copy was not incurred by permanent LOBs in these types of situations, because CR snapshots and version pages enable users to see their own versions of the LOB cheaply.
You can gain pseudo- REF semantics by using pointers to locators in OCI and by having multiple pointers to locators point to the same temporary LOB locator, if necessary. In PL/SQL, you must avoid using more than one locator for each temporary LOB. The temporary LOB locator can be passed by reference to other procedures.
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 database keeps track of temporary LOBs for each session in a v$ view called V$TEMPORARY_LOBS , which contains information about how many temporary LOBs exist for each session. V$ views are for DBA use. From the session, the database can determine which user owns the temporary LOBs. By using V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS , a DBA can see how much space is being used by a session for temporary LOBs. These tables can be used by DBAs to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.
The following notes are specific to temporary LOBs:
All functions in DBMS_LOB return NULL if any of the input parameters are NULL . All procedures in DBMS_LOB raise an exception if the LOB locator is input as NULL .
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.
DBMS_LOB can read and modify BLOBs , CLOBs , and NCLOBs ; it provides read-only operations for BFILEs . The bulk of the LOB operations are provided by this package.
93.2 DBMS_LOB Deprecated Subprograms
The DBMS_LOB.LOADFROMFILE program is deprecated from the DBMS_LOB package in Oracle Database 12 c release 12.2
Oracle recommends that do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.
Use DBMS_LOB.LoadClobFromFile or DBMS_LOB.LoadBlobFromFile instead.
93.3 DBMS_LOB Security Model
This package must be created under SYS . Operations provided by this package are performed under the current calling user, not under the package owner SYS .
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
You can provide secure access to BFILEs using the DIRECTORY feature discussed in BFILENAME function in the Oracle Database SecureFiles and Large Objects Developer's Guide and the Oracle Database SQL Language Reference .
For information about the security model pertaining to temporary LOBs, see Operational Notes.
93.4 DBMS_LOB Constants
This topic describes the constants used by the DBMS_LOB package
These are shown in following tables:
Table 93-1 DBMS_LOB Constants - Basic
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
Table 93-2 DBMS_LOB Constants - Option Types
Set/Get the SECUREFILE compress option value
Set/Get the SECUREFILE Deduplicate option value
Get the SECUREFILE encrypt option value
Table 93-3 DBMS_LOB Constants - Option Values
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
Table 93-4 DBMS_LOB Constants - DBFS State Value Types
LOB has never been archived
LOB was archived, but as been read back in to the RDBMS
LOB is currently archived
Table 93-5 DBMS_LOB Constants - DBFS Cache Flags
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.
Table 93-6 DBMS_LOB Constants - Miscellaneous
Maximum number of bytes allowed in the content type string
The maximum length of DBFS pathnames
93.5 DBMS_LOB Datatypes
The table in this topic describes the datatypes used by DBMS_LOB.
Table 93-7 Datatypes Used by DBMS_LOB
Source or destination binary 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 ).
Specifies the size of a buffer or LOB, the offset into a LOB, or the amount to access.
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.
93.6 DBMS_LOB 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.
Starting from 12.2 release, you can select a persistent LOB locator from a remote table into a local variable. The remote column can be of type BLOB, CLOB, or NCLOB. You cannot select BFILE from a remote table. The LOB variable that refers to the LOB value in a remote table is called a remote locator.
All the DBMS_LOB APIs other than the ones that are meant for BFILEs will now accept and support operations on remote LOB locators. All the APIs that take in two locators must have both LOBs collocated at one database.
To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain LOB columns.
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.
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.
Pass the DIRECTORY object and the filename of the external LOB you are adding to the BFILENAME function to create a LOB locator for your external LOB.
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.
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.
For temporary LOBs, you must use the OCI, PL/SQL, or another programmatic interface to create or manipulate them. Temporary LOBs can be either BLOBs , CLOBs , or NCLOBs .
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 also an interface to let you group temporary LOBs together into a logical bucket. The duration represents this logical store for temporary LOBs. Each temporary LOB can have separate storage characteristics, such as CACHE / NOCACHE . There is a default store for every session into which temporary LOBs are placed if you don't specify a specific duration. Additionally, you are able to perform a free operation on durations, which causes all contents in a duration to be freed.
There is no support for consistent read (CR), undo, backup, parallel processing, or transaction management for temporary LOBs. Because CR and roll backs are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.
Because CR, undo, and versions are not generated for temporary LOBs, there is potentially a performance impact if you assign multiple locators to the same temporary LOB. Semantically, each locator should have its own copy of the temporary LOB.
A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB. Other locators no longer see the same data as the locator through which the modification was made. A deep copy was not incurred by permanent LOBs in these types of situations, because CR snapshots and version pages enable users to see their own versions of the LOB cheaply.
You can gain pseudo- REF semantics by using pointers to locators in OCI and by having multiple pointers to locators point to the same temporary LOB locator, if necessary. In PL/SQL, you must avoid using more than one locator for each temporary LOB. The temporary LOB locator can be passed by reference to other procedures.
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 database keeps track of temporary LOBs for each session in a v$ view called V$TEMPORARY_LOBS , which contains information about how many temporary LOBs exist for each session. V$ views are for DBA use. From the session, the database can determine which user owns the temporary LOBs. By using V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS , a DBA can see how much space is being used by a session for temporary LOBs. These tables can be used by DBAs to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.
The following notes are specific to temporary LOBs:
All functions in DBMS_LOB return NULL if any of the input parameters are NULL . All procedures in DBMS_LOB raise an exception if the LOB locator is input as NULL .
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.
Large Objects (LOB) это тип данных используемый для хранения больших объектов — различные форматы текстов, изображения, видео, звуковые файлы. Использование LOB для хранения данных позволяет эффективно манипулировать данными в приложении.
Компоненты LOB
LOB состоит из локатора и значения.
Локатор – это внутренний указатель на фактическое значение большого объекта.
Значение – это реальное содержимое объекта.
LOB хранит локатор в таблице и данные в различных сегментах, за исключением случая, когда размер данных меньше 4000 байт.
Максимальный размер LOB составляет ( 4 GB (4 294 967 295 байт) — 1 ) * (значение CHUNK parameter of LOB storage); размер может достигать до 128 терабайт.
Типы данных LOB
Виды LOB
Как внутренние, так и внешние большие объекты могут быть использованы как столбцы таблицы, переменные в pl/sql, атрибуты объектов.
Internal LOB
Внутренние большие объекты — хранятся в табличных пространствах БД, поддерживаются следующие типы данных: BLOB, CLOB, and NCLOB.
Persistent and Temporary LOB
Внутренние большие объекты могут быть временными или постоянными.
Постоянные LOB существуют в строках таблицы БД.
Временные LOB создается для использования только в пределах локального приложения.
Временный экземпляр становится постоянным если его вставить в строку таблицы.
Свойства Temporary LOB
- Данные хранятся не в таблице, а во временном табличном пространстве
- Временные LOB быстрее чем постояннные, так как не генерируется redo и rollback данные
- Используется только в сесии. По окончанию сесии удаляется
- Для создания временного LOB используется DBMS_LOB.CREATETEMPORARY
External LOB
Внешние большие объекты — вид данных, который хранится в файлах операционной системы, вне табличных пространств БД. Используется тип данных – BFILE. BFILE может быть только внешним.
Использование пакета DBMS_LOB
DBMS_LOB предоставляет методы манипулирования внутренними и внешними LOBами.
Методы DBMS_LOB можно условно разделить на два типа mutators и observers:
- mutators — могут изменять LOB : APPEND, COPY, ERASE, TRIM, WRITE, FILECLOSE, FILECLOSEALL, and FILEOPEN.
- observers — могу читать LOB: COMPARE, FILEGETNAME, INSTR, GETLENGTH, READ, SUBSTR, FILEEXISTS, and FILEISOPEN.
Для инициализации LOB локаторов используются следующие функции:
- EMPTY_CLOB() — функция возвращает LOB локатор для CLOB колонки
- EMPTY_BLOB() — функция возвращает LOB локатор для BLOB колонки
CLOB и BLOB колонки, так же могут быть инициализированы с помощью символьной или raw строки, если длина не превыщает 4000 байт.
Читайте также: