LOB STORAGE Clause-Datatypes
And lastly, the CREATE TABLE statement returned from DBMS_METADATA previously included the following for SecureFiles:
LOB (“TXT”) STORE AS SECUREFILE (…STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
And here is the corresponding output for BasicFiles:
LOB (“TXT”) STORE AS BASICFILE ( …STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
Both SecureFiles and BasicFiles have a full storage clause you can employ to control the physical storage characteristics. It should be noted that this storage clause applies to the LOBSEGMENT and the LOBINDEX equally—a setting for one is used for the other.
The management of the storage with SecureFiles is less complicated than that of a BasicFiles. Recall that a SecureFiles LOB must be created within an ASSM-managed tablespace, and therefore the following attributes no longer apply: FREELISTS, FREELIST GROUPS, and FREEPOOLS.
For a BasicFiles LOB, the relevant settings for a LOB would be the FREELISTS and FREELIST GROUPS (when not using ASSM, as discussed in Chapter 10). The same rules apply to the LOBINDEX segment, as the LOBINDEX is managed the same as any other index segment. If you have highly concurrent modifications of LOBs, multiple FREELISTS on the index segment might be recommended.
As mentioned in the previous section, using the keep or recycle pools for LOB segments can be a useful technique to allow you to cache LOB data, without damaging your existing default buffer cache. Rather than having the LOBs age out block buffers from normal tables, you can set aside a dedicated piece of memory in the SGA just for these objects. The BUFFER_POOL clause could be used to achieve that.
BFILEs
The last of the LOB types to talk about is the BFILE type. A BFILE type is simply a pointer to a file in the operating system. It is used to provide read-only access to these operating system files.
Note The built-in package UTL_FILE provides read and write access to operating system files, too. It does not use the BFILE type, however.
When you use BFILEs, you will also be using an Oracle DIRECTORY object. The DIRECTORY object simply maps an operating system directory to a string or a name in the database (providing for portability; you refer to a string in your BFILEs, not an operating system– specific file naming convention). So, as a quick example, let’s create a table with a BFILE column, create a DIRECTORY object, and insert a row referencing a file in the file system:
$ sqlplus eoda/foo@PDB1
SQL> create table t( id int primary key,os_file bfile);Table created.
SQL> create or replace directory my_dir as ‘/tmp/’; Directory created.
SQL> insert into t values ( 1, bfilename( ‘MY_DIR’, ‘test.dmp’ ) ); 1 row created.
For this example, I’ll create a test.dmp file in the /tmp directory using the UNIX/ Linux dd command:
dd if=/dev/zero of=/tmp/test.dmp bs=1056768 count=1
We can see the file pointed to is 1MB in size. Note that the use of MY_DIR in the INSERT statement was intentional. If we use mixed case or lowercase, we would get the following:
SQL> update t set os_file = bfilename( ‘my_dir’, ‘test.dmp’ ); 1 row updated.
SQL> select dbms_lob.getlength(os_file) from t; select dbms_lob.getlength(os_file) from t
This example points out that DIRECTORY objects in Oracle are identifiers, and identifiers are stored in uppercase by default. The BFILENAME built-in function accepts a string, and this string’s case must match the case of the DIRECTORY object exactly as stored in the data dictionary. So, we must either use uppercase in the BFILENAME function or use quoted identifiers when creating the DIRECTORY object:
SQL> create or replace directory “my_dir” as ‘/tmp/’;Directory created.
SQL> select dbms_lob.getlength(os_file) from t;DBMS_LOB.GETLENGTH(OS_FILE)
I recommend against using quoted identifiers; rather, use the uppercase name in the BFILENAME call. Quoted identifiers are not usual and tend to create confusion downstream.
A BFILE (the pointer object in the database, not the actual binary file on disk) consumes a varying amount of space on disk, depending on the length of the DIRECTORY object name and the file name. In the preceding example, the resulting BFILE was about 35 bytes in length. In general, you’ll find the BFILE consumes approximately 20 bytes of overhead plus the length of the DIRECTORY object name plus the length of the file name itself.
Note BFILE data is not read consistent as other LOB data is. Since the BFILE is managed outside of the database, whatever happens to be in the file when you dereference the BFILE is what you will get. So, repeated reads from the same BFILE may produce different results—unlike a LOB locator used against a CLOB, BLOB, or NCLOB.