Using LOBs

LOBs (Large OBjects) are a particular data type, used to store large binary or character objects inside the database or outside the database when using BFILEs. In this recipe, we will see how to use LOB fields to avoid performance degradation and space wasting.

Getting ready

The following steps have to be performed initially;

1. Connect to the database as SYSDBA:

   CONNECT /@TESTDB AS SYSDBA
   

2. Grant the following permission to user SH:

   GRANT CREATE ANY DIRECTORY TO SH;
   

3. Create a tablespace for LOBs:

   CREATE TABLESPACE ASSM_TS DATAFILE 'ASSM_TS.DBF' SIZE 100M
   EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
   

How to do it...

In this recipe, we will see how to use LOB felds to avoid performance degradation and space wasting. Follow these steps:

1. Connect to the database as user SH:

   CONNECT s.5h@TESTDB/sh
   

2. Create a table to do some tests copying the CUSTOMERS table and its contents:

   CREATE TABLE MyCustomers AS SELECT * FROM Customers;
   

3. Add a BLOB field to the table:

   ALTER TABLE MyCustomers ADD...