DBA Actions Required Prior to Working with LOBs

Set Maximum Number of Open BFILEs

A limited number of BFILEs can be open simultaneously per session. The initialization parameter, SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session.

The default value for this parameter is 10. That is, you can open a maximum of 10 files at the same time per session if the default value is utilized. If you want to alter this limit, the database administrator can change the value of this parameter in the init.ora file. For example:

SESSION_MAX_OPEN_FILES=20

If the number of unclosed files reaches the SESSION_MAX_OPEN_FILES value then you will not be able to open any more files in the session. To close all open files, use the FILECLOSEALL call.

Using SQL DML for Basic Operations on LOBs

SQL Data Manipulation Language (DML) includes basic operations, such as, INSERT, UPDATE, DELETE -- that let you make changes to the entire value of internal LOBs within Oracle RDBMS.

• Internal LOBs: To work with parts of internal LOBs, you will need to use one of the interfaces described in Chapter 3, "LOB Programmatic Environments", that have been developed to handle more complex requirements. For use case examples refer to the following sections in Chapter 9, "Internal Persistent LOBs":
  • INSERT:
    • INSERT a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()

    • INSERT a Row by Selecting a LOB From Another Table

    • INSERT Row by Initializing a LOB Locator Bind Variable

  • UPDATE:
    • UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()

    • UPDATE a Row by Selecting a LOB From Another Table

    • UPDATE by Initializing a LOB Locator Bind Variable

  • DELETE:
    • DELETE the Row of a Table Containing a LOB

• External LOBs (BFILEs): Oracle8i supports read-only operations on external LOBs. See Chapter 11, "External LOBs (BFILEs)":
  • INSERT:
    • INSERT a Row Using BFILENAME()

    • INSERT a BFILE Row by Selecting a BFILE From Another Table

    • INSERT Row With BFILE by Initializing BFILE Locator

• UPDATE: You can use the following methods to UPDATE or 'write to' a BFILE:
  • UPDATE a BFILE Using BFILENAME()

  • UPDATE a BFILE by Selecting a BFILE From Another Table

  • UPDATE a BFILE by Initializing a BFILE Locator

• DELETE:
  • DELETE the Row of a Table Containing a BFILE

Changing Tablespace Storage for a LOB

It is possible to change the default storage for a LOB after the table has been created.

Oracle8 Release 8.0.4.3

To move the CLOB column from tablespace A to tablespace B, in Oracle8 release 8.0.4.3, requires the following statement:

ALTER TABLE test lob(test) STORE AS (tablespace tools);

However, this returns the following error message:

ORA-02210: no options specified for ALTER TABLE

Oracle8i

• Using ALTER TABLE... MODIFY: You can change LOB tablespace storage as follows:

  Note: The ALTER TABLE syntax for modifying an existing LOB column uses the MODIFY LOB clause not the LOB .. STORE AS clause. The LOB...STORE AS clause is only for newly added LOB columns.

  ALTER TABLE test MODIFY 
    LOB (lob1)
      STORAGE  (
      NEXT         4M
      MAXEXTENTS   100
      PCTINCREASE  50
  

      )
  
  

• Using ALTER TABLE ... MOVE: In Oracle8i, you can also use the MOVE clause of the ALTER TABLE statement to change LOB tablespace storage. For example:

  ALTER TABLE test MOVE
      TABLESPACE tbs1
      LOB (lob1, lob2)
      STORE AS (
            TABLESPACE tbs2
            DISABLE STORAGE IN ROW);